performance test issue

Hi,

Simple query:

select * from MyTable where Id=123456;

The first time I execute this query it takes 14 seconds. However, the second time I execute it takes 0 seconds.

I wanted to clear the cache so the second execution takes 14 second just like the first execution. So i tried:

CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

But no success. I'm using ms sql server management studio (2005). I also tried the option Reset client statistics. I even restarted the sql server. No matter what I try, it looks like the result keeps somewhere saved.

Does somebody have a solution for this?

Cheerswhat is the problem if the query is executing faster ?|||

Quote:

Originally Posted by koekie17

Hi,

Simple query:

select * from MyTable where Id=123456;

The first time I execute this query it takes 14 seconds. However, the second time I execute it takes 0 seconds.

I wanted to clear the cache so the second execution takes 14 second just like the first execution. So i tried:

CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

But no success. I'm using ms sql server management studio (2005). I also tried the option Reset client statistics. I even restarted the sql server. No matter what I try, it looks like the result keeps somewhere saved.

Does somebody have a solution for this?

Cheers

Well this is first time I have seen somebody asking to degrade the performance of the query.

Performance problem -- Execute stored procedure

Hello,
I have a problem on running one particular stored procedure. It takes
less than 1 second when I run this stored procedure using SQL query
analyzer. However, when I run the same stored procedure using
Reporting Services, it takes 3,4 minutes to execute. This stored
procedure returns 52 rows with 16 fields. Does anyone know why and
how to solve this problem?
Thanks!I have never seen this myself but have heard of it before. For whatever
reason the query plan is messed up for that stored procedure when executing
it from RS. Try one of the below (I would start off with the With Recompile
as a test of whether this is the problem).
Forcing a Stored Procedure to Recompile
SQL Server provides three ways to force a stored procedure to recompile:
a.. The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
b.. Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for this
stored procedure; the stored procedure is recompiled each time it is
executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each time.
Use of this option is uncommon and causes the stored procedure to execute
more slowly, because the stored procedure must be recompiled each time it is
executed.
If you only want individual queries inside the stored procedure to be
recompiled, rather than the entire stored procedure, specify the RECOMPILE
query hint inside each query you want recompiled. This behavior mimics SQL
Server's statement-level recompilation behavior noted above, but in addition
to using the stored procedure's current parameter values, the RECOMPILE
query hint also uses the values of any local variables inside the stored
procedure when compiling the statement. Use this option when atypical or
temporary values are used in only a subset of queries belonging to the
stored procedure. For more information, see Query Hint (Transact-SQL).
c.. You can force the stored procedure to be recompiled by specifying the
WITH RECOMPILE option when you execute the stored procedure. Use this option
only if the parameter you are supplying is atypical or if the data has
significantly changed since the stored procedure was created.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<chiekot@.taiweb.com> wrote in message
news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> Hello,
> I have a problem on running one particular stored procedure. It takes
> less than 1 second when I run this stored procedure using SQL query
> analyzer. However, when I run the same stored procedure using
> Reporting Services, it takes 3,4 minutes to execute. This stored
> procedure returns 52 rows with 16 fields. Does anyone know why and
> how to solve this problem?
> Thanks!
>|||On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this myself but have heard of it before. For whatever
> reason the query plan is messed up for that stored procedure when executing
> it from RS. Try one of the below (I would start off with the With Recompile
> as a test of whether this is the problem).
> Forcing a Stored Procedure to Recompile
> SQL Server provides three ways to force a stored procedure to recompile:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon and causes the stored procedure to execute
> more slowly, because the stored procedure must be recompiled each time it is
> executed.
> If you only want individual queries inside the stored procedure to be
> recompiled, rather than the entire stored procedure, specify the RECOMPILE
> query hint inside each query you want recompiled. This behavior mimics SQL
> Server's statement-level recompilation behavior noted above, but in addition
> to using the stored procedure's current parameter values, the RECOMPILE
> query hint also uses the values of any local variables inside the stored
> procedure when compiling the statement. Use this option when atypical or
> temporary values are used in only a subset of queries belonging to the
> stored procedure. For more information, see Query Hint (Transact-SQL).
> c.. You can force the stored procedure to be recompiled by specifying the
> WITH RECOMPILE option when you execute the stored procedure. Use this option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <chie...@.taiweb.com> wrote in message
> news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > Hello,
> > I have a problem on running one particular stored procedure. It takes
> > less than 1 second when I run this stored procedure using SQL query
> > analyzer. However, when I run the same stored procedure using
> > Reporting Services, it takes 3,4 minutes to execute. This stored
> > procedure returns 52 rows with 16 fields. Does anyone know why and
> > how to solve this problem?
> > Thanks!
Also, to improve the performance of the stored procedure in general,
you could evaluate it with the Database Engine Tuning Advisor and
implement the suggested indexes where acceptable. Hope this helps
further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Bruce and Enrique, Thank you very much for your responses. We solved
this problem. What our DBA told me is that he changed that the stored
procedure is created with SET QUOTED_IDENTIFIER to ON. I appreciate
all your suggestions.
Retards,
Chieko
On Oct 15, 6:03 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>
>
> > I have never seen this myself but have heard of it before. For whatever
> > reason the query plan is messed up for that stored procedure when executing
> > it from RS. Try one of the below (I would start off with the With Recompile
> > as a test of whether this is the problem).
> > Forcing a Stored Procedure to Recompile
> > SQL Server provides three ways to force a stored procedure to recompile:
> > a.. The sp_recompile system stored procedure forces a recompile of a
> > stored procedure the next time it is run.
> > b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> > in its definition indicates that SQL Server does not cache a plan for this
> > stored procedure; the stored procedure is recompiled each time it is
> > executed. Use the WITH RECOMPILE option when stored procedures take
> > parameters whose values differ widely between executions of the stored
> > procedure, resulting in different execution plans to be created each time.
> > Use of this option is uncommon and causes the stored procedure to execute
> > more slowly, because the stored procedure must be recompiled each time it is
> > executed.
> > If you only want individual queries inside the stored procedure to be
> > recompiled, rather than the entire stored procedure, specify the RECOMPILE
> > query hint inside each query you want recompiled. This behavior mimics SQL
> > Server's statement-level recompilation behavior noted above, but in addition
> > to using the stored procedure's current parameter values, the RECOMPILE
> > query hint also uses the values of any local variables inside the stored
> > procedure when compiling the statement. Use this option when atypical or
> > temporary values are used in only a subset of queries belonging to the
> > stored procedure. For more information, see Query Hint (Transact-SQL).
> > c.. You can force the stored procedure to be recompiled by specifying the
> > WITH RECOMPILE option when you execute the stored procedure. Use this option
> > only if the parameter you are supplying is atypical or if the data has
> > significantly changed since the stored procedure was created.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <chie...@.taiweb.com> wrote in message
> >news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > > Hello,
> > > I have a problem on running one particular stored procedure. It takes
> > > less than 1 second when I run this stored procedure using SQL query
> > > analyzer. However, when I run the same stored procedure using
> > > Reporting Services, it takes 3,4 minutes to execute. This stored
> > > procedure returns 52 rows with 16 fields. Does anyone know why and
> > > how to solve this problem?
> > > Thanks!
> Also, to improve the performance of the stored procedure in general,
> you could evaluate it with the Database Engine Tuning Advisor and
> implement the suggested indexes where acceptable. Hope this helps
> further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -

