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

    Share the Post:
    Share on facebook
    Share on twitter
    Share on linkedin

    Overview

    The Latest

    microsoft careers

    Top Careers at Microsoft

    Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

    your company's audio

    4 Areas of Your Company Where Your Audio Really Matters

    Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

    chrome os developer mode

    How to Turn on Chrome OS Developer Mode

    Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS