SP_TABLEOPTION AND DBCC PINTABLE v7.0
SQL Server allows you the option of pinning a table in memory. Pinning 
a table is an advanced tactic to be employed by database administrators 
that have a very good understanding of SQL Server as well as the 
application that is housed upon it.

By pinning a table in, you effectively keep SQL Server from flushing 
pages of the table from memory. This does not mean that the table is 
preloaded into memory. Rather, when pages are read into memory via 
normal SQL statements, the pages then become marked as pinned pages, 
thus staying in memory. The following example expands upon the DBCC 
PINTABLE information supplied by Microsoft SQL Server Books Online.

syntax: DBCC PINTABLE (database_id, table_id) 
 
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)

To disable the pin, use

DBCC UNPINTABLE (@db_id, @tbl_id)

The following system stored procedure can also enable or disable the 
pin:

EXEC sp_tableoption 'authors', 'pintable','true' 
EXEC sp_tableoption 'authors', 'pintable', 'false' 

To view the current setting, use

EXEC sp_tableoption 'authors', 'pintable', NULL --The NULL parameter 
allows you to see the current setting

Pinning a table is best for small table(s) that are frequently accessed 
and that have a tendency for "hot spots"--pages that have frequent page 
resource contention.

A cautionary note from Microsoft SQL Server Books Online: "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."

------------------------------------------