How to Interact with SQL Server’s Data and Procedure Cache
Showing Memory Usage
You can use the DBCC MEMORYSTATUS console command to view memory usage.
–Clean up the display
SET NOCOUNT ON
–Use the master database
–Show memory usage
Interacting with SQL Server’s cache as a programmer is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do so. Explore the use of the above statements and system tables to round out your knowledge and impress others when there is a problem with the cache and you know exactly what command or system table to use to troubleshoot the error.
For further explanation of the above commands and system tables that are documented see either SQL Server’s Books Online, or to see further explanation of all the above commands and system tables, download and purchase Transact-SQL Language Reference Guide from my website.
Published with the express written permission of the author. Copyright 2002 by the author.