Is there a practical limit to the size of a table I can pin into memory?

Question

I have read about pinning frequently accessed tables into memory rather than let them being accessed from the disk all the time. But what I wish to know is if there is any cutoff limit for the size of a table that could make it a bad candidate for pinning?

Answer

By default, SQL Server automatically brings into its data cache the pages it needs to work with. These data pages will stay in the data cache until there is no room for them, and assuming they are not needed, these pages will be flushed out of the data cache onto disk. At some point in the future when SQL Server needs these data pages again, it will have to go to disk in order to read them again into the data cache for use. If SQL Server somehow had the ability to keep the data pages in the data cache all the time, then SQL Server's performance would be increased because I/O could be significantly reduced on the server.

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 time, which eliminates the process of SQL Server from 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 all that is pinned.

Pinning tables should be considered an advanced SQL Server performance tuning technique, and should not be done unless you are very familiar with SQL Server and the operations of the database whose table or tables you are considering pinning.

As a rule of thumb, you should only pin table with these characteristics:

• The table should be heavily accessed.
• The table should experience mostly SELECT activity, not a lot of INSERT, UPDATE, or DELETE activity (if any).
• The table should not be huge.

Your question asks if there is any maximum cutoff size for tables you want to pin. There are no hard and fast rules, except to say that the table or tables you want to pin must be smaller than the available data cache you have available. If you try to pin a table or tables that exceed the size of the data cache, this could cause SQL Server to fail, and SQL Server would then need to be restarted to resolve the problem.

What I like to do before pinning any tables is first to ensure that the SQL Server Buffer Cache Hit Ratio Performance Monitor counter is running at 90% or higher. If the hit ratio is below 90%, not only would I consider not pinning any tables (because pinning tables may make this ratio worse), I would consider adding more RAM to the server in order to boost SQL Server's performance.

If the hit ratio is 90% or higher, then I will consider pinning tables, but only if after pinning them the hit ratio is still over 90%. If pinning one or more tables reduces your hit ratio to below 90%, then I would not pin that table or table. Of course you won't know until you try. This means that you might try to pin one table, then watch the hit ratio for a day or two to see what happens. If the hit ratio does not fall below 90%, then you are OK, and may want to consider pinning more tables. But only try one table at a time to see what actually happens to the hit ratio and the overall performance of your SQL Server. At the point where the hit ratio goes below 90%, you know you have gone too far.