It can be frustrating when a client calls up and reports that the system is going slow all of sudden "with no one else on the system."

After investigating the usual suspects that slow the system down, indexing issues are what can some to mind next.

Needle in a haystack I hear you say!

Well SQL Server 2005 and above very cleverly keeps track of what it considers missing indexes when detetmining query plans.

You can query the sys.dm_db_missing_index_details management view and there are others if you're interested here.

This is information is gathered for all databases since the last time the SQL Server instance was started. Querying sys.dm_db_missing_index_details will return details of missing instances for all databases in the instance your current database is in.

Came across a very handy feature of the actual and estimated query plans today. When I ran the query I was tuning at the top of the query plan window underneath the query, the following text appeared:

Missing Index Details from Queries 2.sql

The Query Processor estimates that implementing the following index could improve the query cost by 88.9016%.

If I right clicked I got the option to choose the "Missing Index Details..." option which opened a new query window with the index create statement ready to run (I just had to supply the index name).

It was smart enough to suggest index columns and included columns for the index as well.