THE SQL Server Blog Spot on the Web

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

Joe Chang

Detecting Hyper-Threading state

To interpret performance counters and execution statistics correctly, it is necessary to know state of Hyper-Threading (on or off). In principle, at low overall CPU utilization, for non-parallel execution plans, it should not matter whether HT is enabled or not. Of course, DBA life is never that simple (see my other blogs on HT). The state of HT does matter at high overall utilization and in parallel execution plans depending on the DOP. SQL Server does seem to try to allocate threads on distinct physical cores at intermediate DOP (DOP less than or equal to the number of physical cores).

Suppose for example, that maximum throughput on 10 physical cores is 10000 call/s with HT off and 14000 with HT on (overall CPU near 100%). Then the average CPU (worker time) per call is 1ms with HT off, and 1.43 ms with HT on as there are twice as much available worker time with HT on.

In a very well tuned OLTP system, we might have very steady average CPU per call as call volume increases from low overall CPU utilization to near saturation with HT off. With HT on, at low overall CPU, the average CPU per call is the same as with HT off, but average CPU per call increases at some point when there is sharing of physical cores between concurrently running queries. But this is still good because system wide throughput capability has increased.

In a not well tuned database application, there could be contention between concurrent queries that causes average CPU per call to increase as overall system CPU load increases. Without knowing the state of HT, it is hard to make the assessment as to which situation has occurred.

If we have direct sysadmin access to the OS, we could make calls (via WMI) to determine the processor model number, the total number of sockets, the total number of logical processors, then determine with a lookup table matching processor model to the number of physical cores. (again, a pain)
(per LondonDBA, WMI Win32_Processor does report the number of sockets, physical cores, and logical, I must have been thinking of an older API that was not HT aware or even multi-core aware)
But we do not always have sysadmin access to the host OS, as many organization believe separation of DBA, infrastructure (not to mention storage) is a good thing, and even better when these groups do not communicate with each other, (let alone) working together with a common mission.

SQL Server version 2005 was helpful in the DMV sys.dm_os_sys_info
which had two columns: cpu_count and hyperthread_ratio
defined in version 2005 as:
1) "Number of logical CPUs on the system."
2) "Ratio of the number of logical and physical processors."
respectively.

In version 2008, RTM and R2, the definition of hyperthread_ratio was changed to:
"Ratio of the number of logical or physical cores that are exposed by one physical processor package."

in 2012 and 2014, slightly different wording but same meaning:
"Specifies the ratio of the number of logical or physical cores that are exposed by one physical processor package."

WTF???
why the change in definition?
There are actually 3 pieces of information we are interested in:
a) the number of sockets
b) the number of physical cores per socket
c) the state of HT (or the logical processors per socket)

In 2005, we have information to determine the product of A and B, and the value of C, but not atomic values of A and B,
In the 2008 and later version, we can determine A (using the ratio) and the composite product of A x B x C, but not atomic values of B and C.

Ok, then I noticed that in SQL Server version 2012 and 14, there would be a line in the log of the form:
"SQL Server detected 4 sockets with 10 cores per socket and 10 logical processors per socket, 40 total logical processors; using 40 logical processors based on SQL Server licensing."

So from this, I have A, B and C, even though I must parse the error log for this info. In version 2008R, the info is only: "Detected 40 CPUs."
which has no additional information to what is in the DMV. It would be helpful if this information were available directly from the DMV, but then life might be easier?

Addendum

EXEC sys.xp_readerrorlog 0, 1, "detected", "socket"

Published Tuesday, May 27, 2014 2:23 PM by jchang

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

 

Jon Morisi said:

I gave up on trying to get this info out of SQL.  Now I just use Intel's cpucount.exe or cpu-z.

It would be really nice to be able to run a SQL, powershell, or OS command to pull this information.  I really <3 cpucount's ability to do that.  Also it's portable (no install).

I can't seem to find cpucount on intel's website anymore.  It looks like it was replaced by "Intel® Processor Identification Utility", which requires and install :(

(http://tinyurl.com/o6tc4fo)

May 27, 2014 6:29 PM
 

LondonDBA said:

You can use Powershell to retrieve processor details:

Get-WmiObject -ComputerName YourServerName Win32_Processor | Select-Object SocketDesignation,NumberOfCores,NumberOfLogicalProcessors

May 27, 2014 6:37 PM
 

jchang said:

I had mentioned in the text (but had not highlighted it) that this info could be accessed via WMI. I use my C# app ExecStats. Powershell works too, I am just limiting the number of programming tools I use/learn.

Does anyone know what security level is required to access WMI?

is it sysadmin, or is there a lesser level?

May 27, 2014 7:43 PM
 

jchang said:

Jon: It looks like Intel now only makes available the binary for the Processor identification. They use to have source code, win battery management or something

see if you can find anything here

https://software.intel.com/en-us/windows

I took a quick look at PowerExplorer

There used to be App Note 485, but I do not what became of this.

May 27, 2014 8:13 PM
 

RichB said:

I found various issues trying to get the data out with WMI - it seemed to be missing columns (iirc number of cores) depending on the version at hand.  As I recall it was older os/patch levels did not have it; the errors resulting are not elegant.

May 28, 2014 4:22 AM
 

jchang said:

Let me reiterate that: yes this information is available in WMI, but exposing it via DMV is better, especially since the SQL Server knows it.

for now, try appropriate variations of the this:

EXEC sys.xp_readerrorlog 0, 1, "detected", "socket"

May 28, 2014 4:10 PM
 

Glenn Berry said:

You can use

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

to get the processor description.

Microsoft really should add a couple of new columns to sys.dm_os_sys_info so you can get the processor description and whether or not HT is enabled from there.

September 10, 2014 6:18 PM
 

More Clients said:

Joe Chang : Detecting Hyper-Threading state

October 16, 2014 10:50 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

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