DPA can currently show Top Wait reports for a single instance using the Top Waits report or the Trends tab. However, this script will show the Top Waits across all instances that are currently being monitored. It uses hourly data, so it can only be used to show Top SQL for a max of the last 90 days (default). If a longer timeframe is needed, change the following line to use daily data which goes back 5 years by default:
AND es.period = ''D'' -- use daily data
Cursor to Loop Through Each Monitored Instance
A somewhat tricky thing to tackle is combining SQL timings for multiple instances since the data is stored in separate tables for each. For this, I use a cursor to loop through each monitored instance (stored in the COND table), run the main query as a dynamic query and store the results in a table variable named @TopGlobalWaits. Finally, the SQL batch returns the combined results and orders it by the time in seconds.
Variables to Control the Search
At the beginning of the script are two variables that control the date range to search. Obviously the larger the date range and/or the more monitored instances you have, the longer this query will take to execute. If you want to limit the number of instances, modify the cursor definition to limit the number of rows retrieved from COND.
Schema Name
Depending on the user you connect with, you may need to use the "ignite" schema or you may need to exclude it. If you find that ignite.con_event_sum_XX table does not work, remove the "ignite" schema from the table names and try again.
