My old mate sp_recompile

Published 12 October 2007 11:33

As soon as i saw the error messages in the logs i thought to myself "Oh my, that did not happen in testing" (ok, maybe it was more colourful than that).

We were creating a clustered index on a tiny little table and the index went through fine. However, the application started to generate the message "Could not complete cursor operation because the table schema changed after the cursor was declared". My gut reaction was to restart each application server in the cluster but having restarted the first one it made no difference. It suddenly clicked that SQL Server must be dishing out the cursor plan from cache.

Now, I did not want to restart the SQL servers because only a small part of the application was affected and I did not want a more significant outage. So, how do we get the plan out of cache? The table below details your options with the corresponding impact.

Action

Pros

Cons

EXEC sp_recompile 'object'

Minimal impact. When passing a table name all procedures referencing it will be recompiled. Plans in cache not referencing the table will stay in cache.

You have to know the name of the object(s) needing to be recompiled.

DBCC FREEPROCCACHE

Quick and dirty.

The procedure cache for the server is cleared so the server will slow down whilst the cache populates again.

Restart SQL

I suppose you could say you are 100% sure you have nailed the sucker.

You have a system outage and you have to wait for your procedure and buffer cache to repopulate.

 

The lesson to take away here is to always use sp_recompile when making any kind of DDL changes, i also tend to use it on stored procs & views too. I normally always have it in my scripts so believe you me i gave myself a good talking to about forgetting to put it in this time Big Smile.

And on a related note, have you come across sp_refreshview? No? Well, its worth knowing about.

by ACALVETT
Filed under: , , ,

Comments

# DamianMulvena said on 16 October 2007 10:44

I thought the recompile was automatic when you changed an object or index. When is that not the case?

# ACALVETT said on 16 October 2007 13:57

Hi Damian,

Below is an extract for "Recompiling a Stored Procedure" in BOL.

It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).

So basically, recompile never happens for index changes. They do for tables (which i do not make clear in my post) but i would still run recompile on tables.