SQL Server 2022 DOP Feedback: Related Extended Events

Short Round

---

Sort of on the heels of yesterday’s post, here are some Extended Events related to the DOP Feedback feature new to SQL Server 2022.

Here’s the complete text of a session to collect all of the related events that I’ve noticed so far:

CREATE EVENT SESSION 
    dop_feedback 
ON SERVER 
ADD EVENT 
    sqlserver.dop_feedback_eligible_query
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_provided
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_validation
    (
        ACTION(sqlserver.sql_text)
    ),
ADD EVENT 
    sqlserver.dop_feedback_reverted
    (
        ACTION(sqlserver.sql_text)
    )
ADD TARGET package0.event_file(SET filename=N'dop_feedback')
WITH 
(
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
);

There is one additional event in the debug channel called maxdop_feedback_received, but the contents of it don’t appear immediately actionable.

Defining Moments

---

Here are the definitions for each of the events above:

• dop_feedback_eligible_query: Reports when a query plan becomes eligible for dop feedback
• dop_feedback_provided: Reports DOP feedback provided data for a query
• dop_feedback_validation: Reports when the validation occurs for the query runtime stats against baseline or previous feedback stats
• dop_feedback_reverted: This reports when a DOP feedback is reverted

Fairly straightforward, here. Also seems like a decent set of events that you’d wanna have in place.

Thanks, Microsoft.

MAPDOP

---

The map values for each of these events is also available:

+-----------------------+---------+--------------------------------------+
|         name          | map_key |              map_value               |
+-----------------------+---------+--------------------------------------+
| dop_calculation_stage |       0 | SetMaxDOP                            |
| dop_calculation_stage |       1 | SetTraceflag                         |
| dop_calculation_stage |       2 | CalculateBasedOnAvailableThreads     |
| dop_calculation_stage |       3 | PostCalculate                        |
| dop_feedback_state    |       0 | NotAnalyzed                          |
| dop_feedback_state    |       1 | NotEligible                          |
| dop_feedback_state    |       2 | InAnalysis                           |
| dop_feedback_state    |       3 | NoRecommendation                     |
| dop_feedback_state    |       4 | AnalysisStoppedDueToThrottling       |
| dop_feedback_state    |       5 | AnalysisStoppedDueToMaxResetsReached |
| dop_feedback_state    |       6 | AnalysisStoppedMinimumDOP            |
| dop_feedback_state    |       7 | PendingValidationTest                |
| dop_feedback_state    |       8 | VerificationRegressed                |
| dop_feedback_state    |       9 | RegressionDueToAbort                 |
| dop_feedback_state    |      10 | Stable                               |
| dop_statement_type    |       1 | Select                               |
| dop_statement_type    |       2 | Insert                               |
| dop_statement_type    |       3 | Update                               |
| dop_statement_type    |       4 | Delete                               |
| dop_statement_type    |       5 | Merge                                |
+-----------------------+---------+--------------------------------------+

Why two are zero-based and one is not is beyond what I can explain to you, here.

Perhaps that will be addressed in a future release.

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.