Examining the functions and features of database performance tools
To help you determine which tools your organization needs, it's important to review the primary features and functionality of the three database performance tools categories.
The database management system is the heart of modern applications, and keeping things running smoothly in a DBMS is imperative to an organization's bottom line. Database performance tools can contribute to optimal system operations by helping database administrators mitigate or prevent performance issues. But before buying any tools, DBAs and IT managers should have a solid understanding of the key features to consider in evaluating different products.
When building up your database performance management capabilities, each of the three types of tools that are available should be considered: ones that can ensure maximum performance of a DBMS at the system level, ones that manage the performance of database structures to help maintain the efficiency of data in your databases, and ones that monitor SQL and application performance and identify ways to improve the efficiency of application code.
To help you determine which tool or tools your organization needs, let's review the primary features and functionality that should be examined for each category.
What do the different database performance tools offer?
Database system monitoring and management tools. This software focuses on the internals of the DBMS, as well as system resources and database system parameters. Look for tools that constantly monitor and report on the state of the DBMS and enable the DBA to set performance thresholds that, once reached, trigger alerts. These tools generally offer a dashboard that shows key performance metrics and thresholds, along with an interface for changing the threshold settings and other parameters. The interface typically also provides a mechanism to configure reporting requirements and to generate or schedule the production of database performance reports.
In addition, a good database system performance tool will provide information on DBMS resource usage, such as memory used by buffer pools and caches, CPU utilization, and page locking scenarios. More advanced tools offer performance tuning recommendations based on measured system usage.
Because most DBMS implementations don't involve only a single database instance, a system-level database performance tool should deliver a consolidated view to enable DBAs to manage multiple instances from a single interface. This can include features such as automatic discovery of the database instances and wizards to customize installation.
Database structural monitoring and management products. These tools provide the DBA with the ability to identify, analyze and repair database fragmentation and space usage issues. Database analysis tools monitor, review and report on the storage-related aspects of database structures, such as tablespaces and indexes, and analyze options for improving space utilization.
They also automate free-space analysis and management of such problems as out-of-space conditions, database objects reaching their maximum size, and disorganized and fragmented data. And they provide the ability to send alerts to DBAs about these conditions.
Many database structural performance tools also provide the ability to correct the problems they identify by allocating additional storage or defragmenting the data. Defragmenting, also known as reorganization, is typically a multistep process that must be managed. Advanced tools offer the ability to reorganize a database structure while it remains operational and accessible by applications.
SQL monitoring and management software. These tools monitor SQL statements as they run, allowing the DBA or a database performance analyst to identify potentially problematic programs and SQL statements based on resource usage. For example, a SQL monitor can sort SQL statements by CPU usage or I/O requests issued, thereby making it easier to focus tuning efforts on the ones that consume the most resources. The SQL monitor should also be able to associate SQL statements to particular programs, stored procedures, triggers and end users.
After identifying statements with possible issues, SQL database performance tools with access plan analysis features can be used to examine the access plans for those statements individually and by program. The access plan, which is formulated by the DBMS optimizer component, contains the actual methods that will be used to navigate the database to access the required data. The tools simplify SQL tuning with graphs that visually depict the explain path, SQL tuning wizards for optimizing performance and recommendations for modifying the SQL code.
Some SQL database performance tools also provide the ability to simulate production environments in testing. Often, a test environment doesn't have the same system resources as the production one, so access paths can vary, even if the SQL code doesn't change. Tools that can simulate production in test can eliminate such problems.
Database performance tools offered by the leading vendors may span the categories detailed above. There are individual tools available in each category, as well as combinations of tools that work within and across the different categories.
Additional considerations for selecting a performance tool
In evaluating database performance tools, it's important to consider these items as well:
The number of DBMSes being managed. A heterogeneous database performance tool can manage different DBMSes using the same -- or a similar -- interface, whereas a homogeneous database performance tool can manage only a single type of DBMS. IT shops with one primary DBMS, or where each DBA specializes in a particular DBMS, should consider homogeneous database performance tools, as they typically offer more complete performance management capabilities than heterogeneous tools, making it easier to find and fix problems.
For sites with more than one DBMS and a limited number of DBAs, a heterogeneous database performance management tool can provide similar functionality for each database, making it easier for a DBA to oversee multiple platforms. In addition, a heterogeneous tool can mask the differences between DBMSes and simplify performance monitoring and tuning tasks.
When choosing a heterogeneous tool, though, be sure to thoroughly investigate the functionality it provides for each DBMS your organization needs to manage. It's common for this type of tool to offer differing breadth and depth of performance management functionality for each DBMS it supports.
Metrics over time. Although it's imperative that database performance tools be able to identify and remedy current problems, it's also crucial that they can monitor and analyze database performance metrics over time. Most tools that monitor the DBMS, database structures and SQL can also store historical performance details. This feature is known as a database performance data warehouse or historical performance tables.
Keeping a history of your database performance enables you to identify trends over time. For example, with a historical record of performance metrics, you can verify whether performance is better or worse than before. In addition, the ability to create a performance baseline -- and measure future performance against that baseline -- is made possible only by storing historical performance details.
Cost and support. The cost of database performance tools will vary based on the type of tool, vendor and pricing methodology being used. Some vendors of heterogeneous tools charge for the base tool, as well as for each DBMS that's being managed. Others charge based on the size of the instances to be managed; still others simply charge an annual fee to use the product. Support is usually offered 24/7 online and by phone. It's wise to check out the responsiveness of each vendor's support by calling at various times of the day.
Once you have an understanding of the core requirements and capabilities of the different types of database performance management tools, the next step is to examine each of the top vendors and the specific tools they offer for managing database performance.
