Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

sp_WhoIsActive utility for SQL Server troubleshooting

14 September,2015 by Tom Collins

One of my favourite utilities for Production SQL Server troubleshooting is the sp_WhoIsActive utility.

My main uses are to identify blocking leaders, lock escalation,slow running queries. It is possible to get this information in other queries, but I like the way this utility is easy to deploy and get the critical detail .

Quite often this relieves the pressure – buying me time to apply some root-cause analysis.

The utility is built by Adam Machanic . A big thank you!

My favourite uses for sp_WhoIsActive

Find Blocking leaders

    EXEC sp_WhoIsActive      @find_block_leaders = 1

Returns the same data as in comment header

    EXEC sp_WhoIsActive    @help = 1

Outer ad-hoc query, query plans, full stored procedure or batch

    EXEC sp_whoisactive @get_outer_command = 1,@get_plans=1,@get_full_inner_text=1

Get Locks per request . XML format

    EXEC sp_WhoIsActive    @get_locks = 1

Get waits of all waits pending on a request

  EXEC sp_WhoIsActive     @get_task_info = 2

 

How to download sp_WhoIsActive

Follow this link

Read on how to incorporate sp_whoIsActive with SQL Alerts  How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

 

Read more on troubleshooting

SQL Server has good default values - SQL Server DBA

Storage Checklist FAQ - SQL Server DBA

SQL Server Performance Checklist - SQL Server DBA

TempDB performance and strategy checklist - SQL Server DBA

 

 

 

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on sp_WhoIsActive utility for SQL Server troubleshooting


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer