SQL Server 2008 : Performance Tuning - Using Dynamic Management Views

2/18/2011 7:45:23 PM
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:

  • Common Language Runtime

  • Cryptographic

  • Database Mirroring

  • Execution

  • Extended Events

  • Filestream

  • Full-Text Search

  • Index

  • I/O

  • Query Notifications

  • Replication

  • Service Broker

  • SQL Server Operating System

  • Transaction

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 ViewPurpose
sys.dm_exec_requestsProvides information about a request currently executed by SQL Server.
sys.dm_exec_sessionsProvides 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:

OBJECT_NAME(indstat.object_id, indstat.database_id) AS obj_name,
QUOTENAME( [index_name],
WHEN avg_fragmentation_in_percent < 5 THEN 'LOW'
WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
END as frag_level,
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:

SUM(qrystat.total_worker_time) AS Total_CPU_Time,
SUM(qrystat.execution_count) AS Number_of_Executions,
COUNT(*) as Number_of_Statements,
sys.dm_exec_query_stats qrystat
GROUP BY qrystat.plan_handle
ORDER BY sum(qrystat.total_worker_time) DESC

Configuring & Implementing...:Review of Useful DMVs

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.

  •  SQL Server 2008 : Performance Tuning - Working with Database Tuning Advisor
  •  SQL Server 2008 : Performance Tuning - Tracing with SQL Profiler
  •  SQL Server 2008 : Performance Tuning - Working with Query Execution Plans
  •  Externalizing BLOB Storage in SharePoint 2010 (part 2) - Installing and Configuring RBS & Migrating and Moving BLOBs Between BLOB Stores
  •  Externalizing BLOB Storage in SharePoint 2010 (part 1)
  •  Sharepoint 2010 : Managing SharePoint Content Databases
  •  Sharepoint 2010 : Maintaining SQL Server in a SharePoint Environment
  •  Sharepoint 2010 : Monitoring SQL Server in a SharePoint Environment
  •  Database Availability Group Replication in Exchange Server 2010 : Load Balancing in Exchange Server 2010
  •  Database Availability Group Replication in Exchange Server 2010 : Comparing and Contrasting DAG Versus CCR/SCR/SCC
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us