SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server: SARGability

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.

image

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).

image

image

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).

image

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.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...