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.

SQL Server Extended Events
ho hum

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.

SQL Server Extended Events
so what?

Okay. Fine.

SQL Server Extended Events
yep.

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 on 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.



3 thoughts on “SQL Server 2022 Parameter Sensitive Plan Optimization: Extended Events Related To PSP

  1. Max Skewness would be a great name for a steampunk character.

    Have you seen “original_query_plan_hash…” return anything but 0?

Comments are closed.