February 21, 2003 at 12:36 pm
A Lookup Strategy Defined
By: David Sumlin
First off let me say this was a first rate article well conceived. I can't tell you how many look up tables I've created or coded for in the past.
My question, I haven't seen much written on the subject of using DBCC Pintable. Would David's tables as defined in his article be a good candidate for Pintable?
This also brings up another question, obviously size has to be taken into consideration when deciding on pinning a table (less it eats up all your buffer cache), is there a means (automated mind you) of telling that a given table is taking up (x)% of the buffer cache?
Does the storage value for a table to be pinned have a direct correlation to the amount of buffer cache it can own or will potentially take?
I'm still working mostly with 7.0 servers, but were getting there.
John Zacharkan
John Zacharkan
February 21, 2003 at 2:48 pm
quote:
My question, I haven't seen much written on the subject of using DBCC Pintable. Would David's tables as defined in his article be a good candidate for Pintable?
In 98% of all cases PINTABLE is bad to do or justify. However, I have an server application that must answer requests within 3 seconds so the small support table respond better by using PINTABLE. These are the only tables I read and I have setup the whole process on a machine and SQL Server to itself so there is no worry about any of the normal issues with PINTABLE. I have one table I write transactions too and daily at 11PM I move them to the reporting server. The real thing is what other conditions may cause an issue or does PINTABLE offer a bennifit without recourse.
quote:
This also brings up another question, obviously size has to be taken into consideration when deciding on pinning a table (less it eats up all your buffer cache), is there a means (automated mind you) of telling that a given table is taking up (x)% of the buffer cache?
To the best of my knowledge there is not an automated way, however I believe one of the DBCC commands can so you the information and you could write a tool to get the data and make an automated method. But then you need to consider what effect this will have.
quote:
Does the storage value for a table to be pinned have a direct correlation to the amount of buffer cache it can own or will potentially take?
Never looked so cannot directly answer.
February 21, 2003 at 5:09 pm
DBCC Memusage maybe?
I agree with Antares, I doubt you'd see much gain. One would think (well, I would anyway) that if you're hitting the table that much it would stay resident unless you're really really memory constrained.
I think a better tact is to look at caching the info on the client, at least for the session and even better across sessions. Think about a state or country look up list. Why pull that across every time when you don't have to?
Andy
February 24, 2003 at 1:24 pm
Thank you Antares686 for sharing how and when you use pintable.
From a design point of you I agree with you Andy, going forward I'm sure I'll be in on all new designs. I'm looking for performance solutions that are transparent to the developers.
quote:
I think a better tact is to look at caching the info on the client
Your statement
quote:
hitting the table that much it would stay resident unless you're really really memory constrained
I think this really hits on the head, I don't see how pintable would help, memory is not an issue, and if it was pintable would not be an answer.
I have bigger problems with table scans, and long running queries, most of which come from vendor apps.
John Zacharkan
John Zacharkan
February 24, 2003 at 5:52 pm
I fully agree, my only issue is this is a realtime app so those support tables (which are small and used very often) have to be accessed as quickly as possible. Even though nothing else should be in memory I need to be sure they hang around in case some developer without a clue starts querying the tables without thinking. I would never, ever suggest using pintable unless you have some insane reason.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply