I have an intermittent problem on SQL Server 2005 64bit where the stored
procedure query will get out of sync with the data and it starts taking
a long time to return the data.

I've tried issuing sp_recompile on the stored proc and the performance
did not improve at all.

I then dropped and recreated the sproc and, magically, the performance
improved to its original state.

How is sp_recompile different from dropping and recreating a stored proc
from the standpoint of statistics.

Regards

Frank
Can you post here stored procedure's code?

1) There are new RECOMPILE hints  in SQL Server 2005
(OPTION(RECOMPILE)),OPTION(OPTIMIZE FOR ...)
2) Take a  look at execution plan
3) Add WITH RECOMPILE option when you create a stored procedure



Show quoteHide quote

I would recommend caution about WITH RECOMPILE. For a busy proc, this
can creat a big CPU overhead for SQL Server recompiling the same proc
every time it is used.


Show quoteHide quote

peterh wrote:
You are absolutely correct.  I can't afford a recompile.  This proc is
called at least 5-10 times every 5 seconds.

Hello Frank,

According to the BOL, when SQL Server 2005 recompiles stored procedures,
only the statement that caused the recompilation is compiled, rather than
the entire procedure. As a result, SQL Server uses the parameter values as
they exist in the recompiled statement when regenerating a query plan.
These values may differ from those that were originally passed into the
procedure.

So it may be that in your scenario, the parameters used by the stored
procedure when you recompile is different so the query plan is not a
optimized one.

But after you recreate the stored procedure, the parameters used in the
compilation generate a new query plan which is more opimize.

For your reference, please follow this article:

Recompiling Stored Procedures
http://msdn2.microsoft.com/en-us/library/ms190439.aspx

If you have any question, please feel free to let me know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

I think it was just a coincidence that it starting working as you wanted
after you dropped it. Both recompiling and recreating will force a new query
plan the first time it is run after that event. The plan is dependent on the
actual parameters passed in at that time. Make sure your statistics are up
to date and accurate. Also think about using the OPTIMIZE FOR hint if the
values of the parameters may be such that you get some parameters with
values that produce an incorrect plan for the othervalues.

Show quoteHide quote

Andrew J. Kelly wrote:
I do not think that it's a coincidence.  It happened once too many times.

The plans are not different for a compile vs. a recompile. I seriously doubt
that is the issue. Have a look at this white paper, especially the part
about parameter sniffing.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx



Show quoteHide quote

Question about best practices..
Rolling Back from build 3159 to build 3152
can't access server from another PC
SQL 2005 Performance
Major SQL Server 2k5 x64 configuration problem
Storing T-SQL code so it can't be read
Change only USER tables to a new user
xp_msver
Multiple database versions on same server.
SQL 2000 SP4 Backup Fails but no error?