What fun would life be without lots of choices?
Activity monitoring, like life, is much more fun (and effective) when you can pick and choose from a variety of options. And the opposite is true: a lack of options makes for a dull, ineffective, one-trick solution. Case in point? sp_who and sp_who2 had only one parameter each. The super-dull "@loginame [sic]." Yes, those crazy kids at Microsoft left out an "n," but it's not like the parameter was well-named. After all, it wasn't just a filter on a login name; it could also be a SPID, or it could be the word "active," which would make it show all sessions that weren't sleeping (including system sessions). Thanks for the straightforward user experience, Microsoft!
Unlike its predecessors, Who is Active gives you plenty of options with which to work. The procedure has 24 parameters as of the time of this writing; here they are, with their default values:
@filter sysname = ''
@filter_type VARCHAR(10) = 'session'
@not_filter sysname = ''
@not_filter_type VARCHAR(10) = 'session'
@show_own_spid BIT = 0
@show_system_spids BIT = 0
@show_sleeping_spids TINYINT = 1
@get_full_inner_text BIT = 0
@get_plans TINYINT = 0
@get_outer_command BIT = 0
@get_transaction_info BIT = 0
@get_task_info TINYINT = 1
@get_locks BIT = 0
@get_avg_time BIT = 0
@get_additional_info BIT = 0
@find_block_leaders BIT = 0
@delta_interval TINYINT = 0
@output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'
@sort_order VARCHAR(500) = '[start_time] ASC'
@format_output TINYINT = 1
@destination_table VARCHAR(4000) = ''
@return_schema BIT = 0
@schema VARCHAR(MAX) = NULL OUTPUT
@help BIT = 0
I could write a few sentences about each option, but I've already done that. You can find short descriptions in the comment header I've included with Who is Active. But I don't want you to have to print that out, and neither should you be expected to memorize 24 parameters (at least, not right away). Instead of doing either of those things, focus your attention on the most important parameter of all: the last one.
@help, when set to 1, causes the procedure to return the same text found in the comment header, nicely formatted (well, sort of) in the SSMS results pane. Like this:
There are two results in the output: the first contains information about all of the available input parameters; the second contains information about all of the output columns.
Once you understand the options fairly well, you can use the help in another way, to set up your own custom script to call Who is Active. Select the first column (click the header that says “parameter”), and use CTRL-C and CTRL-V to copy and paste the text into a new window. Add a call to Who is Active, remove a few lines, pop in a few commas, and you’ll have something like this:
EXEC sp_WhoIsActive
@filter = '',
@filter_type = 'session',
@not_filter = '',
@not_filter_type = 'session',
@show_own_spid = 0,
@show_system_spids = 0,
@show_sleeping_spids = 1,
@get_full_inner_text = 0,
@get_plans = 0,
@get_outer_command = 0,
@get_transaction_info = 0,
@get_task_info = 1,
@get_locks = 0,
@get_avg_time = 0,
@get_additional_info = 0,
@find_block_leaders = 0,
@delta_interval = 0,
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
@sort_order = '[start_time] ASC',
@format_output = 1,
@destination_table = '',
@return_schema = 0,
@schema = NULL,
@help = 0
Save this to a script, and you’ll be able to easily and quickly call the procedure using any of the parameter combinations you like, without having to type them all in every time. It’s almost like intellisense that actually works!