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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- --//Set thresholds for missing indexes based on environment --//For example, in a TEST environment, the number of user seeks will be quite low compared to a LIVE database --//Look at all missing index suggestions and determine an appropriate number DECLARE @minUserImpact INT = 70 DECLARE @minUserSeeks INT = 200 --//Retrieve missing indexes based on thresholds and generate CREATE statements SELECT MIGS.last_user_seek,MIGS.avg_user_impact,MIGS.avg_total_user_cost,MIGS.user_seeks,MIGS.user_scans,MIGS.avg_user_impact * MIGS.avg_total_user_cost * (MIGS.user_scans + MIGS.user_seeks) AS Benefit, 'CREATE NONCLUSTERED INDEX ' + QUOTENAME('IX_AP_' + CONVERT(varchar(255),NEWID())) + ' ON ' + QUOTENAME(SCM.name) + '.' + QUOTENAME(TBL.name) + ' ' + '(' + ISNULL(MID.equality_columns,'') + CASE WHEN MID.equality_columns IS NOT NULL AND MID.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(MID.inequality_columns,'') + ')' + CASE WHEN MID.inequality_columns IS NOT NULL THEN ' INCLUDE (' + MID.inequality_columns + ')' ELSE '' END AS SQLStatement FROM sys.dm_db_missing_index_group_stats MIGS INNER JOIN sys.dm_db_missing_index_groups MIG ON MIG.index_group_handle = MIGS.group_handle INNER JOIN sys.dm_db_missing_index_details MID ON MID.index_handle = MIG.index_handle INNER JOIN sys.objects TBL ON TBL.object_id = MID.object_id INNER JOIN sys.schemas SCM ON SCM.schema_id = TBL.schema_id WHERE MIGS.avg_user_impact > @minUserImpact AND MIGS.user_seeks > @minUserSeeks ORDER BY Benefit DESC |
We are currently working on an autonomous indexing solution that will create and drop indexes dynamically…
Comments
No comments yet.