Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Analyzing the SQL Server Plan Cache

MSSQLTips author Atif Shehzad By:   |   Read Comments (3)   |   Related Tips: More > Performance Tuning

Problem
The cache management mechanism plays an important role in performance of any system. Like any reliable DBMS, SQL Server enjoys a sophisticated cache management system for optimized performance without the need for any user intervention. There are ways to add a plan or data to the SQL Server cache or to remove a plan or data from SQL Server cache, but these techniques are only recommended for testing or troubleshooting purposes. Keeping in mind the importance of the cache mechanism, how could one get the plans and their usage statistics in SQL Server?

Solution
In releases prior to SQL Server 7.0, the plan cache was a separately configurable cache area in total memory used by SQL Server. Only stored procedures were cached in that part of cache. For this reason it was named the procedural cache. In SQL Server 7.0 and forwards the plan cache is not a separate area of memory in SQL Server. Now SQL Server uses a very dynamic integrated memory management and cache management mechanism.

The following script may be used on SQL Server 2000 and forward. It will provide contents of the SQL Server plan cache along with usage frequency.

Script # 1: Get SQL Server plan cache contents and their usage for SQL Server 2000 and onwards
USE Master
GO SELECT UseCounts, RefCounts,CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL FROM syscacheobjects ORDER BY dbid,usecounts DESC,objtype GO

Following is a partial result set from script #1 for SQL Server 2000.

For SQL Server 2005 and forward, DMVs have been introduced to get this information.  So to get the contents of the plan cache along with usage statistics, you may use the following DMV script.

Script # 2: Get SQL Server plan cache contents and their usage for SQL Server 2005 and onwards
USE master
GO SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) ORDER BY dbid,usecounts DESC; GO

Following is a partial result set from script #2  for SQL Server 2005.

The overall fields used in the above scripts along with their description are as follows:

Column Definition
UseCounts is usage count of this cached object since it was cached
RefCounts is the count of how many times other cached objects have referenced this object
CacheObjType is the type of object in the plan cache.

SQL Server 2000 may have following types of cache objects

  • Compiled Plan
  • Executable Plan
  • Parse Tree
  • Cursor Parse Tree
  • Extended Stored Procedure

SQL Server 2005 and SQL Server 2008 may have following types of cache objects

  • Compiled Plan
  • Parse Tree
  • Extended Proc
  • CLR Compiled Functions
  • CLR Compiled Procedures
ObjType is the type of object with respect to its database.

SQL Server may have the following types of database objects in the plan cache

  • Stored Procedure
  • Prepared statement
  • Ad hoc query
  • ReplProc (replication procedure)
  • Trigger
  • View
  • Default
  • User table
  • System table
  • Check
  • Rule

Note: Prior to SQL Server 2005 ad hoc query was rarely cached. Now from SQL Sever 2005 and forward the ad hoc queries will be cached although the ad hoc query is cached it requires an exact textual match to be used. Even the provided parameters of the query should be the same for reuse.

DatabaseName is the name of the database to which the cached object belongs.  For some batches this column may be NULL.
SQL is the SQL code that is stored in the plan cache.

By using the above scripts you can gain insight into the objects along with their usage frequency in SQL Server. Remember for SQL Server 2000 you need to use the system table syscacheobjects and for SQL Server 2005 and onwards the DMV sys.dm_exec_cached_plans is used primarily.

While testing or troubleshooting you may need to clear the plan cache. You can use the following two commands for this purpose.

Script # 3: Clear whole SQL Server plan cache
DBCC FREEPROCCACHE 
GO
Script #4: Clear SQL Server plan cache for a specific database
DBCC FLUSHPROCINDB (<dbid>)
GO
/*
You can get DBID through following command
Select dbid from sysdatabases where name = <'DBName'>
*/

Beside the above commands, the following operations will also flush the entire plan cache and new batches will require new plans.

  • Detaching any database
  • Upgrading any database to compatibility level 90 or above on SQL Server 2005 or above
  • Running ALTER DATABASE ... MODIFY FILEGROUP command for any database
  • Running ALTER DATABASE ... COLLATE command for modifying collation of any database

Altering a database with any of the following commands will remove all plans cached for that specific database.

  • ALTER DATABASE ..... MODIFY NAME
  • ALTER DATABASE ..... SET ONLINE
  • ALTER DATABASE ..... SET OFFLINE
  • ALTER DATABASE ..... SET EMERGENCY

The following operations will also remove cached plans for a specific database.

  • Dropping a database
  • Database is auto closed

It is relevant to mention here that there is no way to remove just a single query plan from the cache for SQL 2005 and below, but for SQL 2008 this is now possible.  Take a look at this article.

Next Steps

  • Start digging deeper into your database activity to see what plans are cached and how often queries are being used.
  • Stay tuned for other tips about performance tuning.
  • Take a look at these other performance tuning related tips.


Last Update: 1/8/2009


About the author
MSSQLTips author Atif Shehzad
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, February 20, 2009 - 1:12:38 AM - @tif Read The Tip

 Another efficient way to get Database ID of any database is to use DB_ID('DatabaseName').

It can be used in following way to get Database ID of database 'Adventureworks' in this case.

SELECT DB_ID('AdventureWorks')

GO

This function is efficient and handy than to query sysdatabases or sys.databases for this purpose.

 


Monday, March 26, 2012 - 11:16:00 PM - Srinath Read The Tip

Thanks for the awesome Article Atif.


Monday, April 02, 2012 - 10:34:26 AM - patrickmcginnis59 Read The Tip

If you use a case sensitive collation, you'll have to use the correct case, like:

 

USE master
GO
SELECT
usecounts, refcounts,cacheobjtype, objtype, DB_NAME(dbid) as DatabaseName, sql
FROM syscacheobjects
ORDER BY dbid,usecounts DESC,objtype
GO



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.