mssql2k: rebuilding vs reorganizing indexes?

Hi All,
Just wondering if anyone can give me some general advice on the
practical differences between rebuilding and reorganizing indexes in
sql server 2000?
Basically, we have a database that occupies approx 28gb, of which
approximately 4gb is indexes. In the 2 or so years that our app has
been using this server, the indexes have never been rebuilt /
reoganized to the best of our knowledge.
In particular, we are hoping to minimize downtime while we see to this,
so I'm wondering if anyone has any advice to give about whether a
rebuild is generally quicker than a reorganize, or vice versa?
Any help / advice appreciated!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.orgAre you talking about the difference between DBCC INDEXDEFRAG and DBCC
REINDEX?
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151475743.435319.310670@.y41g2000cwy.googlegroups.com...
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Uri Dimant wrote:
> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
> REINDEX?
Hmmm. According to BOL (admittedly the one that comes with SQL Server
Express 2005), I can use, for example:
ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
or:
ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
...to reoagnize or rebuild respectively.
Is this not the case? The server in question is a production SQL Server
2000 server.
Thanks for your reply!
Much warmth,
planetthoughtful
--
"lost in thought"
http://www.planetthoughtful.org|||Tibor Karaszi wrote:
> This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
Hi Tibor,
Sorry, which article, exactly? Were you intending to include a link?
Thanks for your reply!
Much warmth,
planetthoughtful
--
"Lost in thought"
http://www.planetthoughtful.org|||Sorry, here's the URL: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<planetthoughtful@.gmail.com> wrote in message
news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
> Tibor Karaszi wrote:
>> This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
>> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
> Hi Tibor,
> Sorry, which article, exactly? Were you intending to include a link?
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "Lost in thought"
> http://www.planetthoughtful.org
>|||ALTER INDEX is not available in SQL 2000; it is new for SQL 2005.
USE DBCC REINDEX to rebuild and DBCC INDEXDEFRAG to reorganize, and then the
article Tibor provided is perfectly relevant.
--
HTH
Kalen Delaney, SQL Server MVP
<planetthoughtful@.gmail.com> wrote in message
news:1151478353.212906.225050@.i40g2000cwc.googlegroups.com...
> Uri Dimant wrote:
>> Are you talking about the difference between DBCC INDEXDEFRAG and DBCC
>> REINDEX?
> Hmmm. According to BOL (admittedly the one that comes with SQL Server
> Express 2005), I can use, for example:
> ALTER INDEX PK_tReturns ON dbo.tReturns REORGANIZE;
> or:
> ALTER INDEX PK_tReturns ON dbo.tReturns REBUILD;
> ...to reoagnize or rebuild respectively.
> Is this not the case? The server in question is a production SQL Server
> 2000 server.
> Thanks for your reply!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>|||Hi Tibor,
One quick question.
The option which is available on Maintenance plan (under
optimizations->Reorganize data and index pages), will it perform both rebuild
and reorganize...
Does this maintenence plan optimization activity replaces both DBREINDEX and
INDEXDEFRAG operations?
Regards,
Jaison
"Tibor Karaszi" wrote:
> Sorry, here's the URL: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <planetthoughtful@.gmail.com> wrote in message
> news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
> >
> > Tibor Karaszi wrote:
> >> This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
> >> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
> >
> > Hi Tibor,
> >
> > Sorry, which article, exactly? Were you intending to include a link?
> >
> > Thanks for your reply!
> >
> > Much warmth,
> >
> > planetthoughtful
> > --
> > "Lost in thought"
> > http://www.planetthoughtful.org
> >
>|||Maint plan in 2000 executes DBCC DBREINDEX. Doing both DBREINDEX and also INDEXDEFRAG isn't
necessary. Also, Maint Plan doesn't first check the fragmentation, so it is likely that it does a
whole lot of unnecessary rebuilds. In Books Online, DBCC SHOWCOINTIG, you have code that you can use
in a job which only defragments the indexed with a fragmentation level over a certain threshold.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:B1DF0D93-7F2F-4D74-B207-2879B91A85AB@.microsoft.com...
> Hi Tibor,
> One quick question.
> The option which is available on Maintenance plan (under
> optimizations->Reorganize data and index pages), will it perform both rebuild
> and reorganize...
> Does this maintenence plan optimization activity replaces both DBREINDEX and
> INDEXDEFRAG operations?
> Regards,
> Jaison
> "Tibor Karaszi" wrote:
>> Sorry, here's the URL:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <planetthoughtful@.gmail.com> wrote in message
>> news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
>> >
>> > Tibor Karaszi wrote:
>> >> This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
>> >> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
>> >
>> > Hi Tibor,
>> >
>> > Sorry, which article, exactly? Were you intending to include a link?
>> >
>> > Thanks for your reply!
>> >
>> > Much warmth,
>> >
>> > planetthoughtful
>> > --
>> > "Lost in thought"
>> > http://www.planetthoughtful.org
>> >
>>|||Thanks a lot Tibor...
"Tibor Karaszi" wrote:
> Maint plan in 2000 executes DBCC DBREINDEX. Doing both DBREINDEX and also INDEXDEFRAG isn't
> necessary. Also, Maint Plan doesn't first check the fragmentation, so it is likely that it does a
> whole lot of unnecessary rebuilds. In Books Online, DBCC SHOWCOINTIG, you have code that you can use
> in a job which only defragments the indexed with a fragmentation level over a certain threshold.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:B1DF0D93-7F2F-4D74-B207-2879B91A85AB@.microsoft.com...
> > Hi Tibor,
> > One quick question.
> >
> > The option which is available on Maintenance plan (under
> > optimizations->Reorganize data and index pages), will it perform both rebuild
> > and reorganize...
> > Does this maintenence plan optimization activity replaces both DBREINDEX and
> > INDEXDEFRAG operations?
> >
> > Regards,
> > Jaison
> >
> > "Tibor Karaszi" wrote:
> >
> >> Sorry, here's the URL:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> <planetthoughtful@.gmail.com> wrote in message
> >> news:1151478465.492923.232660@.i40g2000cwc.googlegroups.com...
> >> >
> >> > Tibor Karaszi wrote:
> >> >> This article is for 2000, but the essentials applies to 2005 as well. DBREINDEX is REBUILD and
> >> >> INDEXDEFRAG is REORGANIZE. Also note that REBUILD has an ONLINE option in 2005 (EE only).
> >> >
> >> > Hi Tibor,
> >> >
> >> > Sorry, which article, exactly? Were you intending to include a link?
> >> >
> >> > Thanks for your reply!
> >> >
> >> > Much warmth,
> >> >
> >> > planetthoughtful
> >> > --
> >> > "Lost in thought"
> >> > http://www.planetthoughtful.org
> >> >
> >>
> >>
>|||planetthoughtful@.gmail.com wrote:
> Hi All,
> Just wondering if anyone can give me some general advice on the
> practical differences between rebuilding and reorganizing indexes in
> sql server 2000?
> Basically, we have a database that occupies approx 28gb, of which
> approximately 4gb is indexes. In the 2 or so years that our app has
> been using this server, the indexes have never been rebuilt /
> reoganized to the best of our knowledge.
> In particular, we are hoping to minimize downtime while we see to this,
> so I'm wondering if anyone has any advice to give about whether a
> rebuild is generally quicker than a reorganize, or vice versa?
> Any help / advice appreciated!
> Much warmth,
> planetthoughtful
> --
> "lost in thought"
> http://www.planetthoughtful.org
>
Have a look at the stored procedure I've posted here:
http://www.realsqlguy.com/?p=10
Schedule this to run nightly, or whenever you have quiet time in your
database that will permit reindexing activity.|||Tracy McKibben wrote:
> planetthoughtful@.gmail.com wrote:
>> Hi All,
>> Just wondering if anyone can give me some general advice on the
>> practical differences between rebuilding and reorganizing indexes in
>> sql server 2000?
>> Basically, we have a database that occupies approx 28gb, of which
>> approximately 4gb is indexes. In the 2 or so years that our app has
>> been using this server, the indexes have never been rebuilt /
>> reoganized to the best of our knowledge.
>> In particular, we are hoping to minimize downtime while we see to this,
>> so I'm wondering if anyone has any advice to give about whether a
>> rebuild is generally quicker than a reorganize, or vice versa?
>> Any help / advice appreciated!
>> Much warmth,
>> planetthoughtful
>> --
>> "lost in thought"
>> http://www.planetthoughtful.org
> Have a look at the stored procedure I've posted here:
> http://www.realsqlguy.com/?p=10
> Schedule this to run nightly, or whenever you have quiet time in your
> database that will permit reindexing activity.
>
Sorry, if you pulled down that script, there is a bug in it,
search/replace went awry... The correction is posted at the URL above...