SQL Server 2005: Creating V$ACTIVE_SESSION_HISTORY

Are you are DBA like me who works on both Oracle and SQL server (and MySQL)?

Do you miss Oracle performance views like v$session, v$sql and in Oracle 10G v$active_session_history?

I have created a series of tables and procedures which provide roughly the same functionality in SQL server 2005, basically mirroring the same view that is in Oracle 10G. I will backport the stuff to SQL server 2000 at some stage.

These scripts use the new dynamic management views (DMV) available to SQL Server 2005.
Specifically,

1. sys.dm_exec_requests
2. sys.dm_exec_sessions
   

I have tried to map the columns in v$active_session_history to columns from the DMVs, whilst keeping the overhead of selecting the data under control, which means limiting the number of joins. So unfortunately there is no rowwait data included in this iteration.


The steps to install this on your SQL Server 2005 instance

1. Copy and paste these scripts to a sql file.
2. Open up SQL Server Management Studio.
   
3. Select the instance and database where you want to create the tables. I suggest MSDB as the database or a dba specific database if you already have one.
   
4. Open the file as a query.
5. Execute the query. This will create the view, table and two stored procedures.
6. Create a job which executes the stored procedure usp_ins_sql2005_ASH every n secs. It would be prudent to start with 3-5 secs and see how much overhead that causes.
7. Create a job which executes the stored procedure usp_purge_sql2005_ASH. This stored procedure takes a parameter for the number of hours to keep.
   

Note: Check the growth of your table to determine how much data you want to keep. As busy systems are likely to have many rows per sample.

E.g. Sampling every 2 secs will create 30 samples per minute, 1800 samples per hour. If you have on average 5 sessions running or waiting per sample that will be 9000 rows per hour.

I will provide some useful reports from the data you have collected in the next article.

The scripts are attached at the end of this article. Due to html parsing < as a tag you will have to check the purge script before running.

/* Recreating V$ACTIVE_SESSION_HISTORY in SQL Server 2005
Version: 0.1
Created: Paul Moen 2006
*/


drop view uv_active_session_history
go
create view uv_active_session_history
as
select
getdate() as sample_time,
req.session_id,
req.sql_handle as sql_id,
req.plan_handle as sql_plan_hashvalue,
req.database_id,
req.user_id,
req.command as sql_opcode,
req.status as session_state,
req.blocking_session_id as blocking_session,
req.wait_type as event,
req.wait_time,
sess.program_name as program,
sess.client_interface_name as module
from sys.dm_exec_requests req join sys.dm_exec_sessions sess
on req.session_id = sess.session_id
where req.user_id <> 1
go

drop table active_session_history
go
select top 0 * into active_session_history
from uv_active_session_history
go

drop procedure usp_ins_sql2005_ASH
go
create  procedure usp_ins_sql2005_ASH
as
insert into active_session_history
select * from uv_active_session_history
go

drop procedure usp_purge_sql2005_ASH
go

create procedure usp_purge_sql2005_ASH
@hour int
as
delete from active_session_history
where sample_time < dateadd(hh,-1*@hour,getdate())
go