Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Sargability - Queries on an Indexed Table Sometimes have Slow Performance


By:   |   Read Comments (1)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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.

-- Create a demonstration table
CREATE TABLE chartable (charcolumn CHAR(36) NOT NULL);
-- Insert 10 000 000 rows WITH twoexp1 AS (SELECT 1 x UNION SELECT 2), twoexp2 AS (SELECT 1 x FROM twoexp1, twoexp1 a), twoexp4 AS (SELECT 1 x FROM twoexp2, twoexp2 a), twoexp8 AS (SELECT 1 x FROM twoexp4, twoexp4 a), twoexp16 AS (SELECT 1 x FROM twoexp8, twoexp8 a), twoexp32 AS (SELECT 1 x FROM twoexp16, twoexp16 a), twoexp33 AS (SELECT 1 x FROM twoexp32, twoexp1) INSERT INTO chartable SELECT TOP 10000000 NEWID() FROM twoexp33;

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.


Execution plan shows a table scan

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:

Execution plan shows an index seek

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:

Execution plan shows a table scan

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:

Execution plan shows a table scan

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.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Rick Kohler Rick Kohler has been a full-stack developer for 35 years and a part-time SQL Server DBA since the late 1990's. Rick loves software development.

View all my tips
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, June 28, 2016 - 10:29:11 AM - James Koehling Back To Top

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.

 

 


Learn more about SQL Server tools