June 28, 2008 at 1:47 pm
Hi,
One of our client have their production database server in UKRAINE. Last Friday, all the users were affected with the 'disk out of space' errror. However, on backing up log and a subsequent restart did seem to bring back the thing in order, but another problem surfaced......A job which was taking around 30 minutes......now clocks a little more than 12 hours..!!!
I had a discussion with the DBA in charge who confirmed that nothing precisely was changed, indexes are dfragged and re-built every night (and Friday was no exception). The processor utilization was somewhat high, around 60-100% but the memory utilizaton was around 50%. I was told that the SP (which is actually run through the job) is a complex one, it uses cursors and there is scope for optimization. Data files are located on the local drive thus nullifying the network congestion possibility.
Accepting all this, what can go wrong that caused this drastic shift of performance, given that NOTHING else was changed.
It is a P1 issue and will greatly appreciate any suggestion that can work as a pathfinder to us.
Regards,
June 28, 2008 at 2:16 pm
Is the job still doing something? Is it still running or is it running for 12 hours continuously?
I'd run a trace to be sure that something didn't get hung up or get blocked. Or see if it actually is doing work for that 12 hours and it's just slow.
June 28, 2008 at 7:18 pm
My guess would be that either your stats need to be updated/regenerated or you've got a parameter sniffing issue with the proc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 5:45 am
Thanks for your response. The job did finish successfully.
Barry, could you please be a little elaborate about parameter sniffing. I've heard about it but do not have a clear idea.
Regards,
June 29, 2008 at 5:50 am
Try this link: http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 29, 2008 at 9:56 am
Thank you so much.
Regards,
July 2, 2008 at 7:41 am
Thank you for your continuous help and guidance. Problem was in the poorly written code, too many cursors. The same has been optimized and the job now comletes in a few minutes.
Regards,
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply