Now that DPA has added the Index Advisor feature (affectionately called the What If Advisor, as in what if I add this index), when I show it to people, they often ask to see the advice across all instances rather than for a single instance. A goal of the feature is to help you understand which suggested indexes would save the most time in your instance, so it makes a lot of sense to see this globally as well. In other words, which index will be the most helpful in my entire environment.
DPA Data Source
There are two main tables (each monitored instance has a set of these tables) that hold the Index Advisor data and they are:
- CON_WHATIF_SRC_XX - Holds a recommended index
- CON_WHATIF_IDX_XX - Join to above table via IDX_ID colum. This is a daily based table that matches up recommended indexes with the queries they might help. There is also information about the estimated time savings, the total SQL wait for the day, and the total executions.
- where XX equates to the ID value for instance within the COND table
SQL Statistics
Because of a slight defect in the way the SQL wait and executions are stored in DPA v2022.4, this query recalculates that data from the CON_STATS_SUM_XX table as well. Again, a table for each instance and this table holds daily and hourly summaries of both wait times and executions. Note the query only uses hourly summaries since there are 90 days of data retained in the repository. I would not suggest going back more than 90 days because some suggestions may be irrelevant by now, or at least changed significantly.
Cursor to Loop Through Each Monitored Instance
The other somewhat tricky thing to tackle is combining advisor and SQL stats tables for multiple instances. 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 @Results. Finally, the SQL batch returns the combined results and orders it by the estimated time savings.
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.
