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."
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."
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?
EXEC sys.xp_readerrorlog 0, 1, "detected", "socket"