Is there a way to force an index to stay in memory with SQL Server 2008?

There used to be a DBCC PINTABLE command but I believe that stopped working in 6.5 or maybe 7.0. The statement will probably still suggest that it worked if you try it, but it just returns, it really is a no-op.

Unfortunately there's not really any way to control which indexes are kept in cache - the best workaround I know of for tables that are periodically hot is to keep them hot manually (which you've already described in your question).

For which indexes are in memory, you can get a rough idea from sys.sm_os_buffer_descriptors. I published a tip about this:

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

Tags:

Performance

Sql Server

Sql Server 2008

Index

Buffer Pool

Related

How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()? What's the difference between a CTE and a Temp Table? How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora Ready-to-Use Database models example Does SQL Server read all of a COALESCE function even if the first argument is not NULL? SQL Server - separate database for reports? What is lock escalation? Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database? How will large index INCLUDE fields affect system performance? What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure? Is there a reason to update statistics manually? What does the term "projection" refer to in an SQL query (or view definition)

Recent Posts