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 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...
> 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
> 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...
> > 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
> > GGYK
> >
>|||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 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...
> > > 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
> > > GGYK
> > >
> >
> >
>
>