Analyzing the SQL Server Plan Cache

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.

SQL Server plan cache contents for SQL Server 2000 and later

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 the script for SQL Server 2000.

SQL Server plan cache contents for SQL Server 2005 and later

In SQL Server 2005 and later there are DMVs that can be used to get this information.

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 the script for SQL Server 2005.

Columns and Definition

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

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.

Clear whole SQL Server plan cache

DBCC FREEPROCCACHE 
GO

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'>
*/

Other Ways Plan Cache Can Be Cleared

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 database compatibility level
• 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

Starting in SQL Server 2008 you can also clear individual plans from the cache. You can read more about DBCC FREEPROCCACHE and how this can be done.

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.