SARGability – Page 2 – Darling Data

SARGability Week: Using Dynamic SQL To Fix Non-SARGable Queries

Posted on August 27, 2021May 16, 2022 by Erik Darling

Specific

The non-SARGABLE pattern that dynamic SQL helps you deal with is the the catch all query pattern, which can look like:

col = @parameter or @parameter is null
col = isnull(@parameter, col)
col = coalesce(@parameter, col)

Or any similar variation of null/not null checking of a parameter (or variable) in the where/join clause at runtime.

Dynamic SQL allows you to build up the specific where clause that you need for the non-NULL set of search filters.

But First

Let’s look at one of my favorite demos, because it very simply shows the goofy kind of things that can go wrong when you don’t practice basic query hygiene.

I’m going to create these two indexes:

CREATE INDEX osc ON dbo.Posts
    (OwnerUserId, Score, CreationDate);

CREATE INDEX po ON dbo.Posts
    (ParentId, OwnerUserId);

They are fundamentally and obviously different indexes.

The query has a where clause on OwnerUserId and CreationDate, and an order by on Score.

The select list is, of course, everything.

DECLARE 
    @OwnerUserId int = 22656,
    @CreationDate datetime = '20190101',
    @SQLString nvarchar(MAX) = N'
SELECT 
    p.*
FROM dbo.Posts AS p
WHERE (p.OwnerUserId   = @OwnerUserId  OR @OwnerUserId IS NULL)
AND   (p.CreationDate >= @CreationDate OR @CreationDate IS NULL)
ORDER BY p.Score DESC;
';

EXEC sys.sp_executesql 
    @SQLString,
  N'@OwnerUserId INT,
    @CreationDate DATETIME',
    @OwnerUserId,
    @CreationDate;

But when we execute it, it uses the index on ParentId and OwnerUserId.

This is completely bizarre given the requirements of the query.

Now Second

Yes yes, I know, Captain Recompile. A hint will fix this problem. But then you might have another problem. Or a whole bunch of other problems.

Here’s an example of nice, safe dynamic SQL that gets the correct index used and a much more efficient query overall.

DECLARE 
    @OwnerUserId int = 22656,
    @CreationDate datetime = '20190101',
    @SQLString nvarchar(MAX) = N'
SELECT 
    p.*
FROM dbo.Posts AS p
WHERE 1 = 1' + NCHAR(10)

IF @OwnerUserId IS NOT NULL
BEGIN
    SET @SQLString += N'AND p.OwnerUserId   = @OwnerUserId' + NCHAR(10)
END

IF @CreationDate IS NOT NULL
BEGIN
    SET @SQLString += N'AND p.CreationDate >= @CreationDate' + NCHAR(10)
END

SET @SQLString += N'ORDER BY p.Score DESC;'

PRINT @SQLString;

EXEC sys.sp_executesql 
    @SQLString,
  N'@OwnerUserId INT,
    @CreationDate DATETIME',
    @OwnerUserId,
    @CreationDate;

And On The Seventh Day

The title of this series is SARGability week, and at first I had five posts set to go on this. As I was writing, I realized there were a few other things that I wanted to cover.

Next week I’m going to talk about max data types, user defined functions, and implicit conversions, then wrap things up.

Unless I think of something else. After all, I’m writing this on the 9th. Time travel, baby!

Thanks for reading

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SARGability Week: Using Index Column Order To Fix Non-SARGable Queries In SQL Server

Posted on August 26, 2021May 16, 2022 by Erik Darling

Query Bum

Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.

Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.

Oh well, though. Keep me employed, ISVs.

When we are allowed to change indexes, sometimes we can fix problems enough to avoid needing to change the code.

Big Time

Let’s start by creating two different indexes. They have the same key columns, just in different order.

CREATE INDEX v ON dbo.Votes
    (UserId, CreationDate);

CREATE INDEX vv ON dbo.Votes
    (CreationDate, UserId);

The query that we care about it this one:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE ISNULL(v.UserId, 0) > 0
AND   v.CreationDate >= '20180601';

I know, you’re smart, you’re savvy, you’d never write a query like this. But I see it constantly.

Daring

If we compare query performance using the two different nonclustered indexes, the one that leads with CreationDate is the clear winner.

Bookends

The main advantage of a Seek here is we’re able to seek to a much smaller range of rows first, and then apply the non-SARGable predicate to UserId.

Obviously, scanning 182,692,000 rows is a bit slower than seeking to 32,411,155 rows and applying the residual predicate.

