CPU and Memory Usage per SQL Server Thread

Root Folder > SQL Server
SQL Server 7.0 and 2000 use its own internal thread scheduler (called the UMS) when running in either native thread mode or in fiber mode. By examining the UMS's Scheduler Queue Length, you can help determine whether or not that the CPU or CPUs on the server are presenting a bottleneck.
This is similar to checking the System Object: Processor Queue Length in Performance Monitor. If you are not familiar with this counter, what this counter tells you is how many threads are waiting to be executed on the server. Generally, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck.
The advantage of using the UMS's Schedule Queue Length over the System Object: Processor Queue Length is that it focuses strictly on SQL Server threads, not all of the threads running on a server.
To view what is going on inside the UMS, you can run the following undocumented command:
<b>DBCC SQLPERF (UMSSTATS)</b>

 Below is an explanation of some of the key statistics:
The "num users" tells you the number of SQL threads there are for a specific scheduler.
The "num runnable," or better known as the "Scheduler Queue Length," is the key indicator to watch. Generally, this number will be 0, which indicates that there are no SQL Server threads waiting to run. If this number is 2 or more, this indicates a possible CPU bottleneck on the server. Keep in mind that the values presented by this command are point data, which means that the values are only accurate for the split second when they were captured, and will be always changing. But if you run this command when the server is very busy, the results should be indicative of what is going on at that time. You may want to run this command multiple time to see what is going on over time.
The "num workers" refers to the actual number of worker threads there are in the thread pool.
The "idle workers" refers to the number of idle worker threads.
The "cntxt switches" refers to the number of context switches between runnable threads.
The "cntxt switches(idle)" refer to the number of context switches to "idle" threads.
As you can see, this command is for advanced users, and is just one of many tools that can be used to see internally how SQL Server is performing.

Add Feedback