The other day, I had a very interesting conversation with one of my clients about Scheduler_ID With a Large Number in dm_os_schedulers while working with the Comprehensive Database Performance Health Check. During the consultation, I had provided the script for SQL SERVER – Measure CPU Pressure – Detect CPU Pressure.
If you look at the script carefully, you will notice The client DBA asked me the purpose of the WHERE condition where I have only retrieved schedulers which are less than 255. (click here to read the original script)
You can run the following query without WHERE condition and you will see the count of the rows to jumping to double the number and also see the column scheduler_id with some very large number. Here is the image.
Description of Scheduler_ID
You can see from this image that there are scheduler_id from 0 to 1 which represents the number of scheduler on my machine but there is also scheduler id which very large number (integer). In SQL Server, there can be a maximum of 255 schedulers for the users (that represents scheduler_id from 0 to 254). Scheduler 255 represents the dedicated administrator connection (DAC). There is one DAC scheduler per instance.
Every scheduler which has ID above 1048576 is known as hidden schedulers and reserved for the internal use of SQL Server. Resource monitors that coordinate memory pressure use scheduler 257 and scheduler 258, one per NUMA node.
Large Number in Scheduler_ID
It is very clear from the above description of the scheduler_id with a large number on it are hidden schedulers. While they are hidden schedulers are important to SQL Server Engine, they are of no use to end users like us. Practically, we should not build any script which depends on them or use them to do our own analysis. This is the reason, the original script has WHERE condition which is filtering out all the rows which are not relevant to end-users.
There are many such interesting trivia I encounter during the Comprehensive Database Performance Health Check.
Reference: Pinal Dave (https://blog.sqlauthority.com)
