SQL Server Procedure Cache

Question

If you run multiple DBs on the same SQL Server do they all fight for Procedure Cache? What I am trying to figure out is how does SQL Server determine how long to hold onto Procedure Cache? If other DBs are consuming memory will that impact the procedure cache for a given DB on that same server?

I am finding that on some initial loads of page within our application that it is slow, but once the queries are cachced it is obviously fast. Just not sure how long SQL Server keeps procedure cache and if other DBs will impact that amount of time.

Answer 1

The caching/compiling happens end to end

• IIS will unload after 20 mins of not used by default.
• .net compilation to CLR
• SQL compilation
• loading data into memory

This is why the initial calls take some time

Generally stuff stays in cache:

• while still in use
• no memory pressure
• still valid (eg statistics updates will invalidate cached plans)

If you are concerned, add more RAM. Also note that each database will have different load patterns and SQL Server will juggle memory very well. Unless you don't have enough RAM...

Answer 2

From the documentation:

Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

This link might also be of interest to you: Most Executed Stored Procedure?