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

 

Understanding Dynamic Management Views and Functions in SQL Server 2008


By:   |   Last Updated: 2010-04-01   |   Comments (2)   |   Related Tips: More > 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.

Script #1 - Listing DMV and DMF of the system

--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_lengthsc.PRECISIONsc.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_id

Required 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.

-- Script #2 - Basic syntax for granting/denying VIEW STATE 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.

-- Script #3 - Sysprocesses system table example

--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 
50

Script #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.

-- Script #4 - Sysprocesses with fn_get_sql example

 --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.

-- Script #5 - DMV and DMF example

USE 
master 
GO 
--Returns the all user process running on SQL Server  
--along with query being executed by each process 
SELECT DB_NAME(database_idAS [Database][text] AS [Query]  
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.plan_handlest 
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.

-- Script #6 - View DMV and DMF

--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


Last Updated: 2010-04-01


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

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, June 13, 2014 - 2:08:09 PM - Greg Robidoux Back To Top

William,

what you need to do is create a Stored Procedure and then call this stored procedure to delete the data.  Check out this tutorial on creating stored procedures: http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/

You can create the stored procedure to delete the data from the table and then execute the stored procedure as needed.


Friday, June 13, 2014 - 1:41:43 PM - William Hodgson Back To Top

I am teaching myself as I go using SQL Server 2008 r2.  I have a generalized question though.

I am trying to create a view that will delete rows of data.

 I come from an MS Access enviroment and this I would usualy develop in the query container.

I assumed (yes I know)) that views were SQLS term for queries. This must be wrong because the server keeps telling me that I cannot use the DELETE syntax in  a view sql script.

I don't want to use the TRUNCATE because I may have to roll the transaction back.

So "how" and "what" do I write  that I can run(call) at will from my Access front end?

I want to keep the work on the server but I don't want to have to go to the server everytime I want to run it.

Hope this is the right place to ask this sort of question.

I am a subscriber to your tips, and try to make some sense out of them.

I am way down on the knowledge chain.

 

Thanks

Bill


Learn more about SQL Server tools