Objects in Plan Cache: The Big Picture

In addition to the Dynamic Management Views and Functions discussed so far, there is another metadata object called syscacheobjects that is really just a pseudotable. Prior to SQL Server 2005, there were no Dynamic Management Objects, but we did have about half a dozen of these pseudotables, including sysprocesses and syslockinfo that took no space on disk and were materialized only when someone executed a query to access them, in a similar manner to the way that Dynamic Management Objects work. These objects are still available in SQL Server 2005. In SQL Server 2000, the pseudotables are available only in the master database, or by using a full object qualification when referencing them. In SQL Server 2005, you can access syscacheobjects from any database using only the sys schema as a qualification, so we will refer to the object using its schema. Table 5-4 lists some of the more useful columns in the sys.syscacheobjects object.

In SQL Server 2000, the syscacheobjects pseudotable also includes entries for executable plans. That is, the cacheobjtype column could have a value of Executable Plan. In SQL Server 2005, since executable plans are considered dependent objects and are stored completely separately from the compiled plans, they are no longer available through the sys.syscacheobjects view. To access the executable plans, you need to select directly from the sys.dm_exec_ cached_plan_dependent_objects function, and pass in a plan_handle as a parameter.

As an alternative to the sys.syscacheobjects view, which is a compatability view and is not guaranteed to exist in future versions, you can create your own view that retrieves the same information from the SQL Server 2005 Dynamic Management Objects. The script creates a view called sp_cacheobjects in the master database. Remember that any objects with a name starting with sp_, created in the master database, can be accessed from any database without having to fully qualify the object name. Besides being able to access the sp_cacheobjects view from anywhere, another benefit of creating your own object is that you can customize it. For example, it would be relatively straightforward to do one more OUTER APPLY, to join this view with the sys.dm_exec_query_plan function, to get the XML plan for each of the plans in cache.

Table 5-4 Useful Columns in the sys.syscacheobjects View

USE master 
GO
CREATE VIEW sp_cacheobjects
(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, 
refcounts, usecounts, pagesused, setopts, langid, dateformat, 
status, lasttime, maxexectime, avgexectime, lastreads, 
lastwrites, sqlbytes, sql)
AS
SELECT pvt.bucketid,
CONVERT(nvarchar(17), pvt.cacheobjtype) AS cacheobjtype, 
pvt.objtype, 
CONVERT(int, pvt.objectid) AS object_id, 
CONVERT(smallint, pvt.dbid) AS dbid, 
CONVERT(smallint, pvt.dbid_execute) AS execute_dbid, 
CONVERT(smallint, pvt.user_id) AS user_id, 
pvt.refcounts, pvt.usecounts,
pvt.size_in_bytes / 8192 AS size_in_bytes,
CONVERT(int, pvt.set_options) AS setopts, 
CONVERT(smallint, pvt.language_id) AS langid, 
CONVERT(smallint, pvt.date_format) AS date_format, 
CONVERT(int, pvt.status) AS status, 
CONVERT(bigint, 0), 
CONVERT(bigint, 0), 
CONVERT(bigint, 0), 
CONVERT(bigint, 0), 
CONVERT(bigint, 0), 
CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2), 
CONVERT(nvarchar(3900), fgs.text)
FROM (SELECT ecp.*, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans ecp
OUTER APPLY 
   sys.dm_exec_plan_attributes(ecp.plan_handle) epa) AS ecpa
PIVOT (MAX(ecpa.value) for ecpa.attribute IN
(“set_options”, “objectid”, “dbid”, 
“dbid_execute”, “user_id”, “language_id”, 
“date_format”, “status”)) AS pvt
OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs

You might notice that several of the output columns are hardcoded to a value of 0. For the most part, these are columns for data that is no longer maintained in SQL Server 2005. In particular, these are columns that report on performance information for the cached plans. In SQL Server 2000, this performance data was maintained for each batch. In SQL Server 2005, it is maintained on a statement level and available through sys.dm_exec_query_stats. To be compatible with the sys.syscacheobjects view, the new view here returns something in those column positions. If you choose to customize this view, you could choose to remove those columns.

< Back      Next >

© Microsoft. All Rights Reserved.