SQL Server experts know that the DB engine never accesses directly the data pages on disk; rather it uses a special module called “Cache Manager”. It is possible, by using some T-SQL commands, to force the selected tables to stay in the buffer for a longer time. This technique increases the performances of the queries that work with these tables.
The Cache Manager uses a Least Recently Used (LRU) strategy to decide which pages to keep in the buffer and which should be stored on disk when memory must be released to load newer pages. In some cases, though, you might want to change the way SQL Server decides which pages to keep in the buffer. This way, the Cache Manager will keep the pages of the specified tables in the cache, increasing the performance of the queries that work with those tables. Obviously you should not abuse of this possibility, because you might affect queries that work with other tables. In practice, the ideal characteristics of the tables that should be declared as “pinned” are:
Here are the commands that activate/deactivate the pinning mode for a table:
Activation for pubs..authors:
DECLARE @db_id int, @tbl_id intUSE pubsSET @db_id = DB_ID('pubs')SET @tbl_id = OBJECT_ID('pubs..authors')DBCC PINTABLE (@db_id, @tbl_id)
Deactivation for pubs..authors:
DECLARE @db_id int, @tbl_id intUSE pubsSET @db_id = DB_ID('pubs')SET @tbl_id = OBJECT_ID('pubs..authors')DBCC UNPINTABLE (@db_id, @tbl_id)
########################################################
This tip has been originally published on Microsoft Italia’s web site. It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia. You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################
