Hello,
I have a strange issue wherein I am getting a different execution plan on different secondary replicas in SQL Server 2016 SP2 Always on.
Architecture overview:
8 Nodes clusters with the primary and 2 secondary replicas in physical data center and 5 secondary replicas in AWS.
The problematic query works perfectly fine on the 3 replica's in Physical Datacenter but doesn't performs well in all the secondary replica's in AWS (We get non-clustered index scan + Index Spool on a particular table) and execution plan of the physical nodes shows Index seek for the same table.
Why I am having a different exec plan and performance problems for the Nodes in AWS.?
FYI, the table in question is a large table which had 200 Million rows and we deleted around 150 million of rows from the table, we did Index re-organize, stats update and everything however the issue remains the same.
I have checked the following but in this case my query is working perfectly fine on the replica's in the physical data center, the problem is only with the replica's in AWS
https://docs.microsoft.com/en-us/troubleshoot/sql/availability-groups/query-performance-degradation
One Strange thing, I am getting huge worktable logical reads
Table 'Worktable'. Scan count 92, logical reads 14109650, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Also, everything was working perfectly fine before the deletion, issue started with the deletion.