Sql-server – Indexed View Slows Replication

materialized-viewreplicationsql server

I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt.

I have tried to reinitialize the subscription, and even recreate the publication, still the problem remains and as soon as I am adding an indexed view it almost brings down replication.

The interesting part is that I already many indexed views on the subscriber and it just worked well, but recently I have tried to add more indexed views and then it all started, and now I am not even able to have even a single indexed view.

Any advice?

Best Answer

OK I found the solution, and I am writing it here not just to help others that encounter a similar problem, but it is also something worth for everyone that works with indexed views.

First some points on indexed views.

Indexed Views

  1. An index view although it is also an "index" it is also a "view", thus it might benefit when the system is updating the indexed view from non-clustered indexes on the base table (and even covering indexes and statistics).

    (To prove that point I have added indexes on base tables in a database that wasn't used besides getting replication data, and still the dm_db_index_usage_stats system view showed user seeks, and the only one use it was the indexed view.)

    And it appears that the best indexes to add for the benefit of an index view would be for seeking a single value or a small range of values, since this is what the indexed view is basically doing when being used locally or on a subscriber to transactional replication.

  2. Although conditional joins are not prohibited by the indexed view restrictions, still it is a good idea to avoid them (just as in regular queries).

    Consider the following join:

    FROM a
JOIN b ON b.id = a.id
JOIN c ON c.id = a.id
JOIN final ON final.id = b.FinalID OR (b.finalID IS NULL AND final.id = c.FinalID)

    This can cause performance bottlenecks, instead it is better split it up into two extra indexed views and then have a view that combines both using "UNION ALL".

  3. While and indexed view is also a "view" it is also an "index" which in other words that inserts/updates/deletes also has to update the indexed view, thus if the view has performance problems the update will take longer, and in the case of replication if each transaction takes to long then they might pile up after each other, and eventually bring replication to an halt

Please also take in mind when working with replication, that reinitializing the subscriber causes all of the indexes and statistics that have been added at the client to be dropped (if pre_creation_cmd has been set to drop which is the default), and only indexes that are part of the replication itself will be recreated, and one has to recreate all of the subscriber specific indexes manually.

So in my situation I have first added new indexed views that turned out to need better non-clustered indexes, and also some that used conditional joins, but after I reinitialized I didn't bother recreating first the base tables non-clustered indexes and first tried to create the indexed views which caused bottlenecks for the replication process.

Related Question