In my previous blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-minimizing-blocking-of-redo-thread-when-running-reporting-workload-on-secondary-replica.aspx, I described how Readable Secondary eliminates potential REDO thread blocking for DML work running on the primary replica by mapping all isolation levels used in the reporting workloads to Snapshot Isolation. While it was essential that we eliminate REDO thread blocking with concurrent DML on secondary replica, it comes at some overheads as described in this blog.
First thing to understand is that if you do not enable secondary replica for read workload, there is no Snapshot Isolation mapping needed and therefore no additional overhead. Things get interesting when you enable the secondary replica for read workload. You may recall that both Snapshot Isolation and RCSI are based on row versioning. When a row is modified, its previous version is saved in the version store backed by TempDB and a 14 byte pointer is set from the modified row to the versioned row. If the data row gets modified again, the process is repeated. The key point to note here is that newly inserted or modified data rows will have a 14 byte overhead. There are 4 possible scenarios to consider here
There is no change or additional guidance for managing and provisioning for row versioning in this configuration compared to what we recommend for Snapshot Isolation or RCSI. Please refer to Books Online for details of SI and RCSI.
I found it very difficult to get information about the REDO_THREAD_PENDING_WAIT state. Even Microsoft has Wait State documents online that do not even mention that state and Red Gate's help file also ignores it.
This wait state is the second highest state in our Always on - 1 Secondary Read - configuration and we have been wondering whether it is normal or can be improved (wait state capturing according to www.sqlskills.com/.../wait-statistics-or-please-tell-me-where-it-hurts).
From your mentioned reasons in your 3 part blog we can eliminate the disk and network bottlenecks at our site.
Question of course is what the counter represents - if it counts the wait for redo thread to get new work assigned a high value would be good. If however it counts the inability of the redo thread to finish assigned work we would like to find ways to lower that count.
In another thread this wait counter has been defined as beeing used to keep multiple secondaries at about the same data status. It is not applicable to our own scenario but can you confirm that to be another reason for the counter to be high?