Read up on "pinned tables" in BOL ..

Read up on "pinned tables" in BOL. I've never used this feature before, but
according to the reference, DBCC PINTABLE can be used to mark a table so
that it's pages are not flushed from memory. However, I'm thinking that if a
table relatively small (only a few thousand records), the table is hit quite
often, and your sever has average or better memory (1 GB or more), then the
table is mostly held in the buffer cache automatically. The article also
provides a warning that this could fill the buffer cache and not leave
enough room for servicing other tables.
"BBM" <bbm@.bbmcompany.com> wrote in message
news:8C8DC765-3E23-403B-A206-E4ED44CB5117@.microsoft.com...
> Thanks to all the responders. You all had good input. Right now I'm
going
> to proceed just using regular SQL Server Tables/Indexes until I prove to
> myself that performance is an issue. I was hoping that there was some way
to
> tell SQL Server to keep a table in memory, but I guess there's not.
> Is there a way to tell SQL Server to keep it's cache at a certain size?
I'm
> familiar with DB2 and in DB2 you can do that by table. Essentially you
can
> set the cache size for a table so large that the entire table becomes
memory
> resident.
> I am intrigued by some of Aleksandar's responses. Could you elaborate on
> what you had in mind with "in memory" temporary tables?
> Thanks again for your responses.
> BBM
> "JT" wrote:
>
not
concurrency
lot, it
cause
isolation
in
that
one
ways.
during
the
going
part
I've
I
at
ServerFurther to PINTABLE. Note that this doesn NOT stop data being written
to disk. It simply means that data is not cleared from the buffer
cache. This is almost certainly undesirable as JT and Rick have noted,
especially on a system that isn't exclusively dedicated as a database
server.
David Portas
SQL Server MVP
--