Dynamic Management Views and Functions
May 31, 2006
Microsoft introduced dynamic management views (DMV) and functions (DMF) with SQL Server 2005. So what are DMV's and DMF's? How do you use these dynamic management objects? DMVs and DMFs are a mechanism to allow you to look at the internal workings of SQL Server using TSQL. They allow you an easy method for monitoring what SQL Server is doing and how it is performing. They replace the need to query the system tables or using other awkward methods of retrieving system information that you had to use with SQL Server 2000. This article will review how to use DMV's and how you can simplify your ability to retrieve system information.
What Kinds of DMVs and DMFs Are There
There are a number of different DMV's/DMF's, and they fall into two different categories. Some DMV's/DMF's are used to retrieve server wide information and these are said to have server scope. While other DMV's/DMF's are used to obtain database information and therefore have database scope. All DMV's/DMF's are stored in the master database, under the sys schema. Any object in the sys schema whose name starts with "dm_" is known as a dynamic object. The DMV/DMF's have been organized into the following different groups:
Using a DMV or DMF
Using a DMV or DMF is like using any other view or function. The DMV is used in the FROM clause of a select statement. A DMV or DMF require you to use a 2, 3, or 4 part naming convention when referencing them. Below is an example that uses the sys.dm_exec_requests DMV to return a count of the number of sessions that are executing for each type of command within SQL Server:
select count(*), Command from sys.dm_exec_requests group by Command
Here is an example that uses the sys.dm_exec_cursors DMF to return information about all cursors being processed on an instance of SQL Server.
select * from sys.dm_exec_cursors(0)
Books Online (BOL) documents what each DMV/DMF does, and what parameters need to be passed to the DMF's, as well as the columns returned when using these objects. If your version of BOL does not contain any dynamic management information, then you should download the latest BOL documentation from Microsoft.
Like with the system tables in prior releases, Microsoft reserves the right to change DMVs and/or DMFs between versions. They warn you that any dynamic object is subject to change with future versions of SQL Server. Therefore, keep this in mind when writing code that uses DMVs and DMFs. Any code you write might need to be rewritten or abandoned should Microsoft change how the DMVs or DMFs are coded in future releases of SQL Server.
Authorizing Access to DMVs/DMFs
In order for users to use the DMVs/DMFs they must have "VIEW SERVER STATE" or "VIEW DATABASE STATE" permissions. If someone has been granted "VIEW SERVER STATE" permissions then they have permissions to use all server scope DMVs and DMFs. If a user has been granted "VIEW DATABASE STATE" permissions in a database then they can use all the database scope DMV's in that database.
If you want to restrict users from accessing some DMF's or DMV's then this can be done by denying them access to those DMF's and DMV's. To do this you must first grant them "VIEW SERVER STATE" and/or "VIEW DATABASE STATE" permission, and then deny them access to the DMV's or DMF's you do not want them to use. In order to deny a user access to a DMV or DMF they need to be defined as a user in the master database. This works because deny permissions are checked first before the grant access permissions.
Some Examples of using DMVs
In SQL Server 2000, you did not have a direct method to get how much transaction log space was used via TSQL. In order to get that information you had to use the "DBCC SQLPERF (LOGSPACE)" command, pipe the output into a temporary table, then read the temporary table to get the amount of log space used. Using a DMV you can easily get at the amount of log space used. In the example below, I have used the "sys.dm_os_performance_counters" to display the amount of used transaction log space in the current database:
select instance_name ,cntr_value 'Log File(s) Used Size (KB)' from sys.dm_os_performance_counters where counter_name = 'Log File(s) Used Size (KB)'
The "sys.dm_os_performance_counter" DMV allows you to return a number of different perfmon counters. This DMV provides you with a method to review performance monitoring information using TSQL. This one DMV could be used to build a custom performance monitoring utility.
Here is some code that I found in Books Online. This code uses the "sys.dm_exec_query_stats" DMV and the "sys.dm_exec_sql_text" DMF to show the average CPU time for the top 5 query execution plans in cache.
SELECT TOP 5 total_worker_time/execution_count 'Avg CPU Time', SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)statement_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_worker_time/execution_count DESC;
Note that the above query uses the "APPLY" operator. This operate allows TSQL to invoke a table value function for each row in the table/view. The "APPLY" operator supports two different formats, "CROSS APPLY" and "OUTER APPLY." The "CROSS APPLY" operator only returns rows in the left table/view where the table value function returns a result set. The "OUTER APPLY" returns records from the table/view regardless of whether the table value function returns a result set.
Here is a query that will show you all the indexes that have been used for a given database, in this case in database TEST:
use TEST go SELECT o.name objectname, x.name indexname, user_seeks, user_scans, user_lookups, user_updates from sys.dm_db_index_usage_stats us join sys.sysdatabases d on us.database_id = d.dbid join sys.sysindexes x on us.object_id = x.id and us.index_id = x.indid join sys.sysobjects o on us.object_id = o.id where d.name = 'TEST' and o.type = 'u' order by user_seeks+user_scans+user_lookups+user_updates desc
This query uses the sys.dm_db_index_usage_stats DMV to display the object name, index name, and the number of times each index was used since the SQL Server service has started. I have sorted the results so the most used index will be displayed first. This query is useful to determine which indexes are being used the most.
If you wanted to know all the indexes that have not been used, in a given databases, since SQL Server has started, then you can run the following query:
use TEST go select o.name,i.name from ( SELECT x.id, x.indid from sys.dm_db_index_usage_stats us join sys.sysdatabases d on us.database_id = d.dbid join sys.sysindexes x on us.object_id = x.id and us.index_id = x.indid join sys.sysobjects o on us.object_id = o.id where d.name = 'TEST' and o.xtype = 'u' ) used right outer join sys.sysindexes i on i.id = used.id and i.indid = used.indid join sysobjects o on o.id=i.id where o.xtype = 'u' and used.id is null
Once again, this example used the sys.dm_db_index_usage_stats to determine what indexes are not used. You can use this query to identify indexes that might be candidates for being dropping.
As you can see, there is a lot of cool new functionality that dynamic management views and functions have brought to the TSQL world in SQL Server 2005. Using these DMVs and DMF's can greatly enhance what you can view and identify in SQL Server 2005. I would recommend that you browse through the updated books online documentation and review all the different views and functions that start with "sys.dm_."