Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

All Tags » Procedure Cache   (RSS)
Tuesday, January 23, 2007 7:00 AM

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
Posted by sangeethashekar | 4 Comments
Monday, January 22, 2007 7:00 AM

3.0 Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2

3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM In SQL Server 2000 and 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
Posted by sangeethashekar | 3 Comments
Sunday, January 21, 2007 7:00 AM

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
Posted by sangeethashekar | 2 Comments
Saturday, January 20, 2007 7:00 AM

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
Posted by sangeethashekar | 1 Comments
Friday, January 19, 2007 7:00 AM

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
Posted by sangeethashekar | 1 Comments
Thursday, January 18, 2007 7:00 AM

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
Posted by sangeethashekar | 3 Comments
Wednesday, January 17, 2007 7:00 AM

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
Posted by sangeethashekar | 1 Comments
Tuesday, January 16, 2007 7:00 AM

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,
Posted by sangeethashekar | 1 Comments
Monday, January 15, 2007 7:00 AM

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.
Posted by sangeethashekar | 1 Comments
Sunday, January 14, 2007 7:00 AM

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
Posted by sangeethashekar | 1 Comments
Saturday, January 13, 2007 7:00 AM

6.0 Best Programming Practices

In this section we will outline some programming practices for efficient plan cache usage: 6.1 Client Side Parameterization of Queries If your application has repeated execution of the same query with only parameter values changing from query to another,
Posted by sangeethashekar | 6 Comments
Friday, January 12, 2007 7:00 AM

5.0 Retrieving Query Plans from Plan Cache DMV’s

P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: "" } TABLE.MsoNormalTable { FONT-SIZE: 10pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: "" } The compiled plan is generated for the entire batch
Posted by sangeethashekar | 0 Comments
Thursday, January 11, 2007 7:00 AM

4.0 Query Parameterization

Using parameters or parameter markers in queries increases the ability of SQL Server to reuse compiled plans. There are two places where parameterization of queries can be done: on the client side application (or mid tier) or on the server side. 4.1 Client
Posted by sangeethashekar | 1 Comments
Wednesday, January 10, 2007 7:00 AM

3.0 How Cache Lookups Work

Each cache store is implemented as a hash table. A hash value and cache key pair is used in order to check if the compiled plan is cached for a given query in the cache stores. The hash value of all cache objects is computed as (database_id * object_id)
Posted by sangeethashekar | 1 Comments
Tuesday, January 09, 2007 5:18 PM

2.0 Sql_Handle and Plan_Handle Explained

2.1 What is a Plan_Handle Cached compiled plans are stored in the SQLCP or the OBJCP cache stores. A cached compiled plan can be retrieved from either of these cache stores using the plan_handle of the compiled plan. The plan_handle is a hash value derived
Posted by sangeethashekar | 1 Comments
More Posts Next page »
 
Page view tracker