Database performance tuning is a complex but extremely important task. However, it can be difficult to effectively optimize databases when there are other “fires” to put out, limited resources, and an increasing number of databases to look after. But that doesn’t mean it’s impossible, especially with the right approach.
Wait Time Analysis
Consider for a moment how traditional database monitoring focuses on resource utilization metrics. To illustrate why this isn’t ideal, think about what you would evaluate in trying to shorten your commute to work. What would you measure: The number of tire rotations per second? The engine’s temperature? How much gas is in the tank? These resource statistics would be useless in the context of your goal.
All that would really matter is what has an impact on your trip’s time: detailed insight into how long you spend at each stoplight and which stretches of road have the most stop-and-go traffic. With this information, you could determine if finding a shorter route, avoiding peak hours, taking the expressway, or driving faster would improve your commute.
This is the basis for wait time analysis, an innovative approach to database optimization that focuses on time. Specifically, it allows DBAs to make tuning decisions based on the impact to application response. In turn, this enables IT to always find the root cause of the most important problem impacting end users, and identify which critical resource(s) will resolve it.
Wait Time Analysis for Database Optimization
Wait time analysis helps you understand how much time each SQL statement is spending across all its executions in a given period of time. Keep in mind, you may have a very fast SQL that runs in 100 milliseconds, but if it has to run a million times a day, it will have a big impact on application performance.
It begins with identifying the discrete steps accumulating time. These steps, corresponding to physical I/O operations, manipulating buffers, waiting on locks, and all other minute database operations, are instrumented by most database vendors.
For Microsoft SQL Server, these are called “Wait Types.” For Oracle, SAP Sybase ASE, and IBM DB2, they are referred to as “Wait Events.” These indicate the amount of time spent in each step while sessions wait for each database resource. If they can be accurately monitored and analyzed, the exact bottlenecks and queries causing the delays can be determined.
This all results in response time, defined as the sum of actual processing time plus the time a session spends waiting on availability of resources such as a lock, log file, or hundreds of other wait types/events. When multiple sessions compete for the same processing resources, the wait time becomes the most significant component of the actual response time.
Identifying Bottlenecks With Wait Time Analytics
Armed with wait time information for a database, you can identify the biggest contributor to slow performance and focus on fixing it—whether it is writing to disk, performing a slow query, execution plans changes, or waiting for memory.
You can also use this information to identify trends and predict performance of all SQL statements being executed over time and thereby be proactive in helping applications run better. It can help identify the performance impact of changes to application code, software configuration, or hardware resources.
It is the visibility into how exactly these changes impact performance that makes wait time analysis a powerful tool for resource planning. Organizations have invested in faster hardware and flash storage, expecting it to solve performance problems, only to discover it didn’t. The problem could have been caused by bad SQL or locking and blocking, while storage I/O actually had low contribution to performance.
With the right visibility into how much time an application is waiting for disk read/write operations, it’s easier to predict the performance impact to expect from a storage system with higher IOPS. This is called “performance certainty”: There is no guessing if or how much performance will be improved. You know ahead of time how switching to a different server, VM, or storage system will impact performance and what bottlenecks to work on after the move.
Performance optimization ranks at the top of the list for DBAs, both in terms of importance and percentage of time required. Understanding wait time analysis as an approach to optimization and tuning, especially if aided with the right analysis tools, can make a significant impact in your tuning effectiveness, in application response time, and in your career.