SQL Query Optimization: How to Tune Performance of SQL Queries

December 23rd, 2021

In the article, we are going to examine how to optimize SQL queries and improve query performance by using SQL query optimization tips and techniques, such as execution plans, indexes, wildcards, and many others.

Download a 30-day trial version of dbForge Studio for SQL Server and evaluate its cutting-edge features

When businesses and companies face SQL Server performance challenges, they focus usually on applying performance tuning tools and optimization techniques. This will help not only analyze and make queries run faster but also eliminate performance issues, troubleshoot poor performance, and avoid any chaos or minimize the impact on SQL Server databases.

Contents

SQL query optimization basics

Query optimization is a process of defining the most efficient and optimal way and techniques that can be used to improve query performance based on rational use of system resources and performance metrics. The purpose of query tuning is to find a way to decrease the response time of the query, prevent the excessive consumption of resources, and identify poor query performance.

In the context of query optimization, query processing identifies how to faster retrieve data from SQL Server by analyzing execution steps of the query, optimization techniques, and other information about the query.

12 Query optimization tips for better performance

Monitoring metrics can be used to evaluate query runtime, detect performance pitfalls, and show how they can be improved. For example, they include:

  • Execution plan: A SQL Server query optimizer executes the query step by step, scans indexes to retrieve data, and provides a detailed overview of metrics during query execution.
  • Input/Output statistics: Used to identify the number of logical and physical reading operations during the query execution that helps users detect cache/memory capacity issues.
  • Buffer cache: Used to reduce memory usage on the server.
  • Latency: Used to analyze the duration of queries or operations.
  • Indexes: Used to accelerate reading operations on the SQL Server.
  • Memory-optimized tables: Used to store table data in memory to make reading and writing operations run faster.

Now, we’ll discuss the best SQL Server performance tuning practices and tips you may apply when writing SQL queries.

Tip 1: Use SELECT fields instead of SELECT ALL

The SELECT statement is used to retrieve data from the database. In the case of large databases, it is not recommended to retrieve all data because this will take more resources on querying a huge volume of data.

If we execute the following query, we will retrieve all data from the Person.Person table.

USE AdventureWorks2019
GO

SELECT
  *
FROM Person.Person p

Instead, you can specify the columns from which you need to get data, thus, saving database resources.

For example:

USE AdventureWorks2019
GO

SELECT
    FirstName
   ,LastName
   ,EmailPromotion
FROM Person.Person p

Tip 2: Avoid using SELECT DISTINCT

The SQL DISTINCT operator is used to select only unique values of the column and thus eliminate duplicated values. It has the following syntax:

SELECT DISTINCT column_name FROM table_name;

However, this may require the tool to process large volumes of data and as a result, make the query run slowly. For example, you can check it by enabling performance metrics such as the SET STATISTICS statement and executing the queries with SELECT DISTINCT again. You will see that it does not make a bit of difference.

Instead, to make a query run faster on the SQL Server, you should avoid using SELECT DISTINCT and simply execute the SELECT statement but specify columns.

Tip 3: Create joins with INNER JOIN (not WHERE)

The INNER JOIN statement returns all matching rows from joined tables, while the WHERE clause filters the resulting rows based on the specified condition.

Let’s see how to optimize a SQL query with INNER JOIN on a particular example. We are going to retrieve data from the tables HumanResources.Department and HumanResources.EmployeeDepartmentHistory where DepartmentIDs are the same. First, execute the SELECT statement with the INNER JOIN type:

SELECT
  d.DepartmentID
 ,d.Name
 ,d.GroupName
FROM HumanResources.Department d
INNER JOIN HumanResources.EmployeeDepartmentHistory edh
  ON d.DepartmentID = edh.DepartmentID

Then, use the WHERE clause instead of INNER JOIN to join the tables in the SELECT statement:

SELECT
  d.Name
 ,d.GroupName
 ,d.DepartmentID
FROM HumanResources.Department d
    ,HumanResources.EmployeeDepartmentHistory edh
WHERE d.DepartmentID = edh.DepartmentID

Both queries will output the following result:

Retrieve data using INNER JOIN

However, the query with the WHERE clause will have to waste more resources. Because, first, it generates all possible combinations, then filters them based on the condition, and finally outputs the result.

Tip 4: Avoid using multiple OR in the JOIN predicate

When you need to retrieve multiple columns from several tables, it is recommended to eliminate the usage of the OR operator or split the query into small queries. Instead, it assesses each component of the OR operation which, in turn, may lead to poor performance.

Tip 5: Use WHERE instead of HAVING to define filters

The WHERE clause is used to define a filtering condition on the selected columns while retrieving the data from the table or by joining with multiple tables.

The HAVING clause is used to specify a filtering condition on groups created by the GROUP BY clause. In addition, HAVING filters the rows after all the rows are selected, and is performed after the WHERE clause.

Let’ take a closer look at the following query:

SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName

FROM Person.Person p
INNER JOIN Person.EmailAddress ea
ON p.BusinessEntityID = ea.BusinessEntityID
GROUP BY p.BusinessEntityID
,p.FirstName
,p.LastName
,ea.EmailAddressID
HAVING ea.EmailAddressID BETWEEN 100 AND 200

