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?

Improve this question
3
  • working as designed ? - see the following Microsft documentation ... learn.microsoft.com/en-us/sql/database-engine/configure-windows/…
    – Stephen Morris - Mo64
    Commented Jun 16, 2020 at 12:13
  • Can you include a screenshot that shows your full query of sys.dm_os_schedulers, and the bottom rows? Just making sure we're not missing something obvious in the query, like a cartesian product joining to something else.
    – Brent Ozar
    Commented Jun 16, 2020 at 12:33
  • Query is just a simple "SELECT * FROM sys.dm_os_schedulers". Have added a screenshot for info.
    – JamesLean
    Commented Jun 16, 2020 at 18:15

2 Answers 2

Reset to default
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
Improve this answer
3
  • Thanks Kin, interesting find. But I'm not aware of anything that would be regularly running fn_dump_dblog and the server is running 2012 SP3, so not sure this is the cause.
    – JamesLean
    Commented Jun 17, 2020 at 12:39
  • What is the uptime of the sql instance ? Was it ever rebooted. I cant think of any other process that will just create 1000s of hidden schedulers. You should open a support ticket to Microsoft. Any dump files or error logs indicate anything suspicious ?
    – Kin Shah
    Commented Jun 17, 2020 at 15:16
  • The server was last restarted about 10 days ago. Your query only returns 6 rows unfortunately (even though there are still over 1200 hidden schedulers). We're going to try and restart the service at some point and see if the schedulers all get created straight away, or build up over time. If they do all come back then it may need a call to MS Support, as you say.
    – JamesLean
    Commented Jun 17, 2020 at 20:12
0

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.