SARG is short for Search Argument. This is an important tuning term and something every developer and DBA should know. I plan to do a few blogs on this topic and today marks the first.

What is a Search Argument?

Simply put a SARG is a portion of the query predicate. What’s a predicate?

“Is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.” –Microsoft Docs

https://docs.microsoft.com/en-us/sql/t-sql/queries/predicates

A Search Argument (Predicate) is an expression that helps SQL Server determine how to join, group, or retrieve your data.

Let’s take a look at a simple predicate:

SELECT Name, ProductNumber
FROM Production.Product
WHERE Name = ‘blade’

In this case “Name = ‘blade’” is what we’re discussing. Without this critical piece of the query, your results would consist of the entire table. Adding this in allows the data to be retrieved very efficiently seeking an index, if one exists.

Since this index doesn’t include the ProductNumber column, it’s not as efficient as it could be, as seen by the Key Lookup; but, that’s a topic for another day.

We can see the predicate by hovering over the Index Seek or by highlighting it and viewing the properties window (F4).

SARGable vs non-SARGable

Now that we know what a SARG is let’s discuss one example of SARGable and non-SARGable. To be non-SARGable the predicate must not be efficient and in many cases this would force a scan of the object rather than seeking.

Here’s an example:

SELECT Name, ProductNumber
FROM Production.Product
WHERE Name like ‘_lade’

Note the wildcard “_” at the beginning. An underscore is just like % but in this case of underscore it is for only 1 character whereas % is not limited in this way.

With the “_” in the like, the value could be ‘alade’, ‘blade’, ‘clade’, ‘dlade’, ‘#lade’, ‘9lade’, and on and on and on…

This means SQL Server cannot seek the BTREE and instead must scan it entirely, looking at EVERY value in the table to see what matches.

Here’s the results for a wildcard at the end (SARGable) vs at the beginning (non-SARGable).

In some cases you can’t get around this type of behavior and if this is causing performance concerns you should consider redesigning that portion of your application to be more database friendly. If you’ve got questions on how to redesign, please feel free to comment or shoot me an email from my contact page at SQLTechBlog.com.

In the next post we’ll talk about functions and how these can affect the SARGability of your query.