2

Brand new SQL Server 2016 (SP2/CU1), has high CPU utilization with no mounted databases when the box experiences memory pressure.

I have a test box for nightly builds with a brand new SQL Server 2016 SP2/CU1. There are no databases on the instance besides the 4 default (master, model, tempdb, mdsb). After restarting the instance, the CPU is idle (<1% used by sqlsrv.exe).

If something else runs on the box that uses a lot of memory the CPU usages for the sqlsrv.exe process maxes out 1 CPU. The box (a VM) has 4G of ram allocated to it. Min/Max memory for the instance is set to 1500M/1500M (although that doesn't seem to effect the problem).

Checking this:

select * from sys.dm_os_spinlock_stats where spins > 0

gives this after the instance has been up with memory pressure for < 1 minute.

spinlock_stats

Before the memory pressure went up, the SOS_SCHEDULER was in the same range of the other spin counts and all the values in that table where stable. Under memory pressure, the SOS_SCHEDULER just keep climbing very fast. When the memory pressure drops, all returns to normal.

I've seen a few other people post this same problem and the answer was always "add more memory", which is a pretty poor answer. A test box with no databases should be able to run an idle/empty SQL Server instance even if there is memory pressure.

(And the issue reproduces 100% of the time, so I can perform any diagnostics requested to help figure this out)

Our previous test box was configured with 2.5G of memory and a SQL 2008 R2 instance and it ran beautifully (even with databases).

Update:

After more research, I found this query:

select
(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Any time that memory usage got high enough to trigger the 'process_physical_memory_low' condition to true (1), the sqlservr.exe would grind CPU (but not change it's memory usage from what I could tell).

I ended up discovering this acticle: (https://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/) and setting the value of LowMemoryThreshold to 1 (to try and disable it), and changing the paging space config on the box from automatic to manual with an initial size of 4g and a max of 15g. (box has 4g of ram).

This combination of settings is allowing my test workload (some Db, some non-Db stuff) to run normally without causing SQL server to use up extra CPU. Since I don't have an infinite supply of time to try each of these settings individually or fine tune them further, it may have been just one that was the magic bullet.

I've found what seems to be a workaround for my problem, even though it still smells like a bug in SQL Server's handling of a low-physical-memory event.

Update2: @@Version:

Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64)   Jul 21 2019 21:16:47   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )

select * from dm_os_process_memory: (when the box is idle).

physical_memory_in_use_kb large_page_allocations_kb locked_page_allocations_kb total_virtual_address_space_kb virtual_address_space_reserved_kb virtual_address_space_committed_kb virtual_address_space_available_kb page_fault_count     memory_utilization_percentage available_commit_limit_kb process_physical_memory_low process_virtual_memory_low
------------------------- ------------------------- -------------------------- ------------------------------ --------------------------------- ---------------------------------- ---------------------------------- -------------------- ----------------------------- ------------------------- --------------------------- --------------------------
195168                    0                         0                          137438953344                   11495448                          413324                             137427457896                       1014886              80                            5255752                   0                           0

when the box is loaded:

physical_memory_in_use_kb large_page_allocations_kb locked_page_allocations_kb total_virtual_address_space_kb virtual_address_space_reserved_kb virtual_address_space_committed_kb virtual_address_space_available_kb page_fault_count     memory_utilization_percentage available_commit_limit_kb process_physical_memory_low process_virtual_memory_low
------------------------- ------------------------- -------------------------- ------------------------------ --------------------------------- ---------------------------------- ---------------------------------- -------------------- ----------------------------- ------------------------- --------------------------- --------------------------
64160                     0                         0                          137438953344                   11475032                          285056                             137427478312                       1024316              46                            2703300                   1                           0
  • 1
    How have you confirmed that the box is under memory pressure? – George.Palacios Sep 18 '19 at 13:35
  • By watching in process explorer. We noticed the problem initially because some of our nightly test were failing in strange ways. While watching the box we noticed the sqlsrv.exe process run hot when there was no database action going on. Eventually I managed to do a simple test of opening 15 tabs in chrome of cnn.com (or any heavy website) to use up memory. As soon as the memory load on the box climbed, sqlsrv.exe runs hot (1 whole CPU). Close all those tabs, and it idles back down to <1%. – Chris Holt Sep 18 '19 at 13:41
  • Is the power plan set to high perf ? – Kin Shah Sep 18 '19 at 13:59
  • 1
    When you say "memory pressure", do you mean there's less than "min. memory" available to SQL Server? – mustaccio Sep 18 '19 at 14:03
  • Welcome to DBA.SE and thanks for participating.Could you be a bit more specific with the part "...If something else runs on the box that uses a lot of memory the CPU usages for the sqlsrv.exe process maxes out 1 CPU...." What is that something? Is this a dedicated SQL Server box? Is the VMware host configured the same as the old VMware host containing the 2008 R2 instance? Are they attached to the same storage? Same 8 GBit SAN port speed? (Or other?). Without being a bit more specific your question might be closed as unclear what your asking. Thanks for assisting us in helping you. – John aka hot2use Sep 18 '19 at 14:52

Your Answer

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

Browse other questions tagged or ask your own question.