Managing the performance of database systems and applications is a significant job responsibility for DBAs. From a database perspective, there are three basic performance components that must be performed:
- Monitoring the database management system and the applications accessing it to find problems as they arise. This is typically referred to as performance monitoring.
- Analyzing performance data (logs, trace records, reports, etc.) from the system to determine the root cause of problems.
- Assembling a corrective action to implement a fix to the problems.
There are database performance software products that can aid with all three of these components. But you must be careful to fully understand the capabilities of any database performance management solution, as some simply monitor, others just analyze data or provide fixes for problems, and others deliver functionality combining all of these tasks.
You can also break down database performance management software by the category of performance issues it addresses. Database performance problems can occur in any of the following three areas:
- The DBMS itself, which must interact with other system software and hardware, requiring proper configuration to ensure it functions accurately and performs satisfactorily. Additionally, there are many database system parameters used to configure the behavior of the DBMS and the resources it has available to it. This includes criteria such as memory capacity, I/O throughput, and locking of data pages.
- The database design and schema, including database parameters, table designs, and indexing, can all impact database performance. How the data is organized must also be managed; as data is modified in the database, its efficiency will degrade. Reorganization and defragmentation are required to periodically remedy disorganized data.
- Finally, the SQL and application code itself can cause performance issues. 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.
Database performance tools 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. Of course, a single tool is unlikely to perform all of these tasks, so you likely will need multiple tools (perhaps integrated into a functional suite) to perform all of your required database performance management tasks.
Without proactive tools that can identify problems as they occur, database performance problems are most commonly brought to the attention of the DBA by end users. The phone rings and the DBA hears a complaint that is usually vague and a bit difficult to interpret, such as, “My system is slow today” or, “My screen isn’t as fast as it used to be.” To resolve such issues, DBAs need tools that can help uncover the exact problem and identify a solution. Database performance management tools can be used to find the root cause of such problems as well as to deploy a solution to fix the problem.
Furthermore, many organizations use multiple DBMS products in production, and the same DBA team (and sometimes even the same exact DBA) will have to ensure the performance of more than one DBMS (such as Oracle and SQL Server or Db2 and PostgreSQL). But each DBMS has different interfaces, parameters, and settings that affect how it performs. Database performance tools can mitigate this complexity with intelligent interfaces that make disparate components and settings look and feel similar from DBMS to DBMS.
There are many providers of database performance management tools, including the DBMS vendors (such as IBM, Microsoft, and Oracle), large ISVs (such as BMC and CA), and a wide array of niche vendors that focus on DBA and database performance software (for example, Quest, IDERA, and Navicat).
The exact database performance management solutions you should use depend upon the database systems you utilize, the size of your organization, the amount of data managed, your service level agreements, and your budget. But managing production databases without performance tools is a recipe for failure.