CODEWITHSUNDEEP
.netsql-servercachingstored-procedures
learnerplates
learnerplates

Reputation: 4387

When does a SQL Server stored procedure cache?

(As a .Net developer and not a DBA).

I've read many places that SQL caches Stored Procedures, or more accurately caches the execution plan (by execution plan I presume this is a precompiled version of the SP).

When does the SP execution plan get replaced?

e.g. I call and SP with a certain parameter. I change the SP code. Again I call the SP with a certain parameter.

Is the new SP cached when first called? If I change the value of the parameter is the cached execution plan used?

Please do not give me a link to another SQL article written for DBAs.

Upvotes: 2

Views: 4006

Answers (1)

Bert Wagner
Bert Wagner

Reputation: 881

The plan cache is generated any time one does not exist. This means the first time you create and run a SP, a cache will be created.

If you change the value of a parameter you are submitting to the SP, this will not update the cache. The whole point of the cached plan is so that it doesn't have to be regenerated every time the SP is called. This can cause problems with parameter sniffing however.

You can force a SP's plan to get regenerated by running:

EXEC sp_recompile PROCEDURENAME

All of this information (and much more) can be found here: Kimberly Tripp discusses cached plans .

Upvotes: 2

Related Questions