Tomorrows

If you can rewrite queries like this, you absolutely should. That people still write queries like this is a sad testament to… Well, I’m not sure what.

In tomorrow’s post, we’ll look at how dynamic SQL can help ward off non-SARGable predicates.

Thanks for reading!

Going Further

SARGability Week: Using Temp Tables To Fix Non-SARGable Query Performance Problems In SQL Server

Posted on August 25, 2021May 16, 2022 by Erik Darling

International

This post is especially interesting because it highlights the oddball performance situations you can run into when you write the type of All-In-One™ queries that the optimizer is typically not very good at handling, and also rely on expressions calculated at runtime as predicates.

I mean, it’s especially interesting if you’re into this sort of thing. If you’re not into this sort of thing, you’ll probably find it as interesting as I find posts about financial responsibility or home cooking.

I’ve seen query patterns like this while working with clients, and they’ve always ended poorly.

Anyway, on with the post!

Skeletons

To make sure we have a good starting point, and you can’t tell me that “scanning the clustered index is bad”, let’s create an index:

CREATE INDEX p
ON dbo.Posts 
    (OwnerUserId, Score DESC)
INCLUDE 
    (PostTypeId)
WHERE PostTypeId IN (1, 2);

Now let’s take a look at this query, and what gets weird with it.

WITH top_questions AS
(
    SELECT
        p.OwnerUserId,
        QuestionScore = 
            p.Score,
        tq = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
            	    p.OwnerUserId
            	ORDER BY p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 1
    AND   p.Score > 1
),
     top_answers AS
(
    SELECT
        p.OwnerUserId,
        AnswerScore = 
            p.Score,
        ta = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
            	    p.OwnerUserId
            	ORDER BY p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 2
    AND   p.Score > 1
)
SELECT TOP (1000)
    tq.OwnerUserId,
    tq.QuestionScore,
    ta.AnswerScore
FROM top_questions AS tq
JOIN top_answers AS ta
    ON  tq.OwnerUserId = ta.OwnerUserId
    AND tq.tq = ta.ta
ORDER BY
    tq.QuestionScore DESC,
    ta.AnswerScore DESC;

The non-SARGable portion is, of course, generating and joining on the row_number function. Since it’s an expression that gets calculated at runtime, we have to do quite a bit of work to execute this query.

Community Board

The query plan for this is all over the place, and also bad. Parallel merge joins were a mistake.

The portions of the query plan that are particularly interesting — again, if you’re into this sort of thing — is that there are four Repartition Streams operators, and all of them spill. Like I said above, this is the sort of thing you open yourself up to when you write queries like this.

In all, the query runs for about 50 seconds. This can be avoided by hinting a hash join, of course, for reasons explained here.

But good luck figuring out why this thing runs for 50 seconds looking at a cached, or estimated execution plan, which doesn’t show you spills or operator times.

Breakup

One way to avoid the situation is to materialize the results of each CTE in a #temp table, and join those together.

WITH top_questions AS
(
    SELECT
        p.OwnerUserId,
        QuestionScore = 
            p.Score,
        tq = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
            	    p.OwnerUserId
            	ORDER BY p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 1
    AND   p.Score > 1
)
SELECT
    *
INTO #top_questions
FROM top_questions;

WITH top_answers AS
(
    SELECT
        p.OwnerUserId,
        AnswerScore = 
            p.Score,
        ta = 
            ROW_NUMBER() OVER
            (
                PARTITION BY
            	    p.OwnerUserId
            	ORDER BY p.Score DESC
            )
    FROM dbo.Posts AS p
    WHERE p.PostTypeId = 2
    AND   p.Score > 1
)
SELECT
    *
INTO #top_answers
FROM top_answers;

SELECT TOP (1000)
    tq.OwnerUserId,
    tq.QuestionScore,
    ta.AnswerScore
FROM #top_questions AS tq
JOIN #top_answers AS ta
    ON tq.OwnerUserId = ta.OwnerUserId
    AND tq.tq = ta.ta
ORDER BY
    tq.QuestionScore DESC,
    ta.AnswerScore DESC;

Breakdown

The end result takes about 10 seconds, and has no exchange spills.

Infinito

For completeness, hinting the query with a hash join results in just about the same execution time as the temp table rewrite at 10 seconds. There are also very strong benefits to using Batch Mode. The query as originally written, and with no hints, finishes in about two seconds with no exchange spills, and I absolutely love that.

