SQL Tips by Namwar Rizvi

June 9, 2007

SQL Server and HyperThreading

Filed under: Uncategorized — namwar @ 5:57 PM
Several people have asked me about the performance impact of Hyper Threading in SQL Server. Always remember, Hyper threaded processor simulates a second processor as a virtual processor so it is not actually a dual processor system but since windows reports it as a second processor therefore, SQL Server query optimizer generates query plans for parallel processing which most of the time degrades your query because of the absence of actual second processor. Now you have two two choices for your queries running on hyper threaded server:

1. Use MAXDOP 1 query hint in the query to inform SQL Server that you do not need parallel processor based query plan.
Use Adventureworks
Select * From HumanResources.Department OPTION (MAXDOP 1)
2. Set max degree of parallelism server option to 1
Use master
sp_configure ‘max degree of parallelism’, 1;

I found following discussion quite informative about SQL Server and HyperThreading in Microsoft SQL Server news group:

“The HT technology is not a CPU. The system optimize the CPU access by
providing a better usage of the entire CPU, when you use integer
calculation, only half of the CPU is used, so the floating calculation
engine is free its where HT takes few advantages.
So you have some performance improvement when you run integer related
application and floating calculations (like SQL Server (integer) and 3DSMax
So finally, HT will not help your server, sometimes this can degrade the performance because SQL Server think there is 2 CPU and optimize the queries to spread the calculation for 2 CPUs, but in reality you have only 1 CPU so there is some concurrency problem. its recommended to NOT enable the HT technology for SQL Server.”


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: