{"id":66,"date":"2023-11-11T04:31:27","date_gmt":"2023-11-11T04:31:27","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=66"},"modified":"2023-11-13T02:51:56","modified_gmt":"2023-11-13T02:51:56","slug":"the-art-of-resolving-hadr_sync_commit-waits-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=66","title":{"rendered":"The Art of Resolving HADR_SYNC_COMMIT Waits in SQL Server"},"content":{"rendered":"

Troubleshooting Hadr_sync_commit Wait Types in Always On Availability Groups<\/strong><\/p>\n

Navigating the complexities of SQL Server’s Always On Availability Groups can sometimes feel like solving a complex puzzle. One such challenging aspect involves understanding and resolving wait types related to Hadr_sync_commit. These wait types are like intricate clues that can reveal potential performance bottlenecks or issues in your Always On setup.<\/p>\n

HADR_SYNC_COMMIT is a wait type in sys.dm_os_wait_stats. The purpose of this wait type is to log the duration between transaction ready to commit in the primary replica and all secondary synchronous-commit replicas have acknowledged the hardening of the transaction commit LSN in an AG. It means a transaction in the primary replica cannot be committed until the primary replica received greater hardened LSNs from all secondary synchronous-commit replicas. If transactions in the primary replica are slower than usual, and HADR_SYNC_COMMIT is unusually long, it means there is some performance issue in at least one Primary-Secondary replica data movement flow, or at least one secondary replica is slow in log hardening.<\/span><\/p>\n

Let’s delve into a detailed guide to decode and address these challenges:<\/p>\n

Deciphering Hadr_sync_commit Wait Types<\/strong><\/p>\n

Encountering Hadr_sync_commit as a predominant wait type is akin to uncovering a critical piece of information. It indicates that a session is waiting for a commit to be acknowledged by a synchronous-commit secondary replica. This is a key sign that your primary replica is patiently waiting for the secondary replica to write the log to disk.<\/p>\n

Identifying the Challenges: Common Causes<\/strong><\/p>\n