Useful SQL Server DBCC Commands

DBCC SQLMGRSTATS: Used to produce three different values that can sometimes be useful when you want to find out how well caching is being performed on ad-hoc and prepared Transact-SQL statements.

Example:

DBCC SQLMGRSTATS

Sample Results:

Item                      Status
------------------------- -----------
Memory Used (8k Pages)    5446
Number CSql Objects       29098
Number False Hits         425490

Here's what the above means:

• Memory Used (8k Pages): If the amount of memory pages is very large, this may be an indication that some user connection is preparing many Transact-SQL statements, but it not un-preparing them.
• Number CSql Objects: Measures the total number of cached Transact-SQL statements.
• Number False Hits: Sometimes, false hits occur when SQL Server goes to match pre-existing cached Transact-SQL statements. Ideally, this figure should be as low as possible.

[2000] Added 4-17-2003

*****

DBCC SQLPERF(): This command includes both documented and undocumented options. Let's take a look at all of them and see what they do.

DBCC SQLPERF (LOGSPACE)

This option (documented) returns data about the transaction log for all of the databases on the SQL Server, including Database Name, Log Size (MB), Log Space Used (%), and Status.

DBCC SQLPERF (UMSSTATS)

This option (undocumented) returns data about SQL Server thread management.

DBCC SQLPERF (WAITSTATS)

This option (undocumented) returns data about wait types for SQL Server resources.

DBCC SQLPERF (IOSTATS)

This option (undocumented) returns data about outstanding SQL Server reads and writes.

DBCC SQLPERF (RASTATS)

This option (undocumented) returns data about SQL Server read-ahead activity.

DBCC SQLPERF (THREADS)

This option (undocumented) returns data about I/O, CPU, and memory usage per SQL Server thread. [7.0, 2000] Updated 3-20-2006

*****

DBCC SQLPERF (UMSSTATS): When you run this command, you get output like this. (Note, this example was run on a 4 CPU server. There is 1 Scheduler ID per available CPU.)

Statistic                        Value
-------------------------------- ------------------------
Scheduler ID                     0.0
num users                        18.0
num runnable                     0.0
num workers                      13.0
idle workers                     11.0
work queued                      0.0
cntxt switches                   2.2994396E+7
cntxt switches(idle)             1.7793976E+7
Scheduler ID                     1.0
num users                        15.0
num runnable                     0.0
num workers                      13.0
idle workers                     10.0
work queued                      0.0
cntxt switches                   2.4836728E+7
cntxt switches(idle)             1.6275707E+7
Scheduler ID                     2.0
num users                        17.0
num runnable                     0.0
num workers                      12.0
idle workers                     11.0
work queued                      0.0
cntxt switches                   1.1331447E+7
cntxt switches(idle)             1.6273097E+7
Scheduler ID                     3.0
num users                        16.0
num runnable                     0.0
num workers                      12.0
idle workers                     11.0
work queued                      0.0
cntxt switches                   1.1110251E+7
cntxt switches(idle)             1.624729E+7
Scheduler Switches               0.0
Total Work                       3.1632352E+7

Below is an explanation of some of the key statistics above:

• num users: This is the number of SQL Server threads currently in the scheduler.
• num runnable: This is the number of actual SQL Server threads that are runnable.
• num workers: This is the actual number of worker there are to process threads. This is the size of the thread pool.
• idle workers: The number of workers that are currently idle.
• cntxt switches: The number of context switches between runnable threads.
• cntxt switches (idle): The number of context switches to the idle thread.

[2000] Added 4-17-2003