THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Who is Active: Options (A Month of Monitoring, Part 6 of 30)

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:

F6_01_help

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.

Published Wednesday, April 06, 2011 11:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Brad Schulz said:

Can we turn in our homework here?

This will output the previous statement in the batch, "SELECT * FROM sys.databases":

(Hope the formatting comes out okay)

select substring(text

               ,(StOffset+2)/2

               ,(EnOffset-StOffset)/2)

from (select sql_handle

           ,StOffset=max(statement_start_offset)

           ,EnOffset=max(statement_end_offset)

     from sys.dm_exec_query_stats

     where sql_handle=(select sql_handle

                       from sys.dm_exec_requests

                       where session_id=@@spid)

     group by sql_handle) x

cross apply sys.dm_exec_sql_text(sql_handle)

--Brad

April 6, 2011 2:41 PM
 

Brad Schulz said:

Actually, I made it more compact... Here's solution #2:

select substring(t.text

               ,(s.statement_start_offset+2)/2

               ,(s.statement_end_offset-s.statement_start_offset)/2)

from sys.dm_exec_requests r

join sys.dm_exec_query_stats s on r.sql_handle=s.sql_handle

cross apply sys.dm_exec_sql_text(r.sql_handle) t

where r.session_id=@@spid

 and s.statement_end_offset=r.statement_start_offset-2

April 6, 2011 2:53 PM
 

martinz said:

Great method of setting up a custom script. I've just added it to template explorer in SSMS

April 6, 2011 5:22 PM
 

Adam Machanic said:

Check out the big brain on Brad! :-)

April 7, 2011 9:17 AM
 

Brad Schulz said:

Even though it's nice and compact, and even though it works in this specific example, my second solution will not work overall, because it assumes that whatever "statement" came before it will be IMMEDIATELY before it in the batch (see below).

My first solution mirrors the solution you posted in Part 7 of your series (though I think your use of TOP is better than my GROUP/MAX approach).

But in ALL of these cases, what we're really looking at is not the previous statement, but rather the previous CACHED statement.

If the statement before any of our solutions was just something simple that SQL would not cache (like a variable assignment or something like SELECT 'Adam'), then we will not pick that up.  Your solution (and my first solution) would pick up whatever previously CACHED statement exists before that statement... and my second solution would return nothing.

Anyway, thanks for the homework... it was fun.

--Brad

April 7, 2011 11:07 AM
 

Adam Machanic said:

This is true, Brad. But the situation is actually even worse than you imagine. There are entire classes of statements that will cause *nothing* to cache, in the entire batch. ALTER TABLE is one such statement.

Try:

   SELECT *

   INTO #x

   FROM sys.tables

ALTER TABLE #x

ADD PRIMARY KEY (object_id)

   SELECT *

   FROM sys.databases

   SELECT

       SUBSTRING

       (

           t.text,

           x.statement_start_offset/2,

           (x.statement_end_offset - x.statement_start_offset)/2

       ) AS statement_text

   FROM

   (

       SELECT TOP(1)

           s.sql_handle,

           s.statement_start_offset,

           s.statement_end_offset

       FROM sys.dm_exec_requests AS r

       INNER JOIN sys.dm_exec_query_stats AS s ON

           s.sql_handle = r.sql_handle

           ANd s.statement_start_offset < r.statement_start_offset

       WHERE

           r.session_id = @@SPID

       ORDER BY

           s.statement_start_offset DESC

   ) AS x

   CROSS APPLY sys.dm_exec_sql_text

   (

       x.sql_handle

   ) AS t

   GO

April 7, 2011 11:44 AM
 

Brad Schulz said:

Hmmm... Interesting... Thanks for adding that.

April 7, 2011 1:01 PM
 

Ebis said:

In the help screen I miss the actual version of the actual sp_who_is_Active Procedure... but only a minor-minor-minor request

April 8, 2011 4:55 AM
 

Adam Machanic said:

This post is part 7 of a 30-part series about the Who is Active stored procedure. A new post will run

April 20, 2011 10:40 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM
 

Mike said:

The Who is Active stored procedure is easily one of if not the greatest tuning tools in my arsenal. Thanks for putting so much time and effort into this Adam. I use it daily and couldn't live without it

September 14, 2013 11:06 PM
 

Alan said:

Hi Adam

thanks for the good tip and the insight for that alter table causing batch sql not being cached. I have a question when the query is executing within a batch is the sql_handle/plan_handle in dm_exec_session relate to previous finished query or the current running query? this is due to when troubleshoot procedure plan change we found not able to capture the plan correctly if using dm_exec_query_stats cross apply dm_exec_query_plan since it's for cached, but activity monitor seems able to get the runtime query/plan so what's the DMV it's using.

February 6, 2014 5:44 PM
 

Adam Machanic said:

@Alan

If the plan changes, the new plan will be cached, so I'm not sure what you're seeing. Can you post a repro?

How does Who is Active behave in this situation?

Also, how do you get a plan via Activity Monitor? I don't see that option on this end.

--Adam

February 7, 2014 11:03 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement