Innerise (7/7/2015)

---

Hi All

We have a 2-node SQL Server 2012 AlwaysOn setup, in synchronous mode with readable replica databases. We have a particular reporting query that we're running against one of the read-only replica databases, which is taking in excess of over 2 hours to complete on the replica node. However, when we run the same query against the primary database on the primary node, it completes in 5 seconds.

I've attached part of the 2 of the execution plans shown in Redgate SQL Sentry (Primary.png is the quick query, Replica.png is the slow query). These 4 rows are the first stages of the query (clustered index scans), which I believe are the cause of the slowness in the slow-running replica database query.

You can see that the 'Actual rows', 'Actual Executions' and 'Actual Data Size' are significantly higher in the replica database query. I'm struggling to see how this can be, if the databases are replicas of eachother.

Both nodes (VMs) are very similar in spec. However, I believe that the underlying storage is slower (SaS as opposed to SSD) on the replica - I'll will confirm this later.

Any help would be appreciated, as would any pointers as to where I should spend my troubleshooting time.

Thanks,

Innerise