Performance of SQL Server Stored Proc slows down 300% over a few weeks

Hi
I have a problem on my solution where a stored procedure that normally takes
about 200ms to execute will, over a period of about 1 to 2 weeks, gradually
slow down to over a second. Eventially, it'll even slow down to 3 seconds to
execute.
I have tried defragmenting indexes associated with tables in the query, I've
even dropped and recreated the indexes. This has a small effect but not
dramatic. As an attempt to halt the degradation in performance, I did create
a scheduled job to execute the DBCC INDEXDEFRAG command on a daily basis but
it has not stopped the execution time of the sp from degrading.
I have found that restarting the SQL Server will always bring the execution
time back down to around 200ms, but it is only a temporary solution. In a
week or two, the performance will have dropped it to 1 second again, as
described above.
I'm a little bit stumped. Has anyone else seen this or know how to stop it?
Thanks
AdrianWhat Service Pack? Any other apps running on that box? Memory settings?
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally
> takes about 200ms to execute will, over a period of about 1 to 2 weeks,
> gradually slow down to over a second. Eventially, it'll even slow down to
> 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query,
> I've even dropped and recreated the indexes. This has a small effect but
> not dramatic. As an attempt to halt the degradation in performance, I did
> create a scheduled job to execute the DBCC INDEXDEFRAG command on a daily
> basis but it has not stopped the execution time of the sp from degrading.
> I have found that restarting the SQL Server will always bring the
> execution time back down to around 200ms, but it is only a temporary
> solution. In a week or two, the performance will have dropped it to 1
> second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop
> it?
> Thanks
> Adrian
>|||SQL 2000 SP1. The machine is running quite a few other apps as well but the
machine has 1G on it. The machine doesn't seem to be starving for memory.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
> What Service Pack? Any other apps running on that box? Memory settings?
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx.gbl...
>|||You might want to look at installing SP 3a. There are a lot of reasons to
update, including security and some fixes that address issues such as memory
leaks.
How much memory is SQL server using? How do your memory settings look in
SQL? Are you using a fixed memory setting or allowing SQL to dynamically
allocate memory? Even though SQL doesn't appear to be starving for memory
right now, you might want to monitor memory usage of SQL and your other
apps; particularly during the slow-downs.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
> SQL 2000 SP1. The machine is running quite a few other apps as well but
> the machine has 1G on it. The machine doesn't seem to be starving for
> memory.
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:uvM$CB$LFHA.3500@.TK2MSFTNGP14.phx.gbl...
>|||In addition to the other posts, you might want to check the execution plans
between the different
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Adrian Dams" <adriandams@.yahoo.com> wrote in message news:e5QHDn9LFHA.1528@.TK2MSFTNGP09.phx
.gbl...
> Hi
> I have a problem on my solution where a stored procedure that normally tak
es about 200ms to
> execute will, over a period of about 1 to 2 weeks, gradually slow down to
over a second.
> Eventially, it'll even slow down to 3 seconds to execute.
> I have tried defragmenting indexes associated with tables in the query, I'
ve even dropped and
> recreated the indexes. This has a small effect but not dramatic. As an att
empt to halt the
> degradation in performance, I did create a scheduled job to execute the DB
CC INDEXDEFRAG command
> on a daily basis but it has not stopped the execution time of the sp from
degrading.
> I have found that restarting the SQL Server will always bring the executio
n time back down to
> around 200ms, but it is only a temporary solution. In a week or two, the p
erformance will have
> dropped it to 1 second again, as described above.
> I'm a little bit stumped. Has anyone else seen this or know how to stop it
?
> Thanks
> Adrian
>|||Thanks for the response.
Originally the sql server was set to dynamically allocate memory. The
machine had 500M and the sql server had used up about 220M.
I changed the memory settings so that the SQL Server would only use 150M
fixed. The odd thing is that the SQL Server didn't use 220M as it had
previously done but it did consume 180M and stabilise there - more than
150M!!
I will certainly apply SP3a and see how that helps
Thanks again
Adrian
"Michael C#" <xyz@.yomomma.com> wrote in message
news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
> You might want to look at installing SP 3a. There are a lot of reasons to
> update, including security and some fixes that address issues such as
> memory leaks.
> How much memory is SQL server using? How do your memory settings look in
> SQL? Are you using a fixed memory setting or allowing SQL to dynamically
> allocate memory? Even though SQL doesn't appear to be starving for memory
> right now, you might want to monitor memory usage of SQL and your other
> apps; particularly during the slow-downs.
> "Adrian Dams" <adriandams@.yahoo.com> wrote in message
> news:eFlhiO$LFHA.3420@.tk2msftngp13.phx.gbl...
>|||I like to stop and re-start the SQL Server service to ensure new memory
settings kick in. I don't know exactly what your configuration is or what
apps you have on that box, but I would definitely take a look at whether or
not you really need those other apps running on the same box; and if not,
move them somewhere else. SQL is resource-hungry, and the less other stuff
you have on that box the better off you'll be. Speaking of which, make sure
you have plenty of hard drive space on that box.
"Adrian Dams" <adriandams@.yahoo.com> wrote in message
news:eRIQbJKMFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> Originally the sql server was set to dynamically allocate memory. The
> machine had 500M and the sql server had used up about 220M.
> I changed the memory settings so that the SQL Server would only use 150M
> fixed. The odd thing is that the SQL Server didn't use 220M as it had
> previously done but it did consume 180M and stabilise there - more than
> 150M!!
> I will certainly apply SP3a and see how that helps
> Thanks again
> Adrian
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:evxNzu$LFHA.3812@.TK2MSFTNGP10.phx.gbl...
>|||if it is the case that your stored proc performs to expectations after
a reboot, then you probably want to add a "with recompile" at the top
of the stored proc, so that it will generate a new execution plan each
time it fires. the execution plan is probably falling out of scope over
time and causing table scans. when you restart sql server, the proc
cache is cleared, so a new one is made, hence the better performance.
if you cannot alter the stored proc, then you have two alternatives:
first, you can schedule a job that runs sp_recompile "proc_name"...do
this once a day and you should be fine.
second, you can pass the "with recompile" as a parameter to your stored
proc...this is usually done when the parameter value is atypical or
the data has changed significantly.
hth,
hans|||Thanks for all your suggestions. I will certainly try them and report back
on the progress
Adrian
"Hans Nelsen" <hnelsen@.owh.com> wrote in message
news:1111705078.992925.138370@.z14g2000cwz.googlegroups.com...
> if it is the case that your stored proc performs to expectations after
> a reboot, then you probably want to add a "with recompile" at the top
> of the stored proc, so that it will generate a new execution plan each
> time it fires. the execution plan is probably falling out of scope over
> time and causing table scans. when you restart sql server, the proc
> cache is cleared, so a new one is made, hence the better performance.
> if you cannot alter the stored proc, then you have two alternatives:
> first, you can schedule a job that runs sp_recompile "proc_name"...do
> this once a day and you should be fine.
> second, you can pass the "with recompile" as a parameter to your stored
> proc...this is usually done when the parameter value is atypical or
> the data has changed significantly.
> hth,
> hans
>