Query performance drops due to version_ghost_record_count in AlwaysOn


The title is actually a little bit misleading, because the issue will not only happen on AlwaysOn, but let’s start with this. The symptom is that, you may have alwayson environment and you have readable secondary, when the secondary failed synchronization or secondary sql server service stopped, you may find the query performance on primary degraded also.

 

In this situation, the first suggestion is always try to collect the ACTUAL execution plan, compare the plan before secondary stopped and after. If the plan are totally the same, then you can refer to this article.

 

Check the IO information