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!
But 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 into 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 EXISTS (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 with 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 table variables easily because their ability to have statistics improves query optimization dramatically.
That remains even true 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.
FAQs
Does SQL optimize queries?
Optimizing is a costly process. So, the SQL server maintains an execution plan cache that has the ability to store details about each query. Later, the query is executed on a server on the plan that was chosen for it.
How do I tune a SQL Server database?
There are multiple ways to tune a SQL server database. These include: from the tools menu in SQL server management studio, from the start menu, from the Query Editor in SQL server management studio, and from the tools menu in SQL server profiler. When you initially start Database Engine Tuning Advisor, you’ll get a Connect to Server dialogue box where you may choose which SQL Server instance you wish to connect to.
How do you optimize complex SQL queries?
First, start with an explanation plan feature to check what the cost of the query is. Secondly, check for full scan tables from the explanation plan. Before moving to SQL tuning and applying indexes, check some points and do the changes accordingly. Thirdly, apply the indexes to the table of columns. Lastly, after proper indexing, apply table partitioning techniques.
How do I resolve SQL Server performance issues?
You can resolve SQL server performance issues with 5 steps. These include: Make sure your TempDB database is configured correctly; execute index maintenance on a regular basis; implement indexes that help your queries; check your most expensive queries and stored procedures; and keep an eye on your performance counters.
What is performance tuning in SQL Server DBA?
SQL Server performance optimization is a collection of processes and methods for optimizing relational database queries so that they run as quickly as feasible. Identifying which queries are encountering slowdowns and optimizing them for maximum efficiency are all part of SQL optimization.
Summary
SQL performance tuning is a very important skill to have for software teams as well as DB managers. Despite its need, it is the rarest skill. However, not now as in this guide we gave you 7 practical tips with which you can improve SQL query performance and that too without any special technical knowledge.
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 using EXISTS. 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.
