Replicated server: Select from MView WITH (NOEXPAND) vs (NOLOCK)

• mario17

  SSCertifiable

  Points: 6078

  More actions

  September 15, 2014 at 4:14 pm

  #290726

  Hi, all

  we have very nice MView that worked lighting fast WITH (NOEXPAND) on ServerA against ServerA.db100 and ServerB.db200 tables (thru linked servers).

  Now after replication on ServerR those 2 dbs it very slow but, I noticed that adding with (NOLOCK) restores its speed like on native server. I could not find any explanation to this fact.

  What is going on with NOLOCK ? Is there any theory behind this fact?

  select * from MView with (NOEXPAND) where custID = 100 ---VERY slow ...

  vs

  select * from MView with (NOLOCK where custID = 100 ---super fast

  select * from MView with (NOLOCK,NOEXPAND) where custID = 100 ---super fast

  Thanks

  Maio