This post is part 6 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.
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 have 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!
Homework
The Who is Active help mode generates the output not by sending back a hardcoded copy of the header text, but rather by grabbing the very same text from the plan cache, parsing it, and then outputting it from there. After writing this piece of Who is Active, I realized that the plan cache DMVs have all sorts of interesting properties, one of them being that each batch is split up in the cache based on start and end statement offsets. Today’s challenge begins with the following batch:
SELECT *
FROM sys.tables SELECT *
FROM sys.databases
--Put your code here
GO
Can you replace the “Put your code here” placeholder with code that uses the plan cache DMVs to output the fact that the last statement run was “SELECT * FROM sys.databases?” This code needs to run in the same batch as the rest of the code—thus the GO at the end of the batch. Post your solution in the comments below.