THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 ".

 

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:

stub

 

Have fun!

~Kalen

 

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

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement