Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2013-01-31 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-31 : 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-01-31 : 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 OptimizerTG |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-06 : 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 |
|
|
|
|
|
|
|