re-index takes twoce as long on 2005..

30 Oct 2006 3:18 PM

Methodology

Hi

I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly 4
hours. IF I run exactly the same command on a 2005 box against a restore of
exactly the same database, it takes almost exactly 8 hours.

am I missing someting here? does a re-ind on 2005 work differently than on
2000? is it doing something twice that I havent asked it to, and that it must
be doing by default?

Any help would be appreciated.

ta

31 Oct 2006 2:15 AM

Hari Prasad

Hi,

Can you see if you have enough room in LDF file while doing the reindex in
SQL 2005. Otherwise the autogrow will happen
and slow down the reindex command....

Thanks
Hari

Show quote

"Methodology" <Methodol***@discussions.microsoft.com> wrote in message
news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@microsoft.com...
> Hi
>
> I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly
> 4
> hours. IF I run exactly the same command on a 2005 box against a restore
> of
> exactly the same database, it takes almost exactly 8 hours.
>
> am I missing someting here? does a re-ind on 2005 work differently than on
> 2000? is it doing something twice that I havent asked it to, and that it
> must
> be doing by default?
>
> Any help would be appreciated.
>
> ta

31 Oct 2006 8:51 AM

Methodology

Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
slightly more thsan an hour quicker on my largest table....I think therefore
problem solved.

ta

Show quote

"Hari Prasad" wrote:

> Hi,
>
> Can you see if you have enough room in LDF file while doing the reindex in
> SQL 2005. Otherwise the autogrow will happen
> and slow down the reindex command....
>
> Thanks
> Hari
>
> "Methodology" <Methodol***@discussions.microsoft.com> wrote in message
> news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@microsoft.com...
> > Hi
> >
> > I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly
> > 4
> > hours. IF I run exactly the same command on a 2005 box against a restore
> > of
> > exactly the same database, it takes almost exactly 8 hours.
> >
> > am I missing someting here? does a re-ind on 2005 work differently than on
> > 2000? is it doing something twice that I havent asked it to, and that it
> > must
> > be doing by default?
> >
> > Any help would be appreciated.
> >
> > ta
>
>
>

31 Oct 2006 5:48 PM

Paul S Randal [MS]

They use exactly the same code. Is the hardware the same between 2000 and
2005, specifically number of CPUs and IO bandwidth?

--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

This posting is provided "AS IS" with no warranties, and confers no rights.

Show quote

"Methodology" <Methodol***@discussions.microsoft.com> wrote in message
news:F3B117CE-C3AF-4FCB-A328-A7FC66B7344B@microsoft.com...
> Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
> slightly more thsan an hour quicker on my largest table....I think
> therefore
> problem solved.
>
> ta
>
> "Hari Prasad" wrote:
>
>> Hi,
>>
>> Can you see if you have enough room in LDF file while doing the reindex
>> in
>> SQL 2005. Otherwise the autogrow will happen
>> and slow down the reindex command....
>>
>> Thanks
>> Hari
>>
>> "Methodology" <Methodol***@discussions.microsoft.com> wrote in message
>> news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@microsoft.com...
>> > Hi
>> >
>> > I run a reindex of a 130GB dabase on SQL 2000 and it takes almost
>> > exactly
>> > 4
>> > hours. IF I run exactly the same command on a 2005 box against a
>> > restore
>> > of
>> > exactly the same database, it takes almost exactly 8 hours.
>> >
>> > am I missing someting here? does a re-ind on 2005 work differently than
>> > on
>> > 2000? is it doing something twice that I havent asked it to, and that
>> > it
>> > must
>> > be doing by default?
>> >
>> > Any help would be appreciated.
>> >
>> > ta
>>
>>
>>