Missing Indexes

There are several reasons why you might be missing an index. One of several options is that your users used an ORM tool to generate a primitive database model for you and or the developers type LINQ queries that generates "un controlled" SQL code that does not perform on production size databases.

One other reason why you might get index recommendations is that the volume of the database changes and the generated query plans are different than those of your relative empty development database. SQL Server would not even consider using a index if the table consist of too few rows.

What does this script do

This script will query the SQL Server recommendations or perhaps I'd better call them the errors it found when creating the execution plans. It will take these recommendations and generate either the "undo" script that would drop the to be created indexes, or it create the needed index script.

The script does this, creating the missing indexes, for all databases on the server. I wrote this script, it is based on something I found in Books Online, and ran it as a batch on all servers where it generated a report for the project managers owning the database.

The script is non destructive, meaning it will generate the TSQL, not implement it.

Parameters
The Script

Parameters in the script

The script uses parameters, below is an explanation of how to use them.


Parameter	Value
OnlyDrop	No	Yes and No are the options you can use. when yes a drop script is generated.
To download the script click here.

The Script

-- =====================================================================
-- Walter Verhoeven
-- Database integration ZFS
-- Create missing indexes for SQL server 2005 or later
-- based on server statistics collected by SQL servers
-- execution plan creation engine (as of last SQL server service start)
-- =====================================================================
 
declare @OnlyDrop varchar(3)
set @OnlyDrop = 'No'--Yes or No are possible
 
DECLARE index_cursor CURSOR
READ_ONLY
FOR select 
            db.name as [database], 
            OBJECT_NAME( d.object_id,d.database_id ) as ObjectName,
            d.equality_columns as [Columns1],d.inequality_columns as [Columns2],
            d.included_columns as [covered]
      from sys.dm_db_missing_index_details as D
      join sys.dm_db_missing_index_groups as g on g.index_handle = d.index_handle
      join sys.databases db on db.database_id= d.database_id  
      --where d.Database_id >4 
      --where d.Database_id = db_id('<database name>')       
      order by 1,2,3
 
DECLARE @IndexName nvarchar(400), @schema sysname, @sql nvarchar(4000),@DatabaseOld sysname, @Database sysname,@table sysname,@Column1 varchar(8000), @Column2  varchar(8000), @covering varchar(8000) 
OPEN index_cursor
 
FETCH NEXT FROM index_cursor INTO @Database,@table,@Column1,@Column2,@covering
WHILE (@fetch_status <> -1)
BEGIN
      IF (@fetch_status <> -2)
      BEGIN
            if @DatabaseOld is null or @DatabaseOld!= @Database
            begin
                  print 'USE '+@Database
                  print 'go'
                  set @DatabaseOld = @Database 
                  
            end
              set @sql = N'select @schema= TABLE_SCHEMA from '+@Database+'.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= @table'
              exec sp_executesql @sql,N'@table sysname, @schema sysname output',@table,@schema output
            if @Column1 is not null
            begin
                  set @IndexName = 'IX_'+@table+'_'+@Column1 + case when @covering is not null then '_COVERING_'+@covering else '' end
                  set @IndexName = Replace(@IndexName,'[','')
                  set @IndexName = Replace(@IndexName,']','')    
                  set @IndexName = Replace(@IndexName,' ','')
                  set @IndexName = Replace(@IndexName,',','_')                           
                set @IndexName = substring(@IndexName,1,128)
            print 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'''+@IndexName+''')'
                  print '   DROP INDEX '+@IndexName+' ON '+@schema+'.'+@table
                  print 'GO'
                  
                  if @OnlyDrop='No'
                  begin
                        print 'CREATE NONCLUSTERED INDEX ' +@IndexName
                        print 'ON '+@schema+'.'+@table+' ('+@Column1+')'
                        if @covering is not null print 'INCLUDE ('+@covering+')'
                        print 'WITH (FILLFACTOR = 80,PAD_INDEX = ON);'
 
                        print 'GO'
                  end
            end
            
 
            if @Column2 is not null
            begin
                  set @IndexName = 'IX_'+@table+'_'+@Column2 + case when @covering is not null then '_COVERING_'+@covering else '' end
                  set @IndexName = Replace(@IndexName,'[','')
                  set @IndexName = Replace(@IndexName,']','')    
                  set @IndexName = Replace(@IndexName,' ','')
                  set @IndexName = Replace(@IndexName,',','_')                           
                set @IndexName = substring(@IndexName,1,128)
            print 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'''+@IndexName+''')'
                  print '   DROP INDEX '+@IndexName+' ON '+@schema+'.'+@table
                  print 'GO'
                  
                  if @OnlyDrop='No'
                  begin             
                        print 'CREATE NONCLUSTERED INDEX ' +@IndexName
                        print 'ON '+@schema+'.'+@table+' ('+@Column2+')'
                        if @covering is not null print 'INCLUDE ('+@covering+')'
                        print 'WITH (FILLFACTOR = 80,PAD_INDEX = ON);'
                        print 'GO'
                  end
            end
      END
      FETCH NEXT FROM index_cursor INTO @Database,@table,@Column1,@Column2,@covering
END
 
CLOSE index_cursor
DEALLOCATE index_cursor
GO

Important to know

The fill factor given here is used for CRUD databases where one needs some space in the indexes for insert and updates; when implementing this in a reporting database one should have no free space in the index as one would need more disk reads to load the index data pages from disk.

I highly recommend to create the drop script as you can't generate the drop script later and, this might sound funny, sometimes the added indexes generate a slower execution.

When you are interested in using this script please have also have a look at the "Never Been Used Indexes" script, you will probably need it.