recompile problem 
Author Message
 recompile problem

There seems to be a strange problem with SQL Server 2000.  An example
will describe it best.  There is a stored procedure called
spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
tables.  This sp runs in under a second at the beginning of the year.
It uses a table called PlayerTransactions that increases by 40,000
records each week.  At week 13, it has 444,521 rows.  It also uses a
table called Ftransactions which has approximately the same number of
rows.  The other five tables all have less than 30,000 rows, and the
size of these tables remains the same throughout the year.  After a
certain number of weeks, this sp will take much longer.  In week 13,
it was taking about 9 seconds.  What is strange is that if a run the
sp in the sql query analyzer, it takes under a second.  If I run it on
an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
page, it takes under a second and the problem is solved.  Here are my
questions:

1)      Does running the sp in the sql query analyzer uses a different
execution plan than the one that is running from the .asp page?  This
seems to be the case.  Why would this be?
2)      Why do I have to run the sp_recompile command in order for the
sp to run faster?  Is it because the sp is using an outdated query
plan?  If so, would you suggest I run the sp_recompile once a week or
is there a better way?  What controls when and if SQL Server creates a
new query plan without me using the sp_recompile command?



Sat, 22 May 2004 03:36:04 GMT
 recompile problem

James,

For  a fair judgement, try with DBCC FREEPROCCACHE
Refer booksonline for details.

Dinesh.


Quote:
> There seems to be a strange problem with SQL Server 2000.  An example
> will describe it best.  There is a stored procedure called
> spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
> tables.  This sp runs in under a second at the beginning of the year.
> It uses a table called PlayerTransactions that increases by 40,000
> records each week.  At week 13, it has 444,521 rows.  It also uses a
> table called Ftransactions which has approximately the same number of
> rows.  The other five tables all have less than 30,000 rows, and the
> size of these tables remains the same throughout the year.  After a
> certain number of weeks, this sp will take much longer.  In week 13,
> it was taking about 9 seconds.  What is strange is that if a run the
> sp in the sql query analyzer, it takes under a second.  If I run it on
> an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
> spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
> page, it takes under a second and the problem is solved.  Here are my
> questions:

> 1) Does running the sp in the sql query analyzer uses a different
> execution plan than the one that is running from the .asp page?  This
> seems to be the case.  Why would this be?
> 2) Why do I have to run the sp_recompile command in order for the
> sp to run faster?  Is it because the sp is using an outdated query
> plan?  If so, would you suggest I run the sp_recompile once a week or
> is there a better way?  What controls when and if SQL Server creates a
> new query plan without me using the sp_recompile command?



Sat, 22 May 2004 03:50:48 GMT
 recompile problem
Dinesh,

DBCC FREEPROCCACHE is an undocumented command   :-)

James,

The DBCC FREEPROCCACHE command clears SQL Server memory of all cached query
plans.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.



Quote:
> James,

> For  a fair judgement, try with DBCC FREEPROCCACHE
> Refer booksonline for details.

> Dinesh.



> > There seems to be a strange problem with SQL Server 2000.  An example
> > will describe it best.  There is a stored procedure called
> > spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
> > tables.  This sp runs in under a second at the beginning of the year.
> > It uses a table called PlayerTransactions that increases by 40,000
> > records each week.  At week 13, it has 444,521 rows.  It also uses a
> > table called Ftransactions which has approximately the same number of
> > rows.  The other five tables all have less than 30,000 rows, and the
> > size of these tables remains the same throughout the year.  After a
> > certain number of weeks, this sp will take much longer.  In week 13,
> > it was taking about 9 seconds.  What is strange is that if a run the
> > sp in the sql query analyzer, it takes under a second.  If I run it on
> > an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
> > spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
> > page, it takes under a second and the problem is solved.  Here are my
> > questions:

> > 1) Does running the sp in the sql query analyzer uses a different
> > execution plan than the one that is running from the .asp page?  This
> > seems to be the case.  Why would this be?
> > 2) Why do I have to run the sp_recompile command in order for the
> > sp to run faster?  Is it because the sp is using an outdated query
> > plan?  If so, would you suggest I run the sp_recompile once a week or
> > is there a better way?  What controls when and if SQL Server creates a
> > new query plan without me using the sp_recompile command?



