DBCC PINTABLE (T-SQL)

Marks a table to be pinned, which means Microsoft® SQL Server™ does not flush the pages for the table from memory.

Syntax

DBCC PINTABLE (database_id, table_id)

Arguments

database_id: Is the database identification number (ID) of the table to be pinned. To determine the database ID, use the DB_ID function.
table_id: Is the object identification number (ID) of the table to be pinned. To determine the table ID, use the OBJECT_ID function.

Remarks

DBCC PINTABLE does not cause the table to be read into memory. As the pages from the table are read into the buffer cache by normal Transact-SQL statements, they are marked as pinned pages. SQL Server does not flush pinned pages when it needs space to read in a new page. SQL Server still logs updates to the page and, if necessary, writes the updated page back to disk. SQL Server does, however, keep a copy of the page available in the buffer cache until the table is unpinned with the DBCC UNPINTABLE statement.

DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read.

Caution Although DBCC PINTABLE can provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table larger than the buffer cache is pinned, it can fill the entire buffer cache. A member of the sysadmin fixed server role must shut down SQL Server, restart SQL Server, and then unpin the table. Pinning too many tables can cause the same problems as pinning a table larger than the buffer cache.

Result Sets

DBCC PINTABLE returns this result set:

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.

Permissions

DBCC PINTABLE permissions default to members of the sysadmin fixed server role, the db_owner fixed database role, or the table owner, and are not transferable.

Examples

This example pins the authors table in the pubs database.

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)