WhoIsActive In SSMS

Several years ago now, I put together a post on helpful wrapper procedures for calling sp_WhoIsActive. Since then, I’ve taken to three sp_whoisactive calls as SSMS keyboard shortcuts.

Ctrl + F1: What’s Going On?

Here is my bread and butter query in a format you can easily copy and paste:

EXEC sp_whoisactive @get_full_inner_text = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_task_info = 2, @get_transaction_info = 1, @get_plans = 0, @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]', @sort_order = '[session_id]';

And here it is in a little bit nicer of a format so we can cover it:

EXEC sp_whoisactive
	@get_full_inner_text = 1,
	@get_outer_command = 1,
	@find_block_leaders = 1,
	@get_task_info = 2,
	@get_transaction_info = 1,
	@get_plans = 0,
	@output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]',
	@sort_order = '[session_id]';

This call is typically very fast. There are cases where sp_whoisactive can be slow, such as pulling back execution plans. In scenarios where I just want to see what’s going on with a minimum of fuss, I run this command. I’ve ordered the list so that I can see some of the most important columns for me up-front: how long queries have been running, their total resource utilization, reasons for waits, and blocking details. Getting this info, as well as the command, without scrolling is nice when troubleshooting.

Ctrl + 3 For Queries From Me

This second command focuses on my queries. This is useful when looking at busy servers with a lot of activity going and where it might be difficult to figure out which session is actually yours. Here is the copy-pastable block:

EXEC sp_whoisactive @filter_type = 'login', @filter = '{{YOURNAMEHERE}}', @get_full_inner_text = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_task_info = 2, @get_transaction_info = 1, @get_plans = 0, @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]', @sort_order = '[session_id]';

And the block which humans can read:

EXEC sp_whoisactive
	@filter_type = 'login',
	@filter = '{{YOURNAMEHERE}}',
	@get_full_inner_text = 1,
	@get_outer_command = 1,
	@find_block_leaders = 1,
	@get_task_info = 2,
	@get_transaction_info = 1,
	@get_plans = 0,
	@output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]',
	@sort_order = '[session_id]';

The column set is the same as before but now I’m filtering to my login. As a quick note, you’ll obviously want to replace {{YOURNAMEHERE}} with your login.

Ctrl + 4 For Even More

When I need to get execution plans, that’s when I break out Ctrl+4. Here is the one-liner:

EXEC sp_whoisactive @get_full_inner_text = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_task_info = 2, @get_transaction_info = 1, @get_plans = 1, @output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]', @sort_order = '[session_id]';

And the full query:

EXEC sp_whoisactive
	@get_full_inner_text = 1,
	@get_outer_command = 1,
	@find_block_leaders = 1,
	@get_task_info = 2,
	@get_transaction_info = 1,
	@get_plans = 1,
	@output_column_list = '[session_id][login_name][dd%][CPU][reads][writes][wait_info][blocking_session_id][blocked_session_count][sql_text][sql_command][status][tempdb_%][%]',
	@sort_order = '[session_id]';

The big difference here is the addition of @get_plans = 1, which can make the query slower on busy systems. As a result, this is one I’m less likely to run than the others. Still, when you need an execution plan, this is a valuable query to run.

Conclusion: I Ran out of Mnemonics

Those are three of the ways that I like using sp_whoisactive in day-to-day database development and query tuning work. This is one of the most flexible stored procedures out there, but that flexibility can seem overwhelming at first. Hopefully this post gives you a starting point to build out your own helper queries.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s