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.
I also like using @get_outer_command=1 in case you have embedded stored procedures.
Thanks Ken! Yes, that will definitely help, when we have embedded stored procedure. I sometimes use that as well. Let me add into the post, which will help new users.
Thanks for your sharing.Already i used whoisactive but not following parameter option to better understanding…Please keep your posting…
Sure! will do. If you need a Whoisactive SP with table creation with passing parameter, let you know. I will email you.
You mentioned that “Add the table name before that dynamic SQL”, but I found there are several places in the SP which have the the word “EXEC sp_executesql”, which one should be used to add the table name tbl_whoisactive. I am confused about this. Thanks.
Hi, Thanks for the update. Let me update something like, Search a word “EXEC sp_executesql” and it will be at the end of the procedure and add the table name.
BTW, the latest version of SP_whoisactive is right here.
I will update the link.
Followed your instruction to create the sp.
and I got the following error message after I ran this statement:
EXEC [sp_WhoIsActive] @get_plans = 1,@get_locks = 1,@get_task_info = 2
Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
I put the statement insert into tbl_whoisactive
just right before the last “EXEC sp_executesql” in the sp_whoisactive.
Could you tell me how to fix it? I am working on SQL server 2008 R2 BTW. Thanks a lot.
The example table, I have shown is for default SP – without any parameter.
B/W the warning can be ignored and column matches you need to add column based on the parameter you use.
I will email you the SP with table creation. Enjoy!
Thanks, Muthu, the SP with the table creation you sent to me is working perfectly. Appreciated a lot.