How to Interact with SQL Server’s Data and Procedure Cache

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

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |