This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view syscacheobjects returns one row for every object in the plan cache.  There are parse trees, compiled plans, ad-hoc plans, and others.  All of these pertaining to requests that have resulted in compiled plans, such as executed stored procedures and views that have been utilized.  For all of this information, the compatibility view includes size of the plan, use counts, and the SQL text, as well as other information.

The primary replacement for syscacheobjects is the dynamic management object (DMO) sys.dm_exec_cached_plans; which also returns one row for every object in the plan cache.  While this DMO covers most of the information in syscacheobjects, two others are needed as well.  These are sys.dm_exec_plan_attributes and sys.dm_exec_sql_text.

Decomposing Internal Columns

There are two sets of columns within syscacheobjectsis  that deserve special mention when looking at this compatibility view.  These are the performance related columns and then the SET options (setopts) column.

The performance columns include five columns on the view; which are lasttime, maxexectime, avgexectime, lastreads and lastwrites.  These columns could provide some useful information regarding the most recent performance for the cached object in syscacheobjects.  Unfortunately, all of these columns return a 0 in SQL Server 2012 and appear to have returned this value since SQL Server 2005.

The next column set is the setopts column; which returns information on the SET options that were used when the cached object was compiled.  The value in setopt can be unpacked using bit comparisons.  The known values with their associated SET option are:

• 0×1: ANSI_PADDING
• 0×2: Parallel Plan
• 0×4: FORCEPLAN
• 0×8: CONCAT_NULL_YIELDS_NULL
• 0×10: ANSI_WARNINGS
• 0×20: ANSI_NULLS
• 0×40: QUOTED_IDENTIFIER
• 0×80: ANSI_NULL_DFLT_ON
• 0×100: ANSI_NULL_DFLT_OFF
• 0×200: NoBrowseTable
• 0×400: TriggerOneRow
• 0×800: ResyncQuery
• 0×1000: ARITHABORT
• 0×2000: NUMERIC_ROUNDABORT
• 0×4000: DATEFIRST
• 0×8000: DATEFORMAT
• 0×1000: LanguageID
• 0×2000: UPON

Most of the SET options can be controlled through T-SQL statements.  A few, such as TriggerOneRow, are the results of other settings in the database and related cached object.

Query Via syscacheobjects

An example of a query to return the information of use from syscacheobjects is provided in Listing 1.

Listing 1 – Query for syscacheobjects
SELECT bucketid
    ,cacheobjtype
    ,objtype
    ,objid
    ,dbid
    ,dbidexec
    ,uid
    ,refcounts
    ,usecounts
    ,pagesused
    ,setopts
    ,CONVERT(INT,setopts & 0x1) / 1 AS [ANSI_PADDING]
    ,CONVERT(INT,setopts & 0x2) / 2 AS ParallelPlan
    ,CONVERT(INT,setopts & 0x4) / 4 AS [FORCEPLAN]
    ,CONVERT(INT,setopts & 0x8) / 8 AS [CONCAT_NULL_YIELDS_NULL]
    ,CONVERT(INT,setopts & 0x10) / 16 AS [ANSI_WARNINGS]
    ,CONVERT(INT,setopts & 0x20) / 32 AS [ANSI_NULLS]
    ,CONVERT(INT,setopts & 0x40) / 64 AS [QUOTED_IDENTIFIER]
    ,CONVERT(INT,setopts & 0x80) / 128 AS [ANSI_NULL_DFLT_ON]
    ,CONVERT(INT,setopts & 0x100) / 256 AS [ANSI_NULL_DFLT_OFF]
    ,CONVERT(INT,setopts & 0x200) / 512 AS NoBrowseTable
    ,CONVERT(INT,setopts & 0x400) / 1024 AS TriggerOneRow
    ,CONVERT(INT,setopts & 0x800) / 2048 AS ResyncQuery
    ,CONVERT(INT,setopts & 0x1000) / 4096 AS [ARITHABORT]
    ,CONVERT(INT,setopts & 0x1000) / 8192 AS [NUMERIC_ROUNDABORT]
    ,CONVERT(INT,setopts & 0x1000) / 16384 AS [DATEFIRST]
    ,CONVERT(INT,setopts & 0x1000) / 32768 AS [DATEFORMAT]
    ,CONVERT(INT,setopts & 0x1000) / 65536 AS LanguageID
    ,CONVERT(INT,setopts & 0x1000) / 131072 AS [UPON]
    ,langid
    ,dateformat
    ,status
    ,sqlbytes
    ,sql
