DBCC memory statements.

By: Albert
Ver: 0.1
Date: 30/06/2015
Remark: Some examples of useful DBCC memory related statements.

1. DBCC FREEPROCCACHE()

The statement can be used to clear the plan cache of the entire Instance, or to clear a specific plan (as of SQL2008).
The "plan cache" consists of the pre-calculated access methods to execute queries and stored procedures.
If you clear the whole cache, SQL Server will calculate those access methods again, whenever for example, a stored procedure
is called again. This might impact performance a litle bit, but generally speaking it should not hurt too much.

Why would you use it? In some cases, really badly programmed applications fill up your cache, with ineffective plans.
In very rare cases, it seems better "to start all over again", but you cannot reboot SQL Server.

In other cases, a too large part of the cache might be used for "single use" plans, for code that was once used, but then
was hardly called anymore. There are algolrithms in place to remove old entries, but you might need to speed it up.

Or, as another example, one stored procedure really behaves "crazy", and you want SQL Server to recalculate it again.

Example 1: clear all plans from the Instance:

DBCC FREEPROCCACHE()

Example 2: clear a specific plan (as of SQL2008):

Here is a good example from Microsoft's Technet.

USE AdventureWorks2008R2;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO
result set:

plan_handle.........................................text
-------------------------------------------------- -----------------------------
0x060006001ECA270EC0215D05000000000000000000000000 SELECT * FROM Person.Address;

-- Remove that specific plan from the cache:

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

GO

Full syntax:

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

So, as you can see, 4 "regions" (so to speak) can be cleared: (1) the entire cache, (2) or just one plan handle, (3) but a sql handle
can be a parameter too (and it works similar as a plan handle), and (4) you can clear a certain "pool" using it's "pool name",
and more on pools in section 2.

You can also clear the "plan cache" associated with a specific database, using:

DBCC FLUSHPROCINDB(db_id)

Where the "db_id" is the "database_id". You can simply see all database names, and their id's, using
"SELECT name, database_id FROM sys.databases".
Ofcourse, you can use the function "db_id('databasename')" as well.

2. DBCC MEMORYSTATUS()

This statement delivers you a wealth of detailed memory status information.
It's certainly not easy to interpret all listed items here, but if you take time to study the output,
some parts should be fairly reckognizable.

The output starts with some general memory listings. Next, listings of "MEMORY_CLERKs" follow.
Then, all sorts of listings appear with regards to "pools" and "caches". Etc.. etc..
Actually, it is so much info, that it's useless to highlight some points from the output.
This typically is such a thing where it holds that "you must see it for yourself..."

Example:

DBCC MEMORYSTATUS()

3. DBCC FREESYSTEMCACHE()

You might think that this "looks" a lot like "DBCC FREEPROCCACHE()", but that one deals with cached "plans",
while here we have caches of objects related to a certain database, or the whole instance (not the buffer cache).
For example, we have a "connection pool", or an "object metadata cache", and lots of other systemcaches.
Also, memory is reserved for "result sets" (from queries and the like) as well.

You can clear memory of systemcaches associated with a certain database, or even Instance wide.

You might run such statements when you find in the SQL Server log that you are extremely low on memory.

Examples:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESYSTEMCACHE ('sales') -- sales database
DBCC FREESYSTEMCACHE ('tempdb')
DBCC FREESYSTEMCACHE ('SQL plans')

4. DBCC DROPCLEANBUFFERS()

The checkpoint process should write "dirty" pages (modified pages) to the database files.
In a way, after that, you may call them "clean".

I myself have not seen much results of the command, but others do report some benefits.

So, in case of severe memory pressure, you might take the following actions:

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS()
GO

It's interesting to see how Microsoft formulates the use of this command (from technet):

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache.
This forces all dirty pages for the current database to be written to disk and cleans the buffers.
After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.