SQL Server 2022 Parameter Sensitive Plan Optimization: Extended Events Related To PSP

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. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.