Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC PINTABLE Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 4:37 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
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?
Post #1509141
Posted Monday, October 28, 2013 4:44 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1509144
Posted Monday, October 28, 2013 4:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #1509146
Posted Wednesday, October 30, 2013 11:12 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:02 AM
Points: 529, Visits: 1,688
thanks for your valuable inputs
Post #1509883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse