SQL Server DBCC PINTABLE does nothing anymore

Hi Friends,

In one of my recent assignments, one participant asked if there is any way in SQL Server to warm up the cache of with some heavily used tables to achieve better performance. Which means, he wanted to load the data pages of a particualr table before hand in the buffer pool. I faintly remembered SQL Server DBCC PINTABLE & UNPINTBALE in SQL Server 2000 but do not remember using it in SQL 2k5 or 2k8 or r2. After R&D, I confirmed that DBCC PINTABLE is discontinued.

   

DBCC PINTABLE was introduced in SQL Server 6.5 for the same reasons mentioned above. But since the re-architected release of SQL Server since version 2005, the buffer pool does an excellent job of managing the data pages in memory, pinning a table is not required. Furthermore, if a large table is pinned and continues to grow, it can adversly affect performance. The command still works but has no affect at all.

The next question was, can you still pin and set a max memory limit for that table? 🙂

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “SQL Server DBCC PINTABLE does nothing anymore”

Leave a Reply

Your email address will not be published.