1

I am supporting a customer of ours who has an enterprise edition of our web application living on virtualized servers at their location. They have 3 web servers and 1 DB server. All 4 servers are virtualized with vCenter and have Windows Server 2008 R2 installed.

The DB server is running SQL Server 2008 R2 with 36 GB of RAM, and separate drives for the DB, logs, backups, etc. (7 virtual in total) on a SAS setup, though the backups occur onto a SSD drive.

The web servers are 4 GB RAM each and load balanced. We have a few processes that import data from third parties into our application for end-users, but recently one of these processes have bottle-necked on the server and caused issues. A process that should take hours takes days, and often times fails out, so I have to manually queue up the process in SQL and let it run.

After much investigation, I'm at a loss as to what the issue it. I've confirmed all of the Web Application settings are the same as our hosted environment, which houses customers much larger than this and our DB server is more robust, but the overall load onto the server is great.

Some metrics that I have included cause me to worry about the processes that are actually occurring on the server. I've included these below but I am specifically worried about the Lazy writes/sec and free list stalls/sec, as to me this is indicating that the process is overloading the server in memory and dumping the page to hard disk, which would drive down performance. Is this correct? Can anyone give me ideas on where to go from here? The customer definitely is not liking the idea of adding more resources to the DB server, so I want to be able to prove, definitively, where the issue is at.

Since this is in a virtualized environment, could it also be that the resources just happen to be shared, driving down the performance of the VM and causing this to be flushed to the drive? Any help would be greatly appreciated. My hardware experience has met its limits and I am not a DBA, so I am trying to wrap my head around what all actually goes on in the background with SQL Server.

Thanks!

-Buffer cache hit ratio           1363657/sec
-Buffer cache hit ratio base      1363687/sec
-Page lookups/sec                 28043473454/sec
-Free list stalls/sec             621/sec
-Free pages                       1438/sec
-Total pages                      3932160/sec
-Target pages                     3932160/sec
-Database pages                   3846600/sec
-Reserved pages                   0/sec
-Stolen pages                     84122/sec
-Lazy writes/sec                  77354/sec
-Readahead pages/sec              15305687/sec
-Page reads/sec                   16859120/sec
-Page writes/sec                  7751703/sec
-Checkpoint pages/sec             5408194/sec
-AWE lookup maps/sec              0/sec
-AWE stolen maps/sec              0/sec
-AWE write maps/sec               0/sec
-AWE unmap calls/sec              0/sec
-AWE unmap pages/sec              0/sec
-Page life expectancy             16434/sec
  • What is the SP level of SQL server? What are the total size of databases? Is windows server 32 bit or 64 bit ? Have you tried finding any poor running queries? – BeginnerDBA Nov 9 '17 at 20:03
  • Using SP 1, The main DB size is 312 GB, secondary DBs are 12 GB and 20 MB not including the system DBs. 64-Bit. The queries that are running on the system are the same ones that run on our production server for all our hosted customers. I've compared via winmerge and nothing is different, and the sprocs are pretty well optimized. It is a more cost intensive process than what we see with the rest of our system, but it should not degrade performance like we are seeing. – COleson Nov 9 '17 at 21:23
  • SP1 is way too old.. Not sure if your prod has sp1 as well. Patch to the latest SP which should take care of few things atleast. Seeing the DB size your RAM looks way below the average standards, but totally depend till the time you are out of RAM, one day.. – BeginnerDBA Nov 9 '17 at 22:51
  • Also, just get the above metric exported from perfmon in a graphical way to see if those numbers are constant, tweaks here and there etc. Basically understanding what's the pattern of the issue. When does it come? What is happening at that time. – BeginnerDBA Nov 9 '17 at 23:06
  • Our production server was updated to SQL Server 2016 recently, but I have a VM with the same setup but only 4 GB RAM and it performs on par with their Enterprise server. I'll see if I can get the graphics through perf mon. The two most intense processes seems to have little effect on these stats though, as they stay pretty consistent, almost as if the processes are not actually causing the issue. – COleson Nov 10 '17 at 14:06
1

This sounds like VMware might be using the balloon driver to remove memory from the SQL Server VM.

Ensure the VM is configured with locked memory and a CPU reservation.

Typically, for a SQL Server, you want reserved memory to match the amount of memory assigned to the VM. You'd want to ensure SQL Server is configured with a valid max server memory setting.

Check "Architecting SQL Server on vSphere for some great best-practices from VMware.

Once you've disabled the balloon driver, if you keep seeing a high ratio of stolen pages, check to see what memory clerks are using more than 16MB memory with this query:

SELECT MemoryClerkName = domc.name
    , MemoryClerkType = domc.type
    , Pages = SUM(domc.pages_kb / (domc.page_size_in_bytes / 1024))
    , MB = SUM(domc.pages_kb * 1024) / 1048576
FROM sys.dm_os_memory_clerks domc
WHERE domc.pages_kb > 16384 /* clerks using more than 16MB */
GROUP BY domc.type
    , domc.name
ORDER BY SUM(domc.pages_kb) DESC;

The "Default / MEMORYCLERK\SQLBUFFERPOOL" should typically be the main consumer of memory.

This query will show how much memory SQL Server has allocated, how much it wants to be able to allocate, and how much physical memory is present in the machine:

SELECT TargetMB = dosi.committed_target_kb * 1024 / 1048576
    , CommittedMB = dosi.committed_kb
    , PhysicalMB = dosi.physical_memory_kb * 1024 / 1048576
FROM sys.dm_os_sys_info dosi

This will show you what Windows is reporting for memory state:

SELECT dosm.system_memory_state_desc
FROM sys.dm_os_sys_memory dosm

If there is lots of free memory in your system, you'll see "Available physical memory is high" reported. If you see "Available physical memory is low", you'd probably benefit from adding memory to the server.

  • I think they are using the balloon driver. I looked in the registry and see the value set to 2, but the customer is the only one to have access to vCenter. If I change the value in regedit, and restart the server, could I be introducing any sort of instability? – COleson Nov 10 '17 at 18:27
  • What registry key are you talking about? – Max Vernon Nov 10 '17 at 18:36
  • According to many articles online, you can actually disable the balloon driver within the OS, such as below: kb.vmware.com/s/article/1002586 I'm worried about turning this off on a production DB, because if I crash the machine then it could affect users and I don't have vcenter access. – COleson Nov 11 '17 at 19:09
  • 1
    Doing that with the customer tonight. Thanks for the help. I'm hoping this resolves the issue, though the balloon driver shows no inflation for the server over the past week in vCenter. – COleson Nov 13 '17 at 20:14
  • 1
    Thanks Max. At this point I've at least got the big files processed for the next couple of months. I'm going to mark your answer as the correct one, as I haven't fully solved the issue here, but I think it is multi-tiered, and I need to benchmark this customers files on our production site. – COleson Nov 15 '17 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.