Sql-server – Timeouts when connecting to SQL Server since installing SP1 for Windows 7

In the SQLOS a scheduler is created for each logical processor that SQL Server sees. With hyperthreading enabled this equates to double the schedulers. One of the purposes for the SQLOS is to minimize and prevent context switching from occuring which is why only one scheduler is created for each logical processor. Once the SQLOS creates the schedulers, the total number of workers is divided amongst the schedulers. The SQLOS implements a form of cooperative scheduling in that the workers yield the scheduler as it requires unavailable resources, or reaches its execution quantum allowing other workers to execute on the scheduler. This keeps context switching to a minimum since the scheduler is doing the work and they are bound one for one.

Understanding that background, hyperthreading somewhat works opposite of how SQLOS is specifically designed to function. Specifically, parallelism can be problematic with hyperthreading and can result in high CXPACKET waits since SQLOS may try to run a query at DOP 8 on what is reality a DOP 4 system. If your CPU utilization is low, you might not notice, but the higher your CPU utilization goes the more problematic it could become. I recently had a discussion on twitter regarding this, and the consensus was "It Depends" as to whether or not it would help or hurt.

If you have a lot of signal waits on your server, but you have low CPU utilization, you may see benefit enabling hyperthreading which will double your internal schedulers and spread the workers out more which means they won't wait to execute in the runnable queue as long. However if your workload utilized parallelism heavily you get heavy CXPACKET waits in sys.dm_os_wait_stats, you might look at disabling hyperthreading to see if it reduces your waits.

It would have helped it you had said which version of SQL Server you are trying to install. If you are using SQL 2000 on Windows 2003 you have to install SP2 or higher before SQL can be connected to remotly.