First, the query scans data from the Person.EmailAddress table, then filters data based on the specified range, and finally outputs the resulting rows.

If we execute the same query with the WHERE clause instead of the HAVING clause, the result set will retrieve the data limited to the specified condition, thus, avoiding any additional scans and steps.

SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName

FROM Person.Person p
INNER JOIN Person.EmailAddress ea
ON p.BusinessEntityID = ea.BusinessEntityID
WHERE ea.EmailAddressID BETWEEN 100 AND 200
GROUP BY p.BusinessEntityID
,p.FirstName
,p.LastName
,ea.EmailAddressID

Tip 6: Use wildcards

Wildcards serve as a placeholder of words and phrases and can be added at the beginning/end of them. To make data retrieval more efficient and faster, you can use wildcards in the SELECT statement at the end of a phrase. For example:

SELECT
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

As a result, the query will retrieve a list of customers whose First Name matches the specified condition, i.e. their First Name starts with ‘And’.

Retrieve data using wildcards in the SELECT statement

Tip 7: Use TOP to sample query results

The SELECT TOP command is used to set a limit on the number of records to be returned from the database. To make sure that your query will output the required result, you can use this command to fetch several rows as a sample.

For example, take the query from the previous section and define the limit of 5 records in the result set.

SELECT TOP 5
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,p.Title
FROM Person.Person p
WHERE p.FirstName LIKE 'And%';

This query will retrieve only 5 records matching the condition:

Use LIMIT (TOP in SQL) to sample query results

Tip 8: Run the query during off-peak hours

Another SQL tuning technique is to schedule the query execution at off-peak hours, especially if you need to run multiple SELECT queries from large tables, nested subqueries, looping queries, etc.

Tip 9: Missing indexes

In SQL Server, when you execute a query, the optimizer can generate an execution plan. If it detects the missing index that may be created to optimize performance, the execution plan suggests this in the warning section. With this suggestion, it informs you which columns the current SQL should index and how much performance can be improved upon completion.

Let’s run the Query Profiler available in dbForge Studio for SQL Server to see how it works.

Execution plan displays missing indexes in dbForge Studio for SQL Server

Keep in mind that the missing index does not guarantee better performance, it can only provide a probability. In SQL Server, you can use the following dynamic management views that may help you get a deep insight in using indexes based on query execution history:

  • sys.dm_db_missing_index_details: Provides information about the suggested missing index, except for spatial indexes.
  • sys.dm_db_missing_index_columns: Returns information about the table columns that do not contain indexes.
  • sys.dm_db_missing_index_group_stats: Returns summary information about the missing index group, such as query cost, avg_user_impact (informs you how much performance can be improved by increasing the missing index), and some other metrics to measure effectiveness.
  • sys.dm_db_missing_index_groups: Provides information about missing indexes included in a specific index group.

Tip 10: Minimize the usage of any query hint

When you face performance issues, you may use query hints to optimize queries. They are specified in T-SQL statements and make the optimizer select the execution plan based on this hint. Usually, query hints include NOLOCK, Optimize For and Recompile Merge/Hash/Loop. However, you should carefully consider their usage because sometimes they may cause more unexpected side effects, undesirable impacts, or even break business logic when trying to solve the issue. For example, you write additional code for the hints that can be inapplicable or obsolete after a while. This means that you should always monitor, manage, check, and keep hints up to date.

Tip 11: Minimize large write operations

Writing, modifying, deleting, or importing large volumes of data may impact query performance and even block the table when it requires updating and manipulating data, adding indexes or check constraints to queries, processing triggers, etc. In addition, writing a lot of data will increase the size of log files. Thus, large write operations may not be a huge performance issue, but you should be aware of their consequences and be prepared in case of unexpected behavior.

Tip 12: High table count

When you add multiple tables to a query, you may overload it. In addition, a large number of tables may result in an inefficient execution plan. When generating a plan, the SQL query optimizer needs to identify how the tables are joined, in which order, how and when to apply filters and aggregation.

For SQL query optimization, you can split a single query into several separate queries which can later be joined, remove unnecessary joins, subqueries, tables, etc.

SQL query optimization best practices

SQL Server performance tuning and SQL query optimization are some of the main aspects for database developers and administrators. They need to carefully consider the usage of specific operators, the number of tables on a query, the size of a query, its execution plan, statistics, resource allocation, and other performance metrics – all that may improve and tune query performance or make it worse.

For better query performance, we recommend using tips and techniques presented in the article, such as running queries at off-peak hours, creating indexes, retrieving data only for the specific columns, applying the correct filter, joins, and operators, as well as trying not to overload queries.

Conclusion

In the article, we have covered a lot of fine-tuning techniques and tips to improve performance. We hope that they will work for you and help you avoid any performance issues that may arise.

Also, we suggest that you should try a free fully-functional 30-day trial version of dbForge Studio for SQL Server to work with SQL queries effectively.

Dowload a 30-day trial version of dbForge Studio for SQL Server

Watch the video to get to know the powerful capabilities of our all-in-one SQL Server GUI Tool.

Comments are closed.