What is SARGable?

You may have heard of the term “sargable”. SARG stands for Search ARGument, and it refers to a predicate where an index seek can be used by the optimizer. My rule for making a predicate SARGable is that a column must be by itself on one side of the comparison operator. Using the AdventureWorks2016 database, the predicate in this query is SARGable (the query’s execution plan and IO follow):

SELECT P.LastName,
	P.FirstName
	FROM Person.Person AS P
	WHERE P.LastName LIKE 'R%'
	OPTION (RECOMPILE);

SARGable
SARGable

and the predicate in this query is not SARGable:

SELECT P.LastName,
	P.Title
	FROM Person.Person AS P
	WHERE P.LEFT(LastName, 1) = 'R'
	OPTION (RECOMPILE);

SARGable
SARGable

By having the LastName column as a parameter of a function (LEFT) instead of by itself on one side of the comparison operator, the optimizer can’t seek the index that is on that column and has to resort to a table scan. That also results in reading 3819 pages to satisfy the query instead of 14 in the SARGable version.

In the following example, I started by adding a nonclustered index on Sales.SalesOrderHeader.SubTotal (I’ll leave that to you). The two queries are logically equivalent, but for demonstration purposes I moved the variable in the second query on the same side of the comparison operator as the column, making the predicate non-SARGable.

DECLARE @SubTotal MONEY = 120000;

SELECT * FROM Sales.SalesOrderHeader AS H
	WHERE H.SubTotal > @SubTotal
	OPTION (RECOMPILE);

SELECT * FROM Sales.SalesOrderHeader AS H
	WHERE H.SubTotal - @SubTotal > 0
	OPTION (RECOMPILE);

Here are the IO statistics followed by the two execution plans:
SARGable
SARGable
The SARGable query read 47 pages of data and did an index seek, compared to 118 pages read and an index scan for the non-SARGable version.

So simple: Always put your column by itself on one side of the comparison operator in the predicate. You won’t always see improvements like these, but occasionally you’ll be a hero.

Say No-No to NOLOCK

Do you use the NOLOCK (or the equivalent READUNCOMMITTED) table hint in production code? Let me try to convince you to say No-No to NOLOCK.

When you use the NOLOCK table hint with a SELECT statement, your query does not apply Shared Locks, and does not respect Exclusive Locks. This means that if there is another transaction modifying data, your NOLOCK query can view that data before the transaction is committed or rolled back. Although that sounds like it’s good for performance, to quote Microsoft, “This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).” Bad data fast is usually not a good thing.

Can you get better performance by using NOLOCK? Yes, it can increase concurrency. If there are no other transactions, then your NOLOCK query isn’t any faster (I doubt you could ever measure the speed improvement due to not applying the Shared Locks). If there are other transactions modifying data, they don’t have to wait for your NOLOCK query to complete, and your NOLOCK query doesn’t have to wait for transactions that are modifying data to complete. But when is the likelihood of your NOLOCK query returning data that was never committed, or duplicate records, or missing records? When there are other transactions that are modifying data.

The situation that can give you better performance is the same situation with increased risk of corrupt results.

Say no-no to NOLOCK.