DBCC SQLPERF(waitstats) low wait_time, high signal_wait_time

Last Post 04 Apr 2007 03:44 PM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
darkInertia
New Member
New Member

--
04 Apr 2007 12:30 PM
I have been keeping track of DBCC waitstats using a SP similiar to the download here (note that I do use DBCC SQLPERF(waitstats, clear) after I insert the current results into a table) and am also using this as a resource for what the numbers actually mean. Based on the information in the second article, wait_time is the sum of signal_wait_time and the time it takes to obtain the resource. So, wait_time > signal_wait_time. However, looking at some of my data, the signal_wait_time is actually larger than the wait_time! This happens for OLEDB.

Is the second article incorrect, do I have errors in the DBCC gathering, or is my data/server just messed up? Thanks
SQLUSA
New Member
New Member

--
04 Apr 2007 03:44 PM
This is a very exciting topic.

What is your platform?

Despite the issues, did you learn something interesting from WAITS?

What kind of performance issues do you have with your server?

Kalman Toth
SQL Server 2005 Training - http://www.sqlusa.com
darkInertia
New Member
New Member

--
05 Apr 2007 06:37 AM
It's running MS SQL 2000 on MS windows server 2003, SP1.

OLEDB wait_time dwarfs all other wait_types by several factors. PAGELATCH_EX, CXPACKET, and LATCH_EX are the next three highest wait_times. For signal_wait_time, CXPACKET, WRITELOG, and PAGEIOLATCH_SH are the top three respectively. This server is mostly the testing server, however there is some production work done on it. There is a good amounts of linked server traffic and bulk inserts from it, which is probably why OLEDB is so high.
darkInertia
New Member
New Member

--
05 Apr 2007 08:24 AM
I cal waitstats every 15 minutes, storing the results into a table and then clearing waitstats. Below is a few hours' worth of OLEDB data:

waitstats_id wait_type requests# wait_time signal_wait_time time
187369 OLEDB 88587 72929 269924576 2007-04-04 20:09:03.503
187292 OLEDB 60465 2392329472 2065266176 2007-04-04 19:54:13.163
187215 OLEDB 79212 41286 1720044928 2007-04-04 19:39:02.370
187138 OLEDB 57810 41978 60794260 2007-04-04 19:24:02.703
187061 OLEDB 72968 39337 3194650112 2007-04-04 19:09:02.473
186984 OLEDB 85228 71699 1566596480 2007-04-04 18:54:04.263
186907 OLEDB 121601 21893 379512032 2007-04-04 18:39:02.210
186830 OLEDB 70256 519901088 2095517696 2007-04-04 18:24:03.993
186753 OLEDB 96747 1391895808 1756686592 2007-04-04 18:09:02.810
186676 OLEDB 113340 2797299968 1808967168 2007-04-04 17:54:02.860
186599 OLEDB 44415 2324115968 144369856 2007-04-04 17:39:26.190



Here is the most recent data for all non-negative wait_times; it's sorted by wait_time:

waitstats_id wait_type requests# wait_time signal_wait_time time
192682 OLEDB 66377 2455089408 3833666816 2007-04-05 13:24:14.400
192700 CXPACKET 75184 1909738 9537 2007-04-05 13:24:14.400
192720 PAGEIOLATCH_SH 147044 1604805 2934 2007-04-05 13:24:14.400
192690 WRITELOG 37515 1484259 3650 2007-04-05 13:24:14.400
192710 LATCH_EX 54123 819717 4532 2007-04-05 13:24:14.400
192730 NETWORKIO 17969 742619 0 2007-04-05 13:24:14.400
192722 PAGEIOLATCH_EX 40202 521033 96 2007-04-05 13:24:14.400
192678 IO_COMPLETION 7559 119482 6031 2007-04-05 13:24:14.400
192658 LCK_M_S 48 59227 16 2007-04-05 13:24:14.400
192669 LCK_M_RS_U 3 29437 0 2007-04-05 13:24:14.400
192692 EXCHANGE 96 8309 0 2007-04-05 13:24:14.400
192660 LCK_M_X 17 8250 0 2007-04-05 13:24:14.400
192716 PAGELATCH_EX 208818 8242 4618 2007-04-05 13:24:14.400
192657 LCK_M_SCH_M 1 2234 0 2007-04-05 13:24:14.400
192721 PAGEIOLATCH_UP 63 1889 0 2007-04-05 13:24:14.400
192661 LCK_M_IS 2 1452 0 2007-04-05 13:24:14.400
192714 PAGELATCH_SH 13032 1391 392 2007-04-05 13:24:14.400
192659 LCK_M_U 2 1078 0 2007-04-05 13:24:14.400
192663 LCK_M_IX 12 482 0 2007-04-05 13:24:14.400
192715 PAGELATCH_UP 2790 376 376 2007-04-05 13:24:14.400
192701 PAGESUPP 644 240 16 2007-04-05 13:24:14.400
192702 SHUTDOWN 0 0 0 2007-04-05 13:24:14.400
You are not authorized to post a reply.

Acceptable Use Policy