Stored Procedure Performance

I've got a stored procedure which is called from a VB application on the clients machine. This SPL takes about 2-3 seconds to run. I can run this SPL over and over and recieve 2-3 second results. Then all of sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It will then eventually return back to the normal 2-3 seconds. The client has 2 databases on this instance. One a live and one a test. The live has no problems and does not see this issue. While running this on test I've ran onstat and found no exclusive locks, yet it just sits there. There is no one else hitting this test base other than myself. Any ideas of what to look for. I've put set explain with stored procedure and it revealed nothing. Thanks, Joe P Senior Software Engineer

On 1 Jul 2003 09:22:16 -0700, joepacelli@earthlink.net (Joe Pacelli) wrote: What are your checkpoint durations? >I've got a stored procedure which is called from a VB application on >the clients machine. This SPL takes about 2-3 seconds to run. I can >run this SPL over and over and recieve 2-3 second results. Then all of >sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It >will then eventually return back to the normal 2-3 seconds. >The client has 2 databases on this instance. One a live and one a >test. The live has no problems and does not see this issue. While >running this on test I've ran onstat and found no exclusive locks, yet >it just sits there. There is no one else hitting this test base other >than myself. > >Any ideas of what to look for. > >I've put set explain with stored procedure and it revealed nothing. > >Thanks, >Joe P >Senior Software Engineer

Look at checkpoints [Most likely] Look at the 'set wait mode' and locking issues [you seemed to have checked] Set explain is only relevant when the SPL is created. Joe Pacelli wrote: > > I've got a stored procedure which is called from a VB application on > the clients machine. This SPL takes about 2-3 seconds to run. I can > run this SPL over and over and recieve 2-3 second results. Then all of > sudden it will go to 40+ seconds and stay this way for 1-2 minutes. It > will then eventually return back to the normal 2-3 seconds. > The client has 2 databases on this instance. One a live and one a > test. The live has no problems and does not see this issue. While > running this on test I've ran onstat and found no exclusive locks, yet > it just sits there. There is no one else hitting this test base other > than myself. > > Any ideas of what to look for. > > I've put set explain with stored procedure and it revealed nothing. > > Thanks, > Joe P > Senior Software Engineer -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

On Tue, 01 Jul 2003 12:22:16 -0400, Joe Pacelli wrote: Besides the other suggestions - is it possible that the SPL was compiled with a PDQPRIORITY other than zero? This might cause it to wait for resources when the system is busy. SQL Procedures run at the user's PDQ priority ONLY if they were compiled with PDQPRIORITY=0, if any other value that is the PDQ the procedure will execute at. So if you did something like UPDATE STATISTICS HIGH; on the entire database in a sessions with a high PDQPRIORITY level to speed the statistics sorts the SPL procs will have been compiled with that high priority. So, say you did this at PDQPRIORITY=50, most of the time no problem the SPL is able to gather 50% of the server's resources to itself and it runs like the wind. Othertimes the server is busy and only 10% of total resources are free. In this case the SPL proc will have to wait until it can get access to 50% of the CPU VPs and memory before it can begin to run. One symptom would be if you see multiple threads assigned to a session that is or has just run the offending SPL proc with a PDQPRIORITY=0 set. That's a dead giveaway. Solution: UPDATE STATISTICS FOR PROCEDURE <procname>; under PDQPRIORITY=0. Make sure that you always include the FOR TABLE clause when you run a database-wide UPDATE STATISTICS with positive PDQPRIORITY so that stored procedures are not recompiled by that command. Note that dostats, my utility to update statistics, does tables and procedure separately and resets the PDQPRIORITY of the session to zero (or a use specified value) before recompiling the SPL procs. Dostats is a powerful tool for maintaining the statistics in your database safely and efficiently. Dostats is included in the package utils2_ak available for download from the IIUG Software Repository. Art S. Kagel > I've got a stored procedure which is called from a VB application on the > clients machine. This SPL takes about 2-3 seconds to run. I can run this > SPL over and over and recieve 2-3 second results. Then all of sudden it > will go to 40+ seconds and stay this way for 1-2 minutes. It will then > eventually return back to the normal 2-3 seconds. The client has 2 > databases on this instance. One a live and one a test. The live has no > problems and does not see this issue. While running this on test I've > ran onstat and found no exclusive locks, yet it just sits there. There > is no one else hitting this test base other than myself. > > Any ideas of what to look for. > > I've put set explain with stored procedure and it revealed nothing. > > Thanks, > Joe P > Senior Software Engineer