Determine which database performance tools best mitigate bottlenecks
A collection of articles that takes you from defining technology needs to purchasing options
Even the best-designed database applications experience performance degradation. No matter how well database structures are defined or SQL code is written, things can and do go wrong. And if performance problems aren't corrected quickly, that can be detrimental to a company's bottom line.
When database performance suffers, business processes inside organizations slow down and end users complain. But that isn't the worst of it. If the performance of externally facing systems is bad enough, companies can lose business, as customers who get fed up waiting for applications to respond will go elsewhere.
Because the performance of database systems and applications can be affected by a variety of factors, tools that can find and fix the causes of database performance issues are vital for organizations that rely on database management systems (DBMSes) to run their mission-critical systems. And in today's database-centric IT world, that applies to most businesses.
Types of performance issues to look for
There are many types of database performance problems, which often makes it difficult to track down the cause of individual issues. It's possible, for example, that the database structures or application code are flawed from the beginning. Bad database design decisions and improperly coded SQL statements can result in poor performance.
Or it may be that a system was well-designed initially, but changes over time cause performance to degrade. More data, more users or different data access patterns can slow down even the best database applications. Even maintenance to a DBMS -- or a lack of regularly scheduled database maintenance -- can cause performance to nosedive.
The following are three important indicators that could signal database performance issues at your IT shop:
- Application slowdowns. The most important indication of potential database performance issues is when things that used to run fast begin to run slower. This includes online transaction processing systems used by employees or customers, or batch jobs that process data in bulk for tasks such as payroll processing and month-end reporting.
It can be difficult to monitor processing workloads without database performance management tools. In that case, database administrators (DBAs) and performance analysts have to rely on other methods to detect problems -- in particular, end-user complaints about issues such as application screens taking too long to load or nothing happening for a long time after information is entered into an application.
- System outages. When a system is down, database performance obviously is at its worst. Outages can be caused by database issues such as running out of storage space due to increasing data volumes or a resource such as a data set, partition or package being unavailable.
- The need for frequent hardware upgrades. Organizations that are constantly upgrading servers to larger models with more memory and storage are often candidates for database performance tuning. Tweaking database parameters, tuning SQL statements and reorganizing database objects can be much less expensive than frequently upgrading costly hardware and equipment.
On the flipside, sometimes hardware upgrades are needed to resolve database performance problems. But with the proper database monitoring and management tools, it's possible to mitigate the upgrade costs by pinpointing the cause of the problem and identifying appropriate steps for remediating it. For example, it can be cost-effective to add more memory or implement faster storage devices to resolve I/O bottlenecks affecting a database's performance. And doing so likely will be cheaper than replacing an entire server.
Problems that tools can help manage
When database performance problems do arise, it's unlikely that their exact cause will be immediately apparent. A DBA must translate vague complaints about problems from end users into specific performance-related issues that can cause the problems being described. This can be a difficult and error-prone process, especially without automated tools to guide the DBA.
The ability to collect metrics on database usage and identify specific database problems -- as and when they occur -- is perhaps the most compelling capability of database performance tools. When confronted with a performance complaint, the DBA can use a tool to highlight current and past critical conditions. Instead of having to hunt for the root cause of the problem manually, the software can quickly examine the database and diagnose potential problems.
Most database performance tools can also be used to set performance thresholds that, once tripped, alert the DBA to a problem or turn an on-screen indicator from green to yellow or red. In addition, DBAs can schedule reports on database performance to run at regular intervals in an effort to identify issues that need to be addressed. Advanced tools can both identify and help to remedy problems.
Consider a common scenario: An end user calls the DBA, saying that the screens in an application aren't responding as fast as usual. The DBA, armed with a database performance tool, examines thresholds and looks for red blinking lights. By examining the threshold conditions, the DBA can use the tool to identify the bottlenecks causing contention and can then remediate the problem quickly. Contrast this with the DBA who has no tools and remains in the dark with little idea of what to look for.
Because there are multiple variations of performance problems, advanced performance management tool sets require an array of functionality. Critical capabilities provided by database performance tools include:
- Reviewing SQL performance and optimization.
- Analyzing the efficacy of existing indexes for SQL.
- Viewing storage space and defragmenting disks when necessary.
- Observing and managing system resource usage.
- Simulating production in a test environment.
- Performing root cause analysis of database problems.
No waiting on improving performance
Organizations that deploy database performance tools can avoid prolonged periods of suboptimal performance. These tools can minimize the amount of time required to resolve database performance issues and, at times, even help organizations avoid some performance problems and outages altogether. For example, they enable DBAs to go from being reactive to being proactive about performance problem resolution. Instead of waiting for subpar performance symptoms to be reported by system users, problems can be uncovered and corrective actions taken, either by the DBA or automatically by a tool -- perhaps before users are even aware of a problem.
In addition, the tools can also help organizations effectively manage service levels, which are a measure of operational behavior in IT systems. Service-level management ensures that applications behave accordingly by applying appropriate resources and budget based on their importance to the organization. Database performance software can help DBAs monitor and manage the availability and response times of measured processes.
Tools that monitor and manage database performance are crucial components of an infrastructure that enables organizations to effectively deliver service to their clients and end users. Now that we've examined the types of performance problems that can arise and how database performance tools can help, the next step is to uncover the most important capabilities of the different types of database performance management tools.
Database security tools aren't all created equal
IoT databases aren't the same as traditional enterprise databases
Life after SQL 2005 end of life?