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


advertisement
 

Tip: Employing DBCC PINTABLE and DBCC UNPINTABLE Functions

See how to ensure a table always stays available in SQL Server's cache. You can Pin the table and Unpin it when it is not needed anymore.


advertisement

WEBINAR:

On-Demand

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


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) 

 

Visit the DevX Tip Bank

 





   
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Thanks for your registration, follow us on our social networks to keep up-to-date