21

Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.

Interested to hear from others who have found usage cases where performance value was delivered by trace flag 8048 (promote query memory grant strategy from per-NUMA node to per-core), trace flag 8015 (SQL Server ignores physical NUMA), or SUMA (interleaved sufficiently uniform memory access, a BIOS option on some NUMA machines).

Trace flag 8048

Trace flag 8015

Gory details of system workload, gathered metrics from troubled system, and gathered metrics from the system after intervention follow.

Trace flag 8048 was a 'fix', but was it the best fix? Would SQL Server ignoring physical NUMA due to trace flag 8015 have accomplished the same thing? What about setting the BIOS to interleave memory, leaving the server with SMP-imitating SUMA behavior instead of NUMA behavior?

About the system

  • 4 hex core Xeon E7540 @ 2.00GHz, hyperthreaded
  • 128 GB RAM
  • WS2008R2
  • MSSQL 2008 R2 SP2
  • maxdop 6

About the workload

  • 1000s of Batch scheduled/queued reports driven from 2 report application servers.
  • 3 flavors of batches: daily, weekly, monthly
  • All report application servers connections to SQL Server are made as a single service account
  • Maximum report concurrency = 90

Key findings on the troubled system

  • From Perfmon, 15 second intervals

    • System remains at 95%-100% CPU busy
    • SQL Server buffer page lookups < 10000 per/second
  • From wait and spinlock DMVs, 5 minute intervals

    • High CMEMTHREAD waiters and wait time
    • High SOS_SUSPEND_QUEUE spins and backoffs

Bob Dorr's CSS Engineer Blog post on trace flag 8048 indicates that systems with more than 8 cores per NUMA node can run into similar symptoms due to bottleneck in query memory grant. Trace flag 8048 will change the strategy to per-core instead of per-NUMA node.

The Intervention

MSSQL was restarted with -T8048 in place. The difference was immediately evident: buffer page lookup rate rose over 1 million and spike to 8 million per second. The troubled batch workload, which previously couldn't complete in 24 hours, completed in less than 4 hours. Another batch workload which was not the focus of investigation or intervention was submitted as part of validating the corrective value of trace flag 8048 (and ensuring that its unwanted side effects were minimal). This report batch previously completed in 2 hours; with trace flag 8048 in place the report batch completed in roughly 20 minutes.

Nightly ETL also encountered a benefit. ETL time dropped from roughly 60 minutes to 40 minutes.

Pulling together information from several places, I speculate that the high degree of report queuing, the concurrent report count greater than hardware thread count, and the single user account for all reports combined to put pressure on one NUMA node until worker thread pressure caused it to be disfavored for the next incoming connection request for the same user account, at which point the next NUMA node would get some number of connections near instantly. Each NUMA node would end up with a high probability of stressing the query memory grant bottleneck.

Opening more lanes for query memory grant removed the bottleneck. But, I'm not sure the cost. Bob Dorr's CSS post makes it clear that there is additional memory overhead with trace flag 8048. Is that overhead within the single-page allocator region governed by MSSQL 2008 R2 max server memory? If so, I guess the system will just have some number fewer database pages in the buffer pool cache. If not, should max server memory be lowered to accommodate?

0

1 Answer 1

13

This is an awesome post.

To answer your final question, I'd speculate that your answer is "yes".

That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading. Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.

On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.

Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds. Makes for some interesting analysis when workload changes or trace flags are implemented:

SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC'

and

SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' ) 
GROUP BY wait_type
ORDER BY COUNT (*) DESC
4
  • Thanks for mentioning the sys.dm_os_nodes and sys.dm_os_waiting_tasks. I'm writing a number of stored procedures to profile system actvity, first to pursue a somewhat optimized baseline, then to watch for variances. Right now capturing waits and spins, next comes memory grants (including dop per memory grant)... next individual waiters and nodes as you discussed... then maybe on to memory clerks and cache counters...
    – sql_handle
    Nov 16, 2012 at 3:46
  • 1
    Another interesting counter to look at is in perfmon:SQLServer:Buffer Node:. The counters in that family of interest are Foreign Pages, Free Pages, Page Life Expectancy, Total Pages, Target Pages and Stolen Pages. I'm guessing, that before you implemented the trace flag, that you had a very high quantity of foreign pages - Do you have TF 834 enabled? If so, I've found that it doesn't allocate memory to each numa node in a balanced fashion which leads to a very high quantity of expensive remote numa node memory lookups. The system I had this issue on contained 1TB of ram at the time. Nov 16, 2012 at 14:13
  • good points. I've been watching the buffer node metrics. Most curious was that initially, node 00 had no foreign pages, while the other nodes had massive numbers. I think that was due to our ETL performing the buffer ramp up with a low enough thread count to fit entirely on buffer node/NUMA node 00. We don't have trace flag 834 enabled, but will begin testing with it soon. Our workload testing on Linux Oracle 11gR2 showed great benefit to memory large pages, I think we'll see gains in Windows with SQL Server also.
    – sql_handle
    Nov 17, 2012 at 18:25
  • @Mike Soft NUMA vs TF 8048. I think that soft NUMA would allow me to create 'memory nodes' within NUMA nodes. So if I created soft NUMA for each core, there would (maybe) be 24 lanes for query memory grant requests. But maybe also 24 memory nodes? I'd be worried a little about the overhead in managing 24 memory nodes with every core making 'foreign' page references each time it crosses a soft NUMA boundary, and really foreign references when it crosses a boundary to reference a page which is both different soft NUMA and hard NUMA. I'll tinker and see if I can discern anything.
    – sql_handle
    Nov 17, 2012 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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