We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.
One of the reports intermittently runs for much longer than normal. I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.
However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.
Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?
The servers have exactly the same hardware and the configuration is the same.
Best Answer
It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.
Details here:
FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014