Track sp_whoisactive over time

This has been blogged about elsewhere, but here is what I am using to track sp_whoisactive over time.

But first, the reason I do this is that when a server goes down, someone always blames SQL Server, saying it had a lot running. Now, I can’t reach into the past and see what was running without implementing something manually. So I want to schedule this on a regular basis.  I have it going every 5 minutes on production.    If nothing was happening, then I can show proof that SQL Server was not causing an issue.  Also, there are sometimes long running transactions or blocking processes in the middle of the night, and it’s nice to go find out what those were.

1. Create the table that will track the info.   I personally use the Admin database, which contains all of my DBA utility tables.


CREATE TABLE [dba].[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,
[blocked_session_count] [varchar](30) 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,
[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,
[WhoIsActiveID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_WhoIsActive] PRIMARY KEY
  CLUSTERED ( [WhoIsActiveID] )
)

2. Schedule an Agent job with the following info.

EXEC [master].dbo.sp_WhoIsActive
@get_transaction_info = 1,
@get_plans = 1,
@find_block_leaders = 1,
@DESTINATION_TABLE = 'Admin.dba.WhoIsActive';

Here are some queries I run to get general performance information from the data that was collected:

--Duration
SELECT   TOP 100  collection_time,* 
FROM [Admin].[dba].[WhoIsActive]
WHERE collection_time >= '2014-1-9 07:37'
AND login_name <> 'vcuser'
ORDER BY WhoIsActive.[dd hh:mm:ss.mss] DESC

--Blocking others
SELECT collection_time,blocked_session_count,* 
FROM [Admin].[dba].[WhoIsActive]
WHERE collection_time >= '2014-1-9 07:37'
AND login_name <> 'vcuser'
AND blocked_session_count <> 0
ORDER BY 2 DESC

--Blocked by others
SELECT collection_time,blocked_session_count,* 
FROM [Admin].[dba].[WhoIsActive]
WHERE collection_time >= '2014-1-9 07:37'
AND wait_info LIKE '%LCK%'
ORDER BY 2 DESC

--CPU
SELECT TOP 1000 collection_time,* 
FROM [Admin].[dba].[WhoIsActive]
WHERE collection_time >= '2014-1-9 07:37'
AND login_name <> 'vcuser'
ORDER BY CPU DESC

--Reads
SELECT TOP 100 collection_time,* 
FROM [Admin].[dba].[WhoIsActive]
WHERE collection_time >= '2014-1-9 07:37'
AND login_name <> 'vcuser'
ORDER BY reads DESC
Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s