SQL Server R2 and HyperThreading - Max Worker Threads and performance 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?
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.