Wait status

Last Post 03 Apr 2007 11:31 AM by TRACEYSQL. 39 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Page 2 of 2 << < 12
Author Messages
TRACEYSQL
New Member
New Member

--
31 Mar 2007 05:45 PM
During the time I could ping the sql and do a net perf test which the citrix guy ran saying that there is 600 mg a second allowed and that worked.

So im now stuck on how to configure ODBC to make sure it is clearing out connections properly...and looking for tool...i just ran perfmon and did not get anything listed in here as connections im using my laptop but i opened up access and sql and odbc and nothing appeared in the permon as one connection...

ODBC tracing how fun is that?

TRACEYSQL
New Member
New Member

--
01 Apr 2007 04:20 AM
ERP system bought of the shelf that uses ODBC can't change to OLEDB..
So i down to checking of the ODBC and whats going on there.

We may have 100 users all connecting at one time using the ODBC...So i now trying to figure out if ODBC is closing opening connections properly i can't see the code of ERP either.

Its not first time it has happened usually a reboot fixes it but now im getting closer to the problem.

I just found ODBCPING gosh i wish i had that command early...have it now..
It maybe that we need a controlled ODBC like a load balancer if possible?

but i really like to see inside odbc and what its doing first.
TRACEYSQL
New Member
New Member

--
01 Apr 2007 05:24 AM
Also
http://support.microsoft.com/kb/328476

Am i flooding tcp/ip ? anything i can run to confirm this.

Gosh what a lot of stuff i have to do before the sql accepts the commands.
TRACEYSQL
New Member
New Member

--
01 Apr 2007 02:57 PM
How did you set odbc dsn on citrix? Do you use tcp/ip in dsn? Tried use ip address as server name to bypass dns related problems?

We have TCP/IP connection parameters (Servername) dynamically determine port (1433 but this is greyed out).

Bypass DNS ? how do i do this.

(All of our ip addresses changed due to a domain controller the other day and the network guys only just changed the SQL in TCP/IP i believe is where they did it)....

Cheers...

