Home > Performance, SQL Server > My old mate sp_recompile

My old mate sp_recompile

October 12, 2007 Leave a comment Go to comments

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 Open-mouthed smile

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

Advertisements
Categories: Performance, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: