Transactional Replication - Slow Running Query On Subscriber

  • Hi,

    I am facing a strange problem of a query running slow on a subscriber in a Transactional Replication. This subscriber acts as a Reporting server. When i execute the same query on publisher it takes 2 min and 58 sec to execute whereas on the subscriber it takes more than half an hour.

    I am using OPTION(maxdop 1) in the query.

    When i replace the OPTION(maxdop 1) in the query with OPTION(FAST 10) the execution time on the subscriber reduces to 9 min and 58 sec. I have also created few of the missing indexes on the subscriber, done the Re-indexing as well as Updated all the statistics on the subscriber but could not see any improvemnets. The query execution plan on subscriber also looks OK to me.

    Please could anybody of you please help me out in this issue.I have been trying to fix it since past week but no luck as of now.

    Many Thanks and Regards,

    Satnam

  • Have you checked your indexing on the replicated data as well as statistics and fragmentation?

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • I maybe stating the obvious, lets start by elimination variables:

    Are the SQL versions the same?

    Is the hardware a different setup?

    Have you compared the query execution plans for differences?

    Have you checked for contention by running a lock escalation trace?

  • Thank u all for the response. I have fixed this issue successfully.

    With Thanks,

    Satnam

  • Well done.

    What was it?

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

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