Mastering SQL query optimisation

Databases play a crucial role in most large software systems. Inefficient SQL queries often lead to performance issues in such systems. Optimising SQL queries is a complex topic requiring deep expertise but is often overlooked by many software engineers. This blog post aims to explore various ways to optimise inefficient SQL queries. Before delving into optimisation techniques, let’s briefly understand how data is managed in an SQL server and the factors impacting performance.

In a relational database, data is organised into tables, rows, and columns, and stored on disk. To speed up data retrieval operations, indexes are added to tables. Clustered indexes use a B-tree structure mirroring the physical order of data on disk, while non-clustered indexes contain pointers to data pages. Well-designed indexes facilitate the retrieval of desired data and significantly reduce disk I/O operations, which are slower than in-memory operations.

In addition to indexes, SQL Server maintains several statistics that provide valuable information about the distribution of data within a table and its indexes. The query optimiser uses these statistics to estimate the cost of different execution paths and produce an optimal execution plan for a given query.

Troubleshooting query performance often involves analysing the most expensive operations within an execution plan. The following two operations are always worth looking into as potential candidates for improvements:

Table Scan: A table scan or a clustered index scan is the most I/O-intensive operation, involving reading each row and column from the disk. Only queries returning a substantial number of rows justify this operation.

Index Scan: An index scan traverses index nodes instead of data nodes to identify relevant rows. This operation usually requires fewer I/O operations than a table scan but is still more expensive than an index or a table seek.

Indexes related optimisations

The goal of query optimisation is to reduce the number of disk I/Os and minimise scan operations. Here are various strategies to achieve this:

1. Adding appropriate indexes

Add suitable indexes to transform operations into efficient table seeks, index seeks, or index scans, improving overall query execution. At the end of this blog, you can find a script that can be used to find missing indexes as recommended by SQL Server based on your query usage.

2. Keeping the number of indexes low

While indexes expedite read operations, they introduce additional write overhead during data modifications. Assess the trade-off between performance gains from index usage and the maintenance cost of indexes. Only add indexes where the performance benefits justify the associated maintenance overhead. Delete unused or rarely used indexes.

3. Avoiding SQL functions on index columns

Applying functions directly to indexed columns in query predicates can inhibit optimal index utilisation. In scenarios where such cases are unavoidable and queries are frequently executed, consider introducing a computed column and an index on the computed column to resolve this issue. This approach allows queries to use the indexed computed column directly, optimising performance by avoiding function execution during query processing.

-- Prepare test data
CREATE TABLE Product (Id INT IDENTITY(1, 1), [Code] VARCHAR(20), 
[NAME] VARCHAR(20), [Description] VARCHAR(20), Manufacturer VARCHAR(20))
GO
CREATE NONCLUSTERED INDEX [IX_Product_NAME] ON Product (NAME)
GO
INSERT INTO Product([Code], [Name],[Description], Manufacturer) VALUES(LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20))
GO 1000

SELECT * 
FROM Product
WHERE UPPER(Name) = 'SOME UPPER CASE VALUE'

Figure 1: The query above will not use an index seek, even if there is an index based on ‘Name’. Consider adding a computed column based on ‘UPPER(Name)’ and an index on the computed column to improve query performance.

ALTER TABLE Product ADD UpperName AS (UPPER(Name))
GO
CREATE NONCLUSTERED INDEX [IX_Product_UpperName] ON Product (UpperName)
GO

SELECT * 
FROM PRODUCT
WHERE UpperName = 'SOME UPPER CASE VALUE'

Figure 2: Changed index scan to index seek by adding a computer column and an index based on the computed column

4. Aligning data types

Mismatched data types between indexed columns and query predicates will cause implicit type conversion. This conversion adds overhead to query execution, and SQL Server might not be able to utilise index seek efficiency. Instead of an index seek, an index scan operation will be used. Ensuring proper alignment of data types provides optimal index utilisation, allowing queries to leverage indexes efficiently.

-- Prepare test data
CREATE TABLE Product (Id INT IDENTITY(1, 1), [Code] VARCHAR(20), 
[NAME] VARCHAR(20), [Description] VARCHAR(20), Manufacturer VARCHAR(20))
GO
CREATE NONCLUSTERED INDEX [IX_Product_NAME] ON Product (NAME)
GO
INSERT INTO Product([Code],[Name],[Description], Manufacturer) VALUES(LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20))
GO 1000

