Pinning tables into memory

I have read about pinning the frequently accessed tables into memory rather than let them accessed from the disk all the times. But what I wish to know is: Is there any cutoff limit for the size of a table that could make it a bad candidate for pinning? Any help would be appreciated.

The maximum size of the table pinned into memory is governed by how much RAM in the server is dedicated to the SQL Server installation. A portion of that RAM SQL Server dedicates to the cache; the more RAM used by SQL Server the larger the cache. The cache is divided into several areas; the data cache, the procedure cache, the plan cache, etc. The data cache is the portion of memory where a pinned table resides. Finding the size of the data cache is not an easy number to arrive at. The approximate size of the data cache can be found using Windows 2000 performance counters and some interpolation. The formula is:

 .7 * (SQL Server:Buffer Manager: Cache Size (pages)) * 8/1024.

Use caution when pinning tables into memory. When a table is pinned into memory the entire table is not pinned at once, as the table is accessed it is read into memory, when the lazywriter sweeps through the buffer cache to free buffers, data belonging to a table marked as pinned is not freed. This can lead to problems which require a system administrator to get the server functional again if the table fills the entire cache. Another problem is as the table fills more buffers, there will be fewer buffers for other data needs causing more disk i/o.

Pinning a table into memory may also be done in an attempt to fix a performance problem caused by excessive disk i/o. Monitoring and diagnosing server performance before pinning a table in memory is strongly recommended. Run test cases against a similarly configured staging/testing server while gathering information from Performance Monitor and SQL Server Profiler. This information will assist the DBA in identifying trouble areas in a database (eg, recompiling stored procedures and missing indices) and consequently make alterations based upon that data.

Recommended Reading:

  1. Books On Line: DBCC PINTABLE
  2. "Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook" by Ken England; Chapter 5: SQL Server 2000 and Windows 2000
  3. "Inside SQL Server 7.0" by Kalen Delaney
  4. "Inside SQL Server 2000" by Kalen Delaney


For More Information

  • What do you think about this answer? E-mail the Edtior at [email protected] with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Microsoft SQL Server Installation