Using DBCC MEMUSAGE

The DBCC MEMUSAGE statement displays information about how SQL Server uses memory, and it provides a snapshot of both the data cache and the procedure cache. It is the only means of accurately determining the size of the SQL Server executable code.

The output from DBCC MEMUSAGE provides three types of information:

• How the server's memory was allocated at startup.
• How much memory is used by the 20 largest objects in the buffer cache (including "pinned" tables).
• How much memory is used by the 12 largest objects in the procedure cache-stored procedures, triggers, views, rules, and defaults. If multiple copies of an object are in the procedure cache, DBCC MEMUSAGE sums the total memory used by them. Of the multiple copies, some are precompiled versions of the object (trees), and some are compiled versions (plans). DBCC MEMUSAGE shows the sizes of both the trees and plans, and it shows the total trees and plans used to evaluate the largest objects.

For more information, see the Microsoft SQL Server Transact-SQL Reference.