Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-25
1
Medium Priority
?
370 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:motioneye
1 Comment
 
LVL 31

Accepted Solution

by:
James Murrell earned 1500 total points
ID: 20346306
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question