SQL SERVER – Catching Non-SARGable Queries in Action

If you ever wondered why a query that looks harmless is performing poorly despite having indexes in place, the answer could lie in a subtle performance pitfall—non-SARGable queries. These sneaky queries can silently bypass your indexes, leading to slow scans instead of efficient seeks. Let me share my recent example while helping my client with a Comprehensive Database Performance Health Check.

In this blog post, we’ll explore what makes a query non-SARGable, how to spot these patterns, and how to rewrite your queries to take full advantage of SQL Server’s indexing engine. We’ll use a step-by-step demo with execution plans and statistics to show the difference in performance. We’ll also show how to detect such queries automatically using DMVs.

What Is a Non-SARGable Query?

SARGable stands for Search ARGument Able—a term that refers to queries where the search condition can take full advantage of indexes. A non-SARGable query, on the other hand, includes expressions or functions that prevent SQL Server from seeking through indexes.

Here’s a common example:

SET STATISTICS IO ON;

SELECT *
FROM Employees
WHERE DATEDIFF(DAY, HireDate, GETDATE()) = 1000;

This looks innocent, but the function DATEDIFF(DAY, HireDate, GETDATE()) disables index seek. SQL Server can’t jump directly to matching rows—it has to scan the entire index or table.

Setting Up the Demo

Let’s walk through a demo that shows the impact of non-SARGable patterns. We’ll use a sample Employees table.

USE tempdb;
GO

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees (
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE
);

-- Insert sample data
;WITH Numbers AS (
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM master.dbo.spt_values a CROSS JOIN master.dbo.spt_values b
)
INSERT INTO Employees (FirstName, LastName, HireDate)
SELECT
'First' + CAST(n AS NVARCHAR(10)),
'Last' + CAST(n AS NVARCHAR(10)),
DATEADD(DAY, -n % 3650, GETDATE())
FROM Numbers;

-- Create index on HireDate
CREATE NONCLUSTERED INDEX idx_HireDate ON Employees(HireDate);

Step 1: Run a Non-SARGable Query

SET STATISTICS IO ON;

SELECT *
FROM Employees
WHERE DATEDIFF(DAY, HireDate, GETDATE()) = 1000;

Execution Plan Insight: This looks simple, but DATEDIFF() is applied to the column HireDate. SQL Server has to evaluate this function for every row, making index seek impossible.

Statistics Output Example:

Table 'Employees'. Scan count 1, logical reads 322

Even though only a small number of rows may match, all rows were read.

Step 2: Rewrite as SARGable

Let’s rewrite the query using a date range instead:

SET STATISTICS IO ON;

SELECT *
FROM Employees
WHERE HireDate = CAST(DATEADD(DAY, -1000, GETDATE()) AS DATE);

Execution Plan Insight: This time you’ll get an Index Seek. SQL Server jumps directly to rows in the specified date range.

Statistics Output Example:

Table 'Employees'. Scan count 1, logical reads 96

That’s a dramatic drop in reads—from 322 to 96.

Here is the execution plan for comparison.

Why This Happens

When you apply a function to a column in the WHERE clause, SQL Server can’t use the index efficiently. It doesn’t know ahead of time what values the function will return for each row, so it has to check them all. This applies to:

• YEAR(HireDate)
• CAST(Column AS dataType)
• LEFT(Name, 3)
• ISNULL(Column, ‘default’)

These functions transform the column values and block the index.

Other Common Non-SARGable Patterns

Here are some examples of common non-SARGable conditions, why they’re problematic, and how to rewrite them:

• WHERE MONTH(HireDate) = 1
  • Forces scan due to function on column
  • Better: WHERE HireDate >= ‘2022-01-01’ AND HireDate < ‘2022-02-01’
• WHERE DATEDIFF(day, HireDate, GETDATE()) > 365
  • Function on column makes it non-SARGable
  • Better: WHERE HireDate < DATEADD(day, -365, GETDATE())
• WHERE ABS(Salary) > 5000
  • Function disables index
  • Better: Preprocess absolute values or refactor logic
• WHERE ISNULL(DepartmentID, 0) = 5
  • ISNULL on column disables index
  • Better: Use defaults during data insert or handle NULLs before filtering

Testing With Indexes On Different Columns

Let’s say we now want to filter based on LastName. We can demo the same concept:

CREATE NONCLUSTERED INDEX idx_LastName ON Employees(LastName);

Now compare these two queries:

Non-SARGable:

SELECT * FROM Employees WHERE LEFT(LastName, 1) = 'S';

SARGable:

SELECT * FROM Employees WHERE LastName LIKE 'S%';

The first one uses a function and disables the index. The second one allows an index seek, which is faster.

How to Find Non-SARGable Queries in SQL Server

While SQL Server doesn’t directly label queries as non-SARGable, you can detect them using DMV queries and plan analysis.

1. Use Execution Plan XML to Find Index Scans

SELECT
qs.total_logical_reads,
qs.execution_count,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
st.text AS query_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(//p:IndexScan)', 'int') > 0
ORDER BY avg_logical_reads DESC;

This query returns high-read queries with Index Scans, which may indicate non-SARGable filters.

2. Use Query Store (SQL Server 2016+)

SELECT
qsqt.query_sql_text,
qsp.query_plan
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
WHERE qsp.query_plan LIKE '%SCAN%';

If Query Store is enabled, this helps you track historical performance.

Summary

Non-SARGable queries are like silent performance killers. They hide inside your queries, often looking completely normal, but prevent SQL Server from using indexes efficiently. When SQL Server can’t seek through an index, it scans—leading to unnecessary I/O and CPU usage.

The fix is usually simple:

• Avoid wrapping columns in functions
• Rewrite filters using range conditions
• Use LIKE with wildcards at the end, not the beginning

Final Thoughts

Always check the execution plan. If you see scans where you expect seeks, take a closer look at your WHERE clause. Even a minor change—like replacing a function with a date range—can result in a major performance boost.

To stay proactive, you can also set up automated checks using DMVs or Query Store to flag queries with high logical reads or patterns that indicate non-SARGable behavior. This is not just theory—we do this regularly with our clients as part of our Comprehensive Database Performance Health Check.

During this health check, we review critical workload patterns, analyze execution plans, and identify performance bottlenecks—including silent issues like non-SARGable queries. The goal is simple: uncover hidden inefficiencies and make your system faster and more predictable.

Reference: Pinal Dave (https://blog.sqlauthority.com)