DBA,  Performance

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.

Run the stored procedure without any parameter parameters

You can run the procedure without any parameter. It’s a default mode.

Exec [sp_WhoIsActive]

 

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://sqlblog.com/blogs/adam_machanic/archive/2011/04/06/who-is-active-options-a-month-of-monitoring-part-6-of-30.aspx

 

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://sqlblog.com/blogs/adam_machanic/archive/2011/04/25/capturing-the-output-a-month-of-activity-monitoring-part-25-of-30.aspx

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

Most recent Whoisactive

Updated one with bug fixed – Released on 18-Oct-2016 –>

http://sqlblog.com/blogs/adam_machanic/archive/2016/10/18/sp-whoisactive-the-big-fixes.aspx

 

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.

2. Create the Table:

Pass the parameter that you need with the DB name, table name, it will execute the create table “Whoisactive_lightweight”.

 

DECLARE @script_table VARCHAR(MAX) 

EXEC sp_WhoIsActive

@get_plans = 1,@get_task_info = 2,@get_locks = 1,

@format_output = 0,

@return_schema = 1,

@schema = @script_table OUTPUT 

SET @script_table = REPLACE(@script_table, ‘<table_name>’, ‘dba_data.dbo.Whoisactive_lightweight’)

— PRINT (@script_table)

EXEC(@script_table)

3. Execute the stored procedure to insert data into the table.

It will automatically insert the results from the stored procedure to the given table.

 

EXEC sp_WhoIsActive

@get_plans = 1,@get_task_info = 2,@get_locks = 1,

@format_output = 0,

@destination_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.

 

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 = 0

 

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 Runing_time 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’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

13 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *