THE SQL Server Blog Spot on the Web

Welcome to - 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)


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



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



from (select sql_handle



     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)


April 6, 2011 2:41 PM

Brad Schulz said:

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

select substring(t.text



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.


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.



   INTO #x

   FROM sys.tables


ADD PRIMARY KEY (object_id)


   FROM sys.databases






           (x.statement_end_offset - x.statement_start_offset)/2

       ) AS statement_text



       SELECT TOP(1)




       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


           r.session_id = @@SPID

       ORDER BY

           s.statement_start_offset DESC

   ) AS x

   CROSS APPLY sys.dm_exec_sql_text



   ) AS t


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:


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.


February 7, 2014 11:03 AM
New Comments to this post are disabled

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


Privacy Statement