Improvement
This improvement for Microsoft SQL Server 2017 and 2019 adds a manual method to set the maximum group commit time by using DBCC or startup trace flags (TFs).
Note Before you recommend to a customer that they use these trace flags, first verify with your Escalation Engineer and Product Group that this has to be done.
Resolution
This issue is fixed in the following cumulative updates for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Notes
-
Because the purpose of TF 9546 is to disable group commit, it should be disabled in this situation. That is, do not use TF 9546 if you want to use the mechanism of setting the maximum group commit time.
-
To enable setting maximum group commit time, use new trace flag 12306. This TF can be enabled during SQL Server startup or dynamically (through DBCC TRACEON(12306, -1)).
-
After TF 12306 is enabled, you can further enable 12311, 12312, 12314, 12318. These represent maximum group commit times of 1ms, 2ms, 4ms, and 8ms, respectively. They settings are additive. The maximum group commit time setting is capped at 10ms. However, these should not be repeated. For example, do not specify the same TF more than one time.
-
If you do not want to set maximum group commit time but want, instead, to revert to the default behavior of 10ms, disable TF 12306.
-
The effect of TFs 12311, 12312, 12314, and 12318 occurs when there is a checkpoint in the database. You can rely on the setting of SQL Server or the database to let the checkpoint automatically occur. If you want to let the TFs take effect immediately, you can issue a manual checkpoint.
-
In the SQL Server error log, you will see entries such as the following to indicate that the TFs were read:
-T 12306
-T 12311
-T 12312
Group commit time has been changed from 10 milliseconds to 3 milliseconds.
DBCC TRACEON 12314, server process ID (SPID) 60. This is an informational message only; no user action is required.
DBCC TRACEON 12314, server process ID (SPID) 60. This is an informational message only; no user action is required.
Group commit time has been changed from 3 milliseconds to 7 milliseconds.
References
Learn about the terminology that Microsoft uses to describe software updates.
