SQL Server R2 and HyperThreading - Max Worker Threads and performance

Question

SQL Im getting this error:

This file is generated by Microsoft SQL Server                                                                   
version 10.50.2500.0                                                                                             
upon detection of fatal unexpected error. Please return this file,                                               
the query or program that produced the bugcheck, the database and                                                
the error log, and any other pertinent information with a Service Request.                                       


Computer type is Intel(R) Xeon(R) CPU           X5650  @ 2.67GHz.                                                
Bios Version is HP     - 2                                                                                       
24 X64 level 8664, 2 Mhz processor (s).                                                                          
Windows NT 6.1 Build 7601 CSD Service Pack 1.                                                                    

Memory                               
MemoryLoad = 98%                     
Total Physical = 24565 MB            
Available Physical = 386 MB          
Total Page File = 49129 MB           
Available Page File = 23110 MB       
Total Virtual = 8388607 MB           
Available Virtual = 8353094 MB       
2012-03-05 09:50:27.67 Server      ***Unable to get thread context for spid 0
2012-03-05 09:50:27.67 Server      *    

2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * BEGIN STACK DUMP:
2012-03-05 09:50:27.67 Server      *   03/05/12 09:50:27 spid 2556
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      * Non-yielding Scheduler
2012-03-05 09:50:27.67 Server      *
2012-03-05 09:50:27.67 Server      *    
2012-03-05 09:50:27.68 Server      Stack Signature for the dump is 0x00000000000000FB

I read a post where SQL 2008 R2 perfomance was giving problems with proccesors that have hyperthreading. Do you think this is the main cause of my problems? Would be a nice idea to disable the hyperthreading?

Answer 1

I've seen failure to manage worker threads imperil more than one instance on various versions of SQL Server, at multiple businesses. The net result is memory pressure (which can manifest in different ways). In your case you can see clearly that a worker didn't yield, and what would be forcing it to yield is memory pressure. I haven't seen that result in a total FE like you've got, but again, memory pressure can manifest differently.

Set your workers to the appropriate level based on this MSDN article. Once you do, I would not be surprised to learn it solved your problem completely.

As for disabling hyperthreading, I don't think this is a good idea but opinions vary greatly on the topic. For sure I don't think it's related to your problem though.

Answer 2

spid 2556

You probably increased max_worker threads to allow for such high number of concurrent executing requests. What is your hardware spec (sockets/cores, RAM, HDD)?

As the message says, contact product support for further investigation. But remember: at the end of the day there is only so much water that can be poured into a bucket before it spills.

Answer 3

You sometimes disable hyperthreading for sql server machines because hyperthreading forces each physical cpu core to share the available cpu cache between the actual cpu and the "extra" cpu provided by hyperthreading. Because Sql Server's performance is often I/O bound, rather than CPU bound, it will often get better performance with hyperthreading turned off, because that means each of the remaining CPU cores have more cache available to them, which in turn means better handling and caching of I/O operations. You've optimized for your bottleneck, trading "spare" cpu performance for improved I/O handling.

Before making this change, it's a good idea to actually measure the performance of both settings under something resembling a typical workload for your system, to know for sure which option works better for you.

Of course, the ability to measure implies that your server is working in the first place. Before playing with performance optimizations, you need to fix the error here. Unfortunately I'm not 100% sure what to do to fix this error, but I can tell you two things:

1. Turning off hyperthreading is unlikely to fix the kind of error you posted.
2. The key word that jumped out at me from your error report is "bugcheck". Bugchecks almost always indicate a problem with hardware. Sometimes they indicate a problem with a device driver or corrupted system file, but even in those cases the reason for the driver or corruption issue most often originates with bad hardware.