The Art Of The SQL Server Stored Procedure: Parameter Sniffing

Responsible Reading

---

One of the most fun parts of my job is telling people that parameter sniffing is a good thing. They’re often shocked, because the phrase has such ominous connotations to developers.

The parameters! They get sniffed! Performance! Something! Stuff!

Parameter sniffing does not mean the sky is falling. Like I’ve said before, if it were such a bad idea, it would have been abandoned.

What you have to be mindful of is how data gets distributed over time, and doing a few things to make sure the optimizer has as few reasonable plan choices as possible.

• Indexes keys that cover all where clause predicates at minimum
• Indexes includes that cover all select list columns in special circumstances
• Queries written to best take advantage of those indexes (SARGability, etc.)
• Using temporary objects to isolate critical parts of queries into manageable chunks

That will help cut down on how disastrous plan sharing is between queries that need to process very different amounts of data.

Under really crazy circumstances (and I know, I’ve linked to this before), I use dynamic SQL to fix parameter sensitivity issues.

 

SQL Server 2022 Not To The Rescue

---

SQL Server 2022 has a feature that’s suppose to help with parameter sensitivity issues. It’s okay sometimes, but the current set of limitations are:

• Only equality predicate parameters are evaluated for sensitivity issues
• Only one parameter is selected for managing sensitivity
• Only three query plan choices are generated for different estimation buckets

I have seen it be helpful in a few cases, but there are times when it should obviously kick in, but doesn’t.

There is currently no query hint to force the feature to kick in when it doesn’t (and it should).

Just so you know how I set up for this:

ALTER DATABASE StackOverflow2013 
SET COMPATIBILITY_LEVEL = 160;

Here are my indexes:

CREATE INDEX
    beavis
ON dbo.Posts
    (OwnerUserId, PostTypeId)
WHERE
    (PostTypeId = 1)
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX
    butthead
ON dbo.Votes
    (VoteTypeId, UserId, PostId)
INCLUDE
    (BountyAmount, CreationDate)
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

CREATE INDEX
    stewart
ON dbo.Badges
    (UserId)
WITH
    (MAXDOP = 8, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO

Here are the checks that validate options, etc.

SELECT
    d.name,
    d.compatibility_level,
    d.is_query_store_on
FROM sys.databases AS d
WHERE d.database_id = DB_ID();

SELECT
    dsc.configuration_id,
    dsc.name,
    dsc.value,
    dsc.is_value_default
FROM sys.database_scoped_configurations AS dsc
WHERE dsc.name = N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';

With That Out Of The Way

---

Here’s the stored procedure that causes us grief.

CREATE OR ALTER PROCEDURE
   dbo.VoteSniffing
(
    @VoteTypeId integer
)
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON;

    SELECT
        UserId  = 
            ISNULL(v.UserId, 0),
        Votes2013  = 
            SUM
            (
                CASE
                    WHEN 
                    (
                            v.CreationDate >= '20130101'
                        AND v.CreationDate <  '20140101'
                    )
                    THEN 1
                    ELSE 0
                END
            ),
        TotalBounty  = 
            SUM
            (
                CASE
                    WHEN v.BountyAmount IS NULL
                    THEN 0
                    ELSE 1
                END
            ),
        PostCount  = 
            COUNT(DISTINCT v.PostId),
        VoteTypeId  = 
            @VoteTypeId
    FROM dbo.Votes AS v
    WHERE v.VoteTypeId = @VoteTypeId
    AND   NOT EXISTS
    (
        SELECT
            1/0
        FROM dbo.Posts AS p
        JOIN dbo.Badges AS b
          ON b.UserId = p.OwnerUserId
        WHERE  p.OwnerUserId = v.UserId
        AND    p.PostTypeId = 1
    )
    GROUP BY 
        v.UserId
    ORDER BY
        PostCount DESC;
END;
GO

The good news is that all formatting checks pass here. Thank you, thank you, thank you. You’re far too kind.

Execution Problems

---

There’s only one parameter. It’s an equality predicate. And the VoteTypeId Column is highly volative.

You would think that with data distributions that look this way, there would be some attempt to remedy the situation by the Parameter Sensitive Plan Optimization feature.

Unfortunately, there is not.

Little Big Plan

---

When executed first with 4 as the VoteTypeId, the plan is very fast. Of course it is. We’re finding 733 rows to work with.

EXEC dbo.VoteSniffing
    @VoteTypeId = 4;

I have no qualms with this plan, other than the erroneous No Join Predicate warning. There is a join predicate, and you can see it in the Seek operators.

Heck, you can even see it in the query text. It’s pretty stupid.

When the plan is repeated for VoteTypeId 2, it is a disaster.

EXEC dbo.VoteSniffing
    @VoteTypeId = 2;

A Seek takes 9.5 seconds, a Nested Loops join occurs for 5 seconds, and a spill occurs for 22 seconds.

After the spill, another ~22 seconds is spent in various operators until it’s complete.

In other words, we still have our work cut out for us to tune this thing. If we go back to our list:

• Indexes keys that cover all where clause predicates at minimum ✅
• Indexes includes that cover all select list columns in special circumstances ✅
• Queries written to best take advantage of those indexes (SARGability, etc.) ✅
• Using temporary objects to isolate critical parts of queries into manageable chunks ❌

In this case, if we use a #temp table and isolate the portion of the query that evaluates a parameter (just getting data from the Votes table), we can solve 99% of the parameter sniffing problems between the majority of the VoteTypeIds.

However, VoteTypeId 2 is still a special flower. This is a good place to pause and ask ourselves if repeatedly filling a temporary object with 37 million rows is a good use of time.

This is when some techniques we’ve discussed before would come in handy:

• Dynamic SQL to treat VoteTypeId 2 differently from the others
• A separate stored procedure to intercept VoteTypeId 2 executions

Game Change

---

The reason why this is a more sensible arrangement is because when the plan is compiled initially for VoteTypeId 2, it runs just fine.

It’s also true that when VoteTypeId 4 uses the plan for VoteTypeId 2, it goes from taking 0 seconds across to taking a couple seconds. Sharing is not caring.

This is the fast plan for VoteTypeId 2. It has a lot of operators with additional startup costs that make grabbing small amounts of data slow.

Parameter sensitivity is a tough thing at times. Managing multiple plans for the same query can end up being quite complicated.

There are absolutely times when you’re going to have to throw in the towel and use a statement-level recompile hint to make things function appropriately.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.