Performance prblm with SP

Hi all,
My one SP takes around 30 second to execute. When I
execute the same SP from another SP and put the output
into a #table it takes more than 10 minutes. I m doing
something like this
create proc sp2
begin
create table #t ( ... )
insert into #t execute sp1
.
.
.
end
Tempdb shows numerous locks on sysobjects, syscolumns,
sysindexes and OAM pages.
Can anybody please suggest the cause and remedy for this?
I am running SQL 2000 on Win2K.
Thanks in advance
Best regards
HimanshuHi
Have you checked output from Profiler?
recomplile ,cache missing ?
"Himanshu" <himanshu@.ocwen.co.in> wrote in message
news:252a001c38d90$6998bb50$a601280a@.phx.gbl...
> Hi all,
> My one SP takes around 30 second to execute. When I
> execute the same SP from another SP and put the output
> into a #table it takes more than 10 minutes. I m doing
> something like this
> create proc sp2
> begin
> create table #t ( ... )
> insert into #t execute sp1
> .
> .
> .
> end
> Tempdb shows numerous locks on sysobjects, syscolumns,
> sysindexes and OAM pages.
> Can anybody please suggest the cause and remedy for this?
> I am running SQL 2000 on Win2K.
> Thanks in advance
> Best regards
> Himanshu|||Hiya,
Yes I checked profiler output by running SP many times. It
showed numerous locks acquired on
tempdb..sysobjects/sysccolumns/sysindexes table. And
frustratingly out of 10 run 3 times it runs very fast,
less than 15 seconds.
Any more places to peep?
thanks v much.
>--Original Message--
>Hi
>Have you checked output from Profiler?
>recomplile ,cache missing ?
>
>"Himanshu" <himanshu@.ocwen.co.in> wrote in message
>news:252a001c38d90$6998bb50$a601280a@.phx.gbl...
>> Hi all,
>> My one SP takes around 30 second to execute. When I
>> execute the same SP from another SP and put the output
>> into a #table it takes more than 10 minutes. I m doing
>> something like this
>> create proc sp2
>> begin
>> create table #t ( ... )
>> insert into #t execute sp1
>> .
>> .
>> .
>> end
>> Tempdb shows numerous locks on sysobjects, syscolumns,
>> sysindexes and OAM pages.
>> Can anybody please suggest the cause and remedy for
this?
>> I am running SQL 2000 on Win2K.
>> Thanks in advance
>> Best regards
>> Himanshu
>
>.
>