Figure 3: Index scan for query 1 because the column ‘Name’ and @searchParam1 don’t have the same data type . Changing the data type of @searchParm2 to VARCHAR(20) fixed the performance issue; Instead of the slow index scan, query 2 uses index seek.

5. Reordering index columns

The order of columns in an index impacts its usability for different query patterns. For instance, an index on columns (a, b, c) will be effective for queries involving (a, b) but not for queries focusing solely on column (b). To maximize index utility, consider rearranging index columns to accommodate the most common query patterns. Reordering to (b, a, c) can broaden the index’s applicability across various search predicates.

-- Prepare test data
CREATE TABLE Product (Id INT IDENTITY(1, 1), [Code] VARCHAR(20), 
[NAME] VARCHAR(20), [Description] VARCHAR(20), Manufacturer VARCHAR(20))
GO
CREATE NONCLUSTERED INDEX [IX_Product_Code_NAME_Description] ON Product ([Code],[Name],[Description])
GO
INSERT INTO Product([Code],[Name],[Description], Manufacturer) VALUES(LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20))
GO 1000

Figure 4: Index seek: index is effective for a query with predicates on columns ‘Code’ and ‘Name’.

Figure 5: Index scan: index is not effective for query with predicate on column ‘Name’.

Figure 6: Index is effective for both queries after modifying the column order of the index.

6. Including additional columns in an index

If an index is frequently used for queries requiring a small set of additional columns in the result, consider including these columns as part of the index (included columns). This optimisation avoids unnecessary data node retrievals, enhancing query performance.

-- Prepare test data
CREATE TABLE Product (Id INT IDENTITY(1, 1), [Code] VARCHAR(20), 
[NAME] VARCHAR(20), [Description] VARCHAR(20), Manufacturer VARCHAR(20))
GO
CREATE NONCLUSTERED INDEX [IX_Product_Code] ON Product ([Code])
GO
INSERT INTO Product([Code],[Name],[Description], Manufacturer) VALUES(LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20),LEFT(NEWID(),20))
GO 1000

Figure 7: Index seek + RID lookup to retrieve ‘Manufacturer’

Figure 8: Including column ‘Manufacturer’ into Index eliminates RID lookup.

7. Using sequential keys for clustered index

Utilising strictly increasing sequential keys for a clustered index ensures that a new row is inserted into the last leaf node of the index structure. A new data page is only created if the last data page does not have sufficient disk space to accommodate the new row. This approach allows data pages to be nearly 100% filled, thereby avoiding undesired page splits that would typically occur if the clustered index were not strictly increasing and the data page were nearly full. Implementing this strategy reduces the number of required data pages and avoids the maintenance overhead associated with page splits.

Using a UNIQUEIDENTIFIER, especially when it’s not sequential, can lead to fragmentation within the clustered index. Unlike integer-based keys, which naturally maintain order and minimise page splits, UNIQUEIDENTIFIER values are random and do not ensure sequential insertion. Consequently, each new row insertion might result in a different location within the index, potentially causing page splits and fragmentation. This fragmentation can degrade query performance and increase storage overhead, as the database engine needs to manage scattered data across multiple pages. Therefore, using a UNIQUEIDENTIFIER as a clustered key is generally discouraged for large tables with high insert rates or frequent data modifications.

8. Performing periodic reorganisation/rebuild of indexes

Over time, as rows are inserted, updated, and deleted, an index of a table can become fragmented. This fragmentation reduces the fullness of a page and increases the number of pages required for storing the same amount of index data. Reorganizing or rebuilding an index eliminates fragmentation and reduces storage on the disk. Index reorganization is an online operation and can be performed without blocking the entire table, whereas index rebuild provides a more thorough optimization by dropping the existing index and rebuilding it from scratch.

How to deal with parameter sniffing

We have discussed methods of enhancing query performance by optimising indexes. Even if all tables of the database have appropriate indexes, performance issues can arise because of suboptimal execution plans. Let’s briefly examine what causes SQL Server to choose suboptimal execution plans and how to prevent this from happening.

The process of selecting the optimal execution plan for a given query is very costly in terms of CPU power. To enhance performance, SQL Server caches the execution plan for future use. Parameter sniffing occurs when the cached execution plan, which was chosen based on the initial query parameter when the query first ran, is suboptimal for the same query with a different parameter. The strategy of caching the execution plan works only if data is evenly distributed, and each individual query parameter yields a similar number of resulting rows. There are several mitigation strategies to address this issue.

1. Keeping statistics up-to-date

Perform regular statistics updates, especially after large data modifications, to ensure the query optimizer uses up-to-date values for its cost estimation evaluation.

