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 INTUSE 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) 
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

More From DevX