asked
Is there a way to force a table into memory?
If I have a table, temporary or otherwise, which I want to place in SQL Server memory, is there a way to *force* SQL to do that? If the answer is "SQL handles the memory and will keep it in memory if possible, so don't worry with it" then is there a practical way to verify that the table is indeed in memory? Will it stay there if it is in memory or disappear as soon as there is another server request? Also, if there is an associated index with the table, will the index automatically be in memory also?
There used to be a way to pin a table into memory, but it caused all kinds of problems, so Microsoft removed it from the product. Now, SQL Server manages that memory, and as you say, it will keep it there if possible. It will stay in memory as long as it keeps getting accessed and no other query comes along that needs to dump it out of memory in order to free up space. No, the index won't automatically be in memory. Only if it's accessed and read into the cache. I'm not aware of a way to monitor this except to watch for physical i/o versus logical i/o in the queries.
+1 - I am running out of votes today! (5 left). @Mark - if you're interested, the old way was DBCC PINTABLE (
http://msdn.microsoft.com/en-us/library/ms178015.aspx)
+1 to you both. Interesting note from the article "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."
Ha, ha, ha... So you can run it and it doesn't return an error, but it won't do anything. That's great. I'm surprised we're not seeing all kinds of problems from people "I pinned my table but performance still sucks, help, urgent, urgent."
Proof positive you're not oooollld. I remember using this on SQL Server 6.5 to improve performance... and crash the server occasionally. But then, it was easy to crash the server on 6.5.
Sure - write a bad query that ensures table and index scans and run it every five minutes :) You may "push" valid data out of the buffer but you will have effectively pinned the table into RAM. (Totally Tongue-In-Cheek!)
You could always clean out the buffers before you run it, just to be really sure.
In this blog post, Paul Randal addresses how to check the wasted buffer space of currently cache database contents. He uses sys.dm_os_buffer_descriptors to do that. That strategy can also be used to check how many blocks of a given table or index is in the buffer cache at the moment.
http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/
1 Person is following this question.
