The fastest and most effective way of improving performance of a SQL Server is often query tuning. While adjusting the system-level hardware of servers and networks, such as adding additional memory or processes, can improve technical capacity, these measures tend to be expensive to implement and won’t make up for poorly written SQL queries.
In fact, experienced developers generally agree SQL Server performance issues are caused by SQL queries in need of tuning inefficient database indexing, rather than insufficient hardware specs. However, many SQL Server performance issues can only be improved through query optimization. In this guide, I’ll go over common approaches to generally help you to streamline optimizing query performance.
Essential SQL Server Database Performance Tuning Tips
To improve the effectiveness of SQL queries, database admins need visibility and some knowledge of how SQL queries work. This means knowing the top statements and wait types, blocked queries, SQL execution plans, and the effects missing indexes have on database searchability.
There are three general steps to SQL Server performance tuning:
First, you need to undertake basic query analysis, to make sure you’re familiar with your database tables before you start adjusting.
- Basic Query Analysis
- Check table counts and row counts. The first step is to confirm you’re not operating a table-valued function or view. The distinction is important because there are different performance implications to table-valued functions. SQL Server Management Studio allows you to examine the various elements of each query. You’ll also want to be sure to query the dynamic management views to check the table count and row count.
- Check query filters. It’s important to take note of the total filtered row count, as well. If your query doesn’t have filters—which can end up returning most of a database table—give some thought to whether all the returned data is necessary, as filter-less queries can cause drastic slowdowns.
- Check table selectivity. You should now have a good sense of how many rows are included—this is the size of the logical set the query returns. One way to efficiently check the selectivity of queries is to use SQL diagramming.
- Check additional query columns. Look at the SELECT * or scalar functions and see if the query request involves extra columns. The more data returned, the less efficient the query execution plan may be at using specific indexes. This can also slow down SQL Server performance.
- Advanced SQL Query Analysis
- Use constraints. Make sure you look over the existing constraints, indexes, and keys, so you avoid duplications or creating indexes overlapping with existing ones.
- Review the execution plan. It’s important here to look at the actual plan and not an estimation. The number of rows estimated plans predict are indeed only estimates, so you’ll want to be sure to look at the actual plan, which relies on the runtime statistics. If there’s a discrepancy between the estimated and actual plans, it may warrant further investigation.
- Make a note of the results. Pay extra attention to how many logical I/Os there are—without recording the results, it’ll be nearly impossible to tell what effect the changes you make have on the query performance.
- Make small adjustments. Using your findings, adjust the query. If you make a lot of changes at once, you might have difficulty telling which change was effective. Multiple changes could also negate one another, which can be frustrating and time-consuming to sort through. The best bet is to start by examining which operations are most expensive.
- Run queries again and record the changes. Logical I/Os improving are a sign the query is running more efficiently, but if you want to continue making improvements, you can repeat adjusting to keep tuning other factors within the query until expensive operations have been streamlined. Remember to only make one change at a time.
- Consider adjusting indexes. While adding indexes or adjusting them isn’t always the easiest or safest course of action, it offers another way of reducing logical I/O—especially if you can’t change coding. Examine the existing indexes first, which can help you determine whether a filtered or covering index is best for your circumstances.
- Run the query again. Same as above, you’ll want to record the results after each adjustment.
- Keep an eye out for performance inhibitors. Look out for abuse of wildcards, code-first generators, row-by-row processing, and scalar functions.
- Query Performance Tuning by Using a Database Monitoring Tool
SQL Server performance tuning can be complicated, particularly if you’re doing it all manually. While database performance monitoring tools won’t reveal root cause of query performance issues, by focusing on the health metrics of databases, they make the process significantly easier.
Continuous database monitoring tools like SolarWinds® Database Performance Analyzer (DPA) are built to centralize a vast amount of performance information into a central user interface, enabling database admins to quickly identify the queries causing the slowdowns, as well as the specific wait events responsible for the delays.
What to Know About SQL Server Performance Tuning
SQL performance tuning can be intimidating, especially if it’s new to you. Assessing queries and identifying the places where they can be improved might take a little time to pick up but doing so will benefit the performance of your SQL Server instances immensely. It gets complicated since no single technique works for all SQL queries—the best solution for improvement varies case by case. Using a monitoring tool like Database Performance Analyzer is an easy way to get started understanding the opportunities you have to improve overall database performance with performance tuning.