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.

Capturing the Output (A Month of Activity Monitoring, Part 25 of 30)

This post is part 25 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.


Yesterday’s post was about configuring the output based on what you want to see. Today’s post is about taking that perfect output and persisting it.

There are many reasons that you might like to store the results of a Who is Active call to a table. Some of the real use cases that I’ve been told about by Who is Active users include:

  • Scheduled monitoring. Capturing the results of Who is Active calls in 5 or 10 minute intervals to see what’s happening on the database server throughout the day (or night)
  • Using it as part of a build process, to verify that the correct things are happening in the correct order at the correct times
  • Using it as part of an exception handling system that automatically calls Who is Active every time an error occurs, to snapshot the current state of the database instance

And there are various other use cases in addition to these. The point is that there are a number of reasons that you might want to capture the output.

Unfortunately, it’s not as simple a task as you might think. The first time I tried to make it work, I did something like:

CREATE TABLE #output
(
    ...
)

INSERT #output
EXEC sp_WhoIsActive

This approach failed miserably. If you try it, as I did, you’ll see the following error message:

Msg 8164, Level 16, State 1, Procedure sp_WhoIsActive, Line 3086
An INSERT EXEC statement cannot be nested.

Who is Active uses a number of INSERT EXEC statements, and they cannot be easily changed or removed, so for a while it seemed like all was lost. After a bit of brainstorming, however, I realized that I could simply build yet another INSERT EXEC into Who is Active—one that will insert into a table of your choice.

Of course, first you need a table. And if you’ve been reading this series you’re no doubt aware that the output shape returned by Who is Active is extremely dynamic in nature, and depends on which parameters are being used. So the first option I added was a method by which you can get the output schema. Two parameters are involved: If @return_schema is set to 1, the schema shape will be returned in an OUTPUT parameter called @schema. This is best shown by way of example:

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @output_column_list = '[temp%]',
    @return_schema = 1,
    @schema = @s OUTPUT

SELECT @s

The idea is that you set up your Who is Active call with all of the options you’d like, then bolt on the @return_schema and @schema parameters. Here the column list is being restricted to only those columns having to do with tempdb. If you run this code, the SELECT will return the following result:

CREATE TABLE <table_name> ( [tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL)

This result can be modified by replacing the “<table_name>” placeholder with the name of the table you actually want to persist the results to. Of course this can be done either manually or automatically—after the call to Who is Active, the text is sitting in a variable, so a simple call to REPLACE is all that’s needed. That call could even be followed up by a call to execute the result and create the table...

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @output_column_list = '[temp%]',
    @return_schema = 1,
    @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.monitoring_output')

EXEC(@s)

Of course now you probably want to put something into the table. Crazy! To do this, drop the @return_schema and @schema parameters and replace them with @destination_table—the name of the table into which the results should be inserted:

EXEC sp_WhoIsActive
    @output_column_list = '[temp%]',
    @destination_table = 'tempdb.dbo.monitoring_output'

Now the results of the call will be inserted into the destination table. Just remember that every time you change the Who is Active options, you’ll have to re-acquire the output shape. Even a small change, such as adding an additional column to the output list, will result in a catastrophic error.

EXEC sp_WhoIsActive
    @output_column_list = '[session_id][temp%]',
    @destination_table = 'tempdb.dbo.monitoring_output'

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

How far you take this feature depends on how creative you are. Some of you have come up with elaborate schemes, but I generally keep it simple. Something that I like to do is to set up a short semi-automated process by using Management Studio’s GO [N] option. I use this when I’m doing intense debugging, and will do something like:

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @format_output = 0,
    @return_schema = 1,
    @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.quick_debug')

EXEC(@s)
GO

EXEC sp_WhoIsActive
    @format_output = 0,
    @destination_table = 'tempdb.dbo.quick_debug'

WAITFOR DELAY '00:00:05'
GO 60

This will first create a table in tempdb, after which it will collect the results every five seconds for a five-minute period. I set @format_output to 0 in order to get rid of the text formatting so that I can more easily work with the numeric data. The results can be correlated to performance counters or other external information using the [collection_time] column, which was added to Who is Active specifically to support automated data collection.

 

Homework

Share your experiences with Who is Active’s data collection feature. How are you using it? Have you hit any problems or roadblocks? Any awesome success stories? A few sentences will be great, and more is fine if you’re in the mood to tell a story.

Published Monday, April 25, 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

 

Antony said:

Hi Adam,

enjoying this series - makes me think i've only scratched the surface of how to use this procedure!

I haven't had a chance to look into the cause, but I've experienced a bug using 10.83 & 10.99 on 2005 (SP3 & SP4) and 2008 R2 (RTM) (not used it on any other environments yet):

When I have a service broker activation procedure running, the program_name for ALL the sessions in the result set is the information regarding the activation procedure. As soon as this procedure stops executing, the program_name column returns to normal for the other sessions.

April 25, 2011 2:00 PM
 

Adam Machanic said:

Thanks for reporting this, Antony. I've fixed the issue on my end, so it won't be a problem in the next release.

Best,

Adam

April 25, 2011 4:10 PM
 

Antony said:

Thanks Adam. Having Activation procedures visible by default is going to be very useful in our environment.

April 26, 2011 4:22 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
 

Kukah said:

Hi Adam,

I've noticed a minor difference when outputing WIA into a table.

[sql-text] is no longer accessible with XML hyperlink.

Other XML hyperlinks like [query_plan] (@get_plans), or [additional_info] (@get_additional_info) work fine.

I though you might want to be aware of this.

Kukah

ps:here is the script i used

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive

@get_additional_info = 1,    

@get_plans = 1,

   @format_output = 0,

   @return_schema = 1,

   @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.quick_debug')

EXEC(@s)

GO

EXEC sp_WhoIsActive

@get_additional_info = 1,    

@get_plans = 1,

@format_output = 0,

   @destination_table = 'tempdb.dbo.quick_debug'

WAITFOR DELAY '00:00:05'

GO 10

September 17, 2012 8:55 PM
 

Adam Machanic said:

Hi Kukah,

It's because you have @format_output set to 0. That turns off the XML formatting. (In addition to numeric formatting for various columns.)

--Adam

September 17, 2012 10:07 PM
 

Kukah said:

That's correct Adam.

Thanks for the answer.

I should have thought about that.

I guess I confused myself because other XML hyperlinks were available.

Kukah

September 19, 2012 11:42 AM
 

Danny said:

Hi Adam,

I have tried your examples above on SQL Server 2005 SP4 and I get the following warning.

Warning: Null value is eliminated by an aggregate or other SET operation.

Can you please provide some help.

Thanks,

Danny

May 9, 2013 9:11 PM
 

Adam Machanic said:

@Danny: That's not anything to worry about. It's just an informational message from the query processor.

May 12, 2013 3:46 PM
 

unique fun office cubicle accessories cubicle decor said:

Adam Machanic : Capturing the Output (A Month of Activity Monitoring, Part 25 of 30)

October 19, 2014 8:19 PM

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