By: Matteo Lorini | Updated: 2009-10-19 | Comments (7) | Related: > Dynamic Management Views and Functions
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.
Next Steps
- Check out this script and run it in your development or QA environments to get a handle on the results as well as potential issues before they are promoted to your production environment.
- Once you are comfortable with the script, consider using it in your production environments to better understand your performance. Depending on your needs you could execute the job 24X7, during normal business hours or just as needed. You make the call.
- Check out these related MSSQLTips:
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-10-19
