DBCC pintable

Hi,
After I pin the table to the cache, what exactly will happen if there are
frequent updates and inserts on this table? I also have update trigger on
this table.
To be precise with my question,
(a) if the update is made onto a pinned table, will the updated data also be
available in the cache?
(b) Does update trigger have any performance degradation on the cached table
?
Thanks
GGYKa. All modifications are made in cache. The dirty pages in cache are written
to disk at checkpoint.
Pinning doesn't change anything.
b. Not sure I understand. Again, pinning a tables doesn't change anything re
lated to a trigger.
Note that SQL Server is pretty smart about having frequently accessed pages
in cache. In vast
majority of cases, pinning will not improve performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:48C6BCEA-3273-4D2C-82EC-ACB12428C88D@.microsoft.com...
> Hi,
> After I pin the table to the cache, what exactly will happen if there are
> frequent updates and inserts on this table? I also have update trigger on
> this table.
> To be precise with my question,
> (a) if the update is made onto a pinned table, will the updated data also
be
> available in the cache?
> (b) Does update trigger have any performance degradation on the cached tab
le?
> Thanks
> GGYK
>|||Hi
Don't forget, with a pinned table, the Transaction Log entry is written
first, so you don't save on disk I/O for writes.
I have seen that on some systems, the pinned table has a higher probability
for blocking due to the quicker access that SQL has (no read disk I/O). You
might start to see deadlocks more frequently.
If the table is used enough, it stays in cache, so why use more RAM than you
actually need to?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OlwUSG4uEHA.908@.TK2MSFTNGP11.phx.gbl...
> a. All modifications are made in cache. The dirty pages in cache are
written to disk at checkpoint.
> Pinning doesn't change anything.
> b. Not sure I understand. Again, pinning a tables doesn't change anything
related to a trigger.
> Note that SQL Server is pretty smart about having frequently accessed
pages in cache. In vast
> majority of cases, pinning will not improve performance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:48C6BCEA-3273-4D2C-82EC-ACB12428C88D@.microsoft.com...
are[vbcol=seagreen]
on[vbcol=seagreen]
also be[vbcol=seagreen]
table?[vbcol=seagreen]
>|||Hi,
Thanks for the responses.
GYK
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Don't forget, with a pinned table, the Transaction Log entry is written
> first, so you don't save on disk I/O for writes.
> I have seen that on some systems, the pinned table has a higher probabilit
y
> for blocking due to the quicker access that SQL has (no read disk I/O). Yo
u
> might start to see deadlocks more frequently.
> If the table is used enough, it stays in cache, so why use more RAM than y
ou
> actually need to?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OlwUSG4uEHA.908@.TK2MSFTNGP11.phx.gbl...
> written to disk at checkpoint.
> related to a trigger.
> pages in cache. In vast
> are
> on
> also be
> table?
>
>