In the previous parts, we explained How we can Troubleshooting the Performance issue using built-in reports in SQL Server and How we can collect SQL Server logs using PSSDIAG and BPCheck then we explained How we can analyze and analyze the output of the PSSDIAG tool using SQL Nexus and PAL tool, check the previous parts from here  (Part 1, Part 2Part 3)

Today in Part 4 we will talk about Some of the cases that can lead us for AG latency Yes (SQL Server Always on Latency) one of the most common performance issues is AG latency especially when you implemented and configure the SQL Server Always on between multi-subnet, at this time you can see (HADR_SYNC_COMMIT) wait are very high or if you found the log send queue size is high at this time the SQL Server Database log files will keep increased in the primary and not reduced because of the Log Queue size on the secondary are very high and this meaning something is blocking the log Queue on the secondary, let us see what is HADR_SYNC_COMMIT and compression on AG Log transport

Check this Article to access all of the Parts of Troubleshooting Performance issues Like Microsoft Engineers


Upcoming parts

  • Part 5: How we can Troubleshoot the AG latency using the AG latency tool
  • Part 6: How we can Troubleshoot the AG latency using Custom T-SQL Scripts.

What is HADR_SYNC_COMMIT Wait type and How it can impact the performance

When you have two nodes and the synchronous replication between them in synchronouscommit mode at this time the primary will not commit any transaction until it receives an acknowledgment that all synchronous secondary replicas have finished. The HADR_SYNC_COMMIT Wait type is used to indicate the time of the transaction commit between the Primary replica and other secondaries replicas with synchronouscommit mode

When you see the Transaction are very slow on the primary One of the reasons that can be presented to this problem, (HADR_SYNC_COMMIT) Waite type is very high this means the transaction on the primary take a long time to commit because the Primary replica is waiting for the acknowledgment from the other secondaries replicas with synchronouscommit mode to commit this open transaction and send the result to the Application.

SQL Server compression on AG Log transport

What is Compression on AG and How it can impact the performance of APP transaction, SQL Server used compression technology for data streams between Primary replica and other secondary replicas and this compression by default is enabled in some cases and by default also is enabled in some cases, and you can enable or disable it using trace flag but Microsoft recommends to not do this change until you test it very well. ( Compression reduces network traffic, increases CPU load) that’s why one of the Improvement in SQL Server 2016 is disabled by default between Synchronous-commit replica to reduce the CPU between the two nodes when the primary waiting for the acknowledgment from the other secondaries replicas with synchronouscommit mode, So what about the network latency that can be increased when the Compression is disabled, to avoid this issue you should have a very good network connection between the SQL Server replica with synchronouscommit mode

So, by default in SQL Server 2016 compression is disabled in:

  1. Synchronous-commit replica     
  2. Using automatic seeding
  3. TDE enabled in the database

And by default, in SQL Server 2016 compression is enabled in Asynchronous- commit replica

For more information about this subject check these references

  1. Tune compression for availability group
  2. SQLServer 2016 AlwaysOn and new Log Transport behavior
  3. Performance and transaction log impact on the primary replica

What is ASYNC_NETWORK_IO Waite Type

It is one of the critical Waite types you can see it on SQL Server and this happened on two cases

  1. The Session on SQL Server is waiting for the APP to process the results and send a signal to SQL Server to process more other data and this happened when the APP request very large data from SQL Server
  2. Network issue between the Client APP and SQL Server

For more information about ASYNC_NETWORK_IO Waite Type, I highly recommend you to read this article very helpful.

Keep Following

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.