Top SQL by Any Dimension

DPA does not currently have a long term view of the Top SQL for many of the tabs at the top of the charts when drilling into an instance. You can only see this information in DPA if you drill into a day and a time, and then filter the list by the dimension you want.

However, the DPA repository can be mined to obtain this information for the last 30 days (default data retention). Connect to the DPA repository with Management Studio, open a query and copy/paste the attached script. Modify the top 5 parameters at the top of the script as needed. The @Dimension and @DimensionName variable let's you choose the tab and specific data you want to retrieve.

Note: this script uses the "ignite" schema when referencing tables. Some installation run the DPA repository using the "dbo" schema, so if you get errors about the ignite schema, remove it and the script should then work.