SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

You can use sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.  

In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.  

Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here: sp_whoisactive downloads

Sp_BlitzWho is a procedure written by the Brent Ozar team. It can be found here on Github: BrentOzarULTD/SQL-Server-First-Responder-Kit 

So in the below picture some monitoring software stated that the start time of a blocking victim occured before the lead blocker started.  How is this possible?  It's not.  It was simply using logon_time of the SPID.   I would think that the victim or the Blocked SQL fragment Start time: would be better represented with the Last_Batch column(Last time a client process executed a remote stored procedure call or an EXECUTE statement.) and not the login_time (Time at which a client process logged into the server.)  Issue submitted to vendor. DONE

-- Create a Table for Inserts named WhoIsActive_
DECLARE @destination_table VARCHAR(4000);
SET @destination_table = N'WhoIsActive_' + CONVERT( VARCHAR, GETDATE(), 112 );
DECLARE @schema VARCHAR(4000);
EXEC sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @find_block_leaders = 1, @get_outer_command = 1,@RETURN_SCHEMA = 1
                   ,@SCHEMA = @schema OUTPUT;
SET @schema = REPLACE( @schema, '<table_name>', @destination_table );
EXEC (@schema)
GO

-- This will capture 5 seconds intervals over a duration of 65 seconds to capture 1 full minute of data
-- If you see the statement terminated with msg maximum recursion 100 has been exhausted
-- You can set up your table and run your loop with @find_block_leaders set to 0.
-- You still get the blocking_session_id column and it’s data. You just won’t get the blocked_session_count column.
DECLARE @destination_table VARCHAR(4000), @msg NVARCHAR(4000);
SET @destination_table = N'WhoIsActive_' + CONVERT( VARCHAR, GETDATE(), 112 );
DECLARE @numberOfRuns INT;
SET @numberOfRuns = 13;
WHILE @numberOfRuns > 0
    BEGIN;
        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @find_block_leaders = 1, @get_outer_command = 1
                               ,@DESTINATION_TABLE = @destination_table;
        SET @numberOfRuns = @numberOfRuns - 1;
        IF @numberOfRuns > 0
            BEGIN
                SET @msg = CONVERT( CHAR(19), GETDATE(), 121 ) + N': ' + N'Logged info. Waiting...';
                RAISERROR( @msg, 0, 0 ) WITH nowait;
                WAITFOR DELAY '00:00:05';
            END;
        ELSE
            BEGIN
                SET @msg = CONVERT( CHAR(19), GETDATE(), 121 ) + N': ' + N'Done.';
                RAISERROR( @msg, 0, 0 ) WITH nowait;
            END;
    END;
GO

-- Find your data and omit the unneeded noise from the result set
DECLARE @destination_table NVARCHAR(4000), @dSQL NVARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;
SET @dSQL = N'SELECT collection_time, * FROM dbo.' +
 QUOTENAME(@destination_table) + N' where database_name not in (''msdb'',''master'') '
 + N' and program_name <>''SQL Monitor - Monitoring'''
 + N' order by 1 desc';
--print @dSQL
EXEC sp_executesql @dSQL
GO

And finally in the below picture above you can see that we have a collection time and we can subtract the duration of SPID 192 to get the exact start time as well as the sql_text column actually captured the command where the monitoring software did. Issue submitted to vendor. DONE 

Please remember to drop or truncate any table as needed when completed.

-- This code will truncate the table data - Update the word Truncate to Drop if needed
DECLARE @destination_table NVARCHAR(2000), @dSQL NVARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;
SET @dSQL = N'Truncate Table dbo.' +
QUOTENAME(@destination_table);
print @dSQL
EXEC sp_executesql @dSQL