Geek City: HyperThreaded or Not?

Kalen Delaney — Sat, 08 Dec 2007 23:56:00 GMT

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

Kalen Delaney : Hyperthreading

Geek City: HyperThreaded or Not?