Using SQL DMV’s to generate CREATE INDEX statements

When SQL Server executes queries, it collects data on missing indexes in the Dynamics Management Views (DMV’s).

The index-related DMV’s will provide a list of missing indexes, including the table name, suggested columns and how many times a query was executed that would have benefitted from this index. This is called “number of user seeks”.

If the “number of user seeks” is very low, it’s not going to be worth it to create the index. SQL will suggest an index even if the query was only executed once and may never be run again. Creating too many indexes will negatively impact write performance on those tables.

It’s a thin line between missing indexes and over-indexing. In fact, we can use DMV’s to identify existing indexes that are never used, which we can safely delete, but that’s for another post.

Using the data from the DMVs, we can generate the TSQL statements to actually create those indexes. Below script will generate the TSQL statements for missing indexes exceeding the configured thresholds for the number of user seeks, as well as the average user impact percentage. These parameters must be adjusted for every environment specifically. If you get more than about 50 records, you should probably review the numbers and adjust the thresholds.

We are currently working on an autonomous indexing solution that will create and drop indexes dynamically…


No comments yet.

Leave a Reply

Your email address will not be published. Required fields are marked *