Missing Indexes in Custom Databases

Sometimes the cause of a slow query isn't an up-to-date index but a missing index instead. In this article a couple of steps are provided on how to find possible missing indexes and add them.

Important

Creating additional indexes is ONLY supported in custom databases. This is NOT supported in the framework databases.

Queries With Missing Indexes

Run the following query to list all queries with high CPU usage for which the query planner identified at least one missing index:

Copy

SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
    q.[text],
    p.query_plan,
    qs_cpu.execution_count,
    q.dbid,
    q.objectid,
    q.encrypted AS text_encrypted
FROM
    (SELECT TOP 500 qs.plan_handle,
     qs.total_worker_time,
     qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace 
        qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        //qplan:MissingIndexes')=1

The query above is copied from this Microsoft article, step 4: Optimize index maintenance to improve query performance and reduce resource consumption.

Result:

Adding Indexes

To add missing indexes, do the following steps.

1. Click on the blue URL-like value "ShowPlanXML..." in the column query_plan and Management Studio will show the query with its full query plan:

   

2. Right-click in the plan and select Missing Index Details:

   

3. A new query window is opened with a statement to create the missing index. Just select the statement(s) and press F5 to create the index.