Done some more reading on winsocketlistenbacklog which default on SQL is 5 and that if i do a netstat -n and see over 400 wait stats i should increse the winsocketbacklog (i nhave ot digested this yet as i do not understand ...ODBC sends to TCP/IP i(i can have 400 threads but sql only take on 5 at a time?)
http://support.microsoft.com/kb/328476

Its not good me asking my network guys they look at me like im from outer space....so i basically have to figure it out and tell them which is good this is all fun stuff

TRACEYSQL
New Member
New Member

--
02 Apr 2007 02:58 AM
So unchecking just go direct to the sql server and not use DNS?
I did not know that.
TRACEYSQL
New Member
New Member

--
02 Apr 2007 03:52 PM
To bypass dns ok i think i do that tomorrow as today we had so many time outs sql error through odbc today.

I just found out that they swapped out an old DNS to a new one ...so i have rebooted both sql 1 and 2 to see what happens.

Is there any thing around in a host file in sql that i should be looking at ...incase its still trying to go to the old one.

So i go to the ODBC change the SERVERNAME to the ip address (this is then by passing DNS)....
I have cluster so i check with ip address it is...if i ping servername i should get ip address.

TRACEYSQL
New Member
New Member

--
03 Apr 2007 11:31 AM
Thanks...today is much better i rebooted SQL1, SQL2 cluster....and it must have been our network guys changing DNS and removing the old ones must have had an effect.

What a nightmare

Now back to the original sp_monitor lol

Cheers
TRACEYSQL
New Member
New Member

--
07 Apr 2007 03:34 PM
Hey guys it kept happening and now the network people are putting on network monitoring.
i have events coming to me and i have ping every sec too.

Guess what Friday it happened again i got a ton of errors....with network is down from our citrix..
and some network guy unplugged the cable....hmm...
TRACEYSQL
New Member
New Member

--
10 Apr 2007 03:01 PM
Remember me guys
I been reading.
http://support.microsoft.com/kb/897284/en-us

All of a sudden i get say 15-20 people automatically thrown out of their applications.
I thought it was network.

Example 1: A log write that is stuck for 45 seconds
A SQL Server log file write attempt periodically became stuck for approximately 45 seconds. The log write was not finishing in a timely manner. This behavior created a blocking condition that led to 30-second client time-outs.

The application submitted a commit to SQL Server and the commit became stuck as a log write pending. This behavior caused the query to continue holding locks and to block incoming requests from other clients. Then, other clients started to time out. This compounded the problem because the application was not designed to roll back open transactions when a query time out occurred. This created hundreds of open transactions that were holding locks. Therefore, a severe blocking situation occurred.

For more information about transaction handling and blocking, click the following article number to view the article in the Microsoft Knowledge Base:
224453 (http://support.microsoft.com/kb/224453/) Understanding and resolving SQL Server 7.0 or 2000 blocking problems
The application serviced a Web site using connection pooling. As more connections became blocked, the Web site created more connections. These connections became blocked and the cycle continued.

After approximately 45 seconds, the log write completed. However, by this time, hundreds of connections were backed up. The blocking problems resulted in several minutes of recovery time for SQL Server and for the application. When combined with the application problems, the stalled I/O condition had a very negative affect on the system.
Resolution

mentions
dbcc traceoff(830, -1) do i turn it on
TRACEYSQL
New Member
New Member

--
11 Apr 2007 06:05 AM
Thats what im thinking stuck i/o errors as it describes the problem to a tee.

track_waitstats procedure/script (not got that script) do you mean the sp_monitor one?
....can i get a copy to put in place.

Nothing being recorded in errors at all.........but i do not have all alerts on just blocking trace.


DB are all on san.....(data and log on separate luns)....just been told they are on raid 5
TRACEYSQL
New Member
New Member

--
11 Apr 2007 07:43 AM
So i will do this in query
DBCC TRACEON(818,-1)
DBCC TRACEON(806,-1)
DBCC TRACEON(3605,-1)


Run the trackwaits over a few days and let you know.
Our busy time is on fridays ......

Thanks guys......i feel i am making a progress
TRACEYSQL
New Member
New Member

--
11 Apr 2007 07:54 AM
Im about to put script in production...on one of my SYSTEMADM databases where i run my commands

To run this for the entire day what do i change here.
exec track_waitstats 20,15,'seconds'
TRACEYSQL
New Member
New Member

--
11 Apr 2007 08:06 AM
oK...ALL traces are on.

Ran the script for the defaults
***total*** 48341.0 100.0
CXPACKET 30950.0 64.0
NETWORKIO 10440.0 21.6
PAGEIOLATCH_SH 4690.0 9.7
LATCH_EX 1020.0 2.1
WRITELOG 520.0 1.1
PAGEIOLATCH_EX 490.0 1.0
IO_COMPLETION 160.0 .3
PAGEIOLATCH_UP 40.0 .1
PAGEIOLATCH_DT .0 .0
TRAN_MARK_NL .0 .0
TRAN_MARK_KP .0 .0
TRAN_MARK_SH .0 .0
TRAN_MARK_UP .0 .0
TRAN_MARK_EX .0 .0
TRAN_MARK_DT .0 .0
LATCH_DT .0 .0
PAGELATCH_NL .0 .0
PAGELATCH_KP .0 .0
PAGELATCH_SH .0 .0
PAGELATCH_UP .0 .0
PAGELATCH_EX 10.0 .0
PAGELATCH_DT .0 .0
PAGEIOLATCH_NL .0 .0
PAGEIOLATCH_KP .0 .0
PAGESUPP .0 .0
SHUTDOWN .0 .0
CURSOR .0 .0
EXECSYNC .0 .0
LATCH_NL .0 .0
LATCH_KP .0 .0
LATCH_SH .0 .0
LATCH_UP .0 .0
ASYNC_IO_COMPLETION .0 .0
RESOURCE_SEMAPHORE .0 .0
DTC .0 .0
OLEDB .0 .0
FAILPOINT .0 .0
ASYNC_DISKPOOL_LOCK .0 .0
UMS_THREAD .0 .0
PIPELINE_INDEX_STAT .0 .0
PIPELINE_LOG .0 .0
PIPELINE_VLM .0 .0
LOGBUFFER .0 .0
PSS_CHILD .0 .0
EXCHANGE 20.0 .0
XCB .0 .0
DBTABLE .0 .0
EC .0 .0
TEMPOBJ .0 .0
XACTLOCKINFO .0 .0
LOGMGR .0 .0
CMEMTHREAD .0 .0
MISCELLANEOUS .0 .0
LCK_M_SCH_S .0 .0
LCK_M_SCH_M .0 .0
LCK_M_S .0 .0
LCK_M_U .0 .0
LCK_M_X .0 .0
LCK_M_IS .0 .0
LCK_M_IU .0 .0
LCK_M_IX .0 .0
LCK_M_SIU .0 .0
LCK_M_SIX .0 .0
LCK_M_UIX .0 .0
LCK_M_BU .0 .0
LCK_M_RS_S .0 .0
LCK_M_RS_U .0 .0
LCK_M_RIn_NL .0 .0
LCK_M_RIn_S .0 .0
LCK_M_RIn_U .0 .0
LCK_M_RIn_X .0 .0
LCK_M_RX_S .0 .0
LCK_M_RX_U .0 .0
LCK_M_RX_X .0 .0


If you can tell me how to run for an entire day that be great

Thanks
TRACEYSQL
New Member
New Member

--
11 Apr 2007 08:23 AM
Let me try it for 2 hours today and for tomorrow and see what results i get.
Then post back what i get

I did the 20 second ones
***total*** 14521.0 100.0
PAGEIOLATCH_SH 8000.0 55.1
CXPACKET 3720.0 25.6
LATCH_EX 2170.0 14.9
WRITELOG 320.0 2.2
PAGEIOLATCH_EX 260.0 1.8
PAGEIOLATCH_UP 10.0 .1
NETWORKIO 10.0 .1
PAGELATCH_EX 10.0 .1
IO_COMPLETION 20.0 .1
ASYNC_IO_COMPLETION .0 .0

Can you help me understand the statistics
I can look up the wait stats...
The wait time is the amount for the wait stat for 2 hours
so do i divide this by seconds.
TRACEYSQL
New Member
New Member

--
11 Apr 2007 09:25 AM
Ok i be back in a while with the results.

TRACEYSQL
New Member
New Member

--
11 Apr 2007 10:27 AM
it locked up on me blocking itself so i got the last ones
MISCELLANEOUS 1511 10 10 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_SCH_S 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_SCH_M 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_S 40 3010 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_U 1 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_X 1 20000 20000 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_IS 3 980 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_IU 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_IX 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_SIU 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_SIX 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_UIX 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_BU 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RS_S 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RS_U 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RIn_NL 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RIn_S 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RIn_U 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RIn_X 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RX_S 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RX_U 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LCK_M_RX_X 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
SLEEP 9581 6782670 6757220 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
IO_COMPLETION 28545 22960 800 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
ASYNC_IO_COMPLETION 4 9910 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
RESOURCE_SEMAPHORE 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
DTC 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
OLEDB 791 536653632 199140 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
FAILPOINT 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
RESOURCE_QUEUE 90197 20194750 6795190 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
ASYNC_DISKPOOL_LOCK 32 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
UMS_THREAD 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PIPELINE_INDEX_STAT 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PIPELINE_LOG 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PIPELINE_VLM 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
WRITELOG 37639 48860 5270 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LOGBUFFER 80 240 20 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PSS_CHILD 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
EXCHANGE 410 30 10 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
XCB 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
DBTABLE 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
EC 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
TEMPOBJ 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
XACTLOCKINFO 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LOGMGR 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
CMEMTHREAD 112 10 10 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
CXPACKET 18939 4648950 8240 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PAGESUPP 1922 430 270 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
SHUTDOWN 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
WAITFOR 452 6780000 6780000 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
CURSOR 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
EXECSYNC 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_NL 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_KP 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_SH 1 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_UP 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_EX 1631918 435520 150140 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
LATCH_DT 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PAGELATCH_NL 0 0 0 4/11/2007 2:16:22 PM 4/11/2007 2:16:22 PM
PAGELATCH_KP 0 0 0 4/11/2007 2:16:22 PM 4/11
TRACEYSQL
New Member
New Member

--
12 Apr 2007 09:38 AM
6:30
***total*** 95311 100
NETWORKIO 92930 97.5
PAGEIOLATCH_SH 2100 2.2
IO_COMPLETION 90 0.1
PAGEIOLATCH_EX 100 0.1
WRITELOG 80 0.1

9:00
***total*** 118081 100
CXPACKET 55880 47.3
PAGEIOLATCH_SH 28990 24.6
LATCH_EX 22900 19.4
NETWORKIO 7650 6.5
LCK_M_IS 1000 0.8
WRITELOG 810 0.7
PAGEIOLATCH_EX 550 0.5
PAGELATCH_SH 60 0.1
IO_COMPLETION 160 0.1

1:00
***total*** 30761 100
NETWORKIO 15910 51.7
PAGEIOLATCH_SH 6350 20.6
CXPACKET 4290 13.9
LATCH_EX 2430 7.9
PAGEIOLATCH_EX 830 2.7
WRITELOG 730 2.4
IO_COMPLETION 180 0.6
PAGEIOLATCH_UP 20 0.1
PAGELATCH_UP 20 0.1



TRACEYSQL
New Member
New Member

--
12 Apr 2007 01:59 PM
I ran it at varies times of the day for only this command
exec track_waitstats 20,15,'seconds'

Thanks for the information. I will check all tables for missing indexes, and statistics not udpated on them and if they are doing a full scan. I run profiler for long durations to check for this.

Network (the team does not believe this figure but i have to try and see what else i can do to prove the network wait part).

I would like to schedule this to be run every hour do you think just running the command
exec track_waitstats 20,15,'seconds'
every hour will give me a good benchmark that i can run daily and weekly.


Thanks for being patient

Enjoy your drink.
TRACEYSQL
New Member
New Member

--
25 Apr 2007 07:02 AM
I get this today
***total*** 2957315911.0 100.0
OLEDB 2950698240.0 99.8
LCK_M_S 6501260.0 .2
LCK_M_U 30.0 .0
LCK_M_X .0 .0
LCK_M_IS .0 .0
LCK_M_IU .0 .0
LCK_M_IX .0 .0
LCK_M_SIU .0 .0
LCK_M_SIX .0 .0
LCK_M_UIX .0 .0
LCK_M_BU .0 .0
LCK_M_RS_S .0 .0
LCK_M_RS_U .0 .0
LCK_M_RIn_NL .0 .0
LCK_M_RIn_S .0 .0
LCK_M_RIn_U .0 .0
LCK_M_RIn_X .0 .0
LCK_M_RX_S .0 .0
LCK_M_RX_U .0 .0
LCK_M_RX_X .0 .0
IO_COMPLETION 190.0 .0
ASYNC_IO_COMPLETION .0 .0
RESOURCE_SEMAPHORE .0 .0
DTC .0 .0
MISCELLANEOUS .0 .0
LCK_M_SCH_S .0 .0
LCK_M_SCH_M .0 .0
FAILPOINT .0 .0
ASYNC_DISKPOOL_LOCK .0 .0
UMS_THREAD .0 .0
PIPELINE_INDEX_STAT .0 .0
PIPELINE_LOG .0 .0
PIPELINE_VLM .0 .0
WRITELOG 3310.0 .0
LOGBUFFER .0 .0
PSS_CHILD .0 .0
EXCHANGE .0 .0
XCB .0 .0
DBTABLE .0 .0
EC .0 .0
TEMPOBJ .0 .0
XACTLOCKINFO .0 .0
LOGMGR .0 .0
CMEMTHREAD .0 .0
CXPACKET 8640.0 .0
PAGESUPP .0 .0
SHUTDOWN .0 .0
CURSOR .0 .0
EXECSYNC .0 .0
LATCH_NL .0 .0
LATCH_KP .0 .0
LATCH_SH .0 .0
LATCH_UP .0 .0
LATCH_EX 3900.0 .0
LATCH_DT .0 .0
PAGELATCH_NL .0 .0
PAGELATCH_KP .0 .0
PAGELATCH_SH 20.0 .0
PAGELATCH_UP 20.0 .0
PAGELATCH_EX 110.0 .0
PAGELATCH_DT .0 .0
PAGEIOLATCH_NL .0 .0
PAGEIOLATCH_KP .0 .0
PAGEIOLATCH_SH 19360.0 .0
PAGEIOLATCH_UP .0 .0
PAGEIOLATCH_EX 740.0 .0
PAGEIOLATCH_DT .0 .0
TRAN_MARK_NL .0 .0
TRAN_MARK_KP .0 .0
TRAN_MARK_SH .0 .0
TRAN_MARK_UP .0 .0
TRAN_MARK_EX .0 .0
TRAN_MARK_DT .0 .0
NETWORKIO 80090.0 .0



Hmmm
TRACEYSQL
New Member
New Member

--
25 Apr 2007 09:09 AM
The command is does a clear

proc track_waitstats (@num_samples int=10,@delaynum int=1,@delaytype nvarchar(10)='minutes')
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @num_samples is the number of times to capture waitstats, default is 10 times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from sysobjects where name = 'waitstats')
create table waitstats ([wait type] varchar(80),
requests numeric(20,1),
[wait time] numeric (20,1),
[signal wait time] numeric(20,1),
now datetime default getdate())
else truncate table waitstats
dbcc sqlperf (waitstats,clear) -- clear out waitstats
declare @i int,@delay varchar(8),@dt varchar(3), @now datetime, @totalwait numeric(20,1)
,@endtime datetime,@begintime datetime
,@hr int, @min int, @sec int
select @i = 1
select @dt = case lower(@delaytype)
when 'minutes' then 'm'
when 'minute' then 'm'
when 'min' then 'm'
when 'mm' then 'm'
when 'mi' then 'm'
when 'm' then 'm'
when 'seconds' then 's'
when 'second' then 's'
when 'sec' then 's'
when 'ss' then 's'
when 's' then 's'
else @delaytype
end
if @dt not in ('s','m')
begin
print 'please supply delay type e.g. seconds or minutes'
return
end
if @dt = 's'
begin
select @sec = @delaynum % 60
select @min = cast((@delaynum / 60) as int)
select @hr = cast((@min / 60) as int)
select @min = @min % 60
end
if @dt = 'm'
begin
select @sec = 0
select @min = @delaynum % 60
select @hr = cast((@delaynum / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
select 'hh:mm:ss delay time cannot > 23:59:59'
select 'delay interval and type: ' + convert (varchar(10),@delaynum) + ',' + @delaytype + ' converts to ' + @delay
return
end
while (@i <= @num_samples)
begin
insert into waitstats ([wait type], requests, [wait time],[signal wait time])
exec ('dbcc sqlperf(waitstats)')
select @i = @i + 1
waitfor delay @delay
end
select @now=max(now),@begintime=min(now),@endtime=max(now)
from waitstats where [wait type] = 'TOTAL'
--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait time]) + 1 from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','TOTAL', '***total***') and now = @now

-- insert adjusted totals, rank by percentage descending
insert into waitstats select '***total***',0,@totalwait,@totalwait,@now
select 'start, end, duration'='start: ' + convert(varchar(20),@begintime,20) + ' end: ' + convert(varchar(20),@endtime,20) + ' duration (minutes): ' + convert(varchar(10), datediff(mi,@begintime,@endtime))
select 'waitstats samples'=@num_samples, 'delay, type'= convert (varchar(10),@delaynum) + ',' + @delaytype,'waitfor delay interval'=@delay
select [wait type],[wait time],percentage=cast (100*[wait time]/@totalwait as numeric(20,1))
from waitstats
where [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE','TOTAL')
and now = @now
order by percentage desc

GO
You are not authorized to post a reply.
Page 2 of 2 << < 12


Acceptable Use Policy