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: 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 too 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
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'sp_cacheobjects')
    DROP VIEW sp_cacheobjects;
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)

            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!


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



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.



January 15, 2014 4:53 PM

king said:


January 16, 2018 11:36 PM

king said:



January 16, 2018 11:36 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:44 PM

shenyuhang said:


June 1, 2018 7:05 PM

dongdong8 said:



June 29, 2018 2:51 AM

kesehatan said:

July 4, 2018 10:58 PM

kesehatan said:

July 5, 2018 8:36 PM

kesehatan said:

July 9, 2018 12:43 AM

obat kanker payudara terbaik said:

July 10, 2018 8:52 PM

obat meningkatkan kecerdasan otak said:

July 13, 2018 9:44 PM

obat keputihan berlebihan dan berbau said:

July 15, 2018 8:52 PM

linying123 said:



July 16, 2018 8:35 PM

dongdong8 said:



July 23, 2018 11:31 PM

shenyuhang said:


July 23, 2018 11:39 PM

obat diabetes said:

August 1, 2018 11:17 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:49 PM

qqq said:

August 16, 2018 12:16 AM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:59 PM

chenjinyan said:


August 22, 2018 11:06 PM

shenyuhang said:


August 23, 2018 10:19 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:58 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:21 AM

obat benjolan di tubuh said:

August 30, 2018 7:40 PM

obat wasir said:

September 4, 2018 8:26 PM

obat katarak said:

September 16, 2018 5:46 PM

obat maag said:

September 17, 2018 7:10 PM

obat limfoma said:


September 23, 2018 6:50 PM

xiaojun said:

20180928 junda

September 27, 2018 10:57 PM

kakakaoo said:

October 8, 2018 2:29 AM

kakakaoo said:


November 8, 2018 2:07 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement