March Madness – SQL Server System Tables – syscacheobjects

Ever wonder how SQL Server manages objects inside of its memory? Well, wonder no more! Today I am going to help you understand more about the objects that are currently residing inside of the region of memory known as the plan cache.

As always, please note that this system table will be removed from a future version of SQL Server.

What is this table for?

This table will show the details on how your plan cache is currently being used. Go ahead and see for yourself by running the following code:

SELECT *
FROM master.dbo.syscacheobjects

But that seems to be rather ugly, so let’s focus on just a few columns that can give us some meaningful data:

SELECT CacheObjtype, UseCounts, RefCounts, ObjType, DB_NAME(dbid) as DatabaseName, SQL
FROM master.dbo.syscacheobjects
ORDER BY dbid, usecounts DESC, objtype

There, that’s better.

Why should you care?

You want to make certain that your instance of SQL is using it’s memory as efficiently as possible. Storing plans in memory allows for SQL to be efficient…most of the time. There are times when you can see the effects of “plan bloat”. One way to experience plan bloat is to have many ad-hoc queries that are being executed. These ad-hoc query plans will take up space in the plan cache, and if you have enough of them then you could see your entire plan cache filled with plans that were run once, and will likely not be run again. This is not desirable and you can read more details about the entire plan caching process here.

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? Well then, get yourself acquainted with these DMVs:

• sys.dm_exec_cached_plans
• sys.dm_exec_plan_attributes (Transact-SQL)
• sys.dm_exec_sql_text
• sys.dm_exec_cached_plan_dependent_objects

For a similar query to the one above using syscacheobjects but instead using the newer DMVs, run this:

SELECT Cacheobjtype, UseCounts, RefCounts, Objtype,
    ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid, usecounts DESC

Keeping track of the objects and overall usage of the plan cache on your server is a good idea and one I would recommend you get started on doing as soon as possible even if you are just poking around and taking a peek on a few of your boxes from time to time. I believe you will find opportunities to make some improvements in overall performance in a short amount of time.