FROM syscacheobjects

Query via DMOs

To re-create the same information from syscacheobjects , plus the additional columns discussed in the previous section, the query in Listing 2 can be used.

Listing 2 – Query for Cache Plans
SELECT bucketid
    ,cacheobjtype
    ,objtype
    ,x.objectid AS objid
    ,x.dbid
    ,x.dbid_execute AS dbidexec
    ,x.user_id AS uid
    ,refcounts
    ,usecounts
    ,size_in_bytes/(8*1024) AS pagesused
    ,x.set_options AS setopts
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1) / 1 AS [ANSI_PADDING]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x2) / 2 AS ParallelPlan
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x4) / 4 AS [FORCEPLAN]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x8) / 8 AS [CONCAT_NULL_YIELDS_NULL]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x10) / 16 AS [ANSI_WARNINGS]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x20) / 32 AS [ANSI_NULLS]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x40) / 64 AS [QUOTED_IDENTIFIER]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x80) / 128 AS [ANSI_NULL_DFLT_ON]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x100) / 256 AS [ANSI_NULL_DFLT_OFF]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x200) / 512 AS NoBrowseTable
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x400) / 1024 AS TriggerOneRow
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x800) / 2048 AS ResyncQuery
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 4096 AS [ARITHABORT]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 8192 AS [NUMERIC_ROUNDABORT]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 16384 AS [DATEFIRST]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 32768 AS [DATEFORMAT]
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 65536 AS LanguageID
    ,CONVERT(INT,CONVERT(INT,x.set_options) & 0x1000) / 131072 AS [UPON]
    ,x.language_id AS langid
    ,x.date_format AS dateformat
    ,x.status
    ,LEN(st.text)*2 AS sqlbytes
    ,st.text AS SQL
  ,cp.plan_handle  
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY (
        SELECT MAX(CASE WHEN attribute = 'set_options' THEN value ELSE NULL END) AS set_options
            ,MAX(CASE WHEN attribute = 'user_id' THEN value ELSE NULL END) AS user_id
            ,MAX(CASE WHEN attribute = 'objectid' THEN value ELSE NULL END) AS objectid
            ,MAX(CASE WHEN attribute = 'dbid' THEN value ELSE NULL END) AS dbid
            ,MAX(CASE WHEN attribute = 'dbid_execute' THEN value ELSE NULL END) AS dbid_execute
            ,MAX(CASE WHEN attribute = 'language_id' THEN value ELSE NULL END) AS language_id
            ,MAX(CASE WHEN attribute = 'date_format' THEN value ELSE NULL END) AS date_format
            ,MAX(CASE WHEN attribute = 'status' THEN value ELSE NULL END) AS status
        FROM sys.dm_exec_plan_attributes(cp.plan_handle)) x
    OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st

Additional DMO Information

Besides the deprecation of syscacheobjects, there are a number of other reasons to move on from the compatibility view.  Some of the reasons are:

1. Plan Handle
2. Accurate statistics
3. Full SQL text

The plan_handle, which is found in sys.dm_exec_cached_plans, provides a unique key for execution plans that can be used connect the cache objects to other DMOs to include other information.  For instance, the DMO sys.dm_exec_query_plan can be accessed to retrieve the SHOWPLAN XML for the cached object.

The information provided through the DMOs is more accurate than through the compatibility view.  For instance, including the information in sys.dm_exec_query_stats provides the execution information that has columns but is not actually provided in the compatibility view.  Also, the size of the plan is stored in bytes versus pages to return the actual size of the plan versus a 8K approximation.

Lastly, the full text of the SQL statement associated with the cached object is available.  In the compatibility view, only the first 3900 characters are included.  Through the DMOs, the entire statement is available and returned.

Summary

As noted in this post, there are a number of reasons to move on from syscacheobjects.  With the converted query against the DMOs, it is easy to get to this information and add additional useful information to the query and improve on what was already being used.

Do you see any reason to continue using syscacheobjects?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – Introduction
2. Lost in Translation – Deprecated System Tables – sysaltfiles
3. Lost in Translation – Deprecated System Tables – syscurconfigs