Post reply

DBCC PINTABLE

  • October 28, 2013 at 4:37 pm

    #279315

    Does DBCC PINTABLE command works in SQL Server 2008 R2?

    We want to pin heavily used look up tables in cache. Is it possible in SQL Server 2008 R2?

    Are there any methods available to achieve this?

  • October 28, 2013 at 4:44 pm

    #1662186

    No it does not work, hasn't worked since 2000. No, you cannot force tables into cache, there's no need, SQL's memory management and buffer management is good enough. PinTable tended to cause more problems than it solved.

    Heavily used lookup tables will remain in cache because they're heavily used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 28, 2013 at 4:56 pm

    #1662188

    No - I removed it in SQL Server 2005 because people were causing problems for themselves. See http://www.sqlskills.com/blogs/paul/dbcc-pintable/ for some explanation.

    If you find a table is dropping out of cache, even though heavily used, because other tables are also heavily used and they can't all be kept in memory, put in a SQL Agent job that does a SELECT COUNT (*) with an index hint to force index ID 1. Adjust periodicity as necessary.

    Cheers

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • October 30, 2013 at 11:12 am

    #1662802

    thanks for your valuable inputs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply