4.0 Useful Queries on DMV’s to understand Plan Cache Behavior

    The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use:   select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8  / (1024.0 * 1024.0)…

4

2.0 Diagnosing Plan Cache Related Performance Problems and Suggested Solutions

  Plan cache related performance problems usually manifest in decrease in throughput (or increase in query response time), and some times out of memory errors, connection time outs. None of these symptoms however point to plan cache related performance problems specifically. In order to determine and further isolate the cause of the slowdown the following…

2

Trouble Shooting Query Performance Issues Related to Plan Cache in SQL 2005 RTM and SP1

  If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database application throughput, bloated plan cache or out of memory errors, you will need to gather machine configuration information and system performance data in order to perform a detailed analysis. Below, we will outline the steps to take…

1

12.0 Plan Cache Trace Events and Performance Counters

  12.1 Trace Events   12.1.1 Performance Statistics Trace Event   The performance statistics trace event introduced in SQL Server 2005, gives persisted plan and runtime statistics information. With the information part of the trace event data when combined with the information available through the plan cache DMV sys.dm_exec_query_stats the performance history of any query…

1

11.0 Temporary Tables, Table Variables and Recompiles

  11.1 Temporary Tables versus Table Variables   In order to determine if table variables or temporary tables is the best fit for your application, let us first examine some characteristics of table variables and temporary tables: 1.             Table variables have a scope associated with them. If a table variable is declared in a stored…

3

10.0 Plan Cache Flush

  In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query…

1

9.0 Memory Pressure Limits

  The procedure cache responds to memory pressure like all other cache stores built using the common caching framework. The eviction of cache entries under memory pressure is based on the cost of the entry (plan). When memory pressure conditions are reached, in one clock round zero cost entries are removed from the cache store…

1

8.0 Factors that Affect Batch Cache-ability

  During batch compilation we make a determination regarding the cache-ability of the batch. Each statement in the batch is evaluated either as not-cacheable, cacheable, cache even though zero cost, set cost on re-use or not-cacheable due to sensitive information. A batch is marked cacheable if at least one statement in the batch is cacheable….

1

7.0 Costing Cache Entries

  A uniform costing scheme has been implemented for all cache stores in SQL Server 2005. The cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums) and the number of memory pages, both from single and multi-page allocations. In SQL…

1