What you need to know about database performance software
Database performance software identifies bottlenecks and points of contention, monitors workload and throughput, and manages system and DBMS resource usage.
Ensuring that database systems perform effectively is a core requirement of modern IT management. There are several types of tools that can help database administrators and other IT professionals monitor, manage and improve the performance of databases and the applications that access them. But before you can consider whether you need database performance software, it's important to first define database performance.
At a high level, database performance can be defined as the rate at which a database management system (DBMS) supplies information to users. But to better understand what influences database performance, let's examine the following five factors:
- Workload is the activity the DBMS is requested to perform, which drives the processing demands placed on the system. The workload can include a combination of transactions, batch jobs, ad hoc queries, reporting and analysis applications, database utilities and system commands. Workload can fluctuate drastically by day, hour or even minute. Sometimes it's steady and predictable, but at other times it can spike, such as a heavy transaction workload on Black Friday for a database supporting a retailer's e-commerce site.
- Throughput is a measurement of a system's ability to process data. It's a composite of I/O and CPU speed, and it can be affected by the parallel capabilities of the database server and the efficiency of the operating system and system software.
- Resources are the hardware and software tools at the disposal of the system -- such as database kernel, disk space, memory, cache controllers and microcode. The performance of accessing and modifying data in the database can be improved by the proper allocation and application of resources.
- Optimization speeds up query performance. All types of systems can be optimized, but relational databases are unique in that query optimization is primarily accomplished internal to the DBMS. Additional factors do need to be considered as well, including SQL formulation and database parameters, to enable the database optimizer to create the most efficient access paths.
- Contention results when the demand for a particular system resource is high, for example, if two or more components of the workload are attempting to use a single resource in a conflicting way, such as dual updates to the same piece of data. As contention increases, throughput decreases.
So, database performance can be more accurately defined as the optimization of resource usage to increase throughput and minimize contention, enabling the largest possible workload to be processed.
Types of database performance management software
Before we delve into the different types of database performance management tools, we need to differentiate between performance monitoring and performance management. Although they mean different things, it's easy to confuse the two. In fact, performance monitoring is an aspect of performance management, which consists of these three broad components:
- Monitoring a database system to find problems as they occur.
- Analyzing data from the system to determine a corrective action.
- Implementing a fix to alleviate the problem.
Database performance software can aid in all three areas. But some simply monitor systems or fix problems, while others deliver combined functionality.
This software can also be broken down by the type of database performance issues it addresses. For example, managing the performance of database applications involves the following three components:
- The DBMS. This system software enables data to be stored and accessed by programs. It must interact with other system software and hardware, requiring proper configuration to ensure it functions accurately and performs satisfactorily. Additionally, there are many system parameters used to configure the resources to be used by the DBMS, as well as its behavior. This includes important performance criteria such as memory capacity, I/O throughput and locking of data pages.
- Database structures. The design of databases, tables and indexes can also impact database performance. Issues include the physical design of the database, disk usage, number of tables, index design and data definition language parameters. How the data is organized must also be managed. And as data is modified in the database, its efficiency will degrade. Reorganization and defragmentation are required to periodically remedy disorganized data.
- SQL and application code. Coding efficient SQL statements can be complicated because there are many different ways to write SQL that return the same results. But the efficiency and performance of each formulation can vary significantly. DBAs need tools that can monitor the SQL code that's being run, show the access paths it uses and provide guidance on how to improve the code.
When do you need database performance tools?
Overall, database performance software can identify bottlenecks and points of contention, monitor workload and throughput, review SQL performance and optimization, monitor storage space and fragmentation, and view and manage your system and DBMS resource usage.
Database performance problems are most commonly brought to light by end users who aren't able to do their jobs effectively. Usually, their concerns are somewhat vague and high-level, such as "the system is sluggish" or "my screen isn't working as fast as it used to." This requires pinpointing the exact problem so that tools can be used to find a solution. Database performance management tools can help to find the problem as well as to formulate and implement a solution to the problem.
Database performance software is essential for organizations that have service-level agreements in place to manage the performance of their applications. These tools can help ensure that adequate levels of service are delivered to all IT users in accordance with business priorities and at acceptable cost.
Many organizations use more than one DBMS, and their DBAs are tasked with ensuring the performance of all of their company's database systems. But each DBMS has different interfaces, parameters and settings that affect how it performs. Heterogeneous database performance tools can simplify the confusion by using intelligent interfaces to make these disparate components look and feel similar from DBMS to DBMS.
The database performance software market
The market for tools can be split into three categories: The first is the DBMS vendor market, which includes IBM, Microsoft and Oracle. These companies sell software for their own DBMSs, as well as heterogeneous tools. The second category includes large ISVs and technology providers such as BMC, CA and Quest that sell database performance software as well as other categories of system software. The third category, niche database tools software vendors, includes companies that focus on database management and administration such as Bradmark Technologies, Idera and SolarWinds.
These tools are typically sold directly to the customer and licensed by individual user, by database core or the size of the server being managed. Frequently, database performance tools are bundled with the database software as part of the initial DBMS acquisition.
Database performance software comprises a significant segment of the overall IT performance management software market. It provides many benefits, and there are numerous products to choose from.
