How I use sp_WhoIsActive

sp_whoisactive is a nice little tool that has lately been updated by Adam Machanic.

If you work with SQL Server there is no chance you might have missed it. Actually if you don’t know this tool you haven’t play around with SQL Server enough.

Lately, on BrentOzar.com, I was reading a post and I found one of the question very intriguing: what are your favorite options to use with sp_whoisactive?

My favorite option is…

All the options are very good but my favorite is @destination_table: when used on a job this option allows you to save the output to a table for further investigation.

Let’s create a database and a table

First let’s create a database and a table that will store the data for us:

CREATE DATABASE Monitoring

CREATE TABLE [dbo].[WhoIsActive](
	[dd hh:mm:ss.mss] [varchar](8000) NULL,
	[session_id] [smallint] NOT NULL,
	[sql_text] [xml] NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[wait_info] [nvarchar](4000) NULL,
	[tran_log_writes] [nvarchar](4000) NULL,
	[CPU] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[blocking_session_id] [smallint] NULL,
	[reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[query_plan] [xml] NULL,
	[used_memory] [varchar](30) NULL,
	[status] [varchar](30) NOT NULL,
	[tran_start_time] [datetime] NULL,
	[implicit_tran] [nvarchar](3) NULL,
	[open_tran_count] [varchar](30) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Let’s create a job to store the data

We now have to fill up that table.

Go to SQL Server Agent > Jobs, and create a job called Feed_WhoIsActive and in the steps put this code:

EXEC sp_WhoIsActive @get_plans = 1
	,@get_transaction_info = 1
	,@destination_table = 'Monitoring.dbo.WhoIsActive'

And set the job to run every minute.

Inserting 1440 rows per day is enough. the table will become quite heavy as it has to store the query plan. It depends by how much your database is under pressure but it might get quite large.

Let’s create a cleanup job

We don’t want to store every single day. Storing the last 10 days in enough.

Let’s create a second job called Clean_WhoIsActive that will delete all data older than 10 days.

--Delete from logging table. 
DECLARE @retention INT = 10;
DECLARE @retention_calc DATETIME = DATEADD(DAY, - @retention, GETDATE())

--PRINT @retention_calc  
DELETE
FROM [Monitoring].[dbo].[WhoIsActive]
WHERE [collection_time] < @retention_calc

And set this to run only once per day.

Conclusion

sp_whoisactive is a powerful tool and it’s very basic.

Install it today and get familiar with it.

There is no PowerBI dashbord out there, I might create one in my spare time. But I still have 9 draft to post on my blog…

 

Related

Leave a Reply

You have to agree to the comment policy.