MDW (Management Data Warehouse) Missing Index

The cleanup job for the MDW (mdw_purge_data) has a step that performs the following query…

SELECT qt.sql_handle
    INTO #tmp_notable_query_text
    FROM snapshots.notable_query_text as qt
    WHERE NOT EXISTS (SELECT snapshot_id
                      FROM snapshots.query_stats as qs
                      WHERE qs.sql_handle = qt.sql_handle)

and as a result is missing the following index on:

[snapshots].[query_stats] ([sql_handle])

This index can change the run time of the purge from hours to minutes.

 

This entry was posted in MDW. Bookmark the permalink.