Using dynamic management views to monitor and tune performance of SQL Server

Dynamic management views help monitor SQL Server health and tune performance. Learn how to use DMVs to retrieve critical data on the internal workings of SQL Server.

While they aren't a cure-all, Dynamic Management Views (DMVs) are a solid option for SQL Server monitoring, management and performance-tuning. DMVs are a collection of views and table-valued user-defined functions that enable you to get information on the internal workings of SQL Server. In this article, we outline some of the capabilities of DMVs, DMVs' differences from other tools and how to use them to derive critical data on SQL...

Server.

DMVs are part of the sys schema in the master database. You can find the list of dynamic views in SQL Server Management Studio under "Master/Views/System Views," and the dynamic functions are located under "Master/Functions/System Functions/Table-valued Functions." Each dynamic object's name has a "dm_" prefix. SQL Server 2005 introduced DMVs, and the latest release, SQL Server 2008, includes additional DMVs. Within the context of any user database, you can query the content of DMVs certain DMVs return database specific information, whereas others return instance-wide data. DMV functionality and content may vary from one release to next, but the purpose of a DMV should remain the same.

DMVs: An alternative to system tables
Starting with SQL Server 2005, Microsoft has not allowed data to be updated in system tables. In previous releases of the software, system tables provided one of the few ways of retrieving metadata, and in some cases, using them was the only way to accomplish certain advanced tasks. SQL Server 2005 and f2008 still allow you to query objects that have the same names as system tables in previous releases, even though these objects are now system-supplied views, but you can adjust settings only through system stored procedures.

DMVs provide a better alternative to system tables for several reasons. First, DMVs are dynamic by definition, so their content changes are taking place directly on the server. Also, DMVs offer information that was difficult or impossible to obtain through system tables, performance counters or undocumented DBCC commands prior to SQL Server 2005.

DMVs can be categorized based on the kind of data they return. For example, SQL Server Operating System (SQLOS)-related DMVs return data about memory, threads, performance counters, waits and other information on resource usage, whereas transaction-related DMVs return detailed data on transactions and locking. In addition, SQL Server features specific DMVs for replication, service broker, full-text search, database mirroring and query notifications.

Missing indexes
Now that I've provided an overview of DMVs, let's focus on a subset of DMVs: missing indexes. If you've worked on optimizing SQL Server query performance, you know that there are many tuning techniques. Proper indexing, however, is arguably the most effective method of ensuring that queries execute as fast as possible. SQL Server 2005 introduced the missing indexes feature to help analyze information about indexes that could improve SQL Server performance through DMVs and query execution plan.

When a query is executed, SQL Server Query Optimizer examines existing indexes and considers using them, as opposed to scanning tables. If optimal indexes do not exist, SQL Server stores information about the lack of effective indexes in DMVs. You can review these missing indexes for a given query through an XML execution plan.

To review query execution plan in XML format, run the following command first:

 SET SHOWPLAN_XML ON GO

After you execute your query, the result is displayed as a link, and clicking the link opens the XML document. If there is a missing index that could benefit your query, a section similar to the following displays:

 <MissingIndexes> <MissingIndexGroup Impact="46.3448"> <MissingIndex Database="[AdventureWorksDW]" Schema="[dbo]" Table="[FactCurrencyRate]"> <ColumnGroup Usage="INEQUALITY"> <Column Name="[TimeKey]" ColumnId="2" /> </ColumnGroup> <ColumnGroup Usage="INCLUDE"> <Column Name="[CurrencyKey]" ColumnId="1" /> <Column Name="[AverageRate]" ColumnId="3" /> <Column Name="[EndOfDayRate]" ColumnId="4" /> <Column Name="[full_date]" ColumnId="5" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> </MissingIndexes>

Alternatively, you can examine missing indexes DMVs to find missing indexes for all objects referenced by queries executed since SQL Server was last started. DMVs provide a convenient way of examining missing indexes for all objects rather than having to examine XML execution plan for each query.

SQL Server 2005 exposes the following missing index DMVs:

  • Sys.dm_db_missing_index_group_stats
  • Sys.dm_db_missing_index_groups
  • Sys.dm_db_missing_index_details
  • Sys.dm_db_missing_index_columns

The sys.dm_db_missing_index_group_stats DMV returns statistical information about estimated improvement that each missing index could provide once implemented. This DMV identifies the missing index using a "group_handle." The same column could be used to join this with the sys.dm_db_missing_index_groups DMV, which only contains a group handle and index handle for each missing index. With SQL Server 2005, each index group is assigned a single missing index.

