THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: HyperThreaded or Not?

In almost all my classes, and every time I do any performance consulting, I get the question "How can I determine if our SQL Server is hyperthreaded?"

I was delighted when I found the DMV sys.dm_sys_info, which has a column called hyperthread_ratio. But soon after, I read a blog post from one of my favorite SQL Server bloggers, Buck Woody who indicated that the following query gives you the number of cores, but doesn't specifically tell you if the hyperthreading is enabled.

SELECT cpu_count/hyperthread_ratio AS sockets
FROM sys.dm_os_sys_info

Although he said this gives you the number of cores, that is not correct either, because one socket can contain a multi-core CPU. A computer with a single socket containing a dual-core CPU and no hyperthreading will have a cpu_count value of 2, and a hyperthread_ratio of 2, and a computer with a single socket single core CPU with hyperthreading will also have the same hyperthread_ratio. In both cases, dividing hyperthread_ratio by cpu_count will give 1, the number of sockets. If we have a single socket machine with a dual-core CPU and hyperthreading, cpu_count will be 4 and hyperthread_ratio will also be four, so the ratio again will be one. So the values for single socket possibilities are summarized here:

 

Number of Sockets

Number of Cores

Hyperthreaded? sys.dm_os_info.
cpu_count

sys.dm_os_info.
hyperthread_ratio

cpu_count / hyperthread_ratio

1

2

NO

2

2

1

1

1

YES

2

2

1

1

2

YES

4

4

1

 

So even though Buck said his formula would show you the number of cores, the query itself tells the truth and the output column is named 'sockets'. The ratio of cpu_count to hyperthread_ratio shows the number of sockets, not the number of cores, and there is no way to tell the difference between hyperthreading and dual-core.

I have heard that this relationship and the values returned can be different on Vista or later operating systems, but since I am not running Vista, I have no way to test it out for myself. But maybe you can.

:-)

Have fun!

~Kalen

Published Saturday, December 08, 2007 4:56 PM by Kalen Delaney
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Scott R. said:

Kalen,

Thanks for your post on a topic of interest to me.

I don't claim to be an expert in these areas, but I will share what I have learned.  My understanding of the ability of the OS and RDBMS (SQL Server) to accurately report the socket / core / hyper-thread status of a system is both somewhat limited and dependent on the OS version.  For example, Windows Vista and Windows Server 2008 introduce some new WMI fields in the Win32_Processor class (NumberOfCores and NumberOfLogicalProcessors - see link: http://msdn2.microsoft.com/en-us/library/aa394373.aspx) as well as new interpretations of instances of these classes (prior to Vista / 2008, an instance of Win32_Processor could be a real processor (first core on a socket), a secondary core on a socket, or a hyper-thread).  As an example, this is why you commonly saw hyper-threaded 4-socket single-core servers as 8 processors defined to the OS (in Task Manager).  This is the difference you referred to in your post.

The problems with the new fields and interpretation are:

-  They don't apply to the older OS versions - the bulk of what is deployed in the field today and for the foreseeable future

-  They may tell you if you have hyper-threading disabled (equal number of cores and processors for that Win32_Processor instance of one socket), but I don't think it can differentiate between a processor socket that is hyper-thread-capable but disabled and a processor socket that is not hyper-thread-capable - perhaps a finer point of distinction, but one I find worthwhile to know.

As a result, I have sought out and found other solutions to finding this information on a discoverable basis (without requiring physical access to the system to crack open the box, or to have BIOS setup access at system boot time to see the hyper-thread enabled status).  I posted on these solutions earlier this year in response to a similar topic by Kevin Kline (see link: http://sqlblog.com/blogs/kevin_kline/archive/2007/08/07/determining-sql-server-cores-cpus-and-hyperthreading.aspx).  The Intel CPUCount utility and the WinAudit utility are the best solutions I have found to date.  What I like best about these solutions is that they are independent of OS version and SQL Server version.  I have used CPUCount on Windows versions backup to 2000 for certain (I may have even tried CPUCount on NT 4.0 - I don't recall for certain) and as recent as Windows Vista with no issues.  Note that the Intel CPUCount utility does not accurately report cores per socket on AMD processors (I wonder why?), but does report the total processor core count correctly.  CPUCount appears to work correctly on Intel processors.

I believe these utilities use the CPUID command to the processor to discover the processor capabilities, instead of relying on OS capabilities to do the same.  While the OS may also be using the CPUID command to discover these capabilities, not all versions of the OS discover and present this information the same (if at all).

It would be nice to have query-able answers to these questions (as in a DMV), but for now I am satisfied with the command line utilities and parsing the results if and when needed.

Let me know if you find other solutions to these questions.

Thanks,

Scott R.

December 9, 2007 10:57 AM
 

Adam Machanic said:

December 9, 2007 11:16 AM
 

Kalen Delaney said:

Thanks Scott and Adam!

~Kalen

December 10, 2007 2:01 AM
 

WesleyB said:

Running the above query on my dual core machine in Vista returns 1 like you said above.

December 10, 2007 3:00 PM
 

noeldr said:

December 11, 2007 1:37 PM
 

Linchi Shea said:

I like CPUID (www.cpuid.com) as well, in particular the fact that it's being updated rathre promptly to include new processors (e.g. v1.42 can correctly identify Harpertown and Barcelona).

December 11, 2007 11:13 PM
 

K. Brian Kelley said:

Same as WesleyB, my dual core returns 1 in Vista.

August 19, 2008 6:22 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement