{"id":5598,"date":"2015-09-23T15:39:56","date_gmt":"2015-09-23T13:39:56","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-readable-secondary-replicas-and-index-rebuild-online-vs-offline\/"},"modified":"2015-09-23T15:39:56","modified_gmt":"2015-09-23T13:39:56","slug":"sql-server-alwayson-readable-secondary-replicas-and-index-rebuild-online-vs-offline","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-alwayson-readable-secondary-replicas-and-index-rebuild-online-vs-offline\/","title":{"rendered":"SQL Server AlwaysOn : readable secondary replicas and index rebuild online vs offline"},"content":{"rendered":"

A couple of weeks ago, I was involved in a discussion on the French forum developpez.com<\/a> about SQL Server AlwaysOn and availability groups, index strategies and the impact on the readable secondary replicas. During this discussion, one of the forum member stated that rebuilding an index online had less impact than rebuilding an index offline on a readable secondary replica but I disagree with this affirmative. Is it really true? Let\u2019s verify in this blog post.<\/p>\n

First of all, let\u2019s talk quickly about readable secondary replicas. Each engaged secondary replica has a dedicated redo thread which is responsible to apply the log records that come from the primary replica to the secondary replica (regardless the synchronization type). However, we can be in a situation where this thread can be blocked and it can leave the secondary replicas far behind (with an effective impact on the RTO defined for the concerned databases \/ applications). One of the common situation you may face is the following: a long running query on a readable secondary replica and at the same time a rebuild index operation is in progress followed by other DML commands on the primary replica.<\/p>\n

In fact, rebuilding an index generates a DDL operation (ALTER INDEX \u2026 REBUILD) that must be propagated and applied by the redo thread on each secondary replica. This operation needs to take a Sch-M lock that can block the redo thread while the reporting query is running.<\/p>\n

So, let\u2019s begin on the primary replica \u2026 What is the difference between rebuilding an index offline and online? Well, the latter is very interesting to reduce the overall lock contention. Indeed, with exception of short-time shared (S) at the beginning and schema-modification (Sch-M) locks at the end of the execution, there are no full table locks held. Other users can access the table, and<\/span> read, and modify data from there. The former needs to acquire schema modification (Sch-M) lock for the duration of the execution, which blocks entire access to the table even in read uncommitted transaction isolation level.<\/p>\n

\"blog<\/a><\/p>\n

 <\/p>\n

But wait… The above explanation is only valid on the primary replica. What about the secondary replicas? In fact, the story is not the same because the Sch-M lock must be applied by the redo thread which can be blocked while a long reporting query is running as said above. In this case, the blocking point is shifted from the offline \/ online feature to the total duration of the reporting query itself on each concerned secondary as shown below:<\/p>\n

\"blog<\/a><\/p>\n

 <\/p>\n

In both cases (rebuild index offline and online), we will be blocked on the secondary until the long running query is finished. Furthermore, generally speaking, rebuilding an index offline is quicker than performing the same operation online especially if there is an important concurrent DML activity at the same period. This is why on the above schema you will see the offline index mark before the online index mark in the timeline.<\/p>\n

Let me illustrate this point on my lab environment that consists of a pretty basic configuration:<\/p>\n

\"blog<\/a><\/p>\n

I will use for my tests the famous bigTransactionHistory<\/em> table in the AdventureWorks<\/em> database.<\/p>\n

On the primary replica I will rebuild the following index offline or online to compare each scenario:<\/p>\n