Database performance tuning and query optimization are critical capabilities for modern, data-driven organizations.
Database performance tuning – or database tuning – describes the process of optimizing database performance to maximize the use of system resources to increase efficiency.
The function and goals of query optimization are similar, with query optimization focussing on increasing efficiency around query execution.
The Value of Database Performance Monitoring Tools for Database Tuning and Query Optimization
Database administrators (DBAs) are concerned with many aspects of the systems they support. Their databases have to be available to users when they need them and be backed up to protect enterprise data resources. A very important part of a DBA’s job is to maintain acceptable levels of performance so users are not plagued with excessive wait times when accessing a system.
Database performance monitoring tools help DBA’s achieve this by providing insight as to what to tune and optimize.
Depending on the role a given system plays, its performance can be considered more or less critical to an organization. Test and development systems used primarily by internal teams usually deserve less attention than mission or business-critical systems designed to be accessed by customers. Making customers wait for a database to respond is not the preferred way to do business and can quickly send them in search of an alternate solution.
Where to Start Database Performance Tuning: Adopt a Systematic Approach
Modern relational databases are complicated entities that present DBAs with a wide variety of areas that need to be investigated to address lagging performance. The first step in successfully tuning a badly performing database is to identify the source of the problem. That’s often easier said than done.
In this post, we are going to concentrate on finding the SQL code and queries that are impacting database performance. We will assume that the team has already ensured that the system is properly provisioned for the amount of traffic it is expected to attract. It doesn’t need a faster processor, more disk space, or better network bandwidth. The team has determined that the issue lies with the code and not in how the database and applications are configured.
Now the real fun starts as the DBAs need to find the offending pieces of code. There can be multiple thousands of SQL queries and lines of code in a database implementation. Randomly selecting queries for tuning is inefficient. To avoid wasting time and potentially making performance matters worse, the team needs some guidance regarding which queries to tune.
This is where database monitoring comes in. Through the use of a reliable and robust monitoring tool, a database team can gain insight into the queries that need to be tuned to improve database performance. Several characteristics of the system’s query population can be used to identify likely candidates for focused attention and tuning. A monitoring tool can pinpoint these queries more efficiently than can be done through human observation by a DBA.
Which Queries and Objects Should be Optimized?
The following factors differentiate the queries and objects that should be addressed first when tuning a database. While all queries can theoretically benefit from optimization, there is usually a subset that is causing the majority of the performance problems.
The length of time required to execute a query
A logical place to search for possible optimization is to find the queries that are taking the longest time to execute. Even in cases where a specific query is only executed occasionally, it can be poorly constructed and run for much longer than necessary. This behavior can cause resource bottlenecks and be responsible for performance issues not directly related to this query.
The frequency with which a query is executed
Queries that exhibit a high execution frequency should be identified and carefully investigated for tuning opportunities. A query that is run consistently and is not properly tuned can have a devastating effect on overall performance. Making even a small improvement in the speed with which this type of query executes can make a big difference in overall database performance.
The database objects involved with satisfying a query
If certain tables and database objects are frequently accessed by queries, there may be opportunities to optimize them. This can entail making modifications to the index structure of a specific table which can have a positive impact on the performance of all queries that use it.
Using a reliable monitoring tool that provides the type of information needed to isolate problem queries and objects is necessary for effective optimization.
The Right Tool for Multi-Platform Database Performance Tuning
Precise for Databases provides the tool DBAs need to tune the performance of Microsoft SQL Server, Oracle Database, IBM Db2, and SAP Sybase ASE systems from a unified interface. It’s a comprehensive database monitoring tool that can help teams identify the queries and objects that need to be addressed to improve performance. The tool includes a monitoring platform packed with features to address the needs of a team engaged in database performance tuning.
Precise enables the continuous collection of database transactions so no potential problems or trends are missed. Data is sampled frequently and transactions can be correlated to users, databases, files, and devices to identify exactly where performance is being impacted. Once a transaction has been identified in the dashboard, drill-down capabilities facilitate finding the root cause of the problem.
The tool displays SQL query execution plans so they can be tuned and offers expert advice regarding how to improve objects, statements, and indexes. What-if scenarios can be analyzed to view the impact of proposed changes. A SmarTune process regularly analyzes performance data to identify degraded performance.
Precise gives database teams what they need to improve performance and keep the customers satisfied.