Microsoft SQL Server 2012 Internals

Objects in plan cache: the big picture

In addition to the DMVs discussed so far, another metadata object called syscacheobjects really is just a pseudotable. No DMVs existed prior to SQL Server 2005, but SQL Server did have about half a dozen pseudotables, including sysprocesses and syslockinfo, that took no space on disk and were materialized only when someone executed a query to access them, similarly to the way that DMVs work. These objects are still available in SQL Server 2012. In SQL Server 2000, the pseudotables were available only in the master database, or by using a full object qualification when referencing them. In SQL Server 2012, you can access syscacheobjects from any database using only the sys schema as a qualification. Table 12-4 ...