DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax
> We have one table that is constantly being written to 27X7
That's a lot of hours in a day :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...
> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:
> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>>We have one table that is constantly being written to 27X7
>
> That's a lot of hours in a day :-)
>
--
Ben M

DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax

quote:

---

> We have one table that is constantly being written to 27X7

---

That's a lot of hours in a day :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>

---

|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:

quote:

---

> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>
> That's a lot of hours in a day :-)
>

---

Ben M

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
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?
>
>

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

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?
>
>

dbcc dbreindex/indexdefrag & Update Stats

Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?

CliveAll statistics for that table are updated on DBREINDEX. I would run UPDATE STATISTICS anytime after running INDEXDEFRAG.