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.

SYNTAX
DBCC MEMORYSTATUS


EXAMPLE
–Clean up the display
SET NOCOUNT ON

–Use the master database
USE master

–Show memory usage
DBCC MEMORYSTATUS
GO

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.

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |