Important DBCC Commands for Memory

SQL Server includes DBCC statements for checking the status of memory allocations and clearing memory cache as required.

[DBCC MEMORYSTATUS] works with SQL Server 2000 as well as SQL Server 2005. This statement takes no parameters and produces a comprehensive report of memory allocations.

[DBCC FREEPROCCACHE] clears the procedure cache. This statement takes no parameters and returns no results. It works with both SQL Server 2000 and SQL Server 2005.

[DBCC FREESYSTEMCACHE] removes all unused entries from all caches. This statement is available only with SQL Server 2005; it returns no output.

We pass (‘ALL’) keyword to DBCC FREESYSTEMCACHE to ensure that we want to free all caches. If you don’t specify ‘ALL’ the statement will fail. Optionally you can suppress informational message of this statement by specifying NO_INFOMSGS. There is one more option is MARK_IN_USE_FOR_REMOVAL; this option will remove entries from caches when they are not being used.

[DBCC FREESESSIONCACHE] is new command available with SQL Server 2005. It removes distributed query connection cache used against the current instance of SQL Server. This is executed without any parameter.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: