TSQL Tuesday #88 -Communication is the Key

March 14, 2017

I thank Kennie Nybo Pontoppidan (b|t) for hosting TSQL Tuesday #88. The topic for this month is the daily database-related WT…….

TSQL2SDAY-300x300

Last week I was setting up a collection of wait statistics with five minutes interval using a while loop. I was wrapping up Paul Randal’s (b|t) code from this blog post in a loop and persisting in a table to analyze later.

I made it a habit in my career to have my peers review my code. I sent my code to one of my colleagues and asked him to review it. Meantime I put the code in my test environment before promote to the production server for collection. During my test, I noticed a strange behavior. Interval mentioned in ‘WAITFOR DELAY’ was not consistent. I was getting anything between zero (in minutes) to five. I had no clue at that point what was going on. I sent my code to another person to look at, explaining my observation. Got green signal that the code looks good. Later on, I discovered both of us (person reviewing my code and myself) were testing the same code, in the same environment, at the same time. While the loop was starting at a different time with the same value for ‘WAITFOR DELAY’ and writing result set in the same table.

I was also getting this error because a temporary staging table was being dropped by another running session.

 Msg 208, Level 16, State 1, Procedure rtime_CollectSystemInfo_WaitTypeSummary,
 Line 57 [Batch Start Line 0]
 Invalid object name 'tmp_rtime_SQLskillsStats2'.

Here is a snippet of my code and output.

--This is a modified code from
--http://www.sqlskills.com/blogs/paul/capturing-wait-statistics-period-time/
--This is a snippet of the code and nut the full version
IF EXISTS (SELECT 1
        FROM   sys.tables
        WHERE  NAME = 'tmp_rtime_SQLskillsStats1')
DROP TABLE [tmp_rtime_sqlskillsstats1]; 

IF EXISTS (SELECT 1
        FROM   sys.tables
        WHERE  NAME = 'tmp_rtime_SQLskillsStats2')
DROP TABLE [tmp_rtime_sqlskillsstats2]; 

--init schema
SELECT Getdate() AS [StartTime],
    [wait_type],
    [waiting_tasks_count],
    [wait_time_ms],
    [max_wait_time_ms],
    [signal_wait_time_ms]
INTO   tmp_rtime_sqlskillsstats1
FROM   sys.dm_os_wait_stats
WHERE  1 = 0 

SELECT Getdate() AS [EndTime],
    [wait_type],
    [waiting_tasks_count],
    [wait_time_ms],
    [max_wait_time_ms],
    [signal_wait_time_ms]
INTO   tmp_rtime_sqlskillsstats2
FROM   sys.dm_os_wait_stats
WHERE  1 = 0 

-- get first snapshot
INSERT INTO tmp_rtime_sqlskillsstats1
        (starttime,
            wait_type,
            waiting_tasks_count,
            wait_time_ms,
            max_wait_time_ms,
            signal_wait_time_ms)
SELECT Getdate(),
    [wait_type],
    [waiting_tasks_count],
    [wait_time_ms],
    [max_wait_time_ms],
    [signal_wait_time_ms]
FROM   sys.dm_os_wait_stats 

SET @Delay = '00:' + @WaitMin + ':00' 

WAITFOR delay @Delay 

--get second snapshot
INSERT INTO tmp_rtime_sqlskillsstats2
        (endtime,
            wait_type,
            waiting_tasks_count,
            wait_time_ms,
            max_wait_time_ms,
            signal_wait_time_ms)
SELECT Getdate(),
    [wait_type],
    [waiting_tasks_count],
    [wait_time_ms],
    [max_wait_time_ms],
    [signal_wait_time_ms]
FROM   sys.dm_os_wait_stats
WaitStats

The lesson for myself was to communicate better. When I sent my code for review I should have mentioned the name of the SQL instance where I was testing my code.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.