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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 dont know where to start

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.
Go to Top of Page

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
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -