Solved

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

Posted on 2007-11-25
1
337 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 500 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 Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query display the latest row 10 51
Moving a large MS Sql Database 5 51
SQL QUERY 3 33
Database maintenance 36 98
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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