Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-25
1
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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