September 19, 2002 at 6:06 am
I have a stored procedure that marks old data as ready for deletion and then deletes it from a fairly large (20GB) and complicated database (> 100 tables). The problem, as you might expect, is that there are perfomance issues.
I have managed to tune it to an extent, and set up the appropriate indexes to the stage where if I run run the sql from the SP in query analyser in stages it works acceptably well, performance wise, but from within the stored procedure this is not the case.
The SP calls numerous other SPs and I think the problem is that it calls one and then immediately calls the next so that they end up running at the same time and this causes problems.
How can I get the main SP to wait from step to step.
I am probably missing the wood for the trees at this stage.
Thank you in advance.
Nigel Moore
======================
September 19, 2002 at 6:43 am
The code should run procedurally, so there's no way one statement should run before the previous one has finished.
I think you need to look elsewhere for the problem.
September 19, 2002 at 7:12 am
..but when I run them individually one at a time there is no problem and if I run groups of them, where I know each will take a very short time to run, it works.
Its when I leave in the long running stored procedures I get the problems.
When Run indivdually they too are fine but when run (as I thought procedurally) from one Stored procedure they are a lot slower.
Nigel Moore
======================
September 19, 2002 at 7:34 am
You may be suffering from Stored Procedure recompilation.
Try running Profiler and speficially look for the events SP Recompile.
SQL server often decides to recompile a stored procedure if the underlying data has changed since the start of execution. I have seen SQL Server recompile a stored procedure many times in a single execution.
September 19, 2002 at 11:54 pm
Try after recompiling it again
secong think which can be done is put timestamp and see while running procedure internally which procedure is taking long.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 26, 2002 at 10:07 am
but even when I run it from Query Analyser it is slower if the statements are not run
sigularly or in small groups.
Nigel Moore
======================
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply