Indexed view (MView) on replicated server is very very slow

Question

Indexed view (MView) on replicated server is very very slow

mario17

SSCertifiable

Points: 6079
More actions
September 10, 2014 at 5:30 pm

#397327

Hi, all
That what we got, we had very nice tuned MView defined on Server01 against tables in dbA and dbB (on same Server01).
OK it's time to employ to next env:
So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..
Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked:
"Don't worry man, we took care of everything, go directly to ServerRep for everything..."
And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.
I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...
Can you help me to ask intelligent questions from our DBA and probably smoothly shift them into right direction, I think we need to fix something, or can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ? This MView is very imprtant to us. Alternative will be to replace it with 10 multi joins.
Appreciate you thoughts
Best
Mario

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

mario17 (9/10/2014)
Hi, all
That what we got, we had very nice tuned MView defined on Server01 against tables in dbA and dbB (on same Server01).
OK it's time to employ to next env:
So we got ServerA.dbA and ServerB.dbB and looks like nobody thought about that MVew. IT works thru linked server now, so surely enough it sucks..
Then even more: we got piece of DW on ServerRep with replcicated pieces of ServerA and ServerB, how it works I asked:
"Don't worry man, we took care of everything, go directly to ServerRep for everything..."
And I see that our MView sucks even deeper, practically 0 performance, I think what going on it's still hit that linked server between ServerA.dbA and ServerB.dbB on background.
I still can use it on ServerRep WITH NOEXPAND hint, which means it was properly defined...
Can you help me to ask intelligent questions from our DBA and probably smoothly shift them into right direction, I think we need to fix something, or can we have that scenario at all? Or we can replicate all Tables on ServerRep and then define/redefine this view directly on replicated server ? This MView is very imprtant to us. Alternative will be to replace it with 10 multi joins.
Appreciate you thoughts
Best
Mario

Quick questions, what type of replication and what is the type of article specified for the indexed view?

😎

mario17 SSCertifiable Points: 6079 More actions · Answer 2

Hi, Eirikur

This is one way Xactional Replication, I suspect that was done in bulk, I don't really know answer about article, most likely it default (so could be Table only?), I need to do my homework, and then will update asap.

Or maybe you can give a hint what type it should be? I afraid I need to drive our dba team, as they too busy.

Can we just do Table replication and then define view against replicated tables? Or there is some kind of complications? I think overall we need to revise this MView, as initially it was designed to work on same server's dbs.

Thanks

Best

Mario

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

mario17 (9/11/2014)
Hi, Eirikur
This is one way Xactional Replication, I suspect that was done in bulk, I don't really know answer about article, most likely it default (so could be Table only?), I need to do my homework, and then will update asap.
Or maybe you can give a hint what type it should be? I afraid I need to drive our dba team, as they too busy.
Can we just do Table replication and then define view against replicated tables? Or there is some kind of complications? I think overall we need to revise this MView, as initially it was designed to work on same server's dbs.
Thanks
Best
Mario

If all the underlying tables are replicated (most likely logbased), my suggestion would be starting with the indexed view as "indexed view schema only" article type. When you get the current configurations, post them here and well have a look.

😎

mario17 SSCertifiable Points: 6079 More actions · Answer 4

Tx again, Eirikur.

This is enough information for me to understand that it was not taken into account, it was done by default on the fly. And I can't realy push anything to our dba team, + I'm remote.

I don't see any linked Servers in there so it might be linked thru other ways. So I take a break and will try to play it on my local machine.

Best + Takk fyrir

Mario

CCCP