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
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
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[vbcol=seagreen]
> 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?
>
|||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...
> written to disk at checkpoint.
> related to a trigger.
> pages in cache. In vast
> are
> on
> also be
> table?
>
>
Showing posts with label pintable. Show all posts
Showing posts with label pintable. Show all posts
Wednesday, March 21, 2012
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...
>
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...
>
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.
LeonLeon,
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...
> > 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
>sql
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.
LeonLeon,
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...
> > 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
>sql
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
> > >
> >
> >
>
>
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
> > >
> >
> >
>
>
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?
>
>
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?
>
>
Subscribe to:
Posts (Atom)