• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Founder

    This is journey of a SQL DBA. I now work as a Sr. Database Architect and Solution Consultant, generally in performance tuning, clustering or high availability. The opinions expressed herein are my own personal opinions through real time experience.

    Amit Vaid

  • Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: