Problem

A colleague complained that while some of his SQL Server queries against a particular table
ran fine, others were very slow. For no apparent reason some queries took an
order of magnitude longer than others. He was under some pressure from his users
to speed up the slow queries. How can I correct this issue?

Solution

The solution here was to slightly modify the slow queries, so the index could
be fully used. We made the queries Search Argumentable, or "SARGABLE."

To perform an index seek, SQL Server must be able to compare the index values
on the one hand, against the search value on the other hand. If SQL Server needs
to modify the index values to perform the comparison, it must revert to an index
scan, reading the index sequentially. A query like that is not sargable, and typically
runs much more slowly than an equivalent sargable query.

Demonstration - SQL Server Sargable Query

We will create a simple table and index. We will insert ten million rows into
the table, then search the rows using slightly different queries to illustrate sargability.

Create and Populate a Table

We will generate and store 10,000,000 rows of NEWID values, so we have something
to query.

Baseline Query with No Indexes

We will now run our query with no index, to get a baseline performance measurement.

-- Clear cache
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
-- How many rows begin with the letter A? (no indexes on the table)
SELECT COUNT(*) FROM chartable WHERE charcolumn LIKE 'A%';

The query runs in 8 seconds and uses a table scan as shown below, no surprise there.

Sargable Query with an Index

We will create an index and measure a sargable query. Later we will see how the
sargable query can be transformed into a query that is not sargable, and what impact
that has on run time.

Here is the sargable indexed query.

-- Create an index
CREATE INDEX charindex ON chartable (charcolumn);
-- Clear cache
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
-- How many rows begin with the letter A? (the table has an index now)
SELECT COUNT(*) FROM chartable WHERE charcolumn LIKE 'A%';

This time the query took only 2 seconds, and the execution plan shows an index
seek was used:

Calculation on Indexed Column Loses Sargability

One way to lose sargability is to perform a calculation on the indexed column
in the WHERE clause. Here is a query that looks similar to the one above, but runs
longer.

-- Clear cache
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
-- How many rows begin with the letter A? (we perform a calculation on the indexed column)
SELECT COUNT(*) FROM chartable WHERE SUBSTRING(charcolumn,1,1)='A';

This time the query is back to 7 seconds, and the execution plan is back to a
table scan, ignoring our index:

Different Data Types Can Lose Sargability

Another way to lose sargability is to force SQL Server to convert the indexed
column into a different data type. For example, we will search for a Unicode
value of "A" in
our ASCII column. We will use the same LIKE condition of the query that worked well above.

-- Clear cache
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
-- How many rows begin with the letter A? (we search for a Unicode target in an ASCII column)
SELECT COUNT(*) FROM chartable WHERE charcolumn LIKE N'A%';

This version ran in 5 seconds, and the execution plan is a table scan again,
ignoring our index:

If the column had been Unicode and the target ASCII, SQL Server could have done
an implicit conversion of the ASCII target into Unicode, to match the index, and
used an index seek for a faster query. But SQL Server will not implicitly convert
Unicode to ASCII, so our column values were converted to Unicode and compared to
the Unicode target, leaving out our index once again.

Cleanup

Remove the table that we created at the beginning of this tip.

-- Remove the demonstration table
DROP TABLE chartable;

Summary

As you can see above, slight differences in how a query is constructed can
totally change how it interacts with the database engine and therefore impact
performance.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I have always heard that running a function on the search parameter would prevent the index from engaging, but I have never had it so eloquently explained as in this article. Many thanks for the clear and insightful explanation.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.