What index do I need?

Bob Blackburn

If you have never done performance tuning, it can be a little intimidating. There are whole books on the subject. For those of you who are new to performance tuning, we will look at a quick way to get you started.

You are working on a new report or have been asked to look into an existing report that is taking a long time. We have all seen where one little change can cause a report to go from a run time of seconds to minutes and even hours.

For our example, we will have a report query I was given.  It has some normal union, sub select, and where column in sub select logic.

Here is the Estimated Execution Plan. Details for this are not mandatory. We can see multiple paths; but, it is not pages and pages of detail.

The query was taking over an hour on the development server when I cancelled it. To get the Estimated Execution Plan in SSMS, use this Menu option:

Here is where we get the help we need from SQL Server. Above the plan will be some messages. If it recommends a missing index, it will tell you. Here is the clip:

We can see the missing index has an estimated impact of 27% of processing time. But wait, there’s more. If you right click the message, SSMS will script the index create statement for you (almost).

You have to uncomment the create statement and change index name to a valid meaningful name.

Original: CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

Formatted: CREATE NONCLUSTERED INDEX [IX_table_name_column_name]

Execute the Create statement.

Here is the new plan. Don’t worry about finding the differences. Especially since you can’t read the detail of each box. The important thing is we did not receive any warning messages about missing indexes.

Now, instead of over an hour, the query completes in under 1 minute.  That is much better savings then the estimated 27%.

As quick and convenient as this is, it is not the answer to all your performance problems. You should not just create indexes for a report every time it is recommended. But, it will show you how your data is being used. Long term, it may be better for your system as a whole to rewrite the query. If you don’t have authority to make changes in production, you at least have more information to discuss possible changes with your team.

Labels: ,