Introduction
There is a custom query located here that provides a list of all missing indexes in your environment ranked by the time savings: (2) Global Index Advisor with Time Savings - Custom Queries - Database Performance Analyzer - THWACK. Some people have been asking about automating this, and the best way to achieve this in DPA is by using a custom alert that runs once a day or week.
Custom Alert Query
Use the main query from the Global Index Advisor with Time Savings link above to get the main script. At the bottom is a main SELECT statement that returns the results. Modify that as follows so that it works with DPA alerts:
Query at the end of the script:
SELECT CHECKSUM(recommended_index) checksum_idx, *
FROM @Results
WHERE estimated_saved_secs > 300 -- more than 5 min of time savings
ORDER BY estimated_saved_secs DESC;
Becomes (query is also attached to this post):
SELECT DISTINCT 'Instance: '+instance_name+' - SQL Hash: '+CONVERT(varchar,sql_hash)+' - Index: '+recommended_index AS message, estimated_saved_secs
FROM @Results
WHERE estimated_saved_secs > 300 -- more than 5 min of time savings
ORDER BY estimated_saved_secs DESC;
Custom Alert Definition
Create a custom alert in DPA of type Custom SQL - Mutliple Numeric Alert and plug this SQL statement into it. Run it once ever 7 days or however frequently you need, and make sure to change the Notification Policy setting to "When Level is Not Normal" so DPA sends the email each time it runs.
