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.
Easy – programmers made the 0 based extended event categories. A database person made the one that’s not 0 based 😀
row_number() -1