SQL Server has means built into it to track possible missing indexes.  This used to be found through the use of the Index Tuning Wizard.  The process has improved over time (you can sort of see that from my April Fools post).

As luck would have it, I was recently asked to help fix a query that was somewhat related to the whole process.  You see, since SQL Server 2005, there are DMOs that help to track metadata related to column and index usage.  And if there a query is repeated enough that doesn’t have a good matching index, then the engine may think that a new index is needed.  This potential index information is recorded and becomes visible via the DMOs.

The query that I was asked to help fix was a dynamic query within a cursor that read information from the DMOs in order to generate some missing index information.  That particular query was failing for a couple of reasons, but on the same token it gave me an idea to modify and adapt the query to something more in line with what I might use.  After all, the queries that I used were in need of updating and this gets me started in that direction.

First, a little on why the query was failing.  A common problem with dynamic queries is the placement of quotes and having enough quotes in all required locations.  When you start nesting more levels into a dynamic query, the more confusing the quotes can get.  When running into something like this, I like to print the statement that I am trying to build dynamically.  If it doesn’t look right, then adjust the quotes until it looks right.

The second reason it was failing was a simple oversight.  Whether building dynamic queries or just using variables in your code, make sure you use properly sized variables.  In this case, the dynamic query variable was substantially inadequate.  The use of a print statement also helps to catch these types of errors rather quickly.

There were also a few things that would cause me to not use the original query in any environment.  The first problem is that the script contains a column which is the create statement for each proposed index.  In this create statement, all indexes were given the same name.  That would be a bit of a problem.

The next issue is my concern with the creation of indexes without ensuring that the index is going to provide greater benefit than cost.  Better stated is that the creation of these indexes just because the script spewed them out is no better than to create all of the indexes proposed by the Database Engine Tuning Advisor.  For this, I added a cautionary statement next to every index create statement.

So with these tweaks, as well as other less significant tweaks, here is the query.

Code block

DECLARE AllDatabases CURSOR FOR
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid > 4
 
OPEN AllDatabases
 
DECLARE @DBNameVar NVARCHAR(128),@STATEMENT NVARCHAR(MAX)
 
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'--CHECKING DATABASE ' + @DBNameVar
SET @STATEMENT = N'USE [' + @DBNameVar + ']'+ CHAR(13) +';' +CHAR(13)
+ N'
SELECT SO.name
, ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
*CONVERT(Numeric(19,6), migs.avg_total_user_cost)
*CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) AS Impact
,''DO NOT AUTO CREATE THESE INDEXES - Creating these indexes could be as bad as blindly using DTA'' as Note
, ''CREATE NONCLUSTERED INDEX IDX_'' + SO.name +''_'' + STUFF (
(SELECT ''_'' + column_name FROM sys.dm_db_missing_index_columns(mid.index_handle) WHERE column_usage IN (''Equality'',''InEquality'') FOR XML PATH (''''))
, 1, 1, '''')  + '' ON ['+@DBNameVar+'].'' + schema_name(SO.schema_id) + ''.'' + SO.name COLLATE DATABASE_DEFAULT + '' ( '' + IsNull(mid.equality_columns, '''') + CASE WHEN mid.inequality_columns IS NULL
THEN ''''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''''
ELSE '','' END + mid.inequality_columns END + '' ) '' + CASE WHEN mid.included_columns IS NULL
THEN ''''
ELSE ''INCLUDE ('' + mid.included_columns + '')'' END + '';'' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
AND mid.database_id = DB_ID()
INNER JOIN sys.objects SO WITH (nolock)
ON mid.OBJECT_ID = SO.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY ((CONVERT(Numeric(19,6), migs.user_seeks)+CONVERT(Numeric(19,6), migs.unique_compiles))
*CONVERT(Numeric(19,6), migs.avg_total_user_cost)
*CONVERT(Numeric(19,6), migs.avg_user_impact/100.0)) DESC))
AND OBJECTPROPERTY(SO.OBJECT_ID, ''isusertable'')=1
ORDER BY 2 DESC , 3 DESC' 
 
PRINT @STATEMENT
EXEC SP_EXECUTESQL @STATEMENT
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
 
CLOSE AllDatabases
DEALLOCATE AllDatabases

As I post this message, as I tend to do, I am looking for ways to improve upon the query and make it better.  This script should only be used with caution.  It is to provide an insight into potential missing indexes in each database.  A score is assigned to each potential index.  It is with the highest score indexes, that I typically begin analysis to improve performance.  I typically start from a query and execution plan to performance tune.  There are times when an alternative starting point is necessary.  This script is a tool for those times.  Please head the warning that these should be created with extreme caution.