Solved

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

Posted on 2007-11-25
1
326 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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