SQL Programmability & API Development Team Blog

All posts are AS IS, without any further guarantees or warranties.

Browse by Tags

Tagged Content List
  • Blog Post: 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...
  • Blog Post: 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 the number of memory pages, both from single...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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, then parameterizing the query in the...
  • Blog Post: 5.0 Retrieving Query Plans from Plan Cache DMV’s

    The compiled plan is generated for the entire batch (batch level) while individual statements in the batch have query plans (statement level) associated with them. We demonstrated earlier how to retrieve the compiled plan, and in this section we will examine to retrieve the query plans from the plan...
  • Blog Post: 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 Side Parameterization Client side...
  • Blog Post: 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) mod (hash table size), and this specifies the hash...
  • Blog Post: 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 from the compiled plan of the entire batch...
  • Blog Post: 1.0 Structure of the Plan Cache and Types of Cached Objects

    The plan cache is comprised of 4 separate cache stores: Object Plans (CACHESTORE_OBJCP), SQL Plans (CACHESTORE_SQLCP), Bound Trees (CACHESTORE_PHDR), and Extended Stored Procedures (CACHESTORE_XPROC). Each of these four cache stores conform to a uniform caching policy with respect to costing and removal...
  • Blog Post: Plan Cache Concepts Explained

    Since the release of SQL Server 2005 there have been several questions around how plan caching has been implemented in this release and how to diagnose (and distinguish) plan cache related performance problems. In a series of blog articles we will attempt to address many of these questions. Lists of...
  • Blog Post: What are the different cached objects in the plan cache?

    Procedure cache which is also called as plan cache is primarily a cache of the query plans to improve performance of query execution. By caching the compiled and execution plans we don’t have compile query each time they are executed. This is a huge performance boost and is therefore a very critical...
  • Blog Post: Plan Guides Are Your Best Friends

    Plan guides are used to optimize the performace of a query without modifying it. They are intended for advanced users and help in situations where a query submitted through an application can not be changed. For more details on this feature please visit http://msdn2.microsoft.com/en-us/library/ms190417...
Page 1 of 1 (19 items)