devxlogo

How to improve performance with pinned tables

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 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
    ########################################################

    devxblackblue

    About Our Editorial Process

    At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

    See our full editorial policy.

    About Our Journalist