Sys.dm_db_missing_index_group_stats also returns the number of user queries, system queries that could have benefited from the missing index and the estimated improvement for these user and system queries.

The sys.dm_db_missing_index_columns DMV returns missing index columns, along with column name and column id, and accepts an index handle as the parameter. This DMV also specifies how the columns were referenced in queries: for equality predicate, inequality predicate or included column.

When using the sys.dm_db_missing_index_columns DMV to create helpful indexes, you should use the equality and inequality predicates as index keys. You could use the following query, for example, to retrieve missing index columns and determine whether each column is supposed to be used as the index key or included column:

 SELECT index_handle, column_name, CASE WHEN column_usage IN ('EQUALITY', 'INEQUALITY') THEN 'index_key' WHEN column_usage = 'INCLUDE' THEN 'included_column' END AS column_usage FROM sys.dm_db_missing_index_groups a CROSS APPLY sys.dm_db_missing_index_columns (a.index_handle)

The abbreviated results:

index_handle column_name column_usage
1
TimeKey
index_key
1
CurrencyKey
included_column
1
AverageRate
included_column
1
EndOfDayRate
included_column
1
full_date
included_column


Note: A column identifier returned by sys.dm_db_missing_index_columns shows the order in which columns appear in the table -- it does not provide any hints as to the order of missing index keys.

The sys.dm_db_missing_index_details DMV returns the database identifier, object identifier, object name and columns for missing indexes. The columns are classified as equality, inequality and included columns. This DMV can be used in conjunction with sys.dm_db_missing_index_columns to put together statements for creating missing indexes. All data for a single missing index is returned in a single row; this differs from sys.dm_db_missing_index_columns function, which returns each column in a separate row.

For example, the following query joins missing index DMVs, finds the most beneficial indexes and comes up with commands to create such indexes:

 SELECT avg_user_impact AS average_improvement_percentage, avg_total_user_cost AS average_cost_of_query_without_missing_index, 'CREATE INDEX ix_' + [statement] + ISNULL(equality_columns, '_') + ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') + ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_missing_index_command FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c ON b.index_group_handle = c.group_handle WHERE avg_user_impact > = 40

Results for a single index:

average improvement percentage
46.34
average cost of query without missing index
20.51871252
create missing index command CREATE INDEX
ix_[AdventureWorksDW].[dbo].[FactCurrencyRate]_[TimeKey]
ON [AdventureWorksDW].[dbo].[FactCurrencyRate] ( [TimeKey]) INCLUDE ([CurrencyKey], [AverageRate], [EndOfDayRate], [full_date])

The query above returns any indexes that can provide estimated performance improvement of 40% or more, although a user can modify the percentage of improvement required. The index name formatting can also be modified to suit your naming conventions.

Contrary to what you might expect, the "statement" column of the sys.dm_db_missing_index_details DMV returns the object that should be used to build the missing index. The object is returned in the DatabaseName.OwnerName.ObjectName format. Interestingly, the DMV also returns the database identifier and object identifier columns, so you can also derive the object name by applying the DB_NAME() and OBJECT_NAME() functions to the returned columns.

Note, though, that missing index DMVs show only the data about indexes that could benefit the queries executed since SQL Server was last started. This means that each time you restart SQL Server, all missing index information is discarded. If you want to collect and examine the same information over a greater period of time, copy the contents of each DMVs to user tables.

The missing indexes feature is a great addition to a database administrator's performance tuning arsenal. It is not, however, a perfect solution. Administrators still have to examine the contents of missing index DMVs and carefully consider the costs and benefits of creating the recommended indexes. If your queries include multiple columns for equality or inequality operations, for example, the missing indexes feature does not provide the column order for CREATE INDEX statements. You need to examine the queries that can benefit from missing indexes and determine the order of index keys, which can make a significant difference in the effectiveness of each index.

Finally, consider that while an index can speed up SELECT queries, it could also have negative impact on INSERT, UPDATE and DELETE statements. This is because SQL Server must dedicate memory to maintaining index keys as data is added, modified and removed from the table. For more information on the limitations of the missing indexes feature, consult SQL Server 2008 Books Online.

ABOUT THE AUTHOR

Baya Dewald is a database consultant who helps customers develop highly available and scalable applications with SQL Server and Analysis Services. Throughout his career. he has managed database administrator teams and databases of terabyte caliber. Baya's primary areas of expertise are performance tuning, replication and data warehousing. He can be reached at baya@bayasqlconsulting.com.

This was first published in January 2009

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close