Thursday, December 29, 2011

Missing indexes

I am always using the DMVs to determine if I have missing indexes in my Instance. SQL Server keep track of indexes that SQL Server thinks that could be useful to create. The missing indexes DMVs in SQL Server are a new future in SQL Server 2005.

To look what is needed according to the missing indexes DMVs, I have created the following statement:


CREATE TABLE #rows
(Databaseid int
,ObjectID int
,rowcounts int
)

INSERT INTO #rows
EXEC sp_MSForEachDB 'use [?];select db_id() , id, rows from [?].sys.sysindexes where indid < 2'

SELECT cast((datediff(hh, create_date, getdate()))/24 as varchar(3))
     + ' days and '
     + cast((datediff(hh, create_date, getdate())) % 24 as varchar(2))
     + ' hours' AS how_long_measured
     , @@SERVERNAME AS instance
     , DB_NAME(mid.database_id) AS DatabaseName
     , tmp.rowcounts
     , migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
     , migs.*
     , mid.database_id
     , mid.[object_id]
     , CASE
         WHEN mid.included_columns IS NULL
           THEN 0
           ELSE LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1
       END AS number_included_columns
     , 'CREATE INDEX [missing_index_'
     + CONVERT (varchar, mig.index_group_handle)
     + '_'
     + CONVERT (varchar, mid.index_handle)
     + '_'
     + LEFT (PARSENAME(mid.statement, 1), 32)
     + ']'
     + ' ON '
     + mid.statement
     + ' (' + 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, '')
     + ')'
     + ISNULL (' INCLUDE ('
     + mid.included_columns
     + ')', '') AS create_index_statement
  FROM sys.dm_db_missing_index_groups mig
 INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
 INNER JOIN sys.databases dbs on dbs.name = 'tempdb'
 INNER JOIN #rows tmp ON mid.database_id = tmp.Databaseid AND mid.[object_id] = tmp.ObjectID
 WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 100
   AND rowcounts > 10000
-- Mimimal number of rows to determine if an index is needed
   AND CASE
         WHEN mid.included_columns IS NULL
           THEN 0
           ELSE LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1
       END < 3
-- Max number of included columns to determine if an index is needed
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

DROP TABLE #rows



You can run this script when your SQL Server Instance is running for some time.  Keep in mind I am using minimal rowcounts and maximal number of included columns in my query. I have set rowcounts > 10000 and include columns < 3 in my query. You can update those values after your own need.


While this feature is so cool it almost seems magical, it does have a few limitations you should be aware of:
  • It's not as smart as the Database Engine Tuning Advisor.  If you have identified a query that you know is expensive and needs some help, don't pass up DTA just because the missing index DMVs didn't have any suggestions.  DTA might still be able to help. 
  • The missing index DMVs don't take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
  • The "improvement_measure" column in this query's output is a rough indicator of the (estimated) improvement that might be seen if the index was created.  This is a unitless number, and has meaning only relative the same number for other indexes.  (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
  • The missing index DMVs don't make recommendation about whether a proposed index should be clustered or nonclustered.  This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries.  (DTA can do this, however.)
  • Won't recommend partitioning.
  • It's possible that the DMVs may not recommend the ideal column order for multi-column indexes.
  • The DMV tracks information on no more than 500 missing indexes.


No comments:

Post a Comment