• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

dbcc sqlperf (waitstats) how to evaluate this info when it comes to performance issue?

I would like to know when it comes to performance issue, how do I take the info return from this table to pintpoint the problem,  dbcc sqlperf (waitstats) will show us the wait statistics but I dunno how to use the data from this dbcc commands to relate with the performance that I encounter
1 Solution
James MurrellProduct SpecialistCommented:
taken this from -http://www.sqldev.net/articles/dbcc_sqlperf_waitstats.htm

DBCC SQLPERF(WAITSTATS) reports two wait times: [Wait Time] and [Signal Wait Time]

[Wait Time] is the total wait time between the time that the worker starts waiting on the resource (T0) and the time the workers gets scheduled in (T2).

[Signal Wait Time] is the time between the time that the worker got signaled (T1) that the resource is available and the time it got scheduled in (T2)

This means that you need to calculate the wait time on the resource by substracting [Wait Time] from [Signal Wait Time].

Note: For more information on UMS see Inside the SQL Server 2000 User Mode Scheduler

The table structure returned, has the following shape:

create table #waitstats
    [Wait Type]        nvarchar(32) not null,
    [Requests]         float not null,
    [Wait Time]        float not null,
    [Signal Wait Time] float not null

To populate the table use:

insert into #waitstats exec('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')
select * from #waitstats

DBCC SQLPERF(WAITSTATS, CLEAR) resets the counters, this is a service/instance wide reset, not per session!

NOTE: For SQL Server 2005, please use the sys.dm_os_wait_stats dynamic management view instead of DBCC SQLPERF(waitstats), also the sys.dm_os_waiting_tasks table provides information on all currently waiting tasks in the system.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now