Sat, 22 May 2004 09:09:23 GMT
 recompile problem
It sounds like it could be running slower due to the stored
procedure recompiling.
The plan may not be cached anymore or the data could be
stale so the stored procedure recompiles or it could be
other things. How you handle it depends on what's causing
the problem.
It's probably worth checking the Knowledge Base article on
troubleshooting stored procedure recompilations which covers
monitoring for recompilations as well as different issues
which can cause them:
http://support.microsoft.com/support/kb/articles/Q243/5/86.ASP

--Sue

On Mon, 03 Dec 2001 13:36:04 -0600, James Serra

Quote:

>There seems to be a strange problem with SQL Server 2000.  An example
>will describe it best.  There is a stored procedure called
>spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
>tables.  This sp runs in under a second at the beginning of the year.
>It uses a table called PlayerTransactions that increases by 40,000
>records each week.  At week 13, it has 444,521 rows.  It also uses a
>table called Ftransactions which has approximately the same number of
>rows.  The other five tables all have less than 30,000 rows, and the
>size of these tables remains the same throughout the year.  After a
>certain number of weeks, this sp will take much longer.  In week 13,
>it was taking about 9 seconds.  What is strange is that if a run the
>sp in the sql query analyzer, it takes under a second.  If I run it on
>an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
>spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
>page, it takes under a second and the problem is solved.  Here are my
>questions:

>1)  Does running the sp in the sql query analyzer uses a different
>execution plan than the one that is running from the .asp page?  This
>seems to be the case.  Why would this be?
>2)  Why do I have to run the sp_recompile command in order for the
>sp to run faster?  Is it because the sp is using an outdated query
>plan?  If so, would you suggest I run the sp_recompile once a week or
>is there a better way?  What controls when and if SQL Server creates a
>new query plan without me using the sp_recompile command?



Sat, 22 May 2004 10:39:36 GMT
 recompile problem
BP,

Depends on how you define "undocumented".
AFAIK DBCC FREEPROCCACHE is mentioned in SQL2000 BOL.

Dinesh.


Quote:
> Dinesh,

> DBCC FREEPROCCACHE is an undocumented command   :-)

> James,

> The DBCC FREEPROCCACHE command clears SQL Server memory of all cached
query
> plans.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > James,

> > For  a fair judgement, try with DBCC FREEPROCCACHE
> > Refer booksonline for details.

> > Dinesh.



> > > There seems to be a strange problem with SQL Server 2000.  An example
> > > will describe it best.  There is a stored procedure called
> > > spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
> > > tables.  This sp runs in under a second at the beginning of the year.
> > > It uses a table called PlayerTransactions that increases by 40,000
> > > records each week.  At week 13, it has 444,521 rows.  It also uses a
> > > table called Ftransactions which has approximately the same number of
> > > rows.  The other five tables all have less than 30,000 rows, and the
> > > size of these tables remains the same throughout the year.  After a
> > > certain number of weeks, this sp will take much longer.  In week 13,
> > > it was taking about 9 seconds.  What is strange is that if a run the
> > > sp in the sql query analyzer, it takes under a second.  If I run it on
> > > an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
> > > spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
> > > page, it takes under a second and the problem is solved.  Here are my
> > > questions:

> > > 1) Does running the sp in the sql query analyzer uses a different
> > > execution plan than the one that is running from the .asp page?  This
> > > seems to be the case.  Why would this be?
> > > 2) Why do I have to run the sp_recompile command in order for the
> > > sp to run faster?  Is it because the sp is using an outdated query
> > > plan?  If so, would you suggest I run the sp_recompile once a week or
> > > is there a better way?  What controls when and if SQL Server creates a
> > > new query plan without me using the sp_recompile command?



Sat, 22 May 2004 20:58:12 GMT
 recompile problem
Dinesh,

You're correct ... my mistake ... sorry about that   :-)

BPM



Quote:
> BP,

> Depends on how you define "undocumented".
> AFAIK DBCC FREEPROCCACHE is mentioned in SQL2000 BOL.

> Dinesh.



> > Dinesh,

> > DBCC FREEPROCCACHE is an undocumented command   :-)

> > James,

