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? 🙂