My old mate sp_recompile
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 
And on a related note, have you come across sp_refreshview? No? Well, its worth knowing about.
