SQL statement optimization and database performance optimization more broadly, are some of the most important activities for modern database administrators (DBAs).
Slowly responding databases present issues that need to be addressed promptly for several reasons. Badly performing business-critical databases cause problems ranging from disgruntled users to lost revenue, angry management, and rising stress levels for the support team.
Unfortunately, identifying a database suffering from performance issues and resolving its problems are two very different things.
It’s relatively easy to monitor a database’s response time and compare it to acceptable thresholds. You don’t need to be a DBA to understand reports showing degraded database performance or to field calls from dissatisfied end users.
Finding the specific causes of performance degradation is a much more elusive endeavor. Relational databases are complex entities whose behaviors can be influenced by any number of internal or external circumstances.
Performance can fluctuate mysteriously based on factors like the time of day, the number of concurrent users, and the particular SQL queries that are being executed.
Improving Database Performance
DBAs have several techniques at their disposal to help identify and resolve problems with the SQL statements that drive their databases. Two of the most productive methods of finding and fixing performance issues are database profiling and SQL statement optimization.
Database profiling
Profiling is the practice of continuously sampling a data source so a statistical model can be built demonstrating the load on a database. Through profiling, teams can identify event-based bottlenecks and locate troublesome SQL code to assist in SQL statement optimization. A robust database profiling tool allows users to filter out lightweight and well-performing SQL statements so they can focus on heavyweight SQL.
Heavyweight SQL includes long-running queries as well as often-executed short queries. Both types of queries can result in excessive load on a database. This is often accompanied by degraded database performance.
Profiling can uncover problems that can generally be categorized as:
- Hardware issues such as slow CPU, disks, or network;
- Invalid SQL statements or application locks;
- Problems with database cache or log files;
- Inefficient SQL statements.
The issues identified through profiling need to be addressed to rectify the database’s performance problems. We’re going to specifically look at tuning a database by optimizing SQL statements.
Optimizing SQL Statements
SQL statements and queries present many opportunities for optimization. Monitoring wait times and studying execution plans will help identify the statements that are guilty of negatively impacting performance.
Some general best practices can be used across database platforms to improve inefficient queries.
- Define SELECT fields instead of using SELECT * to minimize the amount of unnecessary data used to satisfy the query.
- Refrain from using SELECT DISTINCT where possible as this construct requires a large amount of processing power.
- Use wildcards at the end of a phrase, again to reduce the number of records that need to be searched to satisfy the request.
- Schedule long-running queries that cannot be further optimized during off-peak hours when the number of concurrent users is at its lowest.
- Properly index database tables. Poorly structured indexes are often the reason a database is underperforming.
An iterative process that includes profiling and optimization should be sufficient to improve slow responding databases.
Streamlining the Process with DB Optimizer
IDERA’s DB Optimizer facilitates the process of optimizing SQL statements and queries on SQLServer, Oracle, Db2, and Sybase databases. It’s a mature software solution that has been helping DBAs maintain system performance for over 20 years and employs a single user interface for increased productivity. The advanced features of DB Optimizer include:
- Automated performance optimization recommendations;
- Visual SQL Tuning (VST) diagrams to more easily understand the impact of statements on the database;
- Wait-time analysis to pinpoint database bottlenecks;
- Load-testing capability to simulate the behavior of alternate SQL queries on a production environment.
An instructive IDERA video walks users through profiling and tuning SQL statements with DB Optimizer. It demonstrates multiple ways DB Optimizer can help improve the performance of supported database platforms. It’s a great introduction to the tool and shows how easily DBAs can perform SQL statement optimization.
DB Optimizer is available as a stand-alone tool as well as bundled with other database management and development tools in DB PowerStudio.