If you, like other software professionals, think that RDBMS settings can be left as default for improved SQL query performance tuning, then you are wrong.
Default RDBMS settings are far from being the optimal ones and not optimizing can result in performance issues.
The good news, however, is that you can improve SQL query performance with little to no technical knowledge.
Despite being indispensable, SQL performance tuning is a rare skill for software teams to have. So, today I will let you know some essential techniques that never change to make your queries run faster in the Microsoft SQL Server!
Before delving into the tuning SQL Server tips, let’s first see what SQL query tuning is exactly.
What is SQL query tuning?
SQL performance tuning is a process of making queries of a relation database run faster. In simple words, it is a set of practices that makes use of a wide array of techniques, tools, and processes to run queries faster.
Although it is not a single tool or technique to improve SQL query performance, it is the most effective one to deploy. Additionally, SQL query tuning is excellent to deploy an optimized product that runs a dozen times faster.
SQL performance tuning is a vast topic that can be a complicated and struggling task to accomplish, but this post can be a good beginning point to start.
So, here are 7 practical tips to fine-tune your SQL queries.
1. Do not use SELECT *
You should not use SELECT *
in your queries.
It is always a better option and one of the SQL best practices to explicit the column list in a SELECT
query than use a * wild card.
The fewer columns you ask for, the fewer data must be loaded from the disk when processing your query.
Return only the columns needed
Do not return all the columns from a table, if you do not need them.
This usage approach causes unnecessary IO consumption and increases network traffic.
In addition, SELECT *
prevents the use of covered indexes, further potentially hurting query performance.
Using explicit columns in queries helps you to create maintainable code.
This ensures code will not break when someone adds new columns to your table — especially if you have views that refer to the original table.
2. Restrict the number of rows returned
Always restrict the number of rows, verify your WHERE
clause, and use TOP
if necessary.
Choose TOP operator when possible
The SET ROWCOUNT and the TOP operator effectively perform the same function.
They work by allowing you to specify a specific number of rows to be returned.
When the specified number of rows is reached, all processing on the query stops, potentially saving SQL Server overhead, and boosting performance.
There are some instances (such as rows returned from an unsorted heap) where it is more efficient.
3. Consider using EXISTS instead of IN
The SQL EXISTS logical operator allows you to check whether a subquery returns any row.
If the subquery requires to scan a large volume of records, stopping the subquery execution as soon as a single record is matched may significantly speed up the query.
Usually, the same is valid for the NOT IN
(subquery) or NOT EXIST
S (subquery).
Check out the performance gains in this tuning!
SQL Optimizer treats EXISTS
, and IN
the same way, whenever it can.
So in many cases, you are unlikely to see any significant performance differences.
In any situation, proper testing and understanding of the features are essential for a better solution.
Take care of NULL values
Be cautious when using the NOT IN
operator if the subquery’s source data contains NULL
values. If a NULL
value is present in the list, the result set is empty!
4. Use more temporary tables and less variable tables
In most cases, temporary tables beat variable tables easily because their ability to contain statistics improves query optimization dramatically.
That remains true even with the enhancements in SQL Server 2019 (Table Variable Deferred Compilation).
Make the right choice
Variable tables are still the right choice for high frequency and low row volume processes.
Also, for tasks where you don’t need to do anything particularly relational with them. For example, to store lists of databases, tables, and indexes to iterate over.
5. Do delete and update in batches
Deleting or updating large amounts of data can be a nightmare if you don’t do it right.
- These statements run as a single transaction, blocking others’ requests for their durations.
- Your log file may grow unexpectedly and cause you problems.
- If something happens to the system while they’re working, it has to roll back the entire operation, which can take a very long time.
There’s no silver bullet here, but a proper solution is to do deletes or updates in smaller batches.
While the smaller batches are committing to disk, others can sneak in and do some work, so concurrency is greatly enhanced.
Also, if the transaction gets killed for whatever reason, it has a small number of rows to roll back, so the database returns online much quicker.
6. Never take user input to build a query
SQL Server will execute all syntactically valid queries that it receives.
Never concatenate user input that is not validated.
String concatenation is the primary point of entry for script injection.
Any procedure that constructs SQL statements should be reviewed to avoid SQL injection.
Use stored procedures to validate user input.
7. Write queries for your data structures
Take advantage of the indexes, foreign key constraints, and other structures that you have in place when you are writing your T-SQL code.
Make sure that your clustered index is working well to help drive your queries to your data in an efficient manner.
Take the time to understand how the Optimizer works and write your code in such a way that you help it, not hurt it.
Avoid multiple SQL Server joins
Try to avoid writing a SQL query using a large number of joins.
The SQL Server query optimizer may not be able to produce a reasonable plan for retrieving the data.
Break down large queries and store the aggregated or intermediate results in temporary tables.
This allows SQL Server to create new statistics, improving the cardinality estimation.
The result is usually better performance, and as we see before, prefer temporary tables instead of table variables.
Summary
SQL performance tuning is a very important skill for software teams to have as well as DB managers. Despite its need, it is the rarest skill.
Here is a quick summary of all seven tips:
SELECT *
should never be used in a query- Limit the number of rows that are returned
- Instead of
IN
, consider usingEXISTS
- Use fewer variable tables and more temporary tables
- Deleting and updating in batches is a good idea
- Never utilize a user’s input to construct a query
- For your data structures, write queries.
Agree? Disagree? Comment below.