Recompiling the SP solves the problem. But how?

[karthik555]

Hello,

I have been encountering this problem. But on an increased frequency off late.

A Stored Procedure which is not giving desired output. Say returns a wrong row or value in the recordset being returned.

When we take the text of the SP from the same database, using SP_Helptext and recompile it by changing CREATE PROCEDURE with ALTER PROCEDURE (NO Other change done to Code), now the SP gives desired output.

Can anyone explain what should cause this?

If there is any DB level setting that I need to do to permanently avoid this, please let me know.

Thanks in advance.

 

[NoCoolHandle]

Ok... as to WHY you are getting incorrect results..
No clue with the exception being that it seems like your proc might be using the WRONG execution plan and or getting incorrect statistics..

I would assume that you need to recompute your statistics to ensure the correct execution plan.

You have a few options..

If you expect your stroed proc is going to always need a different execution plan (due to parameters that would cause it to need to return different numbers of rows and cause any previous execution plan to be invalidated) you can Create your proc with the RECOMPLE option. THis will always cause it to build a plan for your current execution.

(Search on BOL for exact synatax as this is done via memory - sometimes flakey)
I.e.

Create proc ABC 
@x int
[red]with recompile[/red]
as
select col1 from tablex

Another choice you have it to use sp_recompile..

sp_recomple 'storedprocname'

However I would also suggest that you recompute statistics frequently (as data changes might invalidate a QUeryPlan that is already cached for your proc..) you should also defrag your indexes (by sp_reindex and or dbcc defragindex)

TO recompute statistics it is very easy and can be done even when the database is very active (defraging indexes should be done during a slow time as it can cause locks to occour and also takes up more processing power so WILL slow down your database(server))

UPDATE STATISTICS table | view , index


HTH


Rob

PS there is also an sp_updatestats that will run against all tables in a database..

 

[karthik555]

Thanks Rob....

There are over 40000 SPs in our DB. Not sure if I am going to add WITH RECOMPILE in each one of them.

I will try with UPDATE STATISTICS....

But since this problem is not simulatable, I won't know the result immediately.

Any way, thank you so much for giving these information...Will be quite useful..

Regards

Karthik

 

[NoCoolHandle]

Just for the Heck of it... (a couple of thoughts)

1. You don't want to create the proc with Recompile UNLESS you KNOW that the plan will always need to be recreated.. i.e The parameters will almost always require different execution plans.

2. a) You can execute sp_remompile against a table name also.
2. b)
You can also create a nice little proc that "loops" through your storedprocs and executes sp_recompile against all of your procs (no pain that way)

something like

  Create Proc RecompilePlans
   as
   set nocount on
   declare @pName varchar(3000) -- Just incase you have  
                                -- long descriptive names :)

   set @pName = ''
   while not @pName is null
        begin
           select @pName = min(name) from sysobjects where type = 'p' and name > @pName
           if @pName is null
               begin
                  Print 'all done'
                  return 
               end
            exec sp_recompile @pName
        end
go
exec recompileplans

Build this in your db and try executing it...

Rob