Performance of stored procedure is improved after database is backed up and restored

I have a stored procedure that performs very poorly on SQL Server 2000 standard edition. It takes approximately 40 seconds to execute. In the course of troubleshooting the issue I backed up the database to disk and restored it to another SQLServer and the performance issue was resolved. On a hunch I went to the original server, backed up the database to disk, and then restored the database from the backup I just created and voila the problem was resolved. Why would this resolve the issue? I have another database with the same stored procedure with the same problem. I have tried the following on the stored procedure and all related tables with no success:

DBCC DBREINDEX

UPDATE STATISTICS

sp_recompile my_storedprocedure.

What is the restore doing to resolve the issue? What command can I run to resolve this. I have a feeling it is the query plan but wouldn't update statistics correct this?

Is it possible that the physical database files are getting fragmented?

The restore would then lay the files down contiguously, which would improve performance.

Other than that, the content of the database including statistics would be exactly the same after the restore as it was at the time of backup. SQL backup is a physical copy of every allocated byte in the database files.

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
>

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
Adrian
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...
> 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 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
>
|||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.googlegr oups.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
>

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 about updating statistics, or recompiling the sp, have you tried?
AMB
"Adrian Dams" wrote:
> 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
>
>|||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...
> 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
>|||No, I haven't tried that. Thanks for the suggestion. I'll get back to you
with the result
Adrian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:36EEAAEC-4B19-4325-861A-1130FF81D4F6@.microsoft.com...
> What about updating statistics, or recompiling the sp, have you tried?
>
> AMB
> "Adrian Dams" wrote:
>> 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...
>> 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
>|||Is it possible to rebuild the stored procedure via script? This problem
exists on a customer site and the slow down in the sp is a big problem for
them. If possible, and if the rebuild works, I could set it up as a
scheduled job. If its not possible, it won't unfortunately work for me as a
solution
Adrian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:36EEAAEC-4B19-4325-861A-1130FF81D4F6@.microsoft.com...
> What about updating statistics, or recompiling the sp, have you tried?
>
> AMB
> "Adrian Dams" wrote:
>> 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
>>|||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...
>> 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...
>> 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
>>
>|||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 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
>|||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...
>> 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...
>> 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
>>
>>
>|||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...
>> 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...
>> 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...
>> 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
>>
>>
>>
>|||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
>