Running ALTER on stored procedure removes delay

Every so often we have problems running several stored procedures on our database. Specifically, our applications begin timing out. We have been able to fix the problem everytime by running an ALTER on our stored procedures. Note that the we don't change anything in the tsql, we just run ALTER. Example:
If we created the SP using:
CREATE PROCEDURE [dbo].[Test]
AS
SELECT *
FROM TABLES

running

ALTER PROCEDURE [dbo].[Test]
AS
SELECT *
FROM TABLES

will fix our timeout problem. What could running the ALTER do which which could get rid of timeout issues?

It's recompiling, or marking the procedure for re-compile.

Are there a lot of data changes on the underlying tables?

Are you keeping the stats of the tables current?

|||

hello,

when you execute a stored procedure you must write a particular thing i.e.

you must give a command in your query window for using your database.

for example,

if the database name is XYZ the

use xyz

exec test

|||

I figured it was recompiling it, but how would that effect the speed of the procedure?

The underlying tables are altered quite a bit. How do I know if the stats are being updated?

|||

If you have large amounts of data modifications then the execution plan for the SP can become out-dated.

When you recompile it a new plan is constructed based on the current information.

You can view the last update date using this:

Code Snippet

SELECT'Index Name'= i.name,'Statistics Date'=STATS_DATE(i.object_id, i.index_id)

FROMsys.objects o

JOINsys.indexes i ON o.name ='Address'AND o.object_id= i.object_id;

And you can refresh, and keep the statistics current, using the UPDATE STATISTICS statement.

BOL can give you the parameters and options.

|||How do you do that in SQL 2000? We're in the midsts of updating, but we're not quite there yet.|||

Code Snippet

SELECT'Index Name'= i.name,'Statistics Date'=STATS_DATE(i.id, i.indid)

FROMsysobjects o

JOINsysindexes i ON o.name ='Address'AND o.id = i.id;

|||If you are making major changes to the underlying structure on a continuing basis, you need to set the "RECOMPILE" option so the execution plan can be updated. Use this:

CREATE PROC name WITH RECOMPILE
AS
....

|||What do you mean by "changes to the underlying structure"? We're not changing the number of columns or adding and removing tables or indexes. Just continually adding data.|||Changing data changes the statistics on the indexes and could cause the stored execution plan to pick a "non-optimal" index. Either doing what you have been doing with "ALTER PROC" or using the "WITH RECOMPILE" will cause it to recreate the execution plan every time the proc is run, and should pick and optimal path.
sql