Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Clear out the procedure cache
From Wiki
If you have a lot of ad hoc queries with changing values or dynamic SQL that doesn't use sp_executesql with proper parameters then your procedure cache can become bloated with plans that are only used once and less RAM will be available for data. If this is the case then you can empty the procedure cash by executing the following command
- DBCC FREEPROCCACHE
Before doing that make sure that you are suffering from procedure cache bloat because all your plans will be gone To see how big you procedure cache is you can execute the following command on SQL Server 2000, 2005 and 2008
- DBCC PROCCACHE
DBCC FREEPROCCACHE returns the following info
| Column name | Description |
| num proc buffs | Number of possible stored procedures that could be in the procedure cache. |
| num proc buffs used | Number of cache slots holding stored procedures. |
| num proc buffs active | Number of cache slots holding stored procedures that are currently executing. |
| proc cache size | Total size of the procedure cache |
| proc cache used | Amount of the procedure cache holding stored procedures |
| proc cache active | Amount of the procedure cache holding stored procedures that are currently executing |
On SQL Server 2005 and 2008 you can use the sys.dm_exec_cached_plans dynamic management view to see what is in the procedure cache
- SELECT * FROM sys.dm_exec_cached_plans
This dmv returns the following information
| Column name | Data type | Description |
| bucketid | int |
ID of the hash bucket in which the entry is cached. The value indicates a range from 0 through the hash table size for the type of cache. For the SQL Plans and Object Plans caches, the hash table size can be up to 10007 on 32-bit systems and up to 40009 on 64-bit systems. For the Bound Trees cache, the hash table size can be up to 1009 on 32-bit systems and up to 4001 on 64-bit systems. For the Extended Stored Procedures cache the hash table size can be up to 127 on 32-bit and 64-bit systems. For more information about cache types and hash tables, see sys.dm_os_memory_cache_hash_tables (Transact-SQL). |
| refcounts | int |
Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache. |
| usecounts | int |
Number of times this cache object has been used since its inception. |
| size_in_bytes | int |
Number of bytes consumed by the cache object. |
| memory_object_address | varbinary(8) |
Memory address of the cached entry. This value can be used with sys.dm_os_memory_objects to get the memory breakdown of the cached plan and with sys.dm_os_memory_cache_entries_entries to obtain the cost of caching the entry. |
| cacheobjtype | nvarchar(34) |
Type of object in the cache. The value can be one of the following: Compiled Plan Compiled Plan Stub Parse Tree Extended Proc CLR Compiled Func CLR Compiled Proc |
| objtype | nvarchar(16) |
Type of object. The value can be one of the following: Value Description Proc Stored procedure Prepared Prepared statement Adhoc Ad hoc query1 ReplProc Replication-filter-procedure Trigger Trigger View View Default Default UsrTab User table SysTab System table Check CHECK constraint Rule Rule |
| plan_handle | varbinary(64) |
Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the following dynamic management functions: sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_plan_attributes |
| pool_id | int |
The ID of the resource pool against which this plan memory usage is accounted for. |
If you need more detail about the plans then you can use the following query, this query will also return the text of the plan itself
- SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
- q.*,
- cp.plan_handle
- FROM
- sys.dm_exec_cached_plans cp
- cross apply sys.dm_exec_query_plan(cp.plan_handle) p
- cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
If you want to know how to write code that won't bloat the procedure cache read the following blog post: Changing exec to sp_executesql doesn't provide any benefit if you are not using parameters correctly
Contributed by: --SQLDenis 20:01, 11 June 2009 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.