Category: SQL Server 2022

Errant Hairs

I’ve used this proc as an example in the past. It’s a great parameter sniffing demo.

Why is it great? Because there’s exactly one value in the Posts table that causes an issue. It causes that issue because someone hated the idea of normalization.

The better thing to do here would be to have separate tables for questions and answers. Because we don’t have those, we end up with a weird scnenario.

In the Posts table, because questions and answers are lumped in together, there are certain traits that different types of posts can’t share:

• Answers can’t have answers
• Questions can’t have parent questions

There are other examples, but those are the two most obvious ones. But anyway, because of that, every Question has a ParentId of zero, and every Answer has the ParentId of the question it was posted under.

With around 6 million questions in the Posts table, that means there are around 6 million rows with a ParentId of zero, and around 11 million rows with other values.

Current Affairs

In compat level 150, if I run this procedure like so:

EXEC dbo.OptionalRecompile 
    @ParentId = 184618;

EXEC dbo.OptionalRecompile 
    @ParentId = 0;

The query plan is shared, and the second execution eats it:

From 4ms to 2 minutes is pretty bad.

2OH22

In compat level 160, if I do that exact same thing, the plans are immediately different for each execution:

This is a much better situation. Each separate plan is perfectly eligible for reuse. Neat. Great.

This is exactly the kind of query plan shenanigans (plananigans?) that should be avoided.

The only difference in the query text is the QueryVariantID:

