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: sp_cacheobjects for SQL Server 2012

In a post about 4 1/2 years ago, I gave you my version of a replacement for the old pre-2005 pseudotable syscacheobjects. I called it sp_cacheobjects and created it as a view in the master database. With the sp_ prefix, the view can be accessed from any database.

When testing this on SQL Server 2012, I noticed that I almost always got a lot more rows back than I was expecting. Even when I added a WHERE clause to limit the database to only the database I was working in, I STILL got way to many rows back. It turns out that in SQL Server 2012, SQL Server is frequently running background queries checking for both filetable and fulltext activites, and these background queries, that run in all databases, are cached like any other queries. To get the same kinds of results from this view that I got in SQL Server 2008, I needed to add a few extra filters, so I am including the modified version of my view here.

 

-- Create a view to show most of the same information as SQL Server 2000's syscacheobjects
-- This script has been updated for SQL Server 2012 to remove plans dealing with filetable and fulltext activities,
-- and to also not return queries from system databases.

-- by Kalen Delaney, 2012

-- Feel free to remove those filters from the WHERE clause at the bottom
USE master
GO
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'sp_cacheobjects')
    DROP VIEW sp_cacheobjects;
GO
CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec, uid, refcounts, 
                        usecounts, pagesused, setopts, langid, date_first, dateformat, status, lasttime, maxexectime, avgexectime, lastreads,
                        lastwrites, sqlbytes, sql)
AS

            SELECT            pvt.bucketid, CONVERT(nvarchar(19), 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
         WHERE cacheobjtype like 'Compiled%'
         AND text NOT LIKE '%filetable%'
         AND text NOT LIKE '%fulltext%'
         AND pvt.dbid between 5 and 32766;

 

I hope you find this useful!

~Kalen

Published Wednesday, July 31, 2013 6:31 PM by Kalen Delaney
Filed under: ,

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

 

Leif Haraldsson said:

I have also added the date_first column as this property also affects the cache objects.

One object, three cached plans

setopts langid dateformat date_first

258 0 1 1

258 0 1 2

258 0 1 7

October 26, 2013 3:27 PM
 

m mcdonald said:

If you are filtering system databases would you not also include

pvt.dbid <> 32767

Thank you

January 15, 2014 1:41 PM
 

Kalen Delaney said:

Hi Leif... good idea, thanks!

M McDonald... very strange, the version I have that I give to my students has that filter, maybe my cut and paste just stopped short!

I have updated the code.

Thanks

Kalen

January 15, 2014 4:53 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