Using sp_WhoIsActive with Temp Tables to Limit Data Collected

The free sp_WhoIsActive procedure by Adam Machanic can be very useful for monitoring SQL Server: I’m a big fan.

But I’ve learned the hard way that it’s easy to collect too much information with sp_WhoIsActive, particularly if you set up jobs where you have the procedure log data to a table for later review. Collecting the text of SQL Server queries, their execution plans, and more, can take up a lot of room, and I’ve managed to fill up a server drive or two with a little over-eager collection. Whoops!

Sometimes it can be useful to store the results of sp_WhoIsActive in a temporary object, and review the contents before deciding whether or not to store the data permanently.

Or alternately, you might want to do this to programmatically review the results of sp_WhoIsActive at the current moment to check whether you want to fire off an alert based on different criteria.

Because of scoping issues, regular temp tables don’t really work well for this. But you can easily use either a global temporary table, or a short-lived table in a user database.

An example: sessions with tempdb_allocations > 0

Let’s say I want to periodically sample how many tempdb_allocations are being used by different sessions, and collect the query text and query plan if they are available. I only want to keep data for sessions who have tempdb_allocations > 0 permanently.

I could do something like this…

Step 1) Create a permanent table to store the info I care about

The first step is to create a regular database table named dbo.WhoIsActive_tempdb, where we’ll store data for review:

/* This assumes I have a database named dba where I can store monitoring info. 
The code in this post doesn't delete or limit the data you put into this table, so make sure
you either clean it out, have plenty space, or don't insert enough to cause you problems */

use dba;
GO

DECLARE @whoisactive_table VARCHAR(4000) ;
DECLARE @schema VARCHAR(4000) ;

SET @whoisactive_table = QUOTENAME ('dbo') + '.' + QUOTENAME ('WhoIsActive_tempdb')

EXEC sp_WhoIsActive
  @output_column_list = '[tempdb%][%]',
  @get_plans = 1,
  @return_schema = 1,
  @format_output = 0,
  @schema = @schema OUTPUT ;
SET @schema = REPLACE(@schema, '<table_name>', @whoisactive_table) ;

EXEC(@schema) ;

I used the @schema parameter to have sp_WhoIsActive generate the schema for the table itself. Full instructions on doing this by Adam are here.

Since I care about tempdb in the case of this example, I used @output_column_list to specify that those columns should come first, followed by the rest of the columns.

I also elected to set @get_plans to 1 to get query execution plans if they’re available. That’s not free, and they can take up a lot of room, but they can contain a lot of helpful info.

Step 2, Option 1) Sample sp_WhoIsActive into a global temp table, then insert only rows we care about into the permanent table

If we only want to store rows where tempdb_allocations > 0 in the permanent table, then one way to do this is to:

• Create a temporary table each time we run sp_WhoIsActive
• Insert the current sample into that table
• Insert the rows we want to keep permanently into the ‘real’ table

Because of scoping reasons with dynamic SQL, this isn’t easy to do with a normal temporary table. One relatively easy workaround for that is to use a global temporary table. To reduce the possibility of name collisions (like if you have a job running this, and you accidentally run it yourself in a session at the same time), is to use a GUID in the name of the temporary table, like this:

DECLARE @whoisactive_table VARCHAR(4000) ;
DECLARE @schema VARCHAR(4000) ;
DECLARE @dsql NVARCHAR(4000) ;

SET @whoisactive_table = QUOTENAME ('##WhoIsActive_' + CAST(NEWID() as varchar(255)));

EXEC sp_WhoIsActive
  @output_column_list = '[tempdb%][%]',
  @get_plans = 1,
  @return_schema = 1,
  @format_output = 0,
  @schema = @schema OUTPUT ;
SET @schema = REPLACE(@schema, '<table_name>', @whoisactive_table) ;
PRINT @schema
EXEC(@schema) ;

EXEC sp_WhoIsActive
  @output_column_list = '[tempdb%][%]',
  @get_plans = 1,
  @format_output = 0,
  @destination_table = @whoisactive_table;

SET @dsql = N'
INSERT dbo.WhoIsActive_tempdb
SELECT *
FROM ' + @whoisactive_table + N' 
WHERE tempdb_allocations > 0
OPTION (RECOMPILE);'

EXEC sp_executesql @dsql;

SET @dsql = N'DROP TABLE ' + @whoisactive_table + N';'

In this example, I’m running sp_WhoIsActive with the same options I used before. I first use @schema to create a global temporary table with a name like [##WhoIsActive_E91F175D-C09A-44E7-98E7-10A18E038873].

I then select rows from the temp table who have tempdb_allocations > 0, and insert them into dbo.WhoIsActive_tempdb.

But… what if we want to use less tempdb?

That’s a great question.

Let’s say that you’re firing this whole thing off at a time when tempdb is filling up fast, or maybe at a time when you might have allocation contention in tempdb. We can’t prevent our monitoring queries from using tempdb altogether, but maybe we don’t want to create temp tables.

No problem. You can use slightly different code to store sp_WhoIsActive results in a short lived permanent table in the dba database.

Step 2, Option 2) Short lived permanent table in a user database

The only real difference in this code from the second sample above is the “use dba” statement, and a change in the name of @whoisactive_table:

use dba;
GO

DECLARE @whoisactive_table VARCHAR(4000) ;
DECLARE @schema VARCHAR(4000) ;
DECLARE @dsql NVARCHAR(4000) ;

SET @whoisactive_table = QUOTENAME ('dbo') + '.' + QUOTENAME ('WhoIsActive_' + CAST(NEWID() as varchar(255)));

EXEC sp_WhoIsActive
  @output_column_list = '[tempdb%][%]',
  @get_plans = 1,
  @return_schema = 1,
  @format_output = 0,
  @schema = @schema OUTPUT ;
SET @schema = REPLACE(@schema, '<table_name>', @whoisactive_table) ;
PRINT @schema
EXEC(@schema) ;

EXEC sp_WhoIsActive
  @output_column_list = '[tempdb%][%]',
  @get_plans = 1,
  @format_output = 0,
  @destination_table = @whoisactive_table;

SET @dsql = N'
INSERT dbo.WhoIsActive_tempdb
SELECT *
FROM ' + @whoisactive_table + N' 
WHERE tempdb_allocations > 0
OPTION (RECOMPILE);'

EXEC sp_executesql @dsql;

SET @dsql = N'DROP TABLE ' + @whoisactive_table + N';'
GO

When run this way, sp_WhoIsActive still allocates resources in tempdb (be aware that some of that overhead is just part of using a complex query like this), but the object creation is kept in the user database.

Sometimes, you can simply use a filter built into sp_WhoIsActive!

Want to see data just for a single login, program name, database, host name, or session?

Or just want to exclude something based on login, program name, database, host name, or session?

In those case, you might simply use the @filter_type and @filter parameters on sp_WhoIsActive and save yourself some complexity. Here’s the documentation on how to do that.

Happy monitoring!