Sql server Blog Forum Helping SQL server DBAs and Developers

18Jul/1611

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.

 

Muthukkumaran Kaliyamoorthy

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

More Posts

Comments (11) Trackbacks (2)
  1. I also like using @get_outer_command=1 in case you have embedded stored procedures.

  2. Hi Muthu,

    Thanks for your sharing.Already i used whoisactive but not following parameter option to better understanding…Please keep your posting…

  3. HI Muthu,

    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.

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


Leave a comment