In tomorrow’s post, we’ll look at how we can sometimes adjust index key column order to solve SARGability issues.

Thanks for reading!

Going Further

SARGability Week: Using Computed Columns To Fix Non-SARGable Queries

Posted on August 24, 2021May 16, 2022 by Erik Darling

Adventure

We’re going to start this week off by using a computed column to fix a non-SARGable query, because there are a few interesting side quests to the scenario.

Here’s the starting query, which has a few different problems:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE LEN(p.Body) < 200
AND   p.PostTypeId IN (1, 2);

Let’s say we’re doing this to audit short questions and answers for quality.

Since SQL Server doesn’t retain any precise data about string column lengths, we don’t have an effective way to implement this search.

Worse, since the Body column is a max datatype, no expression (SARGable or not) can be pushed to the index scan.

Maxed Out

The query plan shows us a full scan of the clustered index where the filters on PostTypeId are applied, and later on a filter operator that applies the len filter:

This is an ugly query, and if it’s one that we were going to make part of a regular review process, we probably don’t want users to sit around waiting 42 seconds on this every single time.

Getaround

To get this query cranking, we need to add a computed column — note that it doesn’t need to be persisted — and index it.

ALTER TABLE dbo.Posts
    ADD BodyLen AS 
        CONVERT
        (
            bigint, 
            LEN(Body)
        );

CREATE INDEX bodied ON dbo.Posts
    (BodyLen, PostTypeId);

Now our query looks like this:

Prize Money

A lot of people are afraid of computed columns, because they think that they need to be persisted in order to get statistics generated on them, or to index them. You very much do not.

The persisted attribute will write the results of the expression to the clustered index or heap, which can cause lots of locking and logging and trouble.

Indexing the computed column writes the results only to the nonclustered index as it’s created, which is far less painful.

Tomorrow, we’ll look at how we can use temp tables to fix issues with SARGability.

Thanks for reading!

Going Further

SARGability Week: What Is It, Anyway?

Posted on August 23, 2021May 16, 2022 by Erik Darling

Sake Of

SARGability is the in-club way of saying that a search predicate(s) can be used to seek through the key(s) of an index.

Some things that mess it up are:

function(column) = something
column + column = something
column + value = something
value + column = something
column = @something or @something IS NULL
column like ‘%something’
column = case when …
value = case when column…
Mismatching data types

Yes, this has all been written about quite a bit — here and elsewhere — but it’s a query pattern that I still spend a lot of time fixing.

So here we are. More writing about it.

If you’re sick of hearing about it, stop doing it.

Symptomatic

So let’s say we’ve got this table:

CREATE TABLE
    dbo.sargability
(
    id int PRIMARY KEY,
    start_date date,
    end_date date
);

Right now, the only index on this table is on the id column. Since it’s the clustered index (by default, since it’s the primary key), it also “includes” the start_date and end_date columns. Conversely, this also means that any nonclustered indexes we create will have the id column automatically added to them. In the case of a non-unique index, it will be in the key. In the case of a unique index, it will be in the “includes”.

I once had someone go back and forth with me quite a bit about that last point, insisting that the clustered index didn’t have all of the table’s columns in it. But you know, if we run this query, we only touch the clustered index:

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s;

Slippery

This means two things: as long as we avoid the lapses in judgement listed up above, we can seek to a single value or range of values in the id column. The index has put the data in order (in this case ascending).

It also means that start_date and end_date are not in a searchable order, so any query we write that attempts to search/filter values there will have to scan the index (unless we also search/filter the id column).

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s
WHERE id = 1;

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s
WHERE s.start_date = '20210808';

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s
WHERE s.end_date = '20210808';

Recreation

Even though the equality predicates on start_date and end_date are perfectly SARGable, there’s no index for them to use to seek to those values in. They’re only in the clustered index, which is ordered by the id column. Other columns are not in a helpful order.

The fact that both of these queries end up scanning the clustered index may leave you under the impression that the isnull version is an acceptable practice.

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s
WHERE s.end_date = '20210808';

SELECT
    s.start_date,
    s.end_date
FROM dbo.sargability AS s
WHERE ISNULL(s.end_date, '19000101') = '20210808';

But with an index on the column the problem becomes more apparent, with the “good” query seeking and the “bad” query scanning.

CREATE INDEX s ON dbo.sargability(end_date) INCLUDE(start_date);

Evidence

