Non-SARGable Queries cause Performance Issues and you may be writing them without knowing

A non-SARGable query refers to a query that is not able to properly utilise an index, due to its Search ARGuments.

In this post, all examples are demonstrated using the WideWorldImporters database.

The following are examples of non-SARGable queries:

-- Example 1
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE (CONVERT(NVarChar, OrderID)) = 10 

-- Example 2
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE Description LIKE '%Pack%'

The Performance Issue with Example 1:

The query is filtering rows using the OrderID column, and an index is applied on the OrderID column. Naturally, we expect SQL Server to SEEK the data. However, upon inspection of the execution plan, we see this is not the case.

SQL Server chose to do a SCAN instead, which means SQL Server reads every row. This causes SQL Server to do 2974 logical reads.

SCAN is chosen over SEEK because of the CONVERT function being used on the left side of the operand in the WHERE clause. SQL Server has to CONVERT every OrderID value to NVarchar. To do this, SQL Server will have to read every single row in the whole index.

How to make Example 1 SEEK instead of SCAN?

The workaround for this, is to either remove the CONVERT function entirely or apply the CONVERT function on the right side of the operand. Applying it to the right side will avoid a SCAN as SQL Server will only need to CONVERT the input, and afterwards just seek the matching value.

-- New Query  
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE OrderID = (CONVERT(NVarChar, 10))

The new execution plan:

After changing the query, SQL Server elected to perform a SEEK for the data, resulting in only 8 logical reads.

Keep in mind, one logical read represents one 8 KB page being read into memory.

Example 1
Non-SARGable Query: 2974 x 8 = 23,792 KB of memory used
SARGable Query: 8 x 8 = 64 KB of memory used

The Performance Issue with Example 2:

In this case, the query is filtering rows on the Description column for ‘%Pack%’. As the string can be located anywhere, SQL Server will not be able to SEEK this predicate and instead will have to SCAN each row.

This is the execution plan:

This query is a bit more trickier to change as the end user wants to search for the word ‘Pack’ and it can be positioned anywhere in the string.

However, if the end user actually was only looking for rows that started with the word ‘Pack’. This is a drastically different scenario.

  -- New Query
  SELECT OrderID, Description
  FROM [Sales].[OrderLines]
  WHERE Description LIKE 'Pack%'

Removing the ‘%’ at the start of the search, resulted in this new execution plan:

SQL Server now goes for a SEEK as it understands that it is looking for values that start with ‘Pack’.

Example 2
Non-SARGable Query: 2974 x 8 = 23,792 KB of memory used
SARGable Query: 69 x 8 = 552 KB of memory used

Final Thoughts

It is good practice to avoid implementing Non-SARGable queries in your production environments, as having a number of them running frequently will cause performance degradation in your SQL Server instance. Try applying functions on the right side of your operand in the WHERE clause. Additionally, I suggest to avoid using ‘%string%’ in your LIKE predicates if possible.

Advertisement