when SQL re-organizes data and index pages, will data be able to be
inserted and read during these operations?
Thanks,
ben mMaint wiz uses DBCC DBREINDEX.
From memory, DBREINDEX will acquire shared table lock when rebuilding
non-clustered index. This will allow reads, but writes will be blocked.
Again, from memory, DBREINDEX will acquire exclusive table lock when
rebuilding clustered index- This will block both readers (unless code does
dirty reads) and writers.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Ben" <ben@.brainspout.com> wrote in message
news:O9Tb4hR5DHA.2732@.TK2MSFTNGP09.phx.gbl...
quote:|||Would it be possible to manually execute the DBCC REINDEX command with
> When setting up a maintenance plan ,with the maintenance plan wizard,
> when SQL re-organizes data and index pages, will data be able to be
> inserted and read during these operations?
> Thanks,
> ben m
>
an option not to lock writes?
This is a transaction processing box.
thanks again,
-Ben
Tibor Karaszi wrote:
quote:|||Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> Maint wiz uses DBCC DBREINDEX.
> From memory, DBREINDEX will acquire shared table lock when rebuilding
> non-clustered index. This will allow reads, but writes will be blocked.
> Again, from memory, DBREINDEX will acquire exclusive table lock when
> rebuilding clustered index- This will block both readers (unless code does
> dirty reads) and writers.
>
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ben" <ben@.brainspout.com> wrote in message news:ON8FIKa5DHA.1664@.TK2MSFTNGP11.phx.gbl...
quote:|||Do you know of any other maintenance tools that can be used, that will
[c
olor=darkred]
> Would it be possible to manually execute the DBCC REINDEX command with
> an option not to lock writes?
> This is a transaction processing box.
> thanks again,
> -Ben
> Tibor Karaszi wrote:
>
>[/color]
allow writes to the table being modified?
Tibor Karaszi wrote:
quote:|||It is not the client's decision whether to write at the same time as a rebui
> Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
>
ld is happening. It is
the server's decision. In SQK Server, all the methods to rebuilds an index,
will in the end call the
same code (the code which is called when an index is created by CREATE INDEX
), and this code uses
locks to protect against other modifications.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
quote:|||See my reply to your previous post... checkout the whitepaper below that
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
>
gives you full details on this problem.
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben@.brainspout.com> wrote in message
news:#5k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
quote:|||Also, you might want to check out DBCC INDEXDEFRAG in case you haven't alrea
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
>
dy. I do recommend
reading the document that Paul posted. Good stuff.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u$Sut4a5DHA.2064@.TK2MSFTNGP11.phx.gbl...
quote:
> It is not the client's decision whether to write at the same time as a reb
uild is happening. It is
> the server's decision. In SQK Server, all the methods to rebuilds an index, will in the en
d call
the
quote:
> same code (the code which is called when an index is created by CREATE IND
EX), and this code uses
> locks to protect against other modifications.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...
lserver
>
> "Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSF
TNGP09.phx.gbl...
>
No comments:
Post a Comment