Keeping data available in the SQL Server data cache with PINTABLE

By:   |   Comments   |   Related: > TSQL


Problem

Have you ever wondered why even after optimizing a database that it still takes considerable time to fetch the results?  The problem is whenever a query is fired SQL Server fetches the data from the database by means of bringing the data pages into memory.  Depending on the database activity the data you may be using quite frequently may be getting paged in and out of memory which may account for why sometimes it takes longer then other times to fetch the data.  This paging is done automatically, but if you feel that a table is being used very frequently then this is where the DBCC PINTABLE command may come in handy.

Solution

The process of "pinning a table" is a way to tell SQL Server that we don't want it to flush out data pages for specific named tables once they are read in the first place. This in effect keeps these database pages in the data cache all the times, which eliminates the process of SQL Server having to read the data pages, flush them out, and reread them again when the time arrives. As you can imagine, this can reduce I/O for these pinned tables, boosting SQL Server's performance.

To pin a table, the command DBCC PINTABLE is used. For example, the script below can be run to pin a table in SQL Server:

DECLARE @db_id int, @tbl_id int
USE Northwind
SET @db_id = DB_ID('Northwind')
SET @tbl_id = OBJECT_ID('Northwind..categories')
DBCC PINTABLE (@db_id, @tbl_id)

While you can use the DBCC PINTABLE directly, without the rest of the above script, you will find the script handy because the DBCC PINTABLE's parameters refer to the database and table ID that you want to pin, not by their database and table name. This script makes it a little easier to pin a table. You must run this command for every table you want to pin.

Once a table is pinned in the data cache, this does not mean that the entire table is automatically loaded into the data cache. It only means that as data pages from that table are needed by SQL Server, they are loaded into the data cache, and then stay there, not ever being flushed out to disk until you give the command to unpin the table using the DBCC UNPINTABLE. It is possible that part of a table, and not all of it, will be pinned.

When you are done with a table and you no longer want it pinned, you will want to unpin your table. To do so, run this example code:

DECLARE @db_id int, @tbl_id int
USE Northwind
SET @db_id = DB_ID('Northwind')
SET @tbl_id = OBJECT_ID('Northwind..categories')
DBCC UNPINTABLE (@db_id, @tbl_id)

That is all there is to pinning and unpinning tables.  If you have activities that require a specific table to always be accessed the use of pinning could make a significant improvement.

Note for SQL Server 2005:
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.
Next Steps

  • So whenever you are working on large databases, do remember that there is a provision which can be formulated to keep data pages in memory so that the load can be reduced on the memory
  • Be careful in what tables you pin and also be aware of how much memory you have on your server. If you pin very large tables without ever freeing up the space this could impact the data cache for other tables.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sabarish Iyer Sabarish Iyer's bio is coming soon...

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms