This script will return all SQL statements for the given date range ordered by the metric of choice. See comments inside the procedure for more details on the variables used to tweak the output. It uses hourly data which is kept for 90 days by default. If you need longer timeframes, use 'D' in this line and you can summarize up to 5 years of data:
WHERE ss.period = ''H'' -- hourly data, if you have a large time range, use ''D'' for daily data
Note: If you get errors related to the Ignite schema, that's most likely because you are logged in the owner of the repository database. In that case, change @Schema to 'dbo' and it should then work.
Note: There is also a global version of this script that will provide results for all monitored instances: Global Top SQL by any Metric
