Binaryworld - How to improve performance with pinned tables ... [ SQL Server -> Performance ]

SQL Server experts know that the DB engine never accesses directly the data pages on disk; rather it uses a special module called "Cache Manager". It is possible, by using some T-SQL commands, to force the selected tables to stay in the buffer for a longer time. This technique increases the performances of the queries that work with these tables.

The Cache Manager uses a Least Recently Used (LRU) strategy to decide which pages to keep in the buffer and which should be stored on disk when memory must be released to load newer pages. In some cases, though, you might want to change the way SQL Server decides which pages to keep in the buffer. This way, the Cache Manager will keep the pages of the specified tables in the cache, increasing the performance of the queries that work with those tables. Obviously you should not abuse of this possibility, because you might affect queries that work with other tables. In practice, the ideal characteristics of the tables that should be declared as "pinned" are:

The table is frequently used by users
The table is pretty small, in all cases its size should be less than the available RAM amount for the DB engine
Here are the commands that activate/deactivate the pinning mode for a table:

Activation for pubs..authors:

Click here to copy the following block

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID('pubs')
SET @tbl_id = OBJECT_ID('pubs..authors')
DBCC PINTABLE (@db_id, @tbl_id)

Deactivation for pubs..authors:

Click here to copy the following block

DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID('pubs')
SET @tbl_id = OBJECT_ID('pubs..authors')
DBCC UNPINTABLE (@db_id, @tbl_id)