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


By:   |   Updated: 2016-06-24   |   Comments (1)   |   Related: More > Performance Tuning

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


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 Updated: 2016-06-24


get scripts

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





Comments For This Article




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.

 

 



download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools