Geek City: Reducing Cache Bloat and a Metadata Bug : SQL Feeds

Geek City: Reducing Cache Bloat and a Metadata Bug

by Kalen Delaney via Kalen Delaney on 10/29/2008 11:43:00 PM

I've been running some tests with the new SQL Server 2008 configuration option 'optimize for ad hoc workloads' which is intended to reduce cache bloat due to holding onto plans for single use ad hoc queries. This option will allow a new type of cached object to be stored, which isn't the only plan, but merely a stub. There will be a corresponding new cacheobjtype value called "Compiled Plan Stub".

There are several ways of looking at the contents of cache. In SQL 2000, we had a pseudo table called master.dbo.syscacheobjects, and we can still access this object in SQL 2005 and 2008 by referencing the compatibility view sys.syscacheobjects (from any database). However, when I look at this view, I don't see "Compiled Plan Stub", but rather "Compiled Plan Stu ".

So I checked the definition of syscacheobjects, using the function object_definition:

USE master;
SELECT OBJECT_DEFINITION(object_id('syscacheobjects'));

and I saw that syscacheobjects was derived from a column in sys.dm_exec_cached_plans, and converted to an nvarchar(17). Unfortunately, "Compiled Plan Stub" is 18 characters long.

I'll be talking about this new cached object type in my 3-day seminar in London next week, and I'll post more information about using the 'optimize for ad hoc workloads' at a later time.

But normally, I wouldn't have run into this problem, because I have created my own view based on the new SQL 2005 metadata, including sys.dm_exec_cached_plans. Usually, I use my own view, which converts to a longer string, so I wouldn't have seen the string truncation. But I was testing this on a new instance, where I hadn't created my own view yet.

I am including my view definition, which is what I have been using since the SQL Server 2005 came out, to inspect the plan cache. In fact, I usually add a where clause to restrict the output only to Compiled Plans. Note that the view name starting with sp_ means we can access the view from any database:

-- Create a view to show most of the same information as
--   SQL Server 2000's syscacheobjects
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(20), 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

This view shows the full cached object type: