Performance:DBCC SQLPERF(WAITSTATS)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Performance:DBCC SQLPERF(WAITSTATS)

Author

Topic

aelazouzi
Starting Member

7 Posts

Posted - 2007-06-13 : 10:21:12

Hi everyone,

I have the following results of the DBCC SQLPERF(WAITSTATS)statement. Can anybody help me to understand this results. And probabely give me some links where I can find simple explanation to what is represented in this result.

thanx in advans

Wait Type Requests Wait Time Signal Wait Time
-------------------------------- ------------------------ ------------------------ ------------------------
MISCELLANEOUS 12746.0 392.0 392.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 36.0 4480299.0 0.0
LCK_M_U 1.0 0.0 0.0
LCK_M_X 349.0 1.4193338E+9 3.1319048E+7
LCK_M_IS 2.0 0.0 0.0
LCK_M_IU 2.0 0.0 0.0
LCK_M_IX 183.0 4.32164E+7 693.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 1.2385776E+7 5.314921E+8 5.2522048E+8
IO_COMPLETION 425086.0 1807750.0 3279.0
ASYNC_IO_COMPLETION 1761.0 43015.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 935698.0 2.145476E+9 4.1120827E+9
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 1.4897403E+8 1.5617676E+9 5.3452915E+8
ASYNC_DISKPOOL_LOCK 48938.0 48.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 7038.0 6470.0 234.0
PIPELINE_LOG 48452.0 105836.0 15931.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 7.4169144E+7 4.2217632E+7 2666875.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 1562.0 718608.0 3909.0
XCB 0.0 0.0 0.0
DBTABLE 7.0 501.0 0.0
EC 1.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 201443.0 36966.0 14971.0
CXPACKET 1.1343732E+7 7.811609E+8 7272799.0
PAGESUPP 7404.0 20138.0 2920.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 0.0 0.0 0.0
CURSOR 0.0 0.0 0.0
EXECSYNC 24.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 3670.0 78.0 15.0
LATCH_UP 17204.0 168715.0 376.0
LATCH_EX 4.8366852E+7 1.7655811E+8 5215812.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 1.0 0.0 0.0
PAGELATCH_SH 55449.0 6106.0 3136.0
PAGELATCH_UP 500201.0 49741.0 24379.0
PAGELATCH_EX 38162.0 116349.0 762.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 4.6521136E+7 3.8031642E+8 2065733.0
PAGEIOLATCH_UP 93611.0 780440.0 10356.0
PAGEIOLATCH_EX 1.1725162E+7 1.6793085E+8 391561.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 7.3307514E+8 1.1554247E+7 0.0
Total 1.08896E+9 7.2693658E+9 5.2208456E+9

(77 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-13 : 11:23:52

Try search TechNet, I saw article there before.

aelazouzi
Starting Member

7 Posts

Posted - 2007-06-14 : 05:36:53

quote:
Originally posted by rmiao

Try search TechNet, I saw article there before.

Hi rmiao,

I have searched the web to find something about the subject but unfortunately what i found was only a few white papers where the explanation of the terms displayed in the result of the DBCC SQLPERF(WAITSTATS) was minimal. I can't find the explanation of every WAIT TYPE . I can’t find in which unit is WAIT TIME expressed. Is it in second or milliseconds?

Thanx

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-06-14 : 06:16:16

Search Google on "SQL Server 2005 Waits and Queues" and you will find a great whitepaper. I know you are using 2000 but the best documentation is for SQL Server 2005. Most of them haven't changed.

You can also look here: http://www.sqldev.net/articles/wait_types.htm

(The trick is to search on wait types and not wait stats)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources