May 31, 2002 at 10:06 am
When would I pin a table into memory?
In what situation would this benifit me? (ie. would it speed up the next full table scan on that table?)
May 31, 2002 at 10:46 am
Normally you would not, however it you will be working with a specific table for an extended period of time you may do this to prevent it from dropping from the cache and thus require hard drive reads to access it (barring paged memory). Example might be a small table of values for the team names of a call center. You have an app in this situation that receives data from a call center switch and want to be able to push that data into you SQL server database thru a stored procedure. The data is stored with the CC name not the number so you pin the table in memory so these inserts don't have to read the data back into memory to use since you process hundreds of records per second. However SQL will generally holds this in memory longer and retain more so in the case a flush needs to occurr normally since itis used so much. I would avoid pinning tables unless you know you will not cause memory issues or have a very specific need that cannot be meet otherwise. In the previous example the app could have done this itself. The key to pinning a table thou is it reduces round trips to the hard drive for the data.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 10:52 am
We have vb apps. which run 65 sql strings consecutively all using the same table in a join.
I was wondering if sql server was holding it in memory on it's own or if I should pin it.
We have more memory than we ever use.
Totally seperate question: If disk I/O is a bottleneck what would be the HARDWARE fix?
May 31, 2002 at 10:57 am
You could try pinning the table but should be retained if used very often unless something else pushed it out. All I could say is test but there could be other issues I didn't think of.
As for DISK I/O getting a faster array and faster drives is your biggest thing. Also moving to a RAID 10 config can have bennifits. There are many other factors such as age of the box and number of drives in the array that can also have various mpacts beyond the two I listed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply