By: Rick Kohler | Comments (1) | Related: > Performance Tuning
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.
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.
Next Steps
- Review query plans for your long-running queries. Look for scans where you expect seeks.
- Review other tips for working with indexes
- Do not neglect other query optimization techniques
- If necessary, consider making an index on a calculated column
About the author
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. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