> > The DBCC FREEPROCCACHE command clears SQL Server memory of all cached
> query
> > plans.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > James,

> > > For  a fair judgement, try with DBCC FREEPROCCACHE
> > > Refer booksonline for details.

> > > Dinesh.



> > > > There seems to be a strange problem with SQL Server 2000.  An
example
> > > > will describe it best.  There is a stored procedure called
> > > > spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
> > > > tables.  This sp runs in under a second at the beginning of the
year.
> > > > It uses a table called PlayerTransactions that increases by 40,000
> > > > records each week.  At week 13, it has 444,521 rows.  It also uses a
> > > > table called Ftransactions which has approximately the same number
of
> > > > rows.  The other five tables all have less than 30,000 rows, and the
> > > > size of these tables remains the same throughout the year.  After a
> > > > certain number of weeks, this sp will take much longer.  In week 13,
> > > > it was taking about 9 seconds.  What is strange is that if a run the
> > > > sp in the sql query analyzer, it takes under a second.  If I run it
on
> > > > an .asp page, it takes 9 seconds.  If I run the command
"sp_recompile
> > > > spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
> > > > page, it takes under a second and the problem is solved.  Here are
my
> > > > questions:

> > > > 1) Does running the sp in the sql query analyzer uses a different
> > > > execution plan than the one that is running from the .asp page?
This
> > > > seems to be the case.  Why would this be?
> > > > 2) Why do I have to run the sp_recompile command in order for the
> > > > sp to run faster?  Is it because the sp is using an outdated query
> > > > plan?  If so, would you suggest I run the sp_recompile once a week
or
> > > > is there a better way?  What controls when and if SQL Server creates
a
> > > > new query plan without me using the sp_recompile command?



Sun, 23 May 2004 08:26:13 GMT
 recompile problem
Thanks for the response, but it does not explain it.  It seems the
query plan in cache is inefficient.  When I run the sp from an asp
page, it uses the query plan in the cache and it runs slow.  When I
run the sp in SQL Query Analyzer, it does not seem to be using the
query plan in the cache and it runs fast.  Would it be true to say
that sp's run in the SQL Query Analyzer are always recompiled before
they are run?



Quote:
>James,

>For  a fair judgement, try with DBCC FREEPROCCACHE
>Refer booksonline for details.

>Dinesh.



>> There seems to be a strange problem with SQL Server 2000.  An example
>> will describe it best.  There is a stored procedure called
>> spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
>> tables.  This sp runs in under a second at the beginning of the year.
>> It uses a table called PlayerTransactions that increases by 40,000
>> records each week.  At week 13, it has 444,521 rows.  It also uses a
>> table called Ftransactions which has approximately the same number of
>> rows.  The other five tables all have less than 30,000 rows, and the
>> size of these tables remains the same throughout the year.  After a
>> certain number of weeks, this sp will take much longer.  In week 13,
>> it was taking about 9 seconds.  What is strange is that if a run the
>> sp in the sql query analyzer, it takes under a second.  If I run it on
>> an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
>> spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
>> page, it takes under a second and the problem is solved.  Here are my
>> questions:

>> 1) Does running the sp in the sql query analyzer uses a different
>> execution plan than the one that is running from the .asp page?  This
>> seems to be the case.  Why would this be?
>> 2) Why do I have to run the sp_recompile command in order for the
>> sp to run faster?  Is it because the sp is using an outdated query
>> plan?  If so, would you suggest I run the sp_recompile once a week or
>> is there a better way?  What controls when and if SQL Server creates a
>> new query plan without me using the sp_recompile command?



Mon, 24 May 2004 05:46:40 GMT
 recompile problem
Thanks for the response, but it does not explain it.  It seems the
query plan in cache is inefficient.  When I run the sp from an asp
page, it uses the query plan in the cache and it runs slow.  When I
run the sp in SQL Query Analyzer, it does not seem to be using the
query plan in the cache and it runs fast.  Would it be true to say
that sp's run in the SQL Query Analyzer are always recompiled before
they are run?



Quote:
>BP,

>Depends on how you define "undocumented".
>AFAIK DBCC FREEPROCCACHE is mentioned in SQL2000 BOL.

>Dinesh.



