SQL Server Monitoring Scripts with the DMVs

Problem
As a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like: What was SQL Server doing last night when the end users were experiencing slow application response times? Was the issue from SQL Server? Or was it a network or a application issue? The answers to such questions are never easy to identify. If I only knew what SQL Server was doing at that specific point in time. If I knew what SQL Server was doing at that point in time, it may help me understand if the issues was a SQL Server performance issue or not . So how can I figure this out? If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Server. To do this without being overly intrusive, we need to rely on the SQL Server DMVs.

Solution
This tip briefly describes how to successfully combine the usage of two SQL Server DMV’s (sys.dm_exec_requests and sys.dm_exec_sessions) to create a rudimentary, non intrusive and very efficient tool to monitor requests that are being executed against a SQL Server 2005 or SQL Server 2008 instance. The only two DMV’s that I will need to create my T-SQL based monitor script are sys.dm_exec_requests and sys.dm_exec_sessions. It is not my intention to explain the details of those two DMV's, I will only combine them to show what good information comes from them.

The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time. Here is that query:

SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name],
S.[client_interface_name], S.[login_name], R.*
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO

In order to create our simple monitoring tool, let's create the following monitoring table. Here is the script:

CREATE TABLE [dbo].[MyMonitorTable](
[text] [nvarchar](max) NULL,
[query_plan] [xml] NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[login_name] [nvarchar](128) NOT NULL,
[session_id] [smallint] NOT NULL,
[request_id] [int] NOT NULL,
[start_time] [datetime] NOT NULL,
[status] [nvarchar](30) NOT NULL,
[command] [nvarchar](16) NOT NULL,
[sql_handle] [varbinary](64) NULL,
[statement_start_offset] [int] NULL,
[statement_end_offset] [int] NULL,
[plan_handle] [varbinary](64) NULL,
[database_id] [smallint] NOT NULL,
[user_id] [int] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[blocking_session_id] [smallint] NULL,
[wait_type] [nvarchar](60) NULL,
[wait_time] [int] NOT NULL,
[last_wait_type] [nvarchar](60) NOT NULL,
[wait_resource] [nvarchar](256) NOT NULL,
[open_transaction_count] [int] NOT NULL,
[open_resultset_count] [int] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[context_info] [varbinary](128) NULL,
[percent_complete] [real] NOT NULL,
[estimated_completion_time] [bigint] NOT NULL,
[cpu_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[scheduler_id] [int] NULL,
[task_address] [varbinary](8) NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[nest_level] [int] NOT NULL,
[granted_query_memory] [int] NOT NULL,
[executing_managed_code] [bit] NOT NULL,
[group_id] [int] NOT NULL,
[query_hash] [binary](8) NULL,
[query_plan_hash] [binary](8) NULL
)
GO

In order to monitor the SQL Server activity in an automated manner, create a SQL Server Agent Job that executes the code below every minute.

INSERT INTO MyMonitorTable
SELECT T.text, P.query_plan, S.host_name, S.program_name, S.client_interface_name, S.login_name, R.*
FROM sys.dm_exec_requests R
JOIN sys.dm_exec_sessions S on S.session_id=R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) As P
GO

After the job has been running for hours or days, we can query the MyMonitorTable table to see what SQL Server statements have been captured, as shown in the image below:




Script Caveats

It is important to clarify that this simple monitoring script does not capture all transactions like SQL Server Profiler does. This script with the SQL Server Agent Job only samples SQL Server transactions by the minute. You will not capture all queries. This script is aimed at capturing queries that are long running, may causing blocking and run frequently.

Here are also some columns to focus on as you begin to use the script:

For preliminary troubleshooting check out the following columns:
host_name
program_name
database_id
user_id
reads
writes
wait_type
wait_time
last_wait_type
wait_resource
The query_plan column shows (in xml format) the executed plan that a specific statement used. A graphical plan representation is also available if you click on the XML plan.
The blocking_session_id shows which session is currently blocking a statement and the wait type.
The statatement_text shows what query has been executed.
Statement_start_offset and Statement_start_offset can be used to list the exact T-SQL statement that was executed, within the batch job, at the specific point in time.