Procedure Cache

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

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),…
Read More