I had an idea today while working with a performance issue on the REST-service of RemoteX Applications.
In theory having a performance counter with the average execution time of SQL-queries over time. Will allow monitoring of system wide average cost for queries in the system.
That means that overtime its possible to track the changes of the execution times as they grow or shrink during normal production environment conditions. The cost for calculating the average could be relatively cheap to calculate compared to the round trip to the servers, and also the calculation could be threaded of to a different thread, so as not to disturb the worker thread that handles the request to the web server to begin with.
The idea is to examine the changes of the average query time over time, to see if performance of data access is changed between version upgrades, or hardware upgrades for that matter. There are some performance counters built into MSSql that can give data that is somewhat like this. However most of the require heavy load to see any performance upgrade or downgrade. This instead logs an average of the actual execution time for the application.
A benefit with this is that the performance is individual per application, but the actual code or counter could be reused between different applications.