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.
