0

I'm looking at the Plan Cache in SQL Server. I've got a simple stored procedure:

CREATE PROC [dbo].[CountDescriptions](@Description varchar(1))
AS
    SELECT ID 
    FROM MyTable 
    WHERE Description = @Description
GO

If I execute it and immediately check the cache it's generally there, but it seems to expire very quickly and get removed.

This is making it a bit of a pain to play with. Is there a way to artificially change a plan's details to stop the garbage collector (not sure if that's the right word for it) from deleting it so quickly?

Maybe increasing it's cost to compile or something?

Improve this question
0

1 Answer 1

Reset to default
0

SQL Server doesn't evict plans from the cache unless there's some sort of memory pressure, generally speaking. Have a look here for more information:

Plan Cache Internals

This is a little out of date, but the same principles apply.

Improve this answer
2
  • 2
    Plans get evicted for a myriad of reasons. In my experience, memory pressure is one of the absolute least common reasons. Stats updates and index rebuilds are by far the most common, unless you are running a server with an incredibly small amount of memory. The query in the example would probably be considered TRIVIAL and not even get optimized.
    – Kris Gruttemeyer
    Commented Oct 19, 2016 at 0:19
  • Absolutely true. I was assuming that he wasn't doing those things in the meantime, but possible.
    – Joel
    Commented Oct 19, 2016 at 0:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.