dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Feb 5, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Employing DBCC PINTABLE and DBCC UNPINTABLE Functions

SQL Server automatically loads the data it needs to work with into its cache. This is called pages. These data pages stay in SQL Server's cache until there is no room for them and it gets flushed out of the data cache onto disk.

Whenever SQL Server needs these data pages again, it must to go to disk and read them into the cache again. To ensure a table always stays available in SQL Server's cache you could Pin the table and Unpin it when it is not needed anymore. For this you make use of the DBCC PINTABLE and DBCC UNPINTABLE Functions. Here is a very small example:

Pinning

DECLARE @DatabaseID INT, @TableID INT
USE DatabaseName 
SET @DatabaseID = DB_ID('DatabaseName') 
SET @TableID = OBJECT_ID('TableName') 
DBCC PINTABLE (@DatabaseID, @TableID) 

Unpinning

DECLARE @DatabaseID INT, @TableID INT 
USE DatabaseName 
SET @DatabaseID = DB_ID('DatabaseName') 
SET @TableID = OBJECT_ID('TableName') 
DBCC UNPINTABLE (@DatabaseID, @TableID) 
Hannes du Preez
 
Thanks for your registration, follow us on our social networks to keep up-to-date