Default value for Cost Threshold For Parallelism may give deadlocks

[rasmus]

Deadlocks for MSSQL read models have been observed in production systems and a fix seems to be increasing the "Cost Threshold For Parallelism" in SQL Server.

Consider if EventFlow should log a warning upon initialization if the default value is set.

https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

[rasmus]

This will properly need some sections in the documentation as well.

[github-actions]

Hello there!

We hope you are doing well. We noticed that this issue has not seen any activity in the past 90 days.
We consider this issue to be stale and will be closing it within the next seven days.

If you still require assistance with this issue, please feel free to reopen it or create a new issue.

Thank you for your understanding and cooperation.

Best regards,
EventFlow

[github-actions]

Hello there!

We hope you are doing well. We noticed that this issue has not seen any activity in the past 90 days.
We consider this issue to be stale and will be closing it within the next seven days.

If you still require assistance with this issue, please feel free to reopen it or create a new issue.

Thank you for your understanding and cooperation.

Best regards,
EventFlow

[rasmus]

.analyze

[github-actions]

Issue Analysis

• EventFlow’s SQL Server read model path performs a SELECT check followed by INSERT/UPDATE per projection (Source/EventFlow.MsSql/ReadStores/MssqlReadModelStore.cs:97-198). Under load, SQL Server may choose a parallel execution plan even for these narrow queries because the instance-wide “Cost Threshold for Parallelism” (CTFP) default is 5. When several projection workers update/read rows concurrently, those parallel plans can introduce additional exchange operators and tempdb usage, significantly raising the chance of lock escalation and deadlocks.
• The generated SQL targets whole rows (SELECT *, UPDATE ... SET ...) without query hints (Source/EventFlow.Sql/ReadModels/ReadModelSqlGenerator.cs:92-156), so the optimizer is free to pick parallel plans. The Brent Ozar article cited in the issue documents how the default 5-cost threshold regularly triggers parallel plans for OLTP-style workloads, and production observations from the reporter align with that behavior.
• EventFlow opens connections on demand via MsSqlConnectionFactory.OpenConnectionAsync (Source/EventFlow.MsSql/Connections/MsSqlConnectionFactory.cs:32-37) and issues commands through MsSqlConnection.WithConnectionAsync (Source/EventFlow.Sql/Connections/SqlConnection.cs:70-146). There is currently no sanity check or telemetry emitted about the server-level configuration, so consumers can unknowingly run with the hazardous default.

Reproduction Guidance

1. Install SQL Server with the default cost threshold for parallelism = 5.
2. Run an EventFlow-based service that projects into MSSQL using UseMssqlReadModel. Feed a burst of events that update the same read model rows from several threads (the default subscription pipeline processes different aggregates concurrently).
3. Capture deadlock graphs via Extended Events or sp_BlitzLock: repeated deadlocks will show the EventFlow-generated UPDATE and INSERT statements as victims while waiting on page/key locks amplified by parallel operators.
4. Increase the CTFP value (e.g., 50) and rerun step 2; the deadlocks stop because the optimizer now favors serial plans for these OLTP queries.

Mitigation Strategy

• Emitting a diagnostic warning during service startup is feasible and low risk. On the first successful MSSQL connection, execute

  SELECT CAST(value_in_use AS int)
  FROM sys.configurations WITH (NOLOCK)
  WHERE name = 'cost threshold for parallelism';

  If the returned value is ≤ 5, log a warning explaining that the default promotes deadlocks for EventFlow read models and link to the documentation.
• Implementation-wise, the check can live in MsSqlConnectionFactory.OpenConnectionAsync (after OpenAsync) or in MsSqlConnection.WithConnectionAsync guarded by a static Lazy<Task> to ensure it runs once per process. Expose a configuration flag on IMsSqlConfiguration if teams need to suppress the probe.
• Handle permission failures gracefully: querying sys.configurations requires VIEW SERVER STATE. If access is denied (e.g., managed Azure SQL), catch the exception and log at LogDebug level so the warning feature doesn’t break tenant deployments.

Additional Considerations

• Document the warning and best-practice CTFP values in the MSSQL read model guide so operators know why the log appears.
• For teams hit by the deadlock already, raising the threshold to 50–75 or enabling trace flag 4199 (per Microsoft guidance) are immediate remedies; the EventFlow warning simply makes the misconfiguration visible earlier.
• Optional future hardening: offer an integration test or health-check hook that surfaces other problematic server settings (e.g., MAXDOP) alongside CTFP.

Next Steps

• Decide where to insert the one-time configuration probe (connection factory vs. connection wrapper).
• Add a small unit/integration test that fakes the configuration query result to ensure the warning fires only when expected.
• Update user-facing docs describing how to remediate the warning (set CTFP higher, gain server permissions if the check is gated).

Your friendly neighborhood AI