Skip to main content
deleted 106 characters in body
Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel. Reverting back to parallel redo also requires a restart with the traceflag disabled, test this beforehand.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel. Reverting back to parallel redo also requires a restart with the traceflag disabled, test this beforehand.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.

added 108 characters in body
Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel. Reverting back to parallel redo also requires a restart with the traceflag disabled, test this beforehand.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel. Reverting back to parallel redo also requires a restart with the traceflag disabled, test this beforehand.

added 772 characters in body
Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.

A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Source

Parallel Redo is eligible for worker migration as noted in the same source:

In SQL Server 2019, workers associated with availability group parallel redo tasks are enabled for worker migration to address a commonly observed scheduler contention issue among redo tasks on secondary replicas.

When migrating to SQL Server 2019 this feature is enabled by default.

Addition

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag 3459 help?

That trace flag can provide benefit if the queries running on the secondary cannot be adapted ( for example long running queries on the secondary reduced in time). But there could be other reasons such as frequent page splits.

One way to see if running the redo thread serial is beneficial for your set up is looking into the PARALLEL_REDO_TRAN_TURN wait stats but you should always compare these to other information such as redo queue size increase/decrease on the secondary .

Be mindful about enabling this traceflag as redo performance can suffer from going serial instead of parallel.

Source Link
Randi Vertongen
  • 16.5k
  • 4
  • 35
  • 64
Loading