Typical tuning and database performance activities are often based on ratios and sometimes they give misleading results. As database functionality continues to become more sophisticated, new methods of tuning will become more useful.
An example of this is the Wait-Time information in Microsoft SQL Server. This allows tracking of every step of a process so you can identify which steps are causing a lag in performance.
Consider the metaphor of the cashier in relation to performance tuning:
The Cashier is the CPU
Customer Being Checked is Running
Customers Waiting in Line are Runnable
Customer 1 Requires a Price Check
Price Check is Runnable
If you use a ratio or a simple statistic such as total time at a cashier, you may end up taking the wrong action to fix the problem.
Wait-time analysis would give you a clearer picture, by isolating the price check as the place where the longest amount of wait time occured.
Wait-time tables are used to keep track of this information in SQL Server and in newer version of the project there are dynamic views of these tables which make your workeven easier. Having said this, the date and time stamps are not recorded, and the table date is that from database startup time. If you use these tables, you will need to sample the data periodicaly in order to get meaningful comparisions. There are also hundreds of events to it's good to understand which are the most common ones.
There are a number of tools available, including SQL profiler, and some third party tools. Both have their uses. Trace gives very detailed information, and the commercial products typically allow a more granular level of analysis, have graphical interfaces and may be easier to use.
Performance Intelligence
This is the next moniker for perfoamcne management, and you may want to create a database performance warehouse to keep track of your performance data. Some of the key information worth capturing is:
1. At the SQL statement level - Badly written queries are still for the most part the #1 problem in performance.
2. Time Views (The time something took.)
3. Wait State (Measure Every Wait Individually)
4. Historical View (State History to Isolate Trends)
A performance data warehouse is clearly the way to start to monitor problems. Over time you can add reporting and metrics to gain a deeper understanding of what some of your most common performance problems are.
As database functionaly continues to become more complex, the idea of a performance data warehouse will be even more compelling. Hopefully vendors will keep adding functionlity in this area to help us all with the complexities.