DBCC SQLPERF(UMSTATS) The result set: Statistic Value
-------------------------------- ------------------------
Scheduler ID 0.0
num users 7.0
num runnable 0.0
num workers 5.0
idle workers 3.0
work queued 0.0
cntxt switches 0.0
cntxt switches(idle) 0.0
Scheduler ID 1.0
num users 7.0
num runnable 0.0
num workers 5.0
idle workers 3.0
work queued 0.0
cntxt switches 0.0
cntxt switches(idle) 0.0
Scheduler ID 2.0
num users 7.0
num runnable 0.0
num workers 4.0
idle workers 3.0
work queued 0.0
cntxt switches 0.0
cntxt switches(idle) 0.0
Scheduler ID 3.0
num users 6.0
num runnable 1.0
num workers 5.0
idle workers 2.0
work queued 0.0
cntxt switches 0.0
cntxt switches(idle) 0.0
Scheduler Switches 0.0
Total Work 1005814.0
Description of the statistic rows: | Statistic | Description | | Scheduler ID | ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 255. The number of schedulers is depending on the number of (logical) CPU's and if you are using an affinity mask or not. By default you have as many schedulers as you have logical CPU's; for example a dual core CPU with hyper threading turned on will result in 4 schedulers. | | num users | Number of UMS users assigned to the scheduler. A UMS user is a request to the UMS scheduler to support a major action such as a TDS request or sub-process action. | | num runnable | The number UMS users that are on the runnable queue/list. These users are eligible for execution when the current user completes a UMS context switch. | | num workers | The number of worker threads/fibers currently associated with this scheduler. The workers are created as new UMS requests are assigned to the scheduler and no idle worker can be located. The number of max workers per scheduler is determined at startup of the SQL Server (NOT dynamic) by the max worker thread configuration divided by the number of visible schedulers. This count includes workers that are not assigned any task (idle workers). | | idle workers | The number of worker threads/fibers currently associated with this scheduler that are waiting for a new request to process.
On SQL Server 7.0 the idle workers do not terminate until the SQL Server is shutdown.
On SQL Server 2000 the worker thread will cleanup and terminate if idle for 15 minutes. | | work queued | The number of pending UMS work requests (a.k.a. tasks) queued; waiting for a worker thread/fiber to complete the action, so that a worker can pick them up. | | cntxt switches | Number of context (UMS based) switches that have occurred on this scheduler. To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context. | | cntxt switches(idle) | Number of times the scheduler has been waiting for an event while idle. Each scheduler is assigned a thread which performs tasks for the scheduler when all other workers are in an idle or wait state. (checks IO's, timer lists, etc...) | | Scheduler Switches | Total number of context switches for all schedulers in the list | | Total Work | Total number of active users on the all schedulers |
The table structure returned, has the following shape: create table #umsstats
(
[Statistic] nvarchar(32) not null,
[Value] float not null
) To populate the table use: insert into #umsstats exec('dbcc sqlperf(umsstats) with tableresults, no_infomsgs')
select * from #umsstats DBCC SQLPERF(WAITSTATS, CLEAR) resets the counter, this is a service/instance wide reset, not per session! Note: this information only applies to SQL Server 2000, for SQL Server 2005 use the sys.dm_os_schedulers dynamic management view, which contains more detailed information. | 
