Whoisactive capturing a SQL server queries Performance Tuning

 

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.

Exec [sp_WhoIsActive]

I used two table and method to load a data into a table – lightweight (minimal parameter) and heavyweight.

Steps to load:

1. Download the SP from website
2. Create a SP in any of the DB
3. Create a table by passing @return_schema = 1
4. Load a data into a table by running SP with parameters
5. Schedule it in agent job

Lightweight parameters

@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:

Most recent Whoisactive

Download the original Whoisactive procedure:

1. 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.

 

For lightweight:

1. 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,@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_lightweight')

PRINT (@script_table)

EXEC(@script_table)

 

1. 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,@get_outer_command = 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

 

For Heavyweight:

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,

blocking_session_id,query_plan,locks,request_id

 

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.