DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
Leon
Leon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon
|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
>