5

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".

enter image description here

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?

JamesLean
  • 391
  • 1
  • 4
  • 10

2 Answers2

3

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
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

We had same situation and it turned out Microsoft SQL Server VSS Writer creates one Hidden Scheduler per database when it does backup

Duman
  • 1
  • 1