As the title suggests, I am having issues with performance with my subscriber. I run the exact same query (it's a recursive query with a few joins but nothing too serious). I can run this query on my publisher in under 1s but it takes over 30s on the subscriber.

Things I've taken into account so far:

  • This is the only subscriber
  • The two servers are practically identical in terms of computing power (in fact the sub has more firepower).
  • The indexes are the same on both but the publisher has an MSmerge_index (which I think should only exist on the publisher).
  • It's a partial merge but this subscriber doesn't have filtered articles (in other words it has the full data set) so the traffic should be identical besides users running select queries (only 30 users online right now with infrequent requests)

I have duplicated my environment onto development servers and see the same results (slow subscriber, fast publisher).

Is there anything I can do to improve this performance? Some index or modification? Or is this something that I have to live with?

Thanks in advance!

  • Can you upload the actual execution plans for fast and slow queries (you can use pastebin to upload the plan xmls) ? Are the stats updated on subscriber - where query is running slow ? – Kin Shah Dec 2 '15 at 19:39
  • Working on it :) I am renaming any company specific stuff, database names, tables, etc. – tmwoods Dec 2 '15 at 20:02
  • You can use plan explorer to obfuscate information from plan.. if that helps ! – Kin Shah Dec 2 '15 at 20:10
  • Slow: pastebin.com/j5g9GxzX Fast: pastebin.com/EhVNL3sY – tmwoods Dec 2 '15 at 20:14
  • Notepad++ for the win ;) – tmwoods Dec 2 '15 at 20:14

Your Answer

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

Browse other questions tagged or ask your own question.