What's the procedure cache with MSSQL

  • Cache is one of the most important concept in Microsoft SQL Server.
  • How Microsoft SQL Server deals with the cache will impact the performance SQL Server.
  • The more cache, the less disk IO and the less MSSQL will have to redo some of the operations.
  • Before using a stored procedure, Microsoft SQL Server needs to compile it. By storing the compiled stored procedure in cache, MSSQL avoid the step of re-reading from the disk [avoiding disk IO], and avoiding the recompiling of the stored procedure [avoiding CPU cycles].

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Stored procedure cache

  1. SELECT SUM(single_pages_kb) AS 'Cache in Kb',
  2.        SUM(single_pages_kb) / 1024 AS 'Cache in Mb'
  3. FROM sys.dm_os_memory_cache_counters
Cache in Kb          Cache in Mb
-------------------- --------------------
4360                 4

(1 row(s) affected)
  • This is the total amount used by Microsoft SQL Server for the stored procedure cache.
  • It's very low and due to the fact that:
    1. This is a test server.
    2. There is only 512Mb RAM allocated to this server.

Stored procedure cache allocation

  1. SELECT CAST(name AS VARCHAR(28)) AS 'Object',
  2.        single_pages_kb AS 'Cache in Kb',
  3.        entries_count AS 'Num of entries'
  4. FROM sys.dm_os_memory_cache_counters
  5. WHERE type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'CACHESTORE_PHDR', 'CACHESTORE_XPROC')
  6. ORDER BY single_pages_kb DESC
Object                       Cache in Kb          Num of entries
---------------------------- -------------------- --------------------
SQL Plans                    2144                 34
Bound Trees                  344                  6
Object Plans                 216                  1
Extended Stored Procedures   32                   2

(4 row(s) affected)
  • This is used for information only.
  • Microsoft as this point does not provide a way of controlling how to configure how much memory is allocated to the cache.
  • CACHESTORE_SQLCP is the allocated to the sql plans [such as dynamic plans or previous compiled sql plans].
  • CACHESTORE_OBJCP is the allocated to the object plans [such as stored procedures, functions and triggers].
  • CACHESTORE_PHDR is the allocated to the bound trees.
  • CACHESTORE_XPROC is the allocated to the extended procedures.