Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS7,SS2K
Expertise: Intermediate
Jul 10, 2002

How to improve performance with pinned tables

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:

  • The table is frequently used by users
  • The table is pretty small, in all cases its size should be less than the available RAM amount for the DB engine
  • Here are the commands that activate/deactivate the pinning mode for a table:

    Activation for pubs..authors:

    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)	
    
    Deactivation for pubs..authors:

    DECLARE @db_id int, @tbl_id int
    USE pubs
    SET @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
    ########################################################
    Giuseppe Dimauro
     
    Comment and Contribute

     

     

     

     

     


    (Maximum characters: 1200). You have 1200 characters left.

     

     

    Sitemap
    Thanks for your registration, follow us on our social networks to keep up-to-date