How important is database performance tuning? Well, the world essentially runs on data (the business-focused parts of it, anyway). To ensure data is easily and quickly accessible, databases need to be running efficiently and be available at all times. But, in reality, it can be challenging to maintain optimal database performance.
There are an endless number of reasons why databases don’t perform well, but database experts tend to agree that SQL performance issues are almost always the result of poorly written queries and ineffective indexing.
This is where performance tuning comes into play. SQL performance tuning is the process of making SQL statements run as smoothly as possible by finding and taking the quickest route to answer a query. But deciding where to start performance tuning and if it’s needed can be challenging.
Refining your approach to database performance tuning
How you approach database performance tuning will depend on the performance issues you are trying to resolve.There is no one-size-fits-all performance tuning solution, but there are a few best practices that can help you improve database speed and efficiency.
When you are getting started with database performance tuning, it’s helpful to know exactly what you are dealing with before you wade in too deep. So, I put together a list of 10 database performance tuning best practices that cover everything from the basics all the way through to automation.
1. Keep statistics up to date
Table statistics are used to generate optimal execution plans. If the performance tuning tool is using out-of-date statistics, the plan won’t be optimized for the current situation.
2. Don’t use leading wildcards
Leading wildcards in parameters force a full table scan, even if there is an indexed field inside the table. If the database engine must scan all the rows in a table to find what it’s looking for, the delivery speed of your query results suffers. Other queries may suffer as well, since scanning all of that data into memory will cause the CPU utilization to spike and not allow other queries any time in memory.
3. Avoid SELECT *
This tip is particularly important if you have a large table (think hundreds of columns and millions of rows). If an application only needs a few columns, include them individually instead of wasting time querying for all the data. Again, reading extra data will cause CPU utilization to spike and memory to be thrashed. You should check the Page Life Expectancy (PLE) to make sure you are not having this issue.
4. Use constraints
Constraints are an effective way to speed up queries and helps the SQL optimizer come up with a better execution plan, but the improved performance comes at the cost of the data requiring more memory. The increased query speed may be worth it depending on the business objective, but it’s important to be aware of the price.
5. Look at the actual execution plan, not the estimated plan
The estimated execution plan is helpful when you are writing queries because it gives you a preview of how the plan will run, but it is blind to parameter data types which could be wrong. To get the best results when performance tuning, it’s often better to review the actual execution plan because it uses the latest, most accurate statistics.
6. Adjust queries by making one small change at a time
Making too many changes at once tends to muddy the waters. A better, more efficient approach to query tuning is to make changes with the most expensive operations first and work from there.
7. Adjust indexes to reduce I/O
Before you dive into troubleshooting I/O directly, first try adjusting indexes and query tuning. Consider using a covering index that includes all the columns in the query, this reduces the need to go back to the table as it can get all the columns from the index. Adjusting indexes and query tuning have a high impact on almost all areas of performance, so when they are optimized, many other performance issues resolve as well.
8. Analyze query plans
Utilizing artificial intelligence to analyze your execution plan and determine how to change it helps databases execute operations more efficiently.
9. Compare optimized and original SQL
When optimizing SQL queries, be sure to highlight changes in the SQL statement so you can compare the original statement with the optimized version. Gather a baseline metric such as logical I/O to compare against as you tune. Don’t make any changes until you are sure the optimized version is accurate (i.e., includes current statistics) and really does improve performance.
10. Automate SQL optimization
Automated SQL optimization tools not only analyze your SQL statement but can also automatically rewrite it or optimize indexes until it finds the variation that creates the most improvement in the execution time of the query.
Making database performance tuning a routine practice
Performance tuning is key to improving database performance. By focusing on optimizing queries and cleaning up indexes, a large portion of performance issues can be resolved without too much heavy lifting. Regular database performance tuning helps ensure high availability and fast response times, which is a must-have for today’s end users, who demand nothing less than 100 percent from the applications they rely on.