option (PLAN PER VALUE(QueryVariantID = 2, predicate_range([StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 100.0, 1000000.0)))
option (PLAN PER VALUE(QueryVariantID = 3, predicate_range([StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 100.0, 1000000.0)))

And over in Query Store, we have these little lovelies:

SELECT qspf.* FROM sys.query_store_plan_feedback AS qspf;

+------------------+---------+------------+--------------+-----------------------+-------+-------------------+
| plan_feedback_id | plan_id | feature_id | feature_desc |     feedback_data     | state |    state_desc     |
+------------------+---------+------------+--------------+-----------------------+-------+-------------------+
|                6 |       3 |          1 | CE Feedback  | {"Feedback hints":""} |     1 | NO_RECOMMENDATION |
|                7 |       2 |          1 | CE Feedback  | {"Feedback hints":""} |     1 | NO_RECOMMENDATION |
+------------------+---------+------------+--------------+-----------------------+-------+-------------------+

Good job, SQL Server 2022.

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.

Respect Wood

There isn’t a lot interesting in these Extended Events. I was pretty disappointed with them.

The ones I looked at are in this session definition:

CREATE EVENT SESSION 
    psp
ON 
    SERVER 
ADD EVENT 
    sqlserver.parameter_sensitive_plan_optimization
(
    ACTION(sqlserver.sql_text)
),
ADD EVENT 
    sqlserver.parameter_sensitive_plan_optimization_skipped_reason
(
    ACTION(sqlserver.sql_text)
),
ADD EVENT 
    sqlserver.parameter_sensitive_plan_testing
(
    ACTION(sqlserver.sql_text)
),
ADD EVENT 
    sqlserver.query_with_parameter_sensitivity
(
    ACTION(sqlserver.sql_text)
)
ADD TARGET 
    package0.event_file
(
    SET 
        filename = N'psp'
)
WITH 
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 1 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

For the lazy, that’s:

• parameter_sensitive_plan_optimization: This event is fired when a query uses Parameter Sensitive Plan (PSP) Optimization feature.
• parameter_sensitive_plan_optimization_skipped_reason: Occurs when the parameter sensitive plan feature is skipped. Use this event to monitor the reason why parameter sensitive plan optimization is skipped
• parameter_sensitive_plan_testing: Fired when parameter sensitive plan is tested.
• query_with_parameter_sensitivity: This event is fired when a query is discovered to have parameter sensitivity. This telemetry will help us in identifying queries that are parameter sensitive and how skewed the columns involved in the query are.

The only one I haven’t gotten to fire yet in my testing is parameter_sensitive_plan_testing which does not break my heart.

Mythos

Most of what gets returned by those is barely useful. The one that I thought was most promising was the skipped_reason event. I was able to track map values down for that one:

+-------------------------+---------+----------------------------------+
|          name           | map_key |            map_value             |
+-------------------------+---------+----------------------------------+
| psp_skipped_reason_enum |       0 | None                             |
| psp_skipped_reason_enum |       1 | QueryVariant                     |
| psp_skipped_reason_enum |       2 | NoParameter                      |
| psp_skipped_reason_enum |       3 | InMemoryOLTP                     |
| psp_skipped_reason_enum |       4 | AutoParameterized                |
| psp_skipped_reason_enum |       5 | NonCacheable                     |
| psp_skipped_reason_enum |       6 | WithRecompileFlag                |
| psp_skipped_reason_enum |       7 | Unrecompilable                   |
| psp_skipped_reason_enum |       8 | TableVariable                    |
| psp_skipped_reason_enum |       9 | DBScopedConfigOff                |
| psp_skipped_reason_enum |      10 | QueryHint                        |
| psp_skipped_reason_enum |      11 | HasLocalVar                      |
| psp_skipped_reason_enum |      12 | QueryTextTooLarge                |
| psp_skipped_reason_enum |      13 | CursorWithPopulate               |
| psp_skipped_reason_enum |      14 | CLRModule                        |
| psp_skipped_reason_enum |      15 | Tvf                              |
| psp_skipped_reason_enum |      16 | DistributedQuery                 |
| psp_skipped_reason_enum |      17 | FullText                         |
| psp_skipped_reason_enum |      18 | OutputOrModifiedParam            |
| psp_skipped_reason_enum |      19 | UsePlan                          |
| psp_skipped_reason_enum |      20 | PDW                              |
| psp_skipped_reason_enum |      21 | Polybase                         |
| psp_skipped_reason_enum |      22 | EDC                              |
| psp_skipped_reason_enum |      23 | GQ                               |
| psp_skipped_reason_enum |      24 | DatatypesIncompat                |
| psp_skipped_reason_enum |      25 | LoadStatsFailed                  |
| psp_skipped_reason_enum |      26 | PlanGuide                        |
| psp_skipped_reason_enum |      27 | ParamSniffDisabled               |
| psp_skipped_reason_enum |      28 | NonInlinedUDF                    |
| psp_skipped_reason_enum |      29 | SkewnessThresholdNotMet          |
| psp_skipped_reason_enum |      30 | ConjunctThresholdNotMet          |
| psp_skipped_reason_enum |      31 | CompatLevelBelow160              |
| psp_skipped_reason_enum |      32 | UnsupportedStatementType         |
| psp_skipped_reason_enum |      33 | UnsupportedComparisonType        |
| psp_skipped_reason_enum |      34 | SystemDB                         |
| psp_skipped_reason_enum |      35 | UnsupportedObject                |
| psp_skipped_reason_enum |      36 | CompilationTimeThresholdExceeded |
| psp_skipped_reason_enum |      37 | Other                            |
+-------------------------+---------+----------------------------------+

Reading through that list, there are some interesting points where the feature won’t kick in. These points are sort of a ghosts of query anti-patterns past.

• TableVariable
• HasLocalVar
• CursorWithPopulate
• Tvf
• ParamSniffDisabled
• NonInlinedUDF

And, of course, this one gave me quite the chuckle: QueryTextTooLarge.

YOU HEAR THAT ENTITY FRAMEWORK?

Dulls

The results from my Extended Event session, and… I’ll talk through my disappointment in a moment, was not terribly spectacular.

The skipped_reason doesn’t reliably collect query text the way the other events do. That makes identifying the query that got skipped pretty difficult. There may be another action (like the stack) that captures it, but I haven’t had time to fully investigate yet.

Okay. Fine.

What do you do with this? I’m not sure yet. I don’t think I see much use in these just yet.

Hey, tomorrow’s another day. We’ll look at how the feature kicks in and fixes a parameter sniffing issue with this procedure.

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.

Classico

In the bad old days, which are still your days (but at least you have better days to look forward to), SQL Server’s cost-based optimizer would take a parametrized query, come up with a query plan, and then reuse that plan until:

• Plan cache clearing event
• Plan evicting event

There are a bunch of reasons for those things. They’re not important here.

What is important is the future, where SQL Server will have some more options for getting out of parameter sensitive jams.

I talked about a somewhat related feature called Cardinality Estimation Feedback here. This is a different beast, though.

While Cardinality Estimation Feedback is a between-executions like Memory Grant Feedback, the Parameter Sensitive Plan feature is a heuristic runtime decision, sort of like Adaptive Joins and Batch Mode On Row Store.

Like most things, you’ll need to be in the latest compatibility level, 160, and… it hasn’t been announced yet, but traditionally things like this are Enterprise Only.

Time will tell.

Shellac

The way this feature works is, rather than caching a single query plan for every other execution to use, it creates what’s called a Dispatcher plan (if your query qualifies).

You’ll see something like this in the properties of the root node of your query plan, and your query will have some additional funny business at the end of it.

And that other thing:

SELECT TOP (10) 
        u.DisplayName, 
        p.*
    FROM dbo.Posts AS p
    JOIN dbo.Users AS u
        ON p.OwnerUserId = u.Id
    WHERE p.ParentId = @ParentId
    ORDER BY u.Reputation DESC 
    OPTION 
    (
        PLAN PER VALUE
        (
            QueryVariantID = 3, 
            predicate_range
            (
                [StackOverflow2013].[dbo].[Posts].[ParentId] = @ParentId, 
                100.0, 
                1000000.0
            )
        )
    )

Note that I formatted the option part of the query a bit to make it a bit more likable (to me).

The basic message here is that for the @ParentId parameter, there are three plan variants (the limit currently), and this is the third one.

There are also a couple numbers there that indicate the upper and lower bounds for bucketizing the three plan variants.

S/M/L

The basic idea here is to generate small, medium, and large plans for different cardinality estimation ranges.

Most of the examples I’ve been able to come up with have only generated two plans, one for the very low end, and one for the very high end.

As far as I can tell, the limitations for this feature seem to be:

• Only for a single parameter predicate
• The predicate has to be an equality
• You only get three plan choices
• At present, it disassociates queries from the procedures that they live in

In tomorrow’s post, we’ll look at some of the Extended Events attached to this feature that give you some insights into when the feature kicks in, when it doesn’t, and what SQL Server thought about how sensitive your query is.

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.