Dynamic Management Views (DMVs) are almost certainly
the single most useful tool for troubleshooting and performance tuning
SQL Server databases. DMVs and Dynamic Management Functions (DMFs)
provide administrators with simple, yet powerful insight into the
workings of SQL Server and hardware resources (disk, memory, and CPU).
There were 89 DMVs introduced to SQL Server 2005 and 47 new
DMVs with SQL Server 2008, which can be grouped into two broad types:
server scoped (requires VIEW SERVER STATE permission on the server) and
database scoped (requires VIEW DATABASE STATE permission on the
database).
Most DMVs can be categorized as follows:
There’s a SQL trace running continuously and cycling
itself in the background while SQL Server is running. This trace also
gathers the data used by the DMVs. Some DMVs provide snapshot
information, whereas others provide cumulative data since the last
service restart. Trace data is not persisted anywhere within SQL Server,
although this is possible.
DMVs often provide
information that could only be accessed otherwise by querying metadata
or system tables. SQL Server administrators often like a real-time view
of current server activity. They might use the Activity Monitor within
Management Studio, or it they have a background with SQL Server 2000 or
earlier—they’ll probably use SP_WHO or SP_WHO2—both provide session
level activity view. There are, however, a couple of DMVs in Table 1 that provide this information plus much more.
Table 1. Using DMVs to View Current Activity within SQL Server
| Dynamic Management View | Purpose |
|---|
| sys.dm_exec_requests | Provides information about a request currently executed by SQL Server. |
| sys.dm_exec_sessions | Provides an overview of all current sessions (SPIDs) within SQL Server. |
It’s easy to select all
data within a DMV; however, there are great opportunities to write
useful queries to interrogate DMVs. One such example is
sys.dm_db_index_physical_stats. This DMV shows the level of index
fragmentation. In previous versions, this was available through the DBCC
SHOWCONTIG command; however, it was very intensive for Disk IO, and the
results were cumbersome and difficult to manipulate without significant
effort. The following example shows a query that categorizes index
fragmentation as High (more than 30 percent), Medium (less than 30
percent), or Low (less than 5 percent) placing the results with greatest
fragmentation first since this is where we should pay most attention:
SELECT
OBJECT_NAME(indstat.object_id, indstat.database_id) AS obj_name,
QUOTENAME(sysind.name) [index_name],
CASE
WHEN avg_fragmentation_in_percent < 5 THEN 'LOW'
WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
ELSE 'HIGH'
END as frag_level,
indstat.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
AS indstat
INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND
indstat.index_id = sysind.index_id
ORDER BY avg_fragmentation_in_percent DESC
The output of the
sys.dm_db_index_physical_stats can be used as the input to an index
maintenance job. In this scenario, it is possible to build a SQL Server
Agent job that takes action based on the output of
sys.dm_db_index_physical_stats such
as an index reorganize (for indexes with low or medium fragmentation)
or rebuilding indexes (for those indexes with heavy fragmentation).
Providing additional
indexing maintenance, SQL Server can also suggest indexes that would
help improve query performance. This information is provided by a group
of DMVs, the most useful of which is sys.dm_db_missing_index_details.
Using the output from this DMV, we can generate a CREATE INDEX statement
to add the new index and improve performance. However, there are
numerous limitations to the missing indexes feature, for example, -it
doesn’t consider the cost of maintaining an index nor does it specify an
order for columns to be used in the index.
There are DMVs such as
sys.dm_os_* that reflect aspects of the SQL Server Operating System and
can be a useful barometer for understanding more about SQL Server
internals, system memory consumption, and other requirements. The
following query uses the sys.dm_exec_query_stats DVM to display the top
10 queries consuming the most CPU power:
SELECT TOP 10
SUM(qrystat.total_worker_time) AS Total_CPU_Time,
SUM(qrystat.execution_count) AS Number_of_Executions,
COUNT(*) as Number_of_Statements,
qrystat.plan_handle
FROM
sys.dm_exec_query_stats qrystat
GROUP BY qrystat.plan_handle
ORDER BY sum(qrystat.total_worker_time) DESC
These DMVs are each
important in troubleshooting server performance and can be useful in
initially identifying poorly performing queries and then to review
bottlenecks for problem queries. Look at the following DMVs to become
familiar with the contents:
sys.dm_exec_sessions
Both sys.dm_exec_requests and sys_dm_exec_sessions can be used to view
active sessions and requests running on a server. Use these DMVs to
identify long running, or currently poorly performing sessions.
sys.dm_exec_query_stats
This DMV can be useful because it returns details of query execution
statistics from the plan cache plan, rather than just considering
current queries and sessions.
sys.dm_exec_sql_text
Once you’ve identified the worst performing query, use this next DMV to
return the query text. It’s an alternative to DBCC INPUTBUFFER and
accepts a query handle as input (can be retrieved from any of six DMVs
including sys.dm_exec_requests and sys.dm_exec_query_stats).
sys.dm_os_wait_stats
This DMV is useful for reviewing serverwide wait statistics, a useful
tool in identifying resource bottlenecks across the server. Identifying
sources of contention is really helpful in indentifying where
improvements can be make to assist overall server performance.
sys.dm_db_index_usage_stats
This DMV shows usage statistics (scans and seeks) for each index, and
it can be useful in identifying never-used or rarely used indexes. These
indexes can often be candidates for disabling or dropping because they
can be more expensive to maintain than beneficial. However, remember
DMVs are cumulative since the instance last restarted; don’t drop lots
of indexes if SQL Server hasn’t bee running more than a few weeks!
sys.dm_db_missing_index_details
This DMV is great for suggesting new indexes, based on past workload.
The DMV will actively suggest new indexes and can be used to generate
CREATE statements that can be executed to add the new indexes.
