| Author |
 Topic  |
|
|
Kimi86
Yak Posting Veteran
54 Posts |
 Posted - 01/31/2013 : 06:14:54
|
hello,
I have a stored procedure in my production envirronment which intermittenly takes loo long to run.. Most of the time it takes just 5 mins or so but say once in 15 days it goes running upto 4 hours.. Nothing of this sort happens in any other environment.. Since the sp is a long one and has many insert statements i am not sure which part of the sp is creating problem.. Also this runs during off business hours and by the time i reach office in the morning its all done and there is no way to check what happened last night because of which the sp was taking so long...I am clueless where to start my investigation.. I have checked the no of records being processed but even they have not been varing drastically.. Any clues where i can start my investigation??? |
|
|
James K
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/31/2013 : 08:18:49
|
The first thing I would do is to look for scheduled jobs or other tasks that may be running at that time. Especially since this is during off-business hours, it is possible that there are backup jobs or maintenance jobs or something else that is going on. Speak to your sysadmin and DBA and try to correlate the instances of poor performance with their activities.
If that does not lead you to anywhere, run a SQL trace to find out what else is going on at those times. Also, look for any other unscheduled activity - for example, if your files are set to autogrowth, whether the poor performance coincides with autogrowth instances. |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 01/31/2013 : 12:25:01
|
Do you have any maintenance tasks to help updates statistics and rebuild indexes? If not, it’s possible that you have statistical issues that are causing query issues. Or as James pointed out, it could be a busy time for the server. But, if you don’t have maintenance plans in place, I’d highly recommend you check out the scripts/jobs from Ola Hallengren:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
|
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 01/31/2013 : 14:16:01
|
generally for things like this it is also a good idea to (print, select, or raise) information messages with timestamps at every statement then direct output to a table or file. That way you can go back and analyze row counts, duration, errors, etc after the fact.
Be One with the Optimizer
TG |
|
|
|
srimami
Posting Yak Master
151 Posts |
Posted - 02/06/2013 : 03:25:58
|
| Check for the network speed and CPU usage. It might be possible that sometimes background processes are still running in spite of closing them. Run sp_who2 and kill sessions that were running in the background that are not necessary |
|
|
| |
Topic |
|
dont know where to start
