TSQL Tuesday #88 -Communication is the Key

I thank Kennie Nybo Pontoppidan (b|t) for hosting TSQL Tuesday #88. Topic for this month is 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 my colleague and asked him to review. Meantime I put the code in my test environment before promote to 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 same code, in the same environment, at the same time. While loop was starting at different time with same value for ‘WAITFOR DELAY’ and writing result set in the same table.

I was also getting this error because temporary staging table was being dropped by other 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

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.

FacebookTwitterGoogle+Share