SQL Server Memory pool(Buffer Pool) - Procedure Cache and Data Cache

-- SQL Server 7.0之前(SQL Server 6.5),Data Cache與Procedure Cache有獨立控制的memory pool
-- SQL Server 7.0與SQL Server 2000則是共用一個memory pool
--
-- 此memory pool即Buffer pool
-- The buffer pool is managed by a process called the lazywriter

--01.Procedure cache (execution plan cache)

select * from master.dbo.syscacheobjects

--DBCC PROCCACHE
--GO

--Free Porcedure Cache
-- Method 1: Free Porcedure Cache by database
--SELECT DB_ID('pubs')
--DBCC FLUSHPROCINDB (5)
--GO
--
-- Method 2 -Free All Porcedure Cache
-- DBCC FREEPROCCACHE
-- GO




-- 02.Data Buffer Cache
-- Get TOP 20 objects in the data cache
-- BUG: DBCC MEMUSAGE Is Not Supported in SQL Server 7.0
--The DBCC MEMUSAGE statement is not supported in SQL Server 7.0. Executing it on servers running heavy loads with large databases may cause the server to stop responding.
-- http://support.microsoft.com/default.aspx?scid=kb;en-us;196629

DBCC MEMUSAGE

-- Free Buffer Cache
-- DBCC DROPCLEANBUFFERS

-- Force all dirty pages to be written to disk
--CHECKPOINT
--GO



-- 03.Other Useful Command - DBCC MEMORYSTATUS
USE master
GO
DBCC MEMORYSTATUS
GO

-- 04.Other Useful Command - DBCC PINTABLE (@db_id,@object_id)
-- 微軟不建議使用以下功能,但仍然可以使用,建議在測試環境才使用
-- keep a table's data pages in memory
-- demo Database named pubs
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC PINTABLE (5,277576027)
GO

-- release a pinned table's data pages from memory.
USE pubs
GO
select DB_ID('pubs')
GO
-- return 5
select OBJECT_ID('dbo.jobs')
GO
-- return 277576027
DBCC UNPINTABLE (5,277576027)
GO

[reference]
Analyzing SQL Server 2000 Data Caching
How to Interact with SQL Server's Data and Procedure Cache