Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Monitoring Scripts with the DMVs


By:   |   Last Updated: 2009-10-19   |   Comments (7)   |   Related Tips: More > 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:

Monitor
 

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



Last Updated: 2009-10-19


next webcast button


next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 04, 2011 - 4:31:10 AM - Arjun Back To Top

Hi,

This DMV script is failing while executing INSERT Statement.

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

Msg 213, Level 16, State 1, Line 2

Insert Error: Column name or number of supplied values does not match table definition.

Regards,

 


Wednesday, November 11, 2009 - 5:41:39 AM - dmining06 Back To Top

 Thanks a lot for your comment. Such script helped me in the past to identify performance and blocking issues in our production environment. It is also a good tool to verify how busy is your server.

Thanks
Matteo


Tuesday, November 10, 2009 - 3:23:20 PM - rowlandg Back To Top

Nice job! This should help some folks with basic monitoring.

 

 


Saturday, October 31, 2009 - 8:00:17 AM - dmining06 Back To Top

   Sorry for the confusion. I am the author of the article and my posting addressed the following MSSQLTips Team question:

"Do you have any specific examples of performance or blocking problems that your script helped you catch?"

Thank you,
Matteo Lorini

 


Friday, October 30, 2009 - 8:55:57 AM - admin Back To Top

dmining06,

I am not exactly sure what you are asking.  Here are some tips on locking and blocking:

Thank you,
The MSSQLTips Team


Thursday, October 29, 2009 - 7:32:02 AM - dmining06 Back To Top

 

 In the specific example, session_id 62 (spid 62) is creating a chain of blocking.

Login_id Session_id Status Command blocking_session_id blocking_session_id wait_resource
user1 62 running DELETE 0 PAGEIOLATCH_EX SYNC_IO_COMPLETION
user2 68 suspended INSERT 62 LCK_M_IS OBJECT 6:1:1015915
user4 59 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015916
user5 71 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015917
user6 68 suspended DBCC 62 LCK_M_IX OBJECT 6:1:1015918
user7 61 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015919
user12 74 suspended DBCC 62 LCK_M_IX OBJECT 6:1:1015920
user4 56 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015921
user2 61 suspended UPDATE 62 LCK_M_IX OBJECT 6:1:1015922
user21 72 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015923
user2 71 suspended COMMIT TRANSACTI 62 LCK_M_IX OBJECT 6:1:1015924
user22 64 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015925
user55 62 suspended CONDITIONAL 62 LCK_M_IX OBJECT 6:1:1015926
user41 63 suspended SELECT 62 LCK_M_IX OBJECT 6:1:1015927
user2 69 suspended DBCC 62 LCK_M_IX OBJECT 6:1:1015928
user27 69 suspended INSERT 62 LCK_M_IX OBJECT 6:1:1015929
user27 69 suspended DBCC 62 LCK_M_IX OBJECT 6:1:1015930
user62 64 suspended SELECT 62 LCK_M_IX OBJECT 6:1:1015931
user72 63 suspended SELECT 62 LCK_M_IX OBJECT 6:1:1015932
user221 64 suspended DBCC 62 LCK_M_IX OBJECT 6:1:1015933

 


Monday, October 19, 2009 - 9:08:00 AM - admin Back To Top

Matteo Lorini,

Thank you for the tip contribution.

Do you have any specific examples of performance or blocking problems that your script helped you catch?

Thank you,
The MSSQLTips Team


Learn more about SQL Server tools