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 Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob