SQL PASS Day 1: To Free or Not To Free the Proc Cache?

on November 4, 2009

Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations.

Rule of Thumb: The “it depends” answer is usually right.

Opinion 1: Never Ever Clear the Proc Cache on a Production Server

This first opinion came in a good, solid presentation on using execution plans for troubleshooting. There were some good examples of when you want sql to look at the statistics and trigger generating a new plan, and when you don’t. (AKA when parameter sniffing is a good or a bad thing.) But the speaker was wholeheartedly against clearing the proc cache in production.

While I can definitely see this being true for some systems, I have definitely seen advantages of clearing the proc cache on others (more to come below), so I already knew this was too simple an answer for me– at least until I’ve solved the problems I have with out of date statistics on frequently modified large tables.

(Thanks to Grant Fritchey for a great presentation.)

Opinion 2: Be Free, Procedure Cache, be Free!

This second opinion came in a session on using DMVs to troubleshoot performance. This session was even geared toward OLTP systems, and the speaker said he regularly frees the procedure cache on his production sql servers at a given interval. He sees slight CPU pressure after doing so, but has the benefit of being able to capture and trend exactly what procedures go into the cache using the DMVs afterward (with the benefit of clean timestamps).

So in his environment, he has no issues clearing the proc cache.

(Thanks to Dr.DMV for a great talk!)

Opinion 3: It Depends: Check the Size of Your Proc Cache, Free if You Need To (and can handle the CPU for Recompilations)

The third speaker (Maciej Pilecki) talked about looking at the total size of the proc cache, and stressed that as this cache grows, it can steal space from the buffer pool. For each system, you should look at the size of the procedure cache and the amount of execution plan reuse you are getting on the system.

There are two main performance benefits to plan reuse (whether parameterized adhoc queries or procedure queries):

Speed: (recompiling takes time and CPU resources)
Smaller proc cache / More room for buffer pool to hold data in memory

Bonus: Maciej also mentioned how the ‘Optimize for Adhoc Workload’ option in sql 2008 can help alleviate bloat of the adhoc procedure cache. When enabled, this will only cache adhoc plans on their second run– for the first run sql will just store a small record that the query was executed once.

I really enjoyed these sessions, and one of the great things about PASS is the opportunity to hear and synthesize different perspectives on these topics.

Love it!