If you have lookup tables (or other small tables) that are frequently accessed and you don’t want to reduce I/O use the command DBCC PINTABLE. What this does is it keeps the table in the data cache all the time so that you reduce I/O which in turn will boost SQL Server performance
Once you pin a table it is not in memory until it’s first requested and then only the data pages requested are in memory not the whole table
How to pin a table?
To pin a table use the script below
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC PINTABLE (@intDBID, @intTableID)
Be careful not to pin large tables since they will stay in memory and SQL Server will have less memory available for other task
To unpin a table use the same script but replace PINTABLE with UNPINTABLE (see below)
DECLARE @intTableID int, @intDBID int
USE Pubs
SELECT @intTableID = OBJECT_ID('Pubs..authors')
SELECT @intDBID = DB_ID('Pubs')
DBCC UNPINTABLE (@intDBID, @intTableID)
Test it out of you staging/development environment first before doing this on a production box
2 comments:
I'd advise against the use of this command.
First, because SQL Server has some pretty good memory management. All data that is frequently accessed will stay in cache anyway. The only data that might be removed from cache is data that has not been used for a long time. The effect of DBCC PINTABLE for a frequently accessed table is zilch; for an infrequently accessed table, the effect is that this infrequently used data remains in cache, and other (more frequently used) data has to be removed instead. The net result would be a slowdown rather than a speed gain. The only situation I can imagine that might benefit from this command is if a table is used only by one query, that is seldomly executed, but it still is mission-critical that this particular query completes in milliseconds.
Second, because the functionality is dropped from SQL Server 2005. The command is still accepted, but it doesn't do anything. Here's a quote from a message in the beta newsgroups (posted by SQL Server MVP Erland Sommarskog):
"The command is available, but Books Online informs us:
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. "
Well I encountered this scenario at a client who had a SQL box without enough memory.
For one reason or another the client refused to spend money on memory. There were some huge tables that were used for reports. Whenever these reports were run the memory would climb to 100% and stay there until the report was finished (could take up to a minute).
Because of this everything else slowed down to a crawl. We decided after testing to pin a couple of small tables into memory that were used to populate dropdowns. After we pinned them the report queries still ran for a minute or so but the other pages seemed to be much faster since the report query could not take 100% of memory but only the available memory.
But you are right in saying that in 99% of the situations this should not be necessary. In my case it helped, if you don’t use all your memory then there is no reason in doing this. I just brought it out there for the masses so that they can experiment with rarely used (but documented) DBCC commands
Post a Comment