Hope everyone knows about Adam Mechanic SP_whoisactive stored procedure which is very good for DBA’s to use for finding out more information what is going on Sql Server.
Below is the new link where it is moved to github
From the above link you can download the Stored Procedure and execute it on MASTER database or you can create it in a DBA owned USER DATABASE , I have created in MASTER Database
From the above screen shot you can see the details of SP_WHOISACTIVE stored procedure where we have 24 rows and we are going to pass parameters for fetching the required data using the Parameter_name column as it will be a Lightweight to execute the SP instead of executing without any parameters as it will put some pressure on Sql to get the requested output .
Today I used this SP_WHOISACTIVE stored procedure to capture Blocking information, to do that I followed below steps :
1 Downloaded SP_WHOISACTIVE from github site ( which already there in my local )
2 Executed SP in MASTER Database ( You can create in any Database)
3 Also I created One Table by passing the parameter @return_schema =1 ( This table is to Capture the Info)
4 After creating table we need to Load the Data by running the Stored Procedure with required Parameters
5 After completing above all steps now we are ready to Schedule the Agent job to Capture our Information .
You can get information of each parameter by executing the stored with parameter @help=1
With the help of below script you can get all information of what is going on in sql server instance , you can also remove some parameters which is not required for you and make your custom script .
EXEC sp_WhoIsActive @filter = '', @filter_type = 'session', @not_filter = '', @not_filter_type = 'session', @show_own_spid = 0, @show_system_spids = 0, @show_sleeping_spids = 1, @get_full_inner_text = 0, @get_plans = 0, @get_outer_command = 0, @get_transaction_info = 0, @get_task_info = 1, @get_locks = 0, @get_avg_time = 0, @get_additional_info = 0, @find_block_leaders = 0, @delta_interval = 0, @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', @sort_order = '[start_time] ASC', @format_output = 1, @destination_table = '', @return_schema = 0, @schema = NULL, @help = 0
The script will give you output like below :
Now to capture information in a table to see whenever it is required for us we need to first create table which is a destination table to get all data loaded into it by executing the Store procedure SP_WHOISACTIVE.
Use master -- you can change Database Name go DECLARE @script_table VARCHAR(MAX) EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 -- you can add more parameters here ,@return_schema = 1,@format_output = 1, @schema = @script_table OUTPUT SET @script_table = REPLACE(@script_table, '<table_name>', 'dbo.SPWHOI')--Change Table Name PRINT (@script_table)
Output of above script you will get it in result pane, copy it and past in new window to execute the script .I have executed in MASTER database and the table was created :
CREATE TABLE dbo.SPWHOI ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL, [login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[tasks] varchar(30) NULL,[CPU] varchar(30) NULL, [tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL, [reads] varchar(30) NULL,[writes] varchar(30) NULL,[context_switches] varchar(30) NULL,[physical_io] varchar(30) NULL, [physical_reads] varchar(30) NULL,[query_plan] xml NULL,[locks] xml NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL, [open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL, [program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL, [collection_time] datetime NOT NULL)
After creating Table (SPWHOI) now manually try loading the data and retrieve it to see by using below script :
EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 ,@format_output = 1 ,@destination_table = 'master.dbo.SPWHOI' --truncate table master.dbo.SPWHOI select * from master.dbo.SPWHOI
Now you can schedule Agent job to get instance information loaded into a table for every 5 minutes ( this can be changed based on your need ).when job runs data will be populated into the table and the size of the table will increase so, make sure you have enough space on the disk . Below is the code I used in Agent Jobto capture the information .
EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 --Add more parameters as per your need ,@format_output = 1 ,@destination_table = 'master.dbo.SPWHOI'
Job ran and it loaded data into table SPWHOI
If you see above screenshot there was a blocking SPID recorded in our custom Table SPWHOI and also we can see the execution plan ,sql text, [dd hh:mm:ss.mss] which is Runtime of query, wait_info, blocking_session_id, login_name, hostname…e.t.c.,
You can see only blocking info with runtime of the SPID i.e., how much time it took to execute that script from the custom Table SPWHOI
select start_time ST,collection_time as CT,[dd hh:mm:ss.mss] as Running_time,* from master.dbo.SPWHOI where blocking_session_id <>0 order by Running_time desc