>> Dinesh,

>> DBCC FREEPROCCACHE is an undocumented command   :-)

>> James,

>> The DBCC FREEPROCCACHE command clears SQL Server memory of all cached
>query
>> plans.

>> -------------------------------------------
>> BP Margolin
>> Please reply only to the newsgroups.
>> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
>> can be cut and pasted into Query Analyzer is appreciated.



>> > James,

>> > For  a fair judgement, try with DBCC FREEPROCCACHE
>> > Refer booksonline for details.

>> > Dinesh.



>> > > There seems to be a strange problem with SQL Server 2000.  An example
>> > > will describe it best.  There is a stored procedure called
>> > > spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
>> > > tables.  This sp runs in under a second at the beginning of the year.
>> > > It uses a table called PlayerTransactions that increases by 40,000
>> > > records each week.  At week 13, it has 444,521 rows.  It also uses a
>> > > table called Ftransactions which has approximately the same number of
>> > > rows.  The other five tables all have less than 30,000 rows, and the
>> > > size of these tables remains the same throughout the year.  After a
>> > > certain number of weeks, this sp will take much longer.  In week 13,
>> > > it was taking about 9 seconds.  What is strange is that if a run the
>> > > sp in the sql query analyzer, it takes under a second.  If I run it on
>> > > an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
>> > > spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
>> > > page, it takes under a second and the problem is solved.  Here are my
>> > > questions:

>> > > 1) Does running the sp in the sql query analyzer uses a different
>> > > execution plan than the one that is running from the .asp page?  This
>> > > seems to be the case.  Why would this be?
>> > > 2) Why do I have to run the sp_recompile command in order for the
>> > > sp to run faster?  Is it because the sp is using an outdated query
>> > > plan?  If so, would you suggest I run the sp_recompile once a week or
>> > > is there a better way?  What controls when and if SQL Server creates a
>> > > new query plan without me using the sp_recompile command?



Mon, 24 May 2004 05:47:25 GMT
 recompile problem
Thanks for the response, but it does not explain it.  It seems the
query plan in cache is inefficient.  When I run the sp from an asp
page, it uses the query plan in the cache and it runs slow.  When I
run the sp in SQL Query Analyzer, it does not seem to be using the
query plan in the cache and it runs fast.  Would it be true to say
that sp's run in the SQL Query Analyzer are always recompiled before
they are run?

On Tue, 4 Dec 2001 19:26:13 -0500, "BP Margolin"

Quote:

>Dinesh,

>You're correct ... my mistake ... sorry about that   :-)

>BPM



>> BP,

>> Depends on how you define "undocumented".
>> AFAIK DBCC FREEPROCCACHE is mentioned in SQL2000 BOL.

>> Dinesh.



>> > Dinesh,

>> > DBCC FREEPROCCACHE is an undocumented command   :-)

>> > James,

>> > The DBCC FREEPROCCACHE command clears SQL Server memory of all cached
>> query
>> > plans.

>> > -------------------------------------------
>> > BP Margolin
>> > Please reply only to the newsgroups.
>> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
>which
>> > can be cut and pasted into Query Analyzer is appreciated.



>> > > James,

>> > > For  a fair judgement, try with DBCC FREEPROCCACHE
>> > > Refer booksonline for details.

>> > > Dinesh.



>> > > > There seems to be a strange problem with SQL Server 2000.  An
>example
>> > > > will describe it best.  There is a stored procedure called
>> > > > spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
>> > > > tables.  This sp runs in under a second at the beginning of the
>year.
>> > > > It uses a table called PlayerTransactions that increases by 40,000
>> > > > records each week.  At week 13, it has 444,521 rows.  It also uses a
>> > > > table called Ftransactions which has approximately the same number
>of
>> > > > rows.  The other five tables all have less than 30,000 rows, and the
>> > > > size of these tables remains the same throughout the year.  After a
>> > > > certain number of weeks, this sp will take much longer.  In week 13,
>> > > > it was taking about 9 seconds.  What is strange is that if a run the
>> > > > sp in the sql query analyzer, it takes under a second.  If I run it
>on
>> > > > an .asp page, it takes 9 seconds.  If I run the command
>"sp_recompile
>> > > > spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
>> > > > page, it takes under a second and the problem is solved.  Here are
>my
>> > > > questions:

