How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
Thanks
There is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>