Perfmon Thread Count vs SQL Workers

Question

We are trying to monitor SQL Worker Threads and in the past we used the following query:

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

We are now considering just using the PerfMon counter “Process>Thread Count” for each SQL Server Instance. During our analysis, we noticed that there is a slight difference between the PerfMon Thread Count and the Worker Threads as found in dm_os_schedulers or dm_os_workers. We also compared it to what was found in dm_os_threads and still saw a difference. Does anyone know the difference between what is being tracked in those Microsoft provided DMV’s vs what is being captured by PerfMon? The DMV’s are consistently lower than what is found in the Performance Monitor, so they must be "excluding" something. We need to understand what that something is.

For example, the dm_os_threads dmv will show 137 but the perfmon counter will show 141 and the dm_os_scheduler and dm_os_workerss dmv's will show 122 and 123 respectively.

We are on SQL Server 2017, CU22; Windows Server 2016

Answer 1

My original discussion about thread usage can be read in this answer.

But why is the result from dm_os_threads different from the PerfMon result?

The threads DMV is only going to expose threads that SQL Server knows about, thus it'll only have threads that it bootstrapped. Essentially, you'll see only the threads that SQL Server has either started directly or indirectly and hooked certain data structures into the TLS.

A quick example would be, let's say I have a 3rd party antivirus application, let's call it "Sarbon Yack" or SY for short. SY might take it upon themselves to load their own modules in the SQL Server memory space - something not supported. When those modules are loaded, they might start many different threads - however it wasn't SQL Server that started them via the engine, it was in fact a foreign module that started them. They technically live in the confines of the process and are reported as such, but SQL Server doesn't know they exist and they don't have any SQL Server data structures attached to them. These threads won't show up in the DMV.

What is PerfMon including that isn't being included in the DMV?

I don't know the code for perfmon but perfmon doesn't create the values, it just reads from different data sources when the value is asked to be returned (for example by adding the counter). My instincts tell me that the internal value is increased when a thread is created in the process space, and removed when the thread is destroyed by whatever code tracks this.

The value itself has no idea what SQL Server is or how it uses the threads, it's just looking at an abstracted "Process" (which is what you'll find the threads value under as these are per process) and reading the information about number of threads, then returning that value. It doesn't differentiate between what is a SQL boostrapped thread and what isn't.

My best guess on how this is done is probably by calling NtQuerySystemInformation for SystemInformation which would return a list of System_Process_Information structures, that will hold the number of threads in the process. My best guess is that (number of threads) is coalesced into existence by traversing the thread list in the _EPROCESS data structure - but there might also be a counter for this specific thing somewhere in the kernel or user-mode structures and I just don't see it or know it exists.

Thread List In _EPROCESS:

+0x470 ThreadListHead   : _LIST_ENTRY