In one of his blog articles, the proprietor of this web site posed this question to the reader, "You're automatically measuring all the queries that flow through your software, right?"
How do you do this? Every line of code that makes a query against the database is followed by a line of code that increments a counter? Or, are there tools that sit between your app and the DB and do this for you?
SQL Server Profiler is my tool of choice, but only for the DB end obviously.
It should be noted, this is for optimizing queries and performance, and debugging. This is not a tool to be left running all the time, as it can be resource intensive.
I don't know exactly what Jeff was trying to say, but I would guess that he expects you to use whatever query performance monitoring facility you have for your database.
Another approach is to use wrappers for database connections in your code. For example, in Java, assuming you have a DataSource that all of your classes use, you can write your own implementation of DataSource that uses an underlying DataSource to create Connection objects. Your DataSource should wrap those connections in your own Connection objects, which can keep track of the data that flows though them.
I have a C++ wrapper that I use for all my database work. That wrapper (in debug mode) basically does an EXPLAIN QUERY PLAN on every statement that it runs. If it gets back a response that an index is not being used, it ASSERTS. Great way to make sure indexes are used (but only for debug mode)
If your architecture is well designed, it should be fairly easy to intercept all data access calls and measure query execution time. A fairly easy way of doing this is by using an aspect around DB calls (if your language/framework supports aspect programming). Another way is to use a special driver that intercepts all calls, redirect to a real driver and measure query time execution.
We just bought a software product called dynaTrace to do this. It uses byte code instrumentation (MSIL in our case since we use .Net but it does Java as well) to do this. It basically instruments around the methods we choose and around various framework methods to capture the time it takes for each method to execute.
In regards to database calls, it keeps track of each call made (through ADO.Net) and the parameters in the call, along with the execution time. You can then go from the DB call and walk through the execution path the program took to get there. It will show every method call (that you have instrumented) in the path. It is quite badass.
You might use this in a number of different ways but typically this would be used in some kind of load testing scenario with some other product providing load through the various paths of your system. Then you get a list of your DB calls under load and can look at them.
You can also evaluate not just the execution of one call but the count of them to prevent the death of a thousand cuts.