Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier.
It’s a great script with lots of parameter and I am going to show, which are all more important.
Note: It been moved to – http://whoisactive.com/
Run the stored procedure without any parameter parameters
You can run the procedure without any parameter. It’s a default mode.
I used two table and method to load a data into a tbale – lightweight (minimal parameter) and heavyweight.
Steps to load:
- Download the SP from website
- Create a SP in any of the DB
- Create a table by passing @return_schema = 1
- Load a data into a table by running SP with parameters
- Schedule it in agent job
@get_plans = 1 – It will show the execution plans of the running query.
@get_task_info = 2 – It will show the wait type of the query.
Ex: Parallel CXPACKET waits. With the additional info like, 3 times waited for CXPACKET number os milliseconds: (Nx: MINms/AVGms/MAXms)[wait_type]
@get_locks = 1 – It will show an XML column output locks each query, useful for blocking situation.
Other useful parameters – With little overhead
Sometimes, i use these parameters, when I want to do further more analysis.
@delta_interval = 5 – It will capture the delta interval time period of the usage, metrics for more accurate data, not the cumulative one. It will take 5 seconds to return the results.
@get_additional_info = 1 – It will show the default output plus several more than can be dynamically enabled and disabled options.
@get_avg_time = 1 – It will show the query average run time, which will help to find the query runs in each time differently.
@get_outer_command = 1 – It will show the outer batch query/SP, which will help to get the complete query.
Check this for more parameters: http://whoisactive.com/docs/
How effectively use this stored procedure
By default, this stored procedure will show the results in the SSMS. It is useful in the run time check.
In general, the performance reports need to be captured all the time or we do not know, when the issue is occurring.
As every DBAs do, create a table and insert the results in the table. SQL agent job is best for capturing the data in the interval and schedule this SP job every 15 minutes. Sometimes with more parameters which take more time for this SP, can be changed depends on the execution time of the procedure.
Following is the table and stored procedure script.
Updated: I have added this after a reply from Fei Yuan- http://whoisactive.com/docs/25_capturing/
How to create a table for the parameters you pass.
You can get the table code from the “@script_table” parameter and I un-commented the print statement. So it will automatically create the table from Exec, with the name you provided in the SET @script_table = REPLACE(@script_table, ‘<table_name>’, ‘dba_data.dbo.Whoisactive_lightweight‘).
It is a very, very long script and please download from whoisactive.com:
Download the original Whoisactive procedure:
- First download the stored procedure and run in your DBA database. Since, if you collecting more data, it will occupy more disk table space. Get the parameter you needed and add into the following code block and it will return the table schema creation script based on the parameter that you pass.
- Create the Table:
Pass the parameter that you need with the DB name, table name, it will execute the create table “Whoisactive_lightweight”.
use master go create database DBA_Data go -- create SP of whoisactive -- we need to pass ,@return_schema = 1 t create a table drop table Whoisactive_lightweight go use DBA_Data go DECLARE @script_table VARCHAR(MAX) EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 ,@return_schema = 1,@format_output = 1, @schema = @script_table OUTPUT SET @script_table = REPLACE(@script_table, '<table_name>', 'dba_data.dbo.Whoisactive_lightweight') PRINT (@script_table) EXEC(@script_table)
- Execute the stored procedure to insert data into the table.
It will automatically insert the results from the stored procedure to the given table.
-- load it to table -- we need to pass ,@return_schema = 0 to load to a table EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 ,@format_output = 1 ,@destination_table = 'dba_data.dbo.Whoisactive_lightweight' --truncate table dba_data.dbo.Whoisactive_lightweight select * from dba_data.dbo.Whoisactive_lightweight
You can also run in the SSMS without inserting into a table, by adding multiple parameters.
-- just to view in ssms EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1 ,@delta_interval = 5,@get_additional_info = 1,@get_avg_time = 1 ,@get_outer_command = 1,@format_output = 1 drop table Whoisactive_H_weight go -- load with extra parameters DECLARE @script_table VARCHAR(MAX) EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1,@delta_interval = 5,@get_additional_info = 1,@get_avg_time = 1, @get_outer_command = 1,@return_schema = 1,@format_output = 1, @schema = @script_table OUTPUT SET @script_table = REPLACE(@script_table, '<table_name>', 'dba_data.dbo.Whoisactive_H_weight') PRINT (@script_table) EXEC(@script_table) -- load to table EXEC sp_WhoIsActive @get_plans = 1,@get_task_info = 2,@get_locks = 1,@delta_interval = 5,@get_additional_info = 1,@get_avg_time = 1 ,@get_outer_command = 1,@format_output = 1 ,@destination_table = 'dba_data.dbo.Whoisactive_H_weight' select * from dba_data.dbo.Whoisactive_H_weight
The important column that we mostly look:
Runing_time ([dd hh:mm:ss.mss]),wait_info,sql_text,tasks,
A sample select statement: You can select the table and filter conditions based on, the results. I captured one of my production system and there are many quires running more than one hour. Those are all tuned and rewritten 🙂
select start_time ST,collection_time as CT,* from master..tbl_whoisactive where login_name not in ('NT AUTHORITY\SYSTEM','DBA','sqlserver.service') --where blocking_session_id <>0 order by 3 desc
Once again, Thanks to Adam Machanic. Your script helped me all the time. Managing 100+ DB servers which has more performance issue all the week, are tough job and you script gather all the data every 15 minutes and easy for me to review later and give recommendations and fine tuning.