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?

  • How many indexed views do you have at the subscriber? Can you add some detail on the table structure and the current/proposed indexed views? – Mark Storey-Smith Jul 5 '12 at 22:38
  • Its hard to describe in this post, but the point is now that as of now having any indexed view causes it to really slow down (even an indexed view that used to work fine) – yoel halb Jul 5 '12 at 23:02
  • At some point you are of course going to reach "a limit" of what the hardware can deliver. Every indexed view you add is a write overhead and at some point you're going to saturate the capacity of the IO subsystem. If you're adding indexed view, after indexed view, after indexed view... maybe it's time you went back to the drawing board and assessed whether this is a viable strategy. – Mark Storey-Smith Jul 5 '12 at 23:10
  • Again, I have removed all existing indexed views and reinitialized, and now as long there is no indexed view then everything is running fine, but if I am adding even a single indexed view then the problems begin – yoel halb Jul 5 '12 at 23:46
  • You're missing the point. This could be a very chatty conversation so I suggest you visit the heap and we can thrash out the detail there. In nutshell, you need to provide alot more information to get a useful answer. – Mark Storey-Smith Jul 6 '12 at 0:00

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.