Usage Statistics of Execution plan and its categoies

[ahozdemir]

Hi;

Is there any plan to make counter and statistics of execution plan , those way It would be easy to see which plan ( categorised adhco,proc, etc) executed time to time

regards

[DavidWiseman]

Hi. Do you mean capture statement/plan level performance info. e.g. using sys.dm_exec_query_stats or query store?

If you are not already using query store I would recommend enabling it and use it in combination with DBA Dash to get a more complete picture of performance.

Ideally this would also be included in DBA Dash at some point but it needs some thought put into how to accomplish this. e.g. Query store would be the best source to collect from, but this won't always be enabled and it would need to be collected per database. Some SQL instances can have thousands of DBs. Also, there is a danger of just duplicating what query store does and having extra storage overhead. Collecting information at this level can also be problematic for systems with adhoc workloads - even query store had issues with this (improved in recent versions). Instead of pulling this data into the repository DB, the app could maybe use the data directly from query store on the source DB. The issue with this is that the client app doesn't talk directly to any of the source DBs. I can think of some ways I can workaround this but it's not easy.