SQL Server and memory allocation

I often see the SQLServer process requiring more memory without releasing it due to a buggy application or to high usage, I decided to understand deeper what happens.

The memory allocated to SQL Server is divided between data cache and procedure cache. The procedure cache is used by the code objects such as stored procedures, triggers, views, defaults, and rules. SQL Server keeps the code objects in memory as long as possible. The SQL cache manager uses a Least Recently Used (LRU) algorithm to determine which objects are removed from the procedure cache first.

If you want to see a detailed accounting of how memory is being used in your SQL Server express in buffer**, run this undocumented DBCC command:
DBCC MEMORYSTATUS

If you want to see which objects are consuming memory, run this DBCC undocumented command:
DBCC MEMUSAGE
It tells you the top 20 objects in the data cache, how many pages are in cache for each one, how many pages of these are dirty*, and which database they belong to (you can also query the syscacheobjects system table)

To found the Database Name using the database ID run this query:
USE MASTER; SELECT * FROM sysdatabases where ID = <dbid> ;
To found the object using the object ID run this query:
USE <dbname>; SELECT * FROM sysobjects where ID = <objectid> ;

If you want to determine the number of pages a process currently has allocated in the cache, run this query:
SELECT SPID, MEMUSAGE FROM sysprocesses ORDER BY memusage

If you want to force all dirty pages to be written to disk, run this query:
USE <dbname>; CHECKPOINT ;

If you want to release all data pages from memory, run this query:
DBCC DROPCLEANBUFFERS;

If you want to flush the stored procedure cache and cause all stored procedures to recompile the next time they are executed, run this query:
DBCC FLUSHPROCINDB (@dbid);

*A dirty page is one which has been modified in RAM but the changes have not yet been committed to disk in the database itself. SQL Server flushes these dirty pages out of disk in an asynchronous way.

**A buffer is a page in memory that is the same size as a data or index page and is used to hold one page of data from the database. The buffer pool is managed by a process called the lazywriter, this lazywriter uses a clock algorithm to sweep through the buffer pool and free up any clean buffers in order to keep a supply of buffers empty for the next set of data pages.

Share on Google Plus

About Vittorio Pavesi