When Secondary Nodes Affect Primaries

Dmitri Korotkevitch shows that a readable secondary in an Availability Group can negatively impact the primary:

A long time ago in a galaxy far, far away, I had to troubleshoot interesting performance issue in SQL Server. Suddenly, the CPU load on the server started to climb up. Nothing changed in terms of workload. The system still processed the same amount of requests. The execution plans of the critical queries stayed the same. Nevertheless, the CPU usage grew up slowly and steadily by a few percent per hour.

Eventually, we nailed it down. The problem occured in very busy OLTP system with very volatile data. We noticed that system performed much more I/O (logical and physical) than before. It was very strange, because nothing should have changed that day. Finally, we found that we have large number of deleted rows in the database that had not been cleaned up by ghost cleanup task.

Read on to learn what caused this mess.

Related Posts

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

A Tool For Analyzing AG Replica Latency

Simon Su has an interesting tool available: I wrote an article to discuss about data movement latency between AG groups: Now I develop a tool to analyze AG log block movement latency between replicas and create report accordingly. Click through for more info and check it out on Github.

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Categories

September 2018
MTWTFSS
« Aug  
 12
3456789
10111213141516
17181920212223
24252627282930