The Plan Cache
It’s important to understand that the plan cache in SQL Server 2005 is not actually a separate area of memory. Releases prior to SQL Server 7 had two effective configuration values to control the size of the plan cache, which was then called the procedure cache. One specified a fixed size for SQL Server’s total usable memory; the other specified a percentage of that memory (after fixed needs were satisfied) to be used exclusively for storing procedure plans. Also, in releases prior to SQL Server 7, query plans for adhoc SQL statements were never stored in the cache, only the plans for stored procedures. That is why it was called procedure cache in older versions. In SQL Server 2005, the total size of memory is by default dynamic, and the space used for query plans is also very fluid.
Plan Cache Metadata
In the first part of this chapter, we’ll explore the different mechanisms by which a plan can be reused, and to observe this plan reuse (or nonreuse) we only need to look at a couple of different metadata objects. There are actually about a dozen different metadata views and functions that give us information about the contents of plan cache, and that doesn’t include the metadata that gives us information about memory usage by plan cache. Later in the chapter, we’ll look at more details available in the plan cache metadata, but for now we’ll be using just one view and one function. The view is sys.dm_exec_cached_plans, which contains one row for each plan in cache, and we’ll look at the columns usecounts, cacheobjtype, and objtype. The value in usecounts will allow us to see how many times a plan has been reused. The possible values for cacheobjtype and objtype will be described in the next section. We’ll also use the value in the column plan_handle as the parameter when we use the new SQL Server 2005 CROSS APPLY capability to join the sys.dm_exec_cached_plans view with the table-valued function (TVF) sys.dm_exec_sql_text. This is the query we’ll use, which we’ll refer to as the usecount query:
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND [text] NOT LIKE '%dm_exec_cached_plans%';
Clearing Plan Cache
Because SQL Server 2005 has the potential to cache almost every query, the number of plans in cache can become quite large. There is a very efficient mechanism, described later in the chapter, for finding a plan in cache. There is not a direct performance penalty for having lots of cached plans, aside from the memory usage. However, if you have many very similar queries, the lookup time for SQL Server to find the right plan can sometimes be excessive. In addition, from a testing and troubleshooting standpoint, having lots of plans to look at can sometimes make it difficult to find just the plan in which we’re currently interested. SQL Server provides a mechanism for clearing out all the plans in cache, and you will probably want to do that occasionally on your test servers to keep the cache size manageable and easy to examine. You can use either of the following commands:
DBCC FREEPROCCACHE
This command removes all cached plans from memory
DBCC FLUSHPROCINDB (<dbid>)<
This command allows you to specify a particular database id, and then clears all plans from that particular database. Note that the usecount query that we’ll use in this section does not return database id information, but the sys.dm_exec_sql_text TVF has that information available, so dbid could be added to the usecount query.
It is, of course, recommended that you don’t use these commands on your production servers, as it could impact the performance of your running applications. Usually, you want to keep plans in cache.
© Microsoft. All Rights Reserved.