For the rest of the week, we’re going to look at various ways to fix non-SARGable queries with things like computed columns, temp tables, index key column order, and dynamic SQL.

These are the approaches I normally take in my query tuning work, so hopefully others will find them helpful.

Thanks for reading!

Going Further

Why CASE Expressions Are Bad For Query Performance In SQL Server Join And Where Clauses

Posted on August 9, 2021May 16, 2022 by Erik Darling

Off And On

I spend a lot of time fixing queries like this, so I figured that I’d write about it in hopes that I can save someone some time and pain later on.

Obviously, this extends to join clauses as well. A case expression there has just as much chance of causing performance issues.

This pattern crops up in some ORM queries that I’ve seen as well, but I haven’t been able to figure out the code that causes it.

Let’s look at why this causes problems!

Tractor Trailer

To give our query the best possible chance of not sucking, let’s create some indexes.

CREATE NONCLUSTERED INDEX p
ON dbo.Posts 
(
    PostTypeId,
    Score, 
    OwnerUserId
);

CREATE NONCLUSTERED INDEX u
ON dbo.Users 
(
    Reputation
)
INCLUDE 
(
    DisplayName
);

With those in place, let’s look at a simple example query.

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE 1 = 
    CASE 
        WHEN p.PostTypeId = 1
        AND  p.Score >= 10
        THEN 1
        ELSE 0
    END
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

The plan reveals textbook symptoms of a lack of SARGability: an index scan with a predicate, despite a perfectly seekable index being in place:

Shame about that! But we can make things worse, too.

The Worser

If we involve a new column in the case expression, this time from the Users table, the predicate will be applied at a really unfortunate place in the query plan.

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE 1 = 
    CASE 
        WHEN p.PostTypeId = 1
        AND  p.Score >= 10
        AND  u.Reputation > 5000
        THEN 1
        ELSE 0
    END
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

Now all the filtering happens at the join, and the query goes from taking about 1 second to taking about 5 seconds.

If you write queries like this, you’re asking for trouble.

Why Can’t You Just Be Normal?

If we express that logic without a case expression, performance turns out much better. Shocking, right?

SELECT
    u.Id,
    u.DisplayName,
    s = SUM(p.Score)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = 1
AND   p.Score >= 10
AND   u.Reputation > 5000
GROUP BY
    u.Id,
    u.DisplayName
HAVING SUM(p.Score) > 10000
ORDER BY s DESC;

This query takes about 240 milliseconds, which is a nice improvement.

We’re able to seek into our Super Helpful Index™ on the Posts table. Now I know what you’re thinking — we’re hitting the clustered index on the Users table — that must be horrible.

But no; because the Nested Loops Join is of the Apply variety, it makes more sense to use it to seek to a single Id, and evaluate the predicate on Reputation second.

Thanks for reading!

Going Further

Starting SQL: Why Wouldn’t An Index Help Your SQL Server Query Go Faster?

Posted on September 10, 2020May 14, 2022 by Erik Darling

Pennies From Redmond

We’re spoiled over here in SQL Server land. When we run queries, the optimizer will suggest indexes that might help it run faster. I say might, because sometimes it’s wrong. Like I mentioned before, these suggestions are a bit hasty.

If you read the limitations, the first thing they say is “It is not intended to fine tune an indexing configuration”.

And they’re not. But you know what? If you don’t have anyone who is fine tuning an indexing configuration, they’re a heck of a lot better than a whole lot of nothing.

Are You There, Optimizer?

Starting with no nonclustered indexes, and a clustered index on an unrelated column, this query is a prime candidate for an index. And yet, the optimizer has forsaken us in our time of need.

SELECT COUNT_BIG(*) AS records
FROM dbo.Comments AS c
WHERE COALESCE(c.UserId, 0) = 0;

This isn’t specific to COALESCE, any ol’ function — built-in or otherwise — would do it. But the optimizer doesn’t think an index would make this query any faster. That’s a real bummer.

You may hear people say things like “you can’t use indexes with functions”, but that sentence is incomplete. What’s meant is “you can’t use indexes to seek with functions”.

Going Boing

This goes back to the concept of SARGability that I talked about before. If we do a little bit more typing, the optimizer is our friend again. I know, typing more sucks, but you’ll thank me later.

SELECT COUNT_BIG(*) AS records
FROM dbo.Comments AS c
WHERE ( c.UserId = 0 
        OR c.UserId IS NULL );

