Top five query tuning techniques for Microsoft SQL Server


Data technologies have gone through great advancements over the past decades enabling businesses to easily own and operate databases on the cloud and scale up their resources in a few clicks. Faded by technological improvement, people sometimes neglect the basic but essential techniques that can make their database fast and reliable. In this article, we will learn the top five query-tuning techniques for Microsoft SQL Server.

Tools used in the tutorial
Tool Description Link

Detect slow queries

To tune slow queries, you first need to find them. You will need to examine them one by one and prioritize tuning. Before selecting slow queries, prepare a speed threshold to only include tuning candidates. To query slow queries that are slower than your threshold, check the query below.

1 SELECT 2     req.session_id 3     , req.total_elapsed_time AS duration_ms 4     , req.cpu_time AS cpu_time_ms 5     , req.total_elapsed_time - req.cpu_time AS wait_time 6     , req.logical_reads 7     , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 8       ((CASE statement_end_offset 9         WHEN -1 10         THEN DATALENGTH(ST.text) 11         ELSE req.statement_end_offset 12       END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 13       1, 512) AS statement_text 14 FROM sys.dm_exec_requests AS req 15     CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST 16 WHERE total_elapsed_time > {YOUR_THRESHOLD} 17 ORDER BY total_elapsed_time DESC;

This query gives you a list of elapsed time of each query. The slowest query will appear at the top. You can add your threshold figure in the where clause to, for example, select the top five slowest queries.

Start with the basics

Once you find the queries to be tuned, check if the queries are following the basic rules for performance.

  • Use the where condition to limit scanning scope
  • Don’t select everything
  • Use inner join instead of correlated subqueries
  • Try to avoid HAVING and use WHERE
  • Use inner join instead of two tables in the where clause

Use the where condition to limit scanning scope

The goal of running a query is to get the information you need. When you run a query without a condition, the database needs to scan the whole area of a table, which leads to a slower query response. If possible, use the where clause to precisely aim at the data you need.

Don't select everything

People often use the star symbol (*) for convenience. However, if a table consists of many columns and holds a large number of records, selecting all of the columns and rows will consume more resources. Instead of using select all, specify the column names that you want.

Use inner join instead of correlated subqueries

If you use a correlated subquery (or a repeating subquery), the subquery you use gets executed repeatedly. The sample below shows you what a correlated subquery looks like.

1 SELECT column1, column2, .... 2 FROM table1 outer 3 WHERE column1 operator 4     (SELECT column1, column2 5     FROM table2 6     WHERE expr1 = outer.expr2);

The subquery after the operator is run repeatedly until it satisfies the WHERE condition. Instead of using the correlated subquery, consider using the inner join.

Try to avoid the HAVINGclause and use the WHEREstatement

When you run an aggregated query using GROUP BY, you can add a condition using HAVING. It is recommended to use HAVING only on an aggregated field and not to use it when you can replace it with a where condition. A HAVING clause is calculated after a where clause, so it is recommended to limit data scanning prior to the HAVING clause.

Use inner join instead of two tables in the where clause

You can put two tables in the where clause to use like JOIN. Although it is grammatically accepted, it creates inefficient query execution. Cartesian Join or CROSS JOIN refers to the SQL computation that requires a combination of all possible variables. When you use two tables in the where clause, Cartesian Join kicks in, which consumes a lot of resources. Instead, try to use INNER JOIN.

Use EXPLAIN to find pain points

You can use the EXPLAIN command to diagnose your queries. The command explains your query and shows you how a query will be executed step-by-step. You can use the result to find inefficient steps. The syntax of EXPLAIN is simple.


Put the command, EXPLAIN, above your query and then execute it. In the {YOUR_QUERY} section, you can put not only a SELECT query but also an UPDATEINSERT, or DELETE statement.

But, it is not straightforward to interpret the result returned from the explain query since it throws you all kinds of text and numbers. Instead, try a visual interpretation like DbVisualizer. When you get visual interpretation, it becomes much easier and intuitive to interpret query steps and pinpoint where to focus.

DbVisualizer SQL editor
DbVisualizer SQL editor

When you execute an SQL in DbVisualizer, you can see its explain plan at the bottom section as the image above. When you select the Graph View option highlighted in a red box, you can see graph view.

Graph view of query cost
Query cost is indicated by color

In this graph view, each node in the visual presentation contains more detailed information such as different types of costs, arguments, and more.

Index your tables

Indexing your tables can speed up your query performance. It is like how the index at the end of a book works. By referring to the index, you can go to the page and find the information you need. To efficiently index your tables, there are several points you need to consider.

  • Prioritize tables by frequency and importance: before setting indexes, you need a plan. It is a good practice to prioritize your queries by frequency and importance and then start examining the tables to decide their indexes. For example, if you have some queries that are scheduled to be run every hour and the result of those queries is used for generating invoices for customers (which is directly linked to your revenue), they can become top candidates.
  • Choose columns that are often used in the where clause or join keys: when you index columns, you put those columns in an SQL index table so that when those columns are searched, the database can quickly retrieve the records you are looking for. Check which columns you often use in where or join conditions. Adding them to the index table can speed up a search or a join query.
  • Consider column data types: after researching the columns that are frequently used in join and where conditions, check their data type. The most suitable data type for the index is the integer type and the worst candidate is a string type. Also, a column that always has a unique value and a NOT NULL constraint can be a good index candidate.
DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

Use visualization tools like DbVisualizer

When you perform query tuning on your own, it can be pretty challenging and time-consuming. DbVisualizer is a universal tool that can meet all your database needs from running queries to database management and query tuning. It beautifully displays database system data for users to interpret information more easily. Its optimization feature can help you to achieve your tuning goals and make your database operate efficiently.

In addition to query tuning, there are more tasks that can benefit from visualization by DbVisualizer. Among many, you can utilize its visual query builder and automatic ERD generation.

Visual query builder
Visual query builder

The visual query builder lets you simply click the columns you want to select and choose the operations you want to perform. With this visual feature, you can more intuitively build your query.

ERD generator
ERD generator

The ERD generation feature visually displays the relations of your tables. Using the auto-generated diagram, you can efficiently document your table designs and share your ideas with team members.


In this article, we learned the top five query tuning techniques for Microsoft SQL Server. By optimizing your queries, you can more reliably and efficiently serve data requests that come from your online products, dashboards, ad-hoc queries, and other sources. Also, you can reduce costs for upgrading database resources. DbVisualizer can help you to achieve these objectives by providing a single point for users to perform various tasks of the database. Find out more features of DbVisualizer.

About the author
Igor Bobriakov
Igor Bobriakov
Igor is an entrepreneur and educator with a background in marketing and data science. His passion is to help small businesses and startups to utilize modern technology for growth and success. Igor is Data Scientist and ML Engineer. He founded Data Science School in 2016 and wrote numerous award-winning articles covering advanced tech and marketing topics. You can connect with him on LinkedIn or Twitter.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

Leveraging Snowflake's Time Travel

author Ochuko Onojakpor tags SNOWFLAKE TIME TRAVEL 11 MINS 2023-11-28

MariaDB vs MySQL: The Ultimate Comparison

author Antonello Zanini tags MARIADB MySQL 7 MINS 2023-11-23

Change Data Capture: A Comprehensive Guide

author Ochuko Onojakpor tags DATA CAPTURE 14 MINS 2023-11-21

SQL vs NoSQL Databases: Which is Better?

author Ochuko Onojakpor tags NOSQL SQL 16 MINS 2023-11-20

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16

SQL Cheat Sheet: A Comprehensive Guide to SQL Commands and Queries

author Bonnie tags CHEAT SHEET SQL 18 MINS 2023-11-15

Error Establishing a database connection - Common Reasons and Solutions

author Ochuko Onojakpor tags CONNECTION ERROR 12 MINS 2023-11-13

How to work with SQL query optimization

author Antonello Zanini tags PERFORMANCE 10 MINS 2023-11-09

Low-code for DBA's

author Gayatri Sachdeva tags DBA DRONAHQ 4 MINS 2023-10-31

SQL Interview Questions (MySQL): Part 1

author Lukas Vileikis tags MySQL 6 MINS 2023-10-25

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