Geek City: sp_cacheobjects for SQL Server 2017

Several times over the last decade, I've posted a version of a view that lists all the compiled plans in SQL Server's plan cache. It's based on the old pseudo-table syscacheobjects, returning basically the same columns with extra filters added. Over the versions, Microsoft keeps showing me more and more internal operations in the plan cache, that usually just clutter up the output, so I keep having to add filters to get rid of the unwanted rows. There are things like operations that check for filetable or fulltext activity, and as of SQL Server 2014, there are internal operations that look for activity on in-memory tables. I usually don't want to see those operations when I'm trying to find interesting plans in cache that either my users or I have run, and might need some troubleshooting. 

The view is created in the master database has a name that starts with sp_ so I can run it from anywhere.  And here is my latest iteration:

--  Create a view to show most of the same information
--       as SQL Server 2000's syscacheobjects
USE master
DROP VIEW IF EXISTS sp_cacheobjects;
-- You may want to add other filters in the WHERE clause to remove
--   other system operations on your own SQL Server
CREATE VIEW sp_cacheobjects (bucketid, cacheobjtype, objtype, 
                        usecounts, pagesused, objid, dbid, 
                        dbidexec, uid, refcounts, setopts, langid, 
                        dateformat, status, lasttime, maxexectime, 
                        avgexectime, lastreads, lastwrites, sqlbytes, 
                        sql, plan_handle) 

            SELECT  pvt.bucketid, 
              CONVERT(nvarchar(18), pvt.cacheobjtype) as cacheobjtype, 
              pvt.objtype, pvt.usecounts, 
              pvt.size_in_bytes / 8192 as size_in_bytes,
              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,
              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), plan_handle
        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 pvt.dbid between 5 and 32766
    AND text NOT LIKE '%msparam%'
    AND text not like '%xtp%'
    AND text not like '%filetable%'
    AND text not like '%fulltext%';

Over the next couple of weeks I'll show you some examples of what you can do with this view. I'll also be talking about it in my pre-con seminar and one of my regular sessions at SQLDay in Wroclaw next month. 

If you can't make it to Poland, you could tune it to my 2-day online class in June. Check it out on my events page, or go directly to Brent Ozar Unlimited for more details.


Have fun!