Posted by decipherinfosys on March 24, 2009
A reader had asked this question recently: “I have heard from a colleague, that we should be pinning our static data tables into memory for improved performance. Is that true?“. SQL Server DBA’s/Developers who have been working with SQL Server since quite some time will remember that this used to be a common advice in early days of SQL Server – I remember using this in version 6.5 and version 7 for pinning frequently accessed small static data value tables in memory so that SQL Server would not flush the pages for those tables. Never really saw any measurable performance benefit from it. Moreover, SQL Server’s memory management has improved a lot since those very early days of the product.
After all, if the data is very frequently accessed, it would be in the cache anyways and if these are small tables, reading them out of the disc shouldn’t be slower either. Also, if we pin these tables into memory and these do not get used frequently, then we are un-necessarily taking away cache usage from data that does get used frequently.
And another reason not to use this command is because it is now deprecated. If you look up BOL (version used in SQL Server 2005) for DBCC PINTABLE, you will see this warning:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
- BOL – DBCC PINTABLE.