SQL 2008 - Missing Indexes Hints

SQL 2008 - Missing Indexes Hints

Improvements to the Execution Plan UI in SQL Server Management Studio 2008 sure do make it a doddle to improve query execution time.

A client just reported that a particular stored procedure kept timing out.

Viewing the Estimated Execution Plan for this query displayed a nice green message advising of a potentially missing index which could improve performance by >95%:

Right-clicking and selecting “Missing Index Details” then brings up a new query window containing the index creation DDL (I had changed the index name by the time I grabbed this capture):

Dead easy. Repeating the process identified a second index which could further improve performance by >80%.

Of course, you still need to bear in mind other queries which access the table, and the effects on inserts/updates of creating additional indexes, but even so, this really helps the workflow.

The suggested indexes functionality was actually available in SSMS 2005 too, but the results were buried away in the XML version of the execution plan, and obscure properties windows, rather than being so in your face.