Dynamic Management Views and Functions

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:

  • Common Language Runtime related

  • Database Mirroring related

  • Execution related

  • Full-Text Search related

  • Index related

  • I/O related

  • Query Notifications related

  • Replication related

  • Service Broker related

  • SQL Server Operation system

  • Transaction related

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.

Conclusion

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

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles