SQL 2008 Ent SP3.
Hi - We were attempting to deploy a new nonclustered index to a table. The database has about 100 (of 2000+) replicated tables. The table receiving the new index is not replicated, and in fact resides on a separate file group from most of the replicated tables in the database. Index was also created in the separate filegroup.
CREATE INDEX statement was ONLINE = ON. The index candidate is very narrow (one datetime column), and the table is clustered on a BIGINT primary key. The table is huge with nearly 1 billion rows.
What we saw was that replication was blocked for 30-40 seconds, then the blocks would release for 30-40 seconds. This process would continually repeat itself. Replication latency skyrocketed. No other process had any traffic to the table that was receiving the index (it is presently updated nightly by a batch job, which was complete before the CREATE INDEX started).
I don't have the types of locks that the CREATE INDEX statement held, my off-hours DBA did not capture this information. Nothing like this was seen in lower environments.
Just trying to get an idea why this isolated table receiving a nonclustered index would block replication. Any thoughts?