2. Using query hint OPTION(RECOMPILE)

This query hint instructs SQL Server to recompile the query each time it is executed and ignore the cached execution plan. This ensures that the optimal execution plan is chosen for the query but introduces additional overhead for query compilation. This could be problematic for complex queries that are executed very often.

3. Using query hint OPTIMIZE FOR UNKNOWN

This query hint instructs SQL Server to use the average distribution of the data instead of the query parameter to estimate the cost and choose the execution plan, caching it for future use. While the cached execution plan works well for most query parameters, there is no guarantee that the execution plan works for all query parameters. Another caveat is that the chosen execution plan might not be the most efficient plan for many query parameters.

Avoid inefficient queries

The final optimisation strategy we will discuss involves rewriting inefficient queries.

1. Avoid using the LIKE operator starting with ‘%’ or ‘_’

Indexes are most effective when the search can be narrowed down to a subset of rows. Using the LIKE operator with patterns that start with ‘%’ or ‘_’ prevents the search from being limited to a specific subset of rows.

2. Avoid subqueries in WHERE conditions

Queries with subqueries in their WHERE conditions can perform poorly. Instead, consider using EXISTS or INNER JOIN instead of IN, and use NOT EXISTS or LEFT JOIN instead of NOT IN.

Figure 9: For example, by replacing the subquery to use a LEFT JOIN and checking for NULL values in p2.Description, the relative cost decreased from 74% to 26%. The query with LEFT JOIN is almost 3x faster.

Conclusion

Optimising SQL queries is a critical aspect of database performance tuning that can significantly enhance the efficiency of large software systems. By understanding the underlying principles of data organisation, index structures, and query optimisation techniques, developers can proactively address performance bottlenecks and improve overall system responsiveness. Comparing execution plans before and after optimisation, ideally using different parameter sets, is essential to ensure that the chosen optimisations are effective across various use cases. The strategies discussed in this blog only scratch the surface of the topic and serve merely as an introduction to query optimisation. At the end of this blog, you will find some scripts that I consider useful.

Useful scripts

1. Script for finding missing indexes based on query usage

SELECT 
    migs.last_user_seek,
    mid.[statement] AS [Database.Schema.Table],
    mid.equality_columns AS [Equality Columns],
    mid.inequality_columns AS [Inequality Columns],
    mid.included_columns AS [Included Columns],
    migs.unique_compiles AS [Unique Compiles],
    migs.user_seeks AS [User Seeks],
    migs.avg_total_user_cost AS [Avg Total User Cost],
    migs.avg_user_impact AS [Avg User Impact (%)],
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS 
[Index Improvement],
'CREATE INDEX [IX_missing_' + CONVERT(VARCHAR, mig.index_group_handle) + 
'_' + CONVERT(VARCHAR, mid.index_handle)
        + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
        + ' ON ' + mid.[statement]
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL AND 
         mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '')
        + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS 
[CREATE INDEX Statement]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC;

2. Script for finding unused indexes

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE indexes.is_primary_key = 0
    AND indexes. is_unique = 0
    AND dm_db_index_usage_stats.user_updates <> 0 
    AND dm_db_index_usage_stats. user_lookups = 0
    AND dm_db_index_usage_stats.user_seeks = 0
    AND dm_db_index_usage_stats.user_scans = 0
 AND dm_db_index_usage_stats.database_id = DB_ID() 
ORDER BY
    dm_db_index_usage_stats.user_updates DESC

3. Script for finding long running queries

--Clear cached execution plan of the current DB
--ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

SELECT top 50
 DB_NAME(),
 qs.execution_count AS [Execution Count],
 (qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
 (qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
 (qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
 (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
 (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
 (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
 (qs.min_elapsed_time/1000.0) AS [Min Elapsed Time in ms],
 (qs.max_elapsed_time/1000.0) AS [Max Elapsed Time in ms],
 qs.creation_time AS [Creation Time],
 t.text AS [Complete Query Text], 
       qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE t.dbid = DB_ID() AND qp.dbid =  t.dbid
--ORDER BY qs.execution_count DESC --OPTION (RECOMPILE)-- for frequently ran query
-- ORDER BY [Avg Logical Reads in ms] DESC OPTION (RECOMPILE);-- for High Disk Reading query
-- ORDER BY [Avg Worker Time in ms] DESC OPTION (RECOMPILE);-- for High CPU query
ORDER BY [Avg Elapsed Time in ms] DESC OPTION (RECOMPILE);-- for Long Running query