Optimizing database performance is one of the key roles of a database administrator. It can end up taking up the majority of their time and in some cases, become an overwhelming problem. The thing about performance issues is that they are noticeable to the database’s users.
Those users often make it a point to become noticeable to the DBAs responsible for the slow-moving systems.
Even if they have other tasks lined up today, when the performance of an important database is an issue, its resolution may be prioritized by entities outside of the control of the database team.
If a web-based application used by customers is impacted by database performance issues, you will likely be deluged with problem tickets to resolve as the help desk tries to mollify irate users. You can count on management to quickly start pressuring the DBAs for results when the business is adversely affected in this way.
The database team can expect the same type of treatment when widely-used or important in-house systems experience poor performance. In this case, the problem tickets and phone calls may be accompanied by furrow-browed executives who you haven’t seen in months hanging around and requesting status updates.
The trickle-down effects of stress and anxiety are something that most DBAs would like to avoid if at all possible.
Key Optimization Methods for Better Database Performance
SQL databases are made up of a lot of moving parts that can contribute to degraded performance. Problems in the system’s SQL queries are often at least partly to blame, and in many cases may be responsible for the bulk of performance issues that need to be addressed. Here are some of the specific areas to look at regarding a database’s SQL code.
Optimizing SELECT statements is one way to speed up database performance. This can be done in a variety of way including:
- Using indexes on all fields that may be used in the WHERE and JOIN parts of your SQL statements;
- Applying filters to limit the amount of data being used in the query;
- Removing unnecessary tables to reduce processing requirements put on the database;
- Removing calculated fields in JOIN and WHERE clauses. This may entail the modification of the table structure to store the value in a new field.
Splitting queries may be necessary when other optimization techniques do not provide the desired results. This tactic needs to be used judiciously so that the number of views used in a JOIN is minimized.
Automating and Streamlining Database Optimization
IDERA’s DB Optimizer is designed to assist DBAs to optimize their SQL code on many major DBMSs including SQL Server, IBM Db2, Oracle, and Sybase. It uses the same interface for all supported platforms, reducing training time and improving team productivity. The application provides features like an SQL tuning wizard and visual tuning diagrams to streamline and automate optimization.
Some specific ways that DB Optimizer accomplishes this feat are:
- Suggesting SQL rewrites that offer the best alternative to a selected SQL statement;
- Explaining the plan cost of each original statement to enable the expected cost of execution to be evaluated;
- Analyzing indexes visually using color-coding and offering recommendations to optimize performance;
- Testing alternative execution paths to determine the fastest running SQL statements.
Unless you are seriously averse to saving yourself time and trouble, automating database optimization tasks with DB Optimizer is a no-brainer. It can save you countless hours of manual optimizing drudgery while keeping the help desk and management queries to a minimum.
When you think about it, DB Optimizer can be seen as a tool to optimize database performance as well as the work-life of an organization’s database team.