Troubleshooting Performance issues Like Microsoft Engineers Part 4

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 2, Part 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 synchronous–commit 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 synchronous–commit 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 synchronous–commit 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 synchronous–commit 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 synchronous–commit mode

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

Synchronous-commit replica
Using automatic seeding
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

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

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
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.