HiveBrain v1.2.0
Get Started
← Back to all entries
gotchasqlMinor

Why does my server have thousands of "HIDDEN ONLINE" schedulers?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
schedulerswhyhiddenonlinethousandsdoesserverhave

Problem

We have a SQL Server 2012 instance with 16 CPU cores. When looking in sys.dm_os_schedulers we see the expected 16 rows that are "VISIBLE ONLINE", one per core. The error log shows the following standard entry on startup:

SQL Server detected 2 sockets with 8 cores per socket and 8 logical
processors per socket, 16 total logical processors; using 16 logical
processors based on SQL Server licensing. This is an informational
message; no user action is required.

However sys.dm_os_schedulers also contains approx. 1200 rows that are "HIDDEN ONLINE".

The server is not using Availability Groups or Mirroring, and Resource Governor is not enabled.

The problem is that each hidden scheduler appears to be assigned one worker thread, so our monitoring software is constantly alerting that the server is running out of threads, even though queries are running perfectly happily and there does not appear to be any actual impact to performance.

I'm aware that hidden schedulers are only available to internal system processes, but do you know what would cause so many to be shown?

Solution

Converting my comment to answer :

Some one might be running undocumented fn_dump_dblog and it is creating a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It is fixed in 2012 + SP2 though.

Also, you can use this query to find out the creation time of schedulers :

SELECT s.scheduler_id,  s.status, th.started_by_sqlservr, th.creation_time 
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
 JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
  JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
where s.status = 'HIDDEN ONLINE' -- just get the hidden online schedulers for your specific case 
ORDER BY s.scheduler_id

Code Snippets

SELECT s.scheduler_id,  s.status, th.started_by_sqlservr, th.creation_time 
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
 JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
  JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
where s.status = 'HIDDEN ONLINE' -- just get the hidden online schedulers for your specific case 
ORDER BY s.scheduler_id

Context

StackExchange Database Administrators Q#269249, answer score: 3

Revisions (0)

No revisions yet.