Performance - DBMS on each system (iSCSI) or one DBMS for multiple systems (LAN)

[markus_taxacher]

Hello,

we have four servers running Microsoft Hyper-V, which are used to host virtual servers for our SAP systems (Virtualized system landscape).
Each virtual SAP system system is a NetWeaver system running Windows 2008 R2 x64 with a Microsoft SQL Server Instance running on it. The Hyper-V hosts are connected via iSCSI (4 x 1GBit) to a SAN (Dell MD3000i). Currently each Hyper-V host is running three to four SAP systems (1-2 NW ABAP, 1-2 NW JAVA). So overall we have about 15 systems (ABAP/Java+DBMS) up and running, which are using a iSCSI connection.

Unfortunately we are also facing some bad system performance, which seams to be related to bad SQL Server Performance (most of the time long waits - as reported by t-code ST04). In detail:
- Latch Wait Time per Request (ms) exceeds 40 milliseconds
- Wait Time per Log Write (ms) exceeds 10 milliseconds

So I think(!) the bottle-neck could be the iSCSI connection (15 virtual servers share that 4x1 GBit line to the SAN). What if we use a single DMBS (with a database instance for each SAP SID) on one "big" physical server, with physical attached hard disks (so no I/O bottle-neck, because SAS and not iSCSI) and let the SAP systems then connect to this DBMS via GBit-LAN? Do you think this will be even more worse or would it be an performance improvement? Note: Most of the time we are not dealing with large data, most requests are just small updates. I am also aware that having a single DBMS host is a single point of failure, but my question is just regarding the Database Performance for a NetWeaver system. Or do you know another way to have an performance improvement?

Thanks and best regards,
Markus

[Former Member · Accepted solution]

Hi Markus,

The iSCSI connection may be the bottleneck, but I'd focus on tuning in other areas before migrating 15 DB instances to one physical server. What kind of drives are in the SAN? SATA will have noticeably slower I/O. If possible, ensure the LUNs that house the DBs are on SAS.

Are you over committing your CPU and memory on the hosts? I've seen this as a common problem when running SAP in a virtual environment. SAP recommends reserving the resources for each VM.

Take a look at the following note:

Note 1409608 - Virtualization on Windows

-Jake

[Former Member · Accepted solution]

There is a TSQL query provided by Paul Randal on his blog

http://sqlskills.com/BLOGS/PAUL/post/Survey-what-is-the-highest-wait-on-your-system.aspx

Can you run this code in your SQL Server database (using SQL Server Management Studio) and see what are the top wait statistics? Based on the result, you will be able to pinpoint which particular subsystem is causing the performance issue