By: Arshad Ali | Updated: 2010-04-01 | Comments (2) | Related: > Dynamic Management Views and Functions
Problem
Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues.
Solution
In my last tip Understanding Catalog Views in SQL Server 2005 and 2008 I discussed about Catalog views as a more generic and standard interface to access the SQL Server system meta data. In this tip series, I am going to discuss the Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) which changed the entire approach of collecting system health information and diagnosing/troubleshooting the performance issues.
DMV/DMF returns SQL Server runtime state information that can be used to monitor SQL Server health during runtime, troubleshoot the performance bottleneck/issues and proactively work on to minimize the downtime. SQL Server 2005 has 80+ DMV/DMF whereas SQL Server 2008 has 130+; some of the existing DMV and DMF have been extended and additional DMV and DMF have been added to cover features of SQL Server 2008, for example Resource Governor is a new feature and several DMV/DMF have been added to support this new feature. All the DMV and DMF have been categorized on the basis of feature area they cover, you can find this list here.
Script #1 allows you to view all of the DMVs and DMFs in the SQL Server, please note the name of the DMV/DMF starts with "dm_". They all reside in sys schema. The last query in the Script #1 table extends the first and second queries and provides all the columns of these DMV and DMF along with its data type and size.
--Listing all the DMV/DMF using sys.all_objects catalog view
SELECT * FROM sys.all_objects
WHERE name LIKE 'dm_%' ORDER BY name
--Listing all the DMV/DMF using sys.system_objects catalog view
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%' ORDER BY name
--Listing all the DMV/DMF along with its columns, their
--data types and size
SELECT so.name AS [DMV/DMF], sc.name AS [Column],
t.name AS [Data Type], sc.column_id [Column Ordinal],
sc.max_length, sc.PRECISION, sc.scale
FROM sys.system_objects so
INNER JOIN sys.system_columns sc ON so.OBJECT_ID = sc.OBJECT_ID
INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
WHERE so.name LIKE 'dm_%'
ORDER BY so.name, sc.column_idRequired permissions
Dynamic Management Views and Functions have been broadly categorized into two categories:
-
Server-scoped Dynamic Management Views and Functions - They reside in master database and provide SQL Server instance wide information. To access these DMV/DMF you need to have SELECT permission on the objects and VIEW SERVER STATE permission, refer to the Script #2 to grant and deny this permission.
-
Database-scoped Dynamic Management Views and Functions - They reside in each database and provide database wide information. To access these DMV/DMF you need to have SELECT permission on the objects and VIEW DATABASE STATE permission, refer to the Script #2 to grant and deny this permission.
--To grant permissions
GRANT VIEW SERVER STATE TO <[Login]>
GRANT VIEW DATABASE STATE TO <[User]>
--To deny permissions
DENY VIEW SERVER STATE TO <[Login]>
DENY VIEW DATABASE STATE TO <[User]>DMV/DMF and system tables
In earlier versions of SQL Server we needed to use system tables (for example sysprocesses, syslockinfo, syscacheobjects, etc.) to monitor the state of the system. The information provided by some of these system tables was cryptic and mostly undocumented. Hence they required a great deal of knowledge to understand the data. However, starting with SQL Server 2005, these system tables were not the only way to collect system health information. Rather you could use documented DMVs/DMFs which have much more information than the system tables and information in a more intuitive format. On a final note, it is recommended to use the DMVs/DMFs in all future development. You can find the mapping between system tables and DMV/DMF here.
Script #3 demonstrates the use of the sysprocesses system table. This table contains a record for each SQL Server process including both user and system processes. SQL Server reserves spids (system process identifier) 1 to 50 for system processes then assigns spid 51 and greater for all the user processes. In SQL Server 2005, these system tables were replaced with compatibility views hence you can use them with or without specifying the schema name for backward compatibility. To learn more about Compatibility Views, click here.
--Returns all user and system processes running on SQL Server
SELECT * FROM sysprocesses
--Now in SQL Server 2005 and later versions, these
--system tables are compatibility views, hence you can
--use them with or without specifying the schema name
SELECT * FROM sys.sysprocesses
--Returns the all user processes running on SQL Server
SELECT * FROM sysprocesses
WHERE spid > 50Script #4 extends the use of the sysprocesses system table, it uses the fn_get_sql table-valued function, which in turns accepts sql_handle provided by sysprocesses, to return the query being run by the process.
--Returns the all user process running on SQL Server
--along with query being executed by each process
SELECT *, (SELECT [text] FROM fn_get_sql(sql_handle))
FROM sysprocesses
WHERE spid > 50 How DMFs differ from DMVs
Dynamic Management Functions are system defined table-valued functions which accept parameters and a return result-set. For example, let's rewrite the query in the Script #4 with a DMV and DMF. In Script #5 the sys.dm_exec_requests dynamic management view is being CROSS APPLY'ed with the sys.dm_exec_sql_text dynamic management function which accepts a "plan handle" for the query and the same "plan handle" is being passed from the left/outer table expression to the table-valued function to work on and return the data.
USE master
GO
--Returns the all user process running on SQL Server
--along with query being executed by each process
SELECT DB_NAME(database_id) AS [Database], [text] AS [Query]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
WHERE session_Id > 50 -- Consider spids for users only, no system spids. CROSS APPLY and OUTER APPLY are new operators in SQL Server 2005 and later versions, to learn more about it refer to CROSS APPLY and OUTER APPLY in SQL Server tip.
How to view the definition of the DMVs and DMFs
You can also view the definition of a DMV and DMF, the same way you view the definition of a view, stored procedure or function. You can use either the sp_helptext system stored procedure or OBJECT_DEFINITION function. Script #6 demonstrates how to view the definition of the sys.dm_exec_query_stats DMV using these methods.
--Viewing the definition of DMV using sp_helptext
EXEC sp_helptext 'sys.dm_exec_query_stats'
--Viewing the definition of DMV using OBJECT_DEFINITION function
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.dm_exec_query_stats')) Next Steps
- Review Understanding Catalog Views in SQL Server 2005 and 2008 tip.
- Review SQL Server Dynamic Management Views/Functions related tips.
- Review How to isolate the current running commands in SQL Server tip.
- Review my all previous tips.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-04-01
