September 18, 2008 at 5:14 pm
Hi,
This is my first post on this forum. I have been scratching my head over an issue and thought I would throw it out here to see if anyone has any suggestions.
My company’s sales application is on a database that is accessed both by sales counter users and scheduled jobs. At least one of the scheduled jobs runs every 15 minutes for about 4-10 minutes and is so resource-intensive at times that it can impact the sales counter users’ performance. To remedy this, I set up a merge replication environment with the users connected to the publication and the scheduled jobs to the subscription. Both publisher and subscriber are identical 64-bit, Windows 2008 servers running SQL 2005 SP2.
This solution seemed to resolve the user-performance issue and ran fine for several hours. Then the resource-intensive scheduled job began to time out. By running a trace, I was able to identify the timing-out query. I ran this query alone against the publisher and subscriber and saw dramatic performance differences in how fast it ran (12 seconds and 53 minutes, respectively). Yesterday, I updated statistics on the subscription and the performance was then comparable to the publisher. The resource-intensive job running on the subscriber then ran the rest of the day without timing out. I created a maintenance plan to update the statistics daily on the subscription and it ran successfully at 12am this morning. By 5am, the problem job was timing out again at the same query. I again ran the query against the publisher and subscriber and got the same results as before (12 seconds and 53 minutes, respectively). I then updated statistics on one of the subscriber tables in the query and again its performance improved to where it was comparable to the publisher.
It appears that updating statistics frequently is a solution but in my mind it seems overkill to have to do this more frequently than once a day. I have considered doing this every hour on the one table but want to make sure that I am not just applying a bandaid that would mask something else that might be going on. Any suggestions?
September 18, 2008 at 9:19 pm
Are you updating the stats on the ms* related replication tables? If so, then I have experienced this and we do it once a day but also rebuild indexes as well. Not sure if that provides enough for us that we are able to get by longer. I know that if the job fails we know it really quick as replication will start to deadlock itself (which is odd anyway).
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 19, 2008 at 1:05 pm
Thanks for your response.
It appears that the maintenance plan is only updating stats on user tables and not the replication tables.
I checked this again today. I ran the problem query against the publisher and subscriber and got the same disparity of performance that I got yesterday before running UPDATE STATISTICS. I then ran the DBCC SHOW_STATISTICS statement on each of the statistics in one of the tables and compared the results on the publisher to the subscriber. In each case they appeared to be identical. I then updated the statistics on the same table on the subscriber and re-ran the problem query. Again, the subscriber's performance was now comparable to the publisher. It appears that the UPDATE STATISTICS statement does other things that are not readily apparent.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply