Plan Caching in SQL Server 2008
In this paper, Greg explains the inner workings of plan caching in SQL Server 2008, including best practices for reducing recompilations and plan cache pollution ... Source : Greg Low - Technical Article - MSDN.com
Resource_semaphore_query_compile
It looks like Jason and I have shared the same nightmare. In this post, he explores the possible causes of the infamous Resource_Semaphore_Query_Compile error ... Source : Jason Massie - jasonmassie.com
Find the most expensive operations in Execution plans
As Mladen points out, analyzing very large execution plans can be difficult and time consuming. In this post, he shows us a method for quickly finding the most expensive sub-statements within the plan ... Source : Mladen Prajdic - SQLTeam.com
Query Optimizer Tricks
The execution plan chosen for a query depends upon a number of things including the volume of data present. In this post, Ben demonstrates a method for tricking the optimizer into thinking there's a lot more data present than there really is, which is very useful in performance testing exercises ... Source : Ben Nevarez - SQLBlog.com
Optimize for Unknown
Parameterized queries enable many benefits including plan reuse, however, the one downside is the possibility of the first execution creating a plan which leads to subsequent executions performing poorly. One of the options for addressing this issue is the OPTIMIZE FOR UNKNOWN option, covered in this blog post ... Source : SQL ISV Program Management Team - Blog Post
Plan Guides
Plan guides are useful in situations where the source query cannot be changed e.g.; a 3rd party application, but control over the execution plan is required. In this post, Sergey walks us through plan guides in both SQL Server 2005 and 2008 ... Source : Sergey Pustovit - SQL Server Support Blog
Diagnosing Plan Cache Related Performance Problems
In this post, Plan Cache problems are diagnosed using input from 3 sources; sys.dm_os_wait_stats, Performance Monitor counters and dbcc sqlperf(spinlockstats) ... Source : sangeethashekar - SQL Programmability & API Development Team Blog - Blog Post
Procedure Cache usage in 64Bit Systems
One of the great things about 64 bit systems is that the procedure cache has access to the full amount of memory, unlike 32 bit systems where AWE only provides access to memory above 4GB to the data cache. Whilst this is a big improvement, certain (bad) applications that do not parameterize their queries can overwhelm the cache with query plans that will never be reused limiting the effectiveness of the cache for data access. In this post, Lara looks at some of the ways we can tame such applications, including Forced Parameterization, Plan Guides and DBCC free proc cache ... Source : Lara Rubbelke - SQLBlog.com - Blog Post
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
This paper explains how batches are cached and reused in SQL Server 2005, and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations. ... Source : Microsoft.com - Technet Article
Managing the Procedure Cache of Adhoc SQL
SQL Server 2008 introduces the "Optimize for Adhoc Workload" option which greatly assists in reducing Procedure Cache bloat from Adhoc SQL. In this post, Maciej introduces another option in this regard; the ability to clear the "bad" cache whilst retaining the "good" cache i.e.; disposing of Adhoc plans and keeping Stored Proc Plans. Great Stuff ... Source : Maciej - sqlblogcasts.com - Blog Post
Optimize for ad hoc workloads
In this post, Adam covers a new option in SQL Server 2008 called "Optimize for Ad Hoc Workloads" that enables the procedure cache to be used more effectively for applications with lots of ad hoc sql and little parameterization ... Source : Adam Machanic - SQLBlog.com - Blog Post
Parameterized queries and procedure cache
Perhaps the best blog post I've seen on the effect of non parameterized sql on the procedure cache. Tony uses an example that exhausts the procedure cache, and rewrites it using parameterization. He also shows how the procedure cache can be inspected to see the level of usage. Great stuff ... Source : Tony Rogerson - SQLBlogcasts.com - Blog Post
Procedure Cache Size
At what point does SQL Server decide that the Plan Cache is getting too large? As this post points out, it depends on the version of SQL Server ... Source : WesleyB - sqlug.be
Plan Cache Sizing Q & A
In this post, Kalen answers questions originating from her SQL Server Magazine article on Plan Cache Sizing. How to calculate target memory and determining plan cache pressure limits ... Source : Kalen Delaney - SQLblog.com - Blog Post
Ad Hoc vs. Parameterized
Linchi blogs about SQL Server's Forced Parameterization setting and its effects on transactions/sec ... Source : Linchi Shea - SQLblog.com - Blog Post
SP2 does NOT limit the amount of plan cache you can have
Great blog post from Kalen covering the plan cache hard limit (or lack of) depending on the version of SQL Server you are running, and the differences in possible plan cache size between 32 and 64 bit versions ... Source : Kalen Delaney - SQLBlog.com - Blog Post