Forcing All Dirty Pages to be Written to a Disk
You can use the CHECKPOINT statement to force all dirty pages to be written to disk.
Syntax
CHECKPOINT
Example
–Force a database checkpoint
PRINT ‘Force a Checkpoint’
CHECKPOINT
GO
Releasing All Data Pages From Memory
You can use the DBCC DROPCLEANBUFFERS console command to release all data pages from memory.
Syntax
DBCC DROPCLEANBUFFERS
Example
–Remove the clean buffers from the buffer pool
–Clean up the display
SET NOCOUNT ON
DBCC DROPCLEANBUFFERS
GO
Print Out the Buffer Headers and Pages From the Buffer Cache
You can use the DBCC BUFFER console command to print out the buffer headers and pages from the buffer cache.
Syntax
DBCC BUFFER ( [@dbid|’@dbname‘ ]
[,@objid|obname ] [,@numofbuffers]
[,@printopt] )
Example
–Dump contents of buffers
–Clean up the display
SET NOCOUNT ON
–Trace Flag 3604 must be on in order for DBCC command to run
DBCC TRACEON (3604)
–Use the pubs database
USE pubs
–Determine and hold database id for the pubs database
DECLARE @numDBID INTEGER
SET @numDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘pubs’)
–Determine and hold object id for the authors table
DECLARE @numOBID INTEGER
SET @numOBID = OBJECT_ID(‘dbo.authors’)
–Fill buffers
SELECT COUNT(*) FROM dbo.authors
–Display buffers
DBCC BUFFER (@numDBID ,@numOBID,1, 1 )
GO
Displaying the Stored Procedure Cache
The contents of the stored procedure cache can return with the DBCC PROCCACHE console command.
Syntax
DBCC PROCCACHE
Example
–Return the procedure cache information
DBCC PROCCACHE
GO
Remove All Execution Plans From Cache
You can use the DBCC FREEPROCCACHE console command or the DBCC FLUSHPROCINDB console command to flush the stored procedure cache and cause all stored procedures to recompile the next time they are executed.
Syntax
DBCC FLUSHPROCINDB (@dbid)
Example
–Recompile all stored procedures in the pubs database
–Clean up the display
SET NOCOUNT ON
–Determine the id of the pubs database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘pubs’)
–Recompile the stored procedures
DBCC FLUSHPROCINDB (@intDBID)
Syntax
DBCC FREEPROCCACHE
Example
–Remove all elements from the procedure cache
–Clean up the display
SET NOCOUNT ON
DBCC FREEPROCCACHE
GO