Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 dont know where to start
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

79 Posts

Posted - 01/31/2013 :  06:14:54  Show Profile  Reply with Quote
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

3873 Posts

Posted - 01/31/2013 :  08:18:49  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/31/2013 :  12:25:01  Show Profile  Reply with Quote
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:
Go to Top of Page

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 01/31/2013 :  14:16:01  Show Profile  Reply with Quote
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
Go to Top of Page

Posting Yak Master

160 Posts

Posted - 02/06/2013 :  03:25:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000