Reorganize vs. REbuild Index Question

OK, I am assuming that by reorganizing my indexes it will do just that to
optimize the indexes vs. rebuild will do the same but it will also let me
adjust the amount of free space for the index. If I continue to use the
default free space % would these tasks not have the exact same result? Also,
the time it takes to do a reorganize vs. a rebuild would be the same? Is
there anything I am misunderstanding about this maintainenace plan task that
I want to select one or the other?DBCC DBREINDEX does a somewhat better job, but locks the table.
DBCC INDEXDEFRAG does almost as well, but can run while the users accesses
the table/index
However, I beleive you're looking at the SQL Server 2000 maintenance Plan
Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
DROP/CREATE INDEX to rebuild.
Note that the amount of benifite you'll get depends on how the indexes are
being used. OLTP, not so much, DSS potentially a lot.
I suppose they would both take the same amount of time, however, you would
not change the free space % for that reason. Change free space % to a larger
value if the table is insert heavy and to a smaller value the more static
the table is. It goes to performance when using the system.
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
> OK, I am assuming that by reorganizing my indexes it will do just that to
> optimize the indexes vs. rebuild will do the same but it will also let me
> adjust the amount of free space for the index. If I continue to use the
> default free space % would these tasks not have the exact same result?
> Also,
> the time it takes to do a reorganize vs. a rebuild would be the same? Is
> there anything I am misunderstanding about this maintainenace plan task
> that
> I want to select one or the other?
>|||> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in 2000 MP regarding how you
want the "optimization" to be done. MP does DBREINDEX for all indexes in the database.
May I also suggest for Richard below reading:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
> DBCC DBREINDEX does a somewhat better job, but locks the table.
> DBCC INDEXDEFRAG does almost as well, but can run while the users accesses the table/index
> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
> Note that the amount of benifite you'll get depends on how the indexes are being used. OLTP, not
> so much, DSS potentially a lot.
> I suppose they would both take the same amount of time, however, you would not change the free
> space % for that reason. Change free space % to a larger value if the table is insert heavy and to
> a smaller value the more static the table is. It goes to performance when using the system.
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that to
>> optimize the indexes vs. rebuild will do the same but it will also let me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result? Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same? Is
>> there anything I am misunderstanding about this maintainenace plan task that
>> I want to select one or the other?
>|||Thank you Tibor, I was close. Still wrong, but close.
I read the link a while ago and found it to be excellent reading, well worth
your time.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D4C5F3BF-ABFC-4A96-B47F-CD5467322302@.microsoft.com...
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
> DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in
> 2000 MP regarding how you want the "optimization" to be done. MP does
> DBREINDEX for all indexes in the database.
> May I also suggest for Richard below reading:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> DBCC DBREINDEX does a somewhat better job, but locks the table.
>> DBCC INDEXDEFRAG does almost as well, but can run while the users
>> accesses the table/index
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
>> Note that the amount of benifite you'll get depends on how the indexes
>> are being used. OLTP, not so much, DSS potentially a lot.
>> I suppose they would both take the same amount of time, however, you
>> would not change the free space % for that reason. Change free space % to
>> a larger value if the table is insert heavy and to a smaller value the
>> more static the table is. It goes to performance when using the system.
>> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
>> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that
>> to
>> optimize the indexes vs. rebuild will do the same but it will also let
>> me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result?
>> Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same?
>> Is
>> there anything I am misunderstanding about this maintainenace plan task
>> that
>> I want to select one or the other?
>>
>