SQL Server R2 and HyperThreading - Max Worker Threads and performance

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.

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.

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.

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.