>> > > > 1) Does running the sp in the sql query analyzer uses a different
>> > > > execution plan than the one that is running from the .asp page?
>This
>> > > > seems to be the case.  Why would this be?
>> > > > 2) Why do I have to run the sp_recompile command in order for the
>> > > > sp to run faster?  Is it because the sp is using an outdated query
>> > > > plan?  If so, would you suggest I run the sp_recompile once a week
>or
>> > > > is there a better way?  What controls when and if SQL Server creates
>a
>> > > > new query plan without me using the sp_recompile command?



Mon, 24 May 2004 05:47:37 GMT
 recompile problem
James,

There has been a lot of good information provided on this issue, the only
true way to determine if the stored procedure is not in cache when run from
the ASP page is to trace with SQL Profiler and look for a recomple event
after the sproc has been called from the ASP page.

Please see Q243586 Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586

There can be many factors related to perfomance when compairing execution
from Application menthods to Query Analyzer.  If this does not help resolve
this issue then I would suggest opening a case with MS PSS for further
assistance.

Hope this helps,
Robert Ware
=================================================
*  This posting is provided "AS IS" with no warranties, and confers no
rights.
*  Please do not send email directly to this alias.Respond to newsgroup

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit,
please visit http://www.microsoft.com/security.

--------------------

Quote:

>Subject: Re: recompile problem
>Date: Wed, 05 Dec 2001 15:46:40 -0600
>Newsgroups: microsoft.public.sqlserver.server

>Thanks for the response, but it does not explain it.  It seems the
>query plan in cache is inefficient.  When I run the sp from an asp
>page, it uses the query plan in the cache and it runs slow.  When I
>run the sp in SQL Query Analyzer, it does not seem to be using the
>query plan in the cache and it runs fast.  Would it be true to say
>that sp's run in the SQL Query Analyzer are always recompiled before
>they are run?



>>James,

>>For  a fair judgement, try with DBCC FREEPROCCACHE
>>Refer booksonline for details.

>>Dinesh.



>>> There seems to be a strange problem with SQL Server 2000.  An example
>>> will describe it best.  There is a stored procedure called
>>> spGetPlayerTrans.  This procedure returns 300 rows.  This sp uses 7
>>> tables.  This sp runs in under a second at the beginning of the year.
>>> It uses a table called PlayerTransactions that increases by 40,000
>>> records each week.  At week 13, it has 444,521 rows.  It also uses a
>>> table called Ftransactions which has approximately the same number of
>>> rows.  The other five tables all have less than 30,000 rows, and the
>>> size of these tables remains the same throughout the year.  After a
>>> certain number of weeks, this sp will take much longer.  In week 13,
>>> it was taking about 9 seconds.  What is strange is that if a run the
>>> sp in the sql query analyzer, it takes under a second.  If I run it on
>>> an .asp page, it takes 9 seconds.  If I run the command "sp_recompile
>>> spGetPlayerTrans" in sql query analyzer, and then run it on an .asp
>>> page, it takes under a second and the problem is solved.  Here are my
>>> questions:

>>> 1) Does running the sp in the sql query analyzer uses a different
>>> execution plan than the one that is running from the .asp page?  This
>>> seems to be the case.  Why would this be?
>>> 2) Why do I have to run the sp_recompile command in order for the
>>> sp to run faster?  Is it because the sp is using an outdated query
>>> plan?  If so, would you suggest I run the sp_recompile once a week or
>>> is there a better way?  What controls when and if SQL Server creates a
>>> new query plan without me using the sp_recompile command?



Sat, 29 May 2004 00:29:24 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Recompile problem

2. Stored Procedures and Recompile Problem!

3. stored procedure recompile problem

4. Stored procedure recompile problem!

5. WITH RECOMPILE does not recompile???

6. to recompile or not recompile postgres

7. Problems recompiling OCI apps v 7 to v8

8. Problem with SPS that use Cursors (must recompile ...)

9. 'with recompile' problems

10. How recompile SP to avoid restoring problems?

11. Recompiling solves the problem?

12. Recompiling without executing stored procedure


 
Powered by phpBB® Forum Software