Esat Erkec

How to use sargable expressions in T-SQL queries; performance advantages and examples

December 22, 2017 by

The challenge

One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario

  • Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
  • The query executed successfully and does not exhibit any symptoms of performance problems
  • The developer team release this table and query into production
  • One day you take a telephone from your colleague and he says my report is very slow
  • Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it

I think this scenario is all too common to many SQL Server DBAs.

In my opinion developers who work with SQL Server often have a little knowledge about query performance tuning. In this scenario, a little knowledge would have gone a long way to preventing these issues in production.

Sargable queries

Sargable is a word that concatenates the three words: search, argument and able.

As per wikipedia SARGable is defined as “In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE”

Advantage of sargable queries include:

  • consuming less system resources
  • speeding up query performance
  • using indexes more effectively

Indexes

Now we will shortly discuss about index basics then we will cover some examples of both sargable and non-sargable queries.

Indexes are created on tables or views. Indexes can be likened to a book directory and improve the speed of searches. Some examples of different types of indexes are clustered indexes, non-cluster indexes and columnstore indexes.

An index scan is when SQL Server reads all the data in the index pages. The cost of an index scan is very expensive for the SQL Server Engine.

An index seek is when SQL Server reads only matching data in the index pages. This method is more efficient for query performance because it will reduce IO and time consumption.

Examples

Now we will start examples. In our examples we will look at non-sargable queries and their execution plans. Then we will convert a non-sargable query to a sargable query. Finally, we will compare non-sargable and sargable queries time and IO statistics.

Now we will create our dummy table and insert records for queries

This dummy table includes a non-clustered index on the FirstName, ModifiedDate and MiddleName columns.

Using functions in the WHERE clause

In this first example, we will select records in the Person table which starts with the letter “K”.

Non-sargable query:


The SQL Server query optimizer cannot find the result of ths LEFT function values in the index pages. For this reason, the query optimizer chooses a cluster index scan and it needs to read the whole table.

In the below picture, the Estimated Number of Rows to be Read value is 5,991,600. This value is the total row number of the table. But the matching records are approximately 371,400

These functions create the same execution plans in the same conditions

  • SUBSTRING
  • LEFT
  • LTRIM
  • RTRIM
  • User defined functions

Sargable query:

Now we will make a little syntax change in the previous query


SQL Server query optimizer decides to use an index seek operator when the operator cost is low and it can easily find matching records using the B-Tree structure. An index seek operator provides significant performance gains.

In the below picture, the Estimated Number of Rows to be Read value is equal to the Estimated Number of Rows because index seek operator directly achieves a matching result of the query


Type Table Scan count Logical reads Physical reads
Non-sargable Dummy_PersonTable 519,392 0
Sargable Dummy_PersonTable 11,229 0

This table show us IO statistics of these two queries. The sargable query performs significantly better than the Non-sargable query

Type Table CPU Time(ms) Elapsed time(ms)
Non-sargable Dummy_PersonTable 1593 2145
Sargable Dummy_PersonTable 2031830

This table shows us time statistics of queries and it can be seen that the sargable query demonstrates better performance than the Non-sargable query.

Tip: We will add a calculated column for the LEFT function and create a non-clustered index

Now we will look again at the non-sargable query execution plan.

It will use an index seek

This is an alternative solution for performance gain but changing T-SQL syntax is much easier than changing the table structure and adding an index.

DateTime functions

In this example, our target is to write a select query for a specific year. We can write this query as shown below.

Non-sargable query:

We will look at this query execution plan

The SQL Server query optimizer cannot use an index seek because it cannot find the values of the YEAR function on the index pages.

Tip: Maybe you can ask this question. Why does SQL Server query optimizer decide to use a parallel plan? The SQL Server query optimizer decides on a parallel plan because the optimizer works on a cost-based method and it decides that a parallel plan is cheaper than a serial plan. We will compare the parallel and serial execution plans Estimated subtree cost


The parallel execution plan cost is 15.03 and the serial execution plan cost is 17.08. This is the main reason for this decision. There are other reasons to take this decision though:

If

  • SQL Server engine has more than one CPU
  • The SQL Server maximum degree of parallelism (MAXDOP) setting is more than 1
  • The execution plan cost is greater than the cost threshold for parallelism setting value

… then SQL Server query optimizer choice parallel plan.

Sargable query:

In this step we will change the condition in this query.


The SQL Server query optimizer performs an index seek. Now we will compare IO and time statistics

Type Table Scan count Logical reads Physical reads
Non-sargable Dummy_PersonTable 5135260
Sargable Dummy_PersonTable 11850

Type Table CPU time(ms) Elapsed time(ms)
Non-sargable Dummy_PersonTable 1048644
Sargable Dummy_PersonTable 0340

Our results demonstrate that the sargable query has out-performed the non-sargable query

Using ISNULL function on WHERE clause

In this sample we want to select all of records where the MiddleName is equal to “E” or NULL. We can write the query like this. We will activate Include Client Statistics for comparing non-sargable and sargable queries.

Non-sargable query:


Sargable query:

We will change syntax of query and use an index seek


As seen above, the non-sargable (Trial 1) query execution plan used an index scan and the sargable query (Trial 2) used an index seek. These differences the between two queries affected performance. This case clearly shown in the below client statistics chart.

Conclusions

In this article, we discussed sargable and non-sargable queries. Non-sargable queries can result in poor system performance. We can often improve these queries by changing the code, to leverage indexes and in doing so, convert them to sargable queries. Sargable queries can improve performance on CPU and IO as was demonstrated by our analysis and findings.

See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan

References


Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
Esat Erkec
Performance, Query analysis, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

261 Views