THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Reducing Cache Bloat and a Metadata Bug

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
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)
    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:



Have fun!



Published Wednesday, October 29, 2008 5:43 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



tobi said:

Why did they pick these tiny nvarchar lengths anyway? It is not as if they were saving space with that.

May 18, 2013 9:14 AM

Kalen Delaney said:

In a post about 4 1/2 years ago , I gave you my version of a replacement for the old pre-2005 pseudotable

July 31, 2013 8:31 PM

Kalen Delaney said:

In a post about 4 1/2 years ago , I gave you my version of a replacement for the old pre-2005 pseudotable

July 31, 2013 8:35 PM

obat diabetes said:

August 1, 2018 11:31 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:57 PM

obat herbal koresterol terbaik said:

August 23, 2018 11:04 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:30 AM

obat wasir said:

September 4, 2018 8:32 PM

obat katarak said:

September 16, 2018 5:52 PM

obat maag said:

September 17, 2018 7:16 PM

obat limfoma said:


September 23, 2018 6:56 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement