Category: DOP Feedback

SQL Server 2022 DOP Feedback: Related Extended Events

Posted on by Erik Darling

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.

What’s The Point Of DOP Feedback In SQL Server 2022?

Posted on by Erik Darling

Loud Engine

If you’re ever on a long flight and want something to fall asleep to, ask a DBA how to set MAXDOP.

Sometimes I even ask myself that question when I’m tossing and turning at night.

There are a lot of things to consider when fixing settings globally across a workload. For parallelism, it’s less about individual query performance, and more about overall server/workload performance

After all, letting every query go as absolutely parallel as possible is only good up to a point; that point is usually when you start regularly running out of worker threads, or your CPUs could double as crematoriums.

Setting MAXDOP is about limiting the damage that a parallel workload can do to a server. The expectation is that a query running at DOP 8 will run 8x faster than a query running at DOP 1.

But setting MAXDOP for every query isn’t something you catch even the most persnickety performance tuners doing. Perhaps some of the more critical ones, but you know…

Let Me Rust

I’m not going to demo DOP feedback in this post, I’m just going to show you the situation that it hopes to improve upon.

To do that, I’m going to run a simple aggregation query at different degrees of parallelism, and show you the changes in query timing.

At DOP 1:

The query runs for 1.1 seconds, with 886ms consumed while scanning the Posts table.

SQL Server Query Plan
DOPPER DON

At DOP 2:

The query runs just about twice as fast, starting with the scan of the Posts table taking about half as long. This is good scaling. Add one CPU, go twice as fast as you did with one CPU.

SQL Server Query Plan
Rip Van Winkle

At DOP 4:

The gets about twice as fast again! The scan of the Posts table is now down to 263ms, and the query in total is at 330ms. Adding in two more cores seems a good choice, here.

SQL Server Query Plan
Bed Rock

At DOP 8:

The query no longer continues to get 2x faster. This isn’t a knock against DOP 8 in general; my query just happens to hit a wall around DOP 4. With 4 additional CPUs, we only save ~130ms at the end of the day.

SQL Server Query Plan
Anubis

Why This Is Cool

This new feature will help DBAs have to worry less about getting MAXDOP absolutely right across the board. Who knows, we may even see a day where MAXDOP is left at zero.

But you’d never skip that installer step, would you?

Anyway, for anyone out there who is paranoid about setting DOP too high, this should help your queries find a more approximately-good middle ground.

Hopefully it works as advertised.

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.