One of my team members came to me recently and asked what he could do to make his query run faster. He was perplexed because there was an index on the table, but it was still running super slow. I tried to help him troubleshoot, but I found myself confused as well. We left that conversation without a clear direction for how to optimize the query.
This was a source of embarrassment for me. In my role, I’m supposed to be the go-to person for issues like this. Yet there I was, the team’s “leader” with a clear and present gap in my understanding of Query Optimization. It was a humbling realization that I needed to learn more about optimizing queries with indexes.
SARGability
After some digging, I found that the issue was due to a lack of SARGability in the query. But what the heck even is that? Search ARGument Ability (SARGability) is when a query is capable of utilizing an index. More specifically, it means that rather than scanning the entire table, it can seek for what it needs.
Personally, I think Seekable may be a better term for this concept since that is what you look for in the SQL Server Execution Plan. That said, I may use SARGable and seek-able synonymously throughout this article.
Key Concepts of SARGability
\( Seek = SARGable \)\( Scan \ne SARGable \)
SARGability Risk Areas
Most of the time you will find SARGability issues in the WHERE clause, but you can also find them in other parts of your query as well. Here is a full list of risk areas:
- WHERE Clause
- JOIN Clauses
- GROUP BY Clause
- HAVING Conditions
- ORDER BY Clause
Making a Query SARGable
There are so many factors that go into making a query seek-able. In this next section, we’ll dive into the most common things that force the query optimizer to perform a table scan instead of seek.
Avoid Using Functions
Functions are operations applied to columns that are designed to alter their values. These include, but are not limited to:
- String Functions
- UPPER
- LOWER
- SUBSTRING
- Date Functions
- YEAR
- MONTH
- Mathematical Functions
- ABS
- ROUND
- FLOOR
Non-SARGable Example
SELECT * FROM Employees WHERE UPPER(FirstName) = 'JOHN';
SARGable Alternative
SELECT * FROM Employees WHERE FirstName = 'John';
Avoid Explicit and Implicit Data Type Conversions
Explicit data type conversions are performed when you use methods such as CAST and CONVERT. These functions are designed to change a column from one data type to another. Now explicit conversions are not bad in the select statement, you just don’t want to use them in the risk areas we mentioned above.
Along the same line are Implicit Conversions. Implicit conversion occurs when SQL Server automatically converts one data type to another to match the types of the operands in an expression or to compare values. For example, if you compare a column of type VARCHAR to a literal of type INT, SQL Server must convert one of these values to make the comparison possible.
This essentially means that implicit conversions force table scans. There is simply no way for the query optimizer to selectively convert the two columns being compared. It must first convert all values in the table and then do the comparison. This is true even for data types varchar and nvarchar.
To remedy both explicit and implicit data type conversions you need to take a look at both tables and change the underlying table structure so that the two columns’ data types match. Once you’ve aligned the data types, your query should be SARGable.
SARGability Gotchas
We’ve covered the most common scenarios that cause seek-ability issues. But there are quite a few other gotchas that may force the query optimizer to scan a table instead of seek. We’ll go over some of those in this section.
Leading Wildcards
If you utilize a like pattern and you place a wildcard (%) at the front, it will force a table scan. In practice, I’ve used leading wildcards to identify patterns I needed to query on. After figuring out the exact values I would then explicitly reference them in the query (see example below).
Scanning Patterns
SELECT
Description
,Amount
FROM Transactions
WHERE Description LIKE '%Refund%'
Seeking Patterns
SELECT
Description
,Amount
FROM Transactions
WHERE Description IN ('Refund from Vendo','Refund to Client')
Failing to Trigger a Multi-column Index
So we’ve already talked about how certain operations in the wrong place can force your query to scan instead of seek, but you may still be running into seeking issues without these more obvious problems. One thing that may be happening is that you are failing to trigger a clustered index. Let’s explain this with an example:
You have the following index on the Employees table:
CREATE CLUSTERED INDEX IDX_EmployeeID_FirstName_LastName ON Employees(EmployeeID, FirstName, LastName);
You attempt to query the data based on the FirstName column in the clustered index:
SELECT
EmployeeID
,FirstName
,LastName
,Department
FROM Employees
WHERE FirstName = 'John'
Unfortunately, this query performs a scan instead of a seek. But why? It is included in the Clustered index, so why would this not work? The reason is because you did not trigger the index. Clustered indexes are set up in such a way that the first column in the index must be passed first in order for the rest of the index to work.
In this case, because FirstName is the second column in the index, it’s failing to seek on the index. And this is the rule of the optimizer when determining whether to seek or scan the table. If your query does not search sequentially on the index, then it will revert to scanning. So if you are searching on column 2 or 3 it will not work. If you are searching on columns 1 and 3 it will not work. To trigger a search on a multi-column index you must move through it sequentially.
Fixing this issue may be a bit trickier than some of the other problems we’ve discussed because it may involve adjusting the multi-column index. My best advice here is to try to rearrange the clustered index in such a way that it places the most queried column first.
Another potential option could be to create a separate index to handle the needs of your query. That said, indexes can require a lot of storage space and that may not be very desirable.
Assessing SARGability with the Query Execution Plan
Query Execution Plans have not always been my friend. I used to look at them and think: “Wow. This is an ugly mess.” I didn’t like the graphic display and I didn’t find the information that helpful. That is, until I learned how to read them in the context of SARGability.
You have two options when it comes to getting execution plan information. You can request an estimated plan or get the actual plan. Which one you use depends on the situation. The actual plan requires the entire query to run successfully whereas the estimated plan does a lazy check. If your query takes a long time to run, perhaps the Estimated Plan is a better option initially.
Remember that your goal is to make sure the Execution Engine performs a seek on the index rather than scanning the entire table. When asked for an Execution Plan, you can get back information on which behavior is happening. If you’ve done a good job writing your query, then you will see that seek is happening.
What if my query runs fast with a Scan?
I’ve had some people ask me if it’s worth making a query Seekable when it runs quickly with a scan. In these cases, the tables are so small that there isn’t a major impact on performance. The best response I can give is a simple question: “How will it scale?” Will the table continue growing? If the answer is yes, then it’s only a matter of time before you’ll need to make the query SARGable.
Of course, there are exceptions. Not every table you query from will grow. And so scanning may be acceptable in those circumstances. In my own work, we definitely have a few queries that are not fully optimized and we are okay with that. That said, making your queries SARGable is always a desirable best practice that all SQL developers should work towards.
Conclusion
We’ve covered the most important aspects of SARGability and I want to leave you with a few key things to remember. First thing is that a query won’t automatically be optimized by simply adding an index. You need to write your query in a way that utilizes the index effectively. Avoid using functions in the WHERE clause and in the other areas we talked about earlier. Finally, Execution Plans may look ugly, but they aren’t as bad as you think. Focus on looking at the table portions to see if they are being scanned or searched (seek).
I hope you enjoyed this read! If you found it helpful, I invite you to share it with a coworker.
For more reading on SARGability, check out this article: https://www.sqlservercentral.com/blogs/sql-server-sargability