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.

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.