SQL Server Performance Tuning

[7 Tips] How to Improve T-SQL Query Performance

Mark Varnas
No comments

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 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 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 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.

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials