How I gather the Wait Stats every hour

The sys.dm_os_wait_stats DMV can be of great value when troubleshooting, or just finding places to improve. However, the DMV displays cumulative values since it was last reset.

Below the code I use to gather the wait stats values since the last time I gathered the stats, without resetting the DMV values.

CREATE PROCEDURE [sta].[spOSWaitstatsCollect]

AS

DECLARE @oswaitstats TABLE

(

[snapshot_time] [datetime]

NOTNULL,

[wait_type] [nvarchar]

(60)NOTNULL,

[waiting_tasks_count] [bigint]

NOTNULL,

[wait_time_ms] [bigint]

NOTNULL,

[max_wait_time_ms] [bigint]

NOTNULL,

[signal_wait_time_ms] [bigint]

NOTNULL

)

 

INSERT  INTO @oswaitstats

SELECT  *

FROM    sta.OSwaitstats

TRUNCATETABLE sta.OSwaitstats

INSERT  INTO sta.OSwaitstats

SELECT  GETUTCDATE(),

*

FROM    sys.dm_os_wait_stats

ORDERBY wait_type

DECLARE @xml ASXML

SELECT  @xml =(SELECT@@servernameAS [@servername] ,

@@servicenameAS [@servicename] ,

[@wait_type]

= n.wait_type ,

[@snapshot_from]

= o.snapshot_time ,

[@snapshot_to]

= n.snapshot_time ,

[@waiting_tasks_count]

= n.waiting_tasks_count

o.waiting_tasks_count ,

[@wait_time_ms]

= n.wait_time_ms o.wait_time_ms ,

[@signal_wait_time_ms]

= n.signal_wait_time_ms

o.signal_wait_time_ms

FROM   sta.OSwaitstats n

INNERJOIN @oswaitstats o ON o.wait_type = n.wait_type

FOR

XMLPATH(‘WaitType’),

ROOT(‘WaitTypes’),

TYPE

)

IFNOT @xml ISNULL

BEGIN

— Send @XML using Service broker here

END

GO

Advertisements

About sqlsmurf
MS SQL Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: