Force a table to be stored in the buffer pool permanently!

There was a question from one of the users on buffer storage usage.The question was something like this

‘I want to make a table into buffer storage and keep as long as I want.Any query’s can scan this table in buffer straightly?’

This was indeed an interesting question and unfortunately the answer to the question is NO.There is no way you can achieve this in newer versions of SQL Server,ie from SQL Server 2005.

Paul S Randal whom I consider as one of the best person to talk about SQL Server had talked about a DBCC PINTABLE command which actually was used in SQL Server 2000 which will pin the pages from a heavily used.

He has confirmed that this code will does nothing in SQL 2005 or above as these newer version does buffer pool management very efficiently.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s