Maintenance Plan DBCC Functionality

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 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=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message
news:O9Tb4hR5DHA.2732@.TK2MSFTNGP09.phx.gbl...
> 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
>|||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:
> 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.
>|||Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message news:ON8FIKa5DHA.1664@.TK2MSFTNGP11.phx.gbl...
> 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:
> > 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.
> >
>|||Do you know of any other maintenance tools that can be used, that will
allow writes to the table being modified?
Tibor Karaszi wrote:
> Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
>|||It is not the client's decision whether to write at the same time as a rebuild 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=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
> > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> >
>|||See my reply to your previous post... checkout the whitepaper below that
gives you full details on this problem.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/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...
> Do you know of any other maintenance tools that can be used, that will
> allow writes to the table being modified?
> Tibor Karaszi wrote:
> > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> >
>|||Also, you might want to check out DBCC INDEXDEFRAG in case you haven't already. I do recommend
reading the document that Paul posted. Good stuff.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:u$Sut4a5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> It is not the client's decision whether to write at the same time as a rebuild 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=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ben" <ben@.brainspout.com> wrote in message news:%235k9sja5DHA.2764@.TK2MSFTNGP09.phx.gbl...
> > Do you know of any other maintenance tools that can be used, that will
> > allow writes to the table being modified?
> > Tibor Karaszi wrote:
> > > Nope, there's currently no such option for DBCC DBREINDEX, I'm afraid.
> > >
> >
>