Welcome to the first Throwback Thursday blog post of  2014.  Throwback Thursday is a blog series where I dig deep into my evernote collection and find some great content on a single subject and share it with you.  In the fourth installment of the Throwback Thursday series we are going to cover the plan cache.

One of the features that is helpful for performance tuning in SQL Server is the plan cache. Many DBA’s understand that when you execute a stored procedure by default the execution plan gets cached so it can be reused. This allows CPU cycles to be saved as you don’t have to recompile stored procedures every time they are executed. What some DBA’s don’t know is that you can leverage the plan cache to get performance metrics. During this Throwback Thursday we are going to focus on some great community articles, white papers and blogs that can greatly improve your performance tuning process though using the plan cache.

Plan Caching in SQL Server 2008  – This is a great white paper by Microsoft that gives you a great introduction into what the plan cache is and how can make your performance tuning life easier.

How to Drop One Plan from Cache – A lot of DBA’s think that DBCC FREEPROCCACHE is used only to drop all the execution plans from your instance. Grant Fritchey does a great job showing how you can utilize this DBCC command to remove only a single plan.

Finding Top Offenders from Plan Cache – This is how I go about finding top offenders for frequently used execution plans. It’s not the only tool in my tool-belt for performance tuning but it can be a good starting point.

Finding Key Lookups in Plan Cache – At the end of the day, a execution plan is cached as XML. Kendal Van Dyke does a great job showing us how we can probe this XML structure to find key lookup operators inside of the plans that are in the plan cache.

SQL Server Plan Cache: Junk Drawer Your Queries – A great article by Tom LaRock that shows you how plans are stored, how to find plans with specific operators, and how to aggregate similar queries to get helpful usage statistics.

Plan cache, adhoc workloads and clearing the single-use plan cache bloat – Kimberly Tripp did a great job explaining how you can monitor and prevent adhoc workloads  from bloating your plan cache.

Can You Dig It? Plan Cache Series – I saved my personal favorite for last. Jason Strate did a great job providing an series of several  scripts that can be used for reviewing the plan cache.