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.

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s