A key part of query performance tuning is inspecting plans for non-SARGable predicates.
Leverage the Query Store which keeps a history of queries, plans and runtime stats.
Then analyze the SQL Server plans which show the operators and predicates the query optimizer used.
What usually makes a query non-SARGable?
Common patterns are predicates that apply an expression to the column side, such as:
WHERE YEAR(OrderDate) = 2025
WHERE LEFT(LastName,1) = 'S'
WHERE ISNULL(Col,'') = 'X'
WHERE CAST(DateCol AS date) = @d
WHERE Col + 1 = 10
WHERE Name LIKE '%abc'
Those patterns often prevent efficient index seeks. The indicator is usually more logical reads than you expected, which you can confirm with SET STATISTICS IO.
Look for:
Table Scan
Index Scan
A predicate that contains a function or expression on the column
Implicit conversions
With a re-write the goal is fewer logical reads and, often, less CPU and elapsed time.
Typical rewrite:
-- Non-SARGable
WHERE YEAR(OrderDate) = 2025
-- Re-write
WHERE OrderDate >= '20250101'
AND OrderDate < '20260101'
Query #1: Find Slow Running Queries
SELECT TOP (50)
qsq.query_id,
qsp.plan_id,
SUM(rs.count_executions) AS executions,
SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_time,
SUM(rs.avg_duration * rs.count_executions) AS total_duration,
qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS qsq
ON qt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp
ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats AS rs
ON qsp.plan_id = rs.plan_id
GROUP BY
qsq.query_id,
qsp.plan_id,
qt.query_sql_text
ORDER BY total_logical_reads DESC;
Query #2: Find queries that are likely the non-SARGable ones
This is useful when Query Store is off or you want a quick scan of cached plans.
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (100)
qs.total_logical_reads,
qs.total_worker_time,
qs.execution_count,
SUBSTRING(st.text,
(qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_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.exist('//RelOp[@LogicalOp=("Index Scan","Clustered Index Scan","Table Scan")]') = 1
AND (
qp.query_plan.exist('//Predicate//ScalarOperator') = 1
OR qp.query_plan.exist('//SeekPredicates//ScalarOperator') = 1
)
ORDER BY qs.total_logical_reads DESC;
What //Predicate//ScalarOperator means
It’s an XPath expression used to search inside an execution plan XML.
Does this query have any expressions inside predicates?
i.e. Find any expression used inside a predicate anywhere in the execution plan.
// ? search anywhere in the XML
Predicate is a filter condition (like your WHERE clause)
The ScalarOperator is the actual expression logic:
comparisons (=, >, <)
functions (YEAR(), LEFT())
expressions (Col + 1)
Query #3: Detect implicit conversions
Implicit conversions are a very common hidden cause. They can make an otherwise good predicate behave badly. They hurt performance because they often prevent the optimizer from using indexes efficiently and add extra work at runtime. They can break index seeks(biggest issue).
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (100)
qs.total_logical_reads,
qs.execution_count,
st.text,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.exist('//Convert[@Implicit="1"]') = 1
ORDER BY qs.total_logical_reads DESC;.
If this returns a query that is joining or filtering on mismatched data types, fix the data types. Then fix any code & schema data type mismatches.
Convert[@Implicit="1"] in plan happens when SQL Server automatically converts one data type to another to make a comparison work.
Example:
WHERE IntColumn = '123' -- string compared to INT
SQL Server rewrites it internally like:
WHERE IntColumn = CONVERT(INT, '123')
Sometimes it’s the column that gets converted instead - that is a the performance issue.
Why implicit conversions are bad:
Implicit conversions break index seeks (biggest issue)
If SQL Server has to convert the column, it can’t use the index efficiently.
Index scan instead of seek.
Reads way more data than needed.
Implicit conversions cause Higher CPU usage
Every row may need conversion:
On large tables:
thousands/millions of conversions
unnecessary CPU overhead
Implicit conversions cause Bad cardinality estimates
SQL Server may misestimate how many rows match:
leads to poor execution plans
wrong join types
memory issues
Implicit conversions cause more logical reads
Because seeks turn into scans:
SQL reads many more pages
slower queries, more IO pressure
Implicit conversions cause Plan instability
Same query, different parameter types ? different plans
WHERE Col = @param
Sometimes @param is INT
Sometimes VARCHAR
SQL Server may:
generate different plans
or reuse a bad one
Implicit conversions are a Performance destroyer
It’s not obvious in the query
You only see it in the execution plan:
warning icon
CONVERT_IMPLICIT
WHERE UserId = '100'
If UserId is INT:
SQL may convert column ? scan
WHERE UserId = 100
Proper type:
Index seek
Key rule (super important)
Conversions on the COLUMN = bad
Conversions on the VALUE = usually fine
Speed, Speed, Speed !!!!!
|
|
|
|
|
|