The optimizer is back to having good ideas for us. But notice that both of these queries end up taking the same amount of time and doing the same amount of work.

If you were just getting started writing queries, performance tuning, or looking at query plans, you might see this and assume that query performance would remain identical.

It’s sort of a matter of opportunity, here. Like I said before, when you wrap predicates in functions, you lose the ability to seek into indexes.

Sure, you could use an index on UserId in either query, but it doesn’t usually fix a whole lot if you need to scan the entire thing.

Copying In

If we add in the index on UserId and compare plans, the difference is obvious.

Our clearly-expressed query does a lot better here. It’s able to seek, and even running with a serial plan is much faster than the parallel plan for the poorly-expressed query. These things do matter to performance. Functions, even built-in ones, have no relational meaning to the optimizer, and can’t be reasoned with.

When it comes to writing queries, it usually pays to over-communicate. When you take shortcuts, you might not get the best possible outcome.

Tomorrow, we’ll look at hidden missing index requests.

Thanks for reading!

Going Further

When Do Non-SARGable Predicates Not Cause Performance Problems In SQL Server?

Posted on January 21, 2020May 16, 2022 by Erik Darling

Short Answer

If you have selective predicates earlier in the index that filter a lot of rows, the SARGability of trailing predicates matters less.

	CREATE INDEX shorty ON dbo.a_table(selective_column, non_selective_column);

	SELECT COUNT(*) AS records
	FROM dbo.a_table AS a
	WHERE selective_column = 1
	AND ISNULL(non_selective_column, 'whatever') = 'whatever';

Am I saying you should do this? Am I saying that it’s a good example to set?

No. I’m just saying you can get away with it in this situation.

Longer Answer

The less selective other predicates are, the less you can get away with it.

Take these two queries:

	SELECT COUNT(*) AS records
	FROM dbo.Users AS u
	WHERE u.Id = 8
	AND ISNULL(u.Location, N'') = N'';

    SELECT COUNT(*) AS records
    FROM dbo.Users AS u
    WHERE u.Id BETWEEN 8 AND 9693617
	AND ISNULL(u.Location, N'') = N'';

The first one has an equality predicate on the Id, the primary key of the table. It’s going to touch one row, and then evaluate the residual predicate on Location.

The second query has a very non-selective range predicate on Id — still a selective column, just not a selective predicate anymore — so, we do a lot more work (relatively).

If we have this index, and we look at how four logically equivalent queries perform:

CREATE UNIQUE INDEX fast_lane ON dbo.Users(Id, Location);

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Id = 8
AND    ISNULL(u.Location, N'') = N'';

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Id BETWEEN 8 AND 9693617
AND    ISNULL(u.Location, N'') = N'';

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Id = 8
AND   (   u.Location = N''
      OR   u.Location IS NULL );

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Id BETWEEN 8 AND 9693617
AND   (   u.Location = N''
      OR   u.Location IS NULL );

The query plans tell us enough:

It really doesn’t matter if we obey the laws of SARGability here.

Expect Depression

There have been many times when explaining SARGability to people that they went back and cleaned up code like this to find it didn’t make much of a difference to performance. That’s because SARGability depends on indexes that can support seekable predicates. Without those indexes, it makes no practical difference how you write these queries.

Again, I’m not condoning writing Fast Food Queries when you can avoid it. Like I said earlier, it sets a bad example.

Once this kind of code creeps into your development culture, it’s hard to keep it contained.

There’s no reason to not avoid it, but sometimes it hurts more than others. For instance, if Location were the first column in the index, we’d have a very different performance profile across all of these queries, and other rewrites might start to make more sense.

Thanks for reading!

Going Further

Implicit Conversion Is A SARGability Problem, That’s Why It’s A Performance Problem In SQL Server

Posted on December 27, 2019May 16, 2022 by Erik Darling

Concerns

If you compare the things that non-SARGable queries cause issues with alongside the things that bad implicit conversions cause issues with, it’s an identical list.

Increased CPU
Inefficient use of indexes
Poor cardinality estimation
Maybe a bad memory grant based on that
Some “row by row” event

Though we often bucket the problems separately, they’re really the same thing.

That’s because, under the covers, something similar happens.

If you replace “CONVERT_IMPLICIT” with any other function, like ISNULL, COALESCE, DATEADD, DATEDIFF, etc. you may see the same performance degradation.

Probably not the most thought provoking thing you’ve ever heard, but if you understand why one is bad and not the other, this may help you.

Thanks for reading!