How to Pin a Table into Memory?
By Kalman Toth, M.Phil., M.Phil., MCDBA

February 28, 2005

While SQL Server 2000 has a sophisticated memory buffer management, it may lead to performance gain if we help out SQL server with something we know: keep TableX in memory all the time. The result will be that TableX pages will not be flushed out, whatever comes in, will stay in memory. If the table is used frequently, the result is improved performance.

This is the code sample:

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

This is the message:
Warning: Pinning tables should be carefully considered. If a pinned table is larger, or grows larger, than the available data cache, the server may need to be restarted and the table unpinned.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We can check if a table is pinned or not by:

SELECT OBJECTPROPERTY(OBJECT_ID('pubs..authors'),'TableIsPinned')

Which returns 1 if pinned and 0 if not.

To see how many buffers are pinned execute:

DBCC memorystatus

The "inram" entry indicates the pinned tables buffer usage.

What happens if the table in memory grows too large? That is a real danger with pinning. Eventually over-pinning stops the server altogether. The overload sequence start with various strange server messages in the server log, all related to memory utilization.

This is the code sample to unpin a table:

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

For practical purposes, no more than half of the SQL server available memory should be used for pinning. Very frequently used tables should be pinned. Perfomance gain should be monitored. If no gain, pinning should be discontinued.