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?

marc_s
  • 9,052
  • 6
  • 46
  • 52
BanksySan
  • 1,011
  • 1
  • 12
  • 16

1 Answers1

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.

Joel
  • 171
  • 3