May 31, 2011 at 8:26 am
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
May 31, 2011 at 9:46 am
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
June 2, 2011 at 3:48 am
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?
June 2, 2011 at 7:46 am
Thank u all for the response. I have fixed this issue successfully.
With Thanks,
Satnam
June 2, 2011 at 8:40 am
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