October 28, 2013 at 4:37 pm
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
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
October 28, 2013 at 4:56 pm
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
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