Rebuilding clustered index

Working on a vendor database upgrade. They want the clustered index to be
rebuilt. I have huge table with more than 10 million records....have a
clustered index and 10 non-clustered indexes...
what are my options,
As an example lets take Orders table and CIX_Orders is the clustered index.
1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
with drop_existing
How does the above two differ?
Thanks very muchOn SQL 2000 and 2005 these two should be the same.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> Working on a vendor database upgrade. They want the clustered index to be
> rebuilt. I have huge table with more than 10 million records....have a
> clustered index and 10 non-clustered indexes...
> what are my options,
> As an example lets take Orders table and CIX_Orders is the clustered
> index.
> 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
> with drop_existing
> How does the above two differ?
> Thanks very much|||Thanks Kalen...
I am using SQL 2000
My questions is will my non-clustered indexes if I rebuild my clustered
index ?
"Kalen Delaney" wrote:
> On SQL 2000 and 2005 these two should be the same.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> > Working on a vendor database upgrade. They want the clustered index to be
> > rebuilt. I have huge table with more than 10 million records....have a
> > clustered index and 10 non-clustered indexes...
> >
> > what are my options,
> >
> > As an example lets take Orders table and CIX_Orders is the clustered
> > index.
> >
> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >
> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
> > with drop_existing
> >
> > How does the above two differ?
> >
> > Thanks very much
>
>|||Hi,
Yes it will rebuild all the non-clustered indexes if you rebuild the
clustered.
This is because the nonclustered index contains the keys of clustered
index. So to retake the new set of clustered keys; non clustered index is
rebuild.
Thanks
Hari
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||Not if you use one of these two methods, and you aren't redefining anything
about the indexes. If you are just rebuilding the indexes exactly as they
were for the purpose of removing fragmentation, the nonclustered indexes
will not have to be touched. That is why these two methods are preferred
over a separate drop index and create index.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||That is only true if the Clustered index is not unique. If it is unique and
you rebuild the clustered index it does not automatically rebuild the
non-clustered using DBREINDEX. In 2005 it does not matter if the clustered
index is unique or not since they handle the uniquifier in a much better
manor that does not change the uniquifier values when reindexed.
--
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23iVBCS%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yes it will rebuild all the non-clustered indexes if you rebuild the
> clustered.
> This is because the nonclustered index contains the keys of clustered
> index. So to retake the new set of clustered keys; non clustered index is
> rebuild.
> Thanks
> Hari
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>|||Hi Ranga
It is usually more important for the non-clustered indexes to be rebuilt
than clustered indexes. The non-clustered indexes purely provide support for
query performance whilst clustered indexes are really the table storage
structure, so non-clustered indexes always play a performance role whilst
clustered indexes only provide performance support sometimes. Given the
specialised role of non-clustered indexes, it's critical that they be
re-built if you're doing this for performance reasons. We often rebuild our
non-clustered indexes many times between clustered index rebuilds..
Regards,
Greg Linwood
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||Hi Greg
Can you elaborate on this?
While I agree that nonclustered index have more of a performance support
role in more cases, I don't see what that has to do with rebuilding them.
How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
In particular, if the statistics are up to date, and you're using the nc
index to find just a few rows, why is rebuilding a necessary thing?
Since clustered indexes are the table storage, any scan or partial scan of
the data is impacted by the fragmentation of the clustered index, making it
imperative that the clustered index be rebuilt.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Hi Ranga
> It is usually more important for the non-clustered indexes to be rebuilt
> than clustered indexes. The non-clustered indexes purely provide support
> for query performance whilst clustered indexes are really the table
> storage structure, so non-clustered indexes always play a performance role
> whilst clustered indexes only provide performance support sometimes. Given
> the specialised role of non-clustered indexes, it's critical that they be
> re-built if you're doing this for performance reasons. We often rebuild
> our non-clustered indexes many times between clustered index rebuilds..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>|||Hi Kalen
Re> "and you're using the nc index to find just a few rows". This isn't a
good assumption b/c in most OLTPs, ncix's are range-scanned as much as
they're seek'd. Any range or full scan should ideally occur within a ncix
where page density is higher & read io is therefore more efficient than it
can ever be in a cix.. Any range or full scan that occurs within a cix will
always be less efficient other than in the obscure scenario where all
columns in a table are actually required by the query.
Re> Since clustered indexes are the table storage, any scan or partial scan
of the data is impacted by the fragmentation.
Not if the ncix covers the query. In this case, the fragmentation of the
ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
all performance critical queries should be covered by ncixs, so this is
fairly important.
Re>How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
We have empirically measured proof and documented user feedback that
rebuilding ncixs alone usually improves the overall performance of otherwise
well configured oltp system. Happy to show you the data next time you're out
here too (c:
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> Hi Greg
> Can you elaborate on this?
> While I agree that nonclustered index have more of a performance support
> role in more cases, I don't see what that has to do with rebuilding them.
> How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> In particular, if the statistics are up to date, and you're using the nc
> index to find just a few rows, why is rebuilding a necessary thing?
> Since clustered indexes are the table storage, any scan or partial scan of
> the data is impacted by the fragmentation of the clustered index, making
> it imperative that the clustered index be rebuilt.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be rebuilt
>> than clustered indexes. The non-clustered indexes purely provide support
>> for query performance whilst clustered indexes are really the table
>> storage structure, so non-clustered indexes always play a performance
>> role whilst clustered indexes only provide performance support sometimes.
>> Given the specialised role of non-clustered indexes, it's critical that
>> they be re-built if you're doing this for performance reasons. We often
>> rebuild our non-clustered indexes many times between clustered index
>> rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>|||Thanks everyone for their feedback..this is a very interesting topic...I have
another Q..If the clustered index is part of PK constraint, what will the
exact syntax to rebuild it using the both methods (DBReindex and Create with
Drop_Existing)
Thanks,
Ranga
"Greg Linwood" wrote:
> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix will
> always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial scan
> of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
> all performance critical queries should be covered by ncixs, so this is
> fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of otherwise
> well configured oltp system. Happy to show you the data next time you're out
> here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> > Hi Greg
> >
> > Can you elaborate on this?
> >
> > While I agree that nonclustered index have more of a performance support
> > role in more cases, I don't see what that has to do with rebuilding them.
> >
> > How exactly are you finding that rebuilding helps with the performance
> > support of nonclustered indexes?
> >
> > In particular, if the statistics are up to date, and you're using the nc
> > index to find just a few rows, why is rebuilding a necessary thing?
> >
> > Since clustered indexes are the table storage, any scan or partial scan of
> > the data is impacted by the fragmentation of the clustered index, making
> > it imperative that the clustered index be rebuilt.
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> Hi Ranga
> >>
> >> It is usually more important for the non-clustered indexes to be rebuilt
> >> than clustered indexes. The non-clustered indexes purely provide support
> >> for query performance whilst clustered indexes are really the table
> >> storage structure, so non-clustered indexes always play a performance
> >> role whilst clustered indexes only provide performance support sometimes.
> >> Given the specialised role of non-clustered indexes, it's critical that
> >> they be re-built if you're doing this for performance reasons. We often
> >> rebuild our non-clustered indexes many times between clustered index
> >> rebuilds..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> Thanks Kalen...
> >> I am using SQL 2000
> >> My questions is will my non-clustered indexes if I rebuild my clustered
> >> index ?
> >>
> >>
> >>
> >> "Kalen Delaney" wrote:
> >>
> >> On SQL 2000 and 2005 these two should be the same.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> > Working on a vendor database upgrade. They want the clustered index
> >> > to be
> >> > rebuilt. I have huge table with more than 10 million records....have
> >> > a
> >> > clustered index and 10 non-clustered indexes...
> >> >
> >> > what are my options,
> >> >
> >> > As an example lets take Orders table and CIX_Orders is the clustered
> >> > index.
> >> >
> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >
> >> > 2.create clustered index [CIX_Orders] on
> >> > [dbo].[Orders_Temp]([OrderID])
> >> > with drop_existing
> >> >
> >> > How does the above two differ?
> >> >
> >> > Thanks very much
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>|||Hi Greg
Thanks for the detailed response. These are some very interesting points to
think about. However, you said:
> Re> Since clustered indexes are the table storage, any scan or partial
> scan of the data is impacted by the fragmentation.
> Not if the ncix covers the query.
But...if the ncix covers the query, then you don't have a scan of the data
level.
Which is what I was referring to.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix
> will always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial
> scan of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial.
> Ideally, all performance critical queries should be covered by ncixs, so
> this is fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of
> otherwise well configured oltp system. Happy to show you the data next
> time you're out here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg
>> Can you elaborate on this?
>> While I agree that nonclustered index have more of a performance support
>> role in more cases, I don't see what that has to do with rebuilding them.
>> How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> In particular, if the statistics are up to date, and you're using the nc
>> index to find just a few rows, why is rebuilding a necessary thing?
>> Since clustered indexes are the table storage, any scan or partial scan
>> of the data is impacted by the fragmentation of the clustered index,
>> making it imperative that the clustered index be rebuilt.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be rebuilt
>> than clustered indexes. The non-clustered indexes purely provide support
>> for query performance whilst clustered indexes are really the table
>> storage structure, so non-clustered indexes always play a performance
>> role whilst clustered indexes only provide performance support
>> sometimes. Given the specialised role of non-clustered indexes, it's
>> critical that they be re-built if you're doing this for performance
>> reasons. We often rebuild our non-clustered indexes many times between
>> clustered index rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million
>> > records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>>
>|||Kalen,
If the clustered index is built on a indentity column which is is always in
a sequence and ordered, will there be any need to rebuild the clustered index
?
Thanks,
Ranga
"Kalen Delaney" wrote:
> Hi Greg
> Thanks for the detailed response. These are some very interesting points to
> think about. However, you said:
> > Re> Since clustered indexes are the table storage, any scan or partial
> > scan of the data is impacted by the fragmentation.
> > Not if the ncix covers the query.
> But...if the ncix covers the query, then you don't have a scan of the data
> level.
> Which is what I was referring to.
> :-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> > Hi Kalen
> >
> > Re> "and you're using the nc index to find just a few rows". This isn't a
> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> > they're seek'd. Any range or full scan should ideally occur within a ncix
> > where page density is higher & read io is therefore more efficient than it
> > can ever be in a cix.. Any range or full scan that occurs within a cix
> > will always be less efficient other than in the obscure scenario where all
> > columns in a table are actually required by the query.
> >
> > Re> Since clustered indexes are the table storage, any scan or partial
> > scan of the data is impacted by the fragmentation.
> > Not if the ncix covers the query. In this case, the fragmentation of the
> > ncix is all that matters & fragmentation in the cix is immaterial.
> > Ideally, all performance critical queries should be covered by ncixs, so
> > this is fairly important.
> >
> > Re>How exactly are you finding that rebuilding helps with the performance
> > support of nonclustered indexes?
> > We have empirically measured proof and documented user feedback that
> > rebuilding ncixs alone usually improves the overall performance of
> > otherwise well configured oltp system. Happy to show you the data next
> > time you're out here too (c:
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> >> Hi Greg
> >>
> >> Can you elaborate on this?
> >>
> >> While I agree that nonclustered index have more of a performance support
> >> role in more cases, I don't see what that has to do with rebuilding them.
> >>
> >> How exactly are you finding that rebuilding helps with the performance
> >> support of nonclustered indexes?
> >>
> >> In particular, if the statistics are up to date, and you're using the nc
> >> index to find just a few rows, why is rebuilding a necessary thing?
> >>
> >> Since clustered indexes are the table storage, any scan or partial scan
> >> of the data is impacted by the fragmentation of the clustered index,
> >> making it imperative that the clustered index be rebuilt.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> Hi Ranga
> >>
> >> It is usually more important for the non-clustered indexes to be rebuilt
> >> than clustered indexes. The non-clustered indexes purely provide support
> >> for query performance whilst clustered indexes are really the table
> >> storage structure, so non-clustered indexes always play a performance
> >> role whilst clustered indexes only provide performance support
> >> sometimes. Given the specialised role of non-clustered indexes, it's
> >> critical that they be re-built if you're doing this for performance
> >> reasons. We often rebuild our non-clustered indexes many times between
> >> clustered index rebuilds..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> Thanks Kalen...
> >> I am using SQL 2000
> >> My questions is will my non-clustered indexes if I rebuild my clustered
> >> index ?
> >>
> >>
> >>
> >> "Kalen Delaney" wrote:
> >>
> >> On SQL 2000 and 2005 these two should be the same.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> > Working on a vendor database upgrade. They want the clustered index
> >> > to be
> >> > rebuilt. I have huge table with more than 10 million
> >> > records....have a
> >> > clustered index and 10 non-clustered indexes...
> >> >
> >> > what are my options,
> >> >
> >> > As an example lets take Orders table and CIX_Orders is the clustered
> >> > index.
> >> >
> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >
> >> > 2.create clustered index [CIX_Orders] on
> >> > [dbo].[Orders_Temp]([OrderID])
> >> > with drop_existing
> >> >
> >> > How does the above two differ?
> >> >
> >> > Thanks very much
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>|||Are you ever updating any of the rows?
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
> Kalen,
> If the clustered index is built on a indentity column which is is always
> in
> a sequence and ordered, will there be any need to rebuild the clustered
> index
> ?
> Thanks,
> Ranga
> "Kalen Delaney" wrote:
>> Hi Greg
>> Thanks for the detailed response. These are some very interesting points
>> to
>> think about. However, you said:
>> > Re> Since clustered indexes are the table storage, any scan or partial
>> > scan of the data is impacted by the fragmentation.
>> > Not if the ncix covers the query.
>> But...if the ncix covers the query, then you don't have a scan of the
>> data
>> level.
>> Which is what I was referring to.
>> :-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> > Hi Kalen
>> >
>> > Re> "and you're using the nc index to find just a few rows". This isn't
>> > a
>> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
>> > they're seek'd. Any range or full scan should ideally occur within a
>> > ncix
>> > where page density is higher & read io is therefore more efficient than
>> > it
>> > can ever be in a cix.. Any range or full scan that occurs within a cix
>> > will always be less efficient other than in the obscure scenario where
>> > all
>> > columns in a table are actually required by the query.
>> >
>> > Re> Since clustered indexes are the table storage, any scan or partial
>> > scan of the data is impacted by the fragmentation.
>> > Not if the ncix covers the query. In this case, the fragmentation of
>> > the
>> > ncix is all that matters & fragmentation in the cix is immaterial.
>> > Ideally, all performance critical queries should be covered by ncixs,
>> > so
>> > this is fairly important.
>> >
>> > Re>How exactly are you finding that rebuilding helps with the
>> > performance
>> > support of nonclustered indexes?
>> > We have empirically measured proof and documented user feedback that
>> > rebuilding ncixs alone usually improves the overall performance of
>> > otherwise well configured oltp system. Happy to show you the data next
>> > time you're out here too (c:
>> >
>> > Regards,
>> > Greg Linwood
>> > SQL Server MVP
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> >> Hi Greg
>> >>
>> >> Can you elaborate on this?
>> >>
>> >> While I agree that nonclustered index have more of a performance
>> >> support
>> >> role in more cases, I don't see what that has to do with rebuilding
>> >> them.
>> >>
>> >> How exactly are you finding that rebuilding helps with the performance
>> >> support of nonclustered indexes?
>> >>
>> >> In particular, if the statistics are up to date, and you're using the
>> >> nc
>> >> index to find just a few rows, why is rebuilding a necessary thing?
>> >>
>> >> Since clustered indexes are the table storage, any scan or partial
>> >> scan
>> >> of the data is impacted by the fragmentation of the clustered index,
>> >> making it imperative that the clustered index be rebuilt.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> >> Hi Ranga
>> >>
>> >> It is usually more important for the non-clustered indexes to be
>> >> rebuilt
>> >> than clustered indexes. The non-clustered indexes purely provide
>> >> support
>> >> for query performance whilst clustered indexes are really the table
>> >> storage structure, so non-clustered indexes always play a performance
>> >> role whilst clustered indexes only provide performance support
>> >> sometimes. Given the specialised role of non-clustered indexes, it's
>> >> critical that they be re-built if you're doing this for performance
>> >> reasons. We often rebuild our non-clustered indexes many times
>> >> between
>> >> clustered index rebuilds..
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> >> Thanks Kalen...
>> >> I am using SQL 2000
>> >> My questions is will my non-clustered indexes if I rebuild my
>> >> clustered
>> >> index ?
>> >>
>> >>
>> >>
>> >> "Kalen Delaney" wrote:
>> >>
>> >> On SQL 2000 and 2005 these two should be the same.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> >> > Working on a vendor database upgrade. They want the clustered
>> >> > index
>> >> > to be
>> >> > rebuilt. I have huge table with more than 10 million
>> >> > records....have a
>> >> > clustered index and 10 non-clustered indexes...
>> >> >
>> >> > what are my options,
>> >> >
>> >> > As an example lets take Orders table and CIX_Orders is the
>> >> > clustered
>> >> > index.
>> >> >
>> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >> >
>> >> > 2.create clustered index [CIX_Orders] on
>> >> > [dbo].[Orders_Temp]([OrderID])
>> >> > with drop_existing
>> >> >
>> >> > How does the above two differ?
>> >> >
>> >> > Thanks very much
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||Yes...
"Kalen Delaney" wrote:
> Are you ever updating any of the rows?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
> > Kalen,
> > If the clustered index is built on a indentity column which is is always
> > in
> > a sequence and ordered, will there be any need to rebuild the clustered
> > index
> > ?
> >
> > Thanks,
> > Ranga
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Greg
> >>
> >> Thanks for the detailed response. These are some very interesting points
> >> to
> >> think about. However, you said:
> >>
> >> > Re> Since clustered indexes are the table storage, any scan or partial
> >> > scan of the data is impacted by the fragmentation.
> >> > Not if the ncix covers the query.
> >>
> >> But...if the ncix covers the query, then you don't have a scan of the
> >> data
> >> level.
> >> Which is what I was referring to.
> >> :-)
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> >> > Hi Kalen
> >> >
> >> > Re> "and you're using the nc index to find just a few rows". This isn't
> >> > a
> >> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> >> > they're seek'd. Any range or full scan should ideally occur within a
> >> > ncix
> >> > where page density is higher & read io is therefore more efficient than
> >> > it
> >> > can ever be in a cix.. Any range or full scan that occurs within a cix
> >> > will always be less efficient other than in the obscure scenario where
> >> > all
> >> > columns in a table are actually required by the query.
> >> >
> >> > Re> Since clustered indexes are the table storage, any scan or partial
> >> > scan of the data is impacted by the fragmentation.
> >> > Not if the ncix covers the query. In this case, the fragmentation of
> >> > the
> >> > ncix is all that matters & fragmentation in the cix is immaterial.
> >> > Ideally, all performance critical queries should be covered by ncixs,
> >> > so
> >> > this is fairly important.
> >> >
> >> > Re>How exactly are you finding that rebuilding helps with the
> >> > performance
> >> > support of nonclustered indexes?
> >> > We have empirically measured proof and documented user feedback that
> >> > rebuilding ncixs alone usually improves the overall performance of
> >> > otherwise well configured oltp system. Happy to show you the data next
> >> > time you're out here too (c:
> >> >
> >> > Regards,
> >> > Greg Linwood
> >> > SQL Server MVP
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> >> >> Hi Greg
> >> >>
> >> >> Can you elaborate on this?
> >> >>
> >> >> While I agree that nonclustered index have more of a performance
> >> >> support
> >> >> role in more cases, I don't see what that has to do with rebuilding
> >> >> them.
> >> >>
> >> >> How exactly are you finding that rebuilding helps with the performance
> >> >> support of nonclustered indexes?
> >> >>
> >> >> In particular, if the statistics are up to date, and you're using the
> >> >> nc
> >> >> index to find just a few rows, why is rebuilding a necessary thing?
> >> >>
> >> >> Since clustered indexes are the table storage, any scan or partial
> >> >> scan
> >> >> of the data is impacted by the fragmentation of the clustered index,
> >> >> making it imperative that the clustered index be rebuilt.
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >>
> >> >>
> >> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> >> Hi Ranga
> >> >>
> >> >> It is usually more important for the non-clustered indexes to be
> >> >> rebuilt
> >> >> than clustered indexes. The non-clustered indexes purely provide
> >> >> support
> >> >> for query performance whilst clustered indexes are really the table
> >> >> storage structure, so non-clustered indexes always play a performance
> >> >> role whilst clustered indexes only provide performance support
> >> >> sometimes. Given the specialised role of non-clustered indexes, it's
> >> >> critical that they be re-built if you're doing this for performance
> >> >> reasons. We often rebuild our non-clustered indexes many times
> >> >> between
> >> >> clustered index rebuilds..
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> >> Thanks Kalen...
> >> >> I am using SQL 2000
> >> >> My questions is will my non-clustered indexes if I rebuild my
> >> >> clustered
> >> >> index ?
> >> >>
> >> >>
> >> >>
> >> >> "Kalen Delaney" wrote:
> >> >>
> >> >> On SQL 2000 and 2005 these two should be the same.
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >>
> >> >>
> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> >> > Working on a vendor database upgrade. They want the clustered
> >> >> > index
> >> >> > to be
> >> >> > rebuilt. I have huge table with more than 10 million
> >> >> > records....have a
> >> >> > clustered index and 10 non-clustered indexes...
> >> >> >
> >> >> > what are my options,
> >> >> >
> >> >> > As an example lets take Orders table and CIX_Orders is the
> >> >> > clustered
> >> >> > index.
> >> >> >
> >> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >> >
> >> >> > 2.create clustered index [CIX_Orders] on
> >> >> > [dbo].[Orders_Temp]([OrderID])
> >> >> > with drop_existing
> >> >> >
> >> >> > How does the above two differ?
> >> >> >
> >> >> > Thanks very much
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||Updates sometimes requires that rows have to split, if you change the
clustered key, or if the row becomes larger, and page splitting causes
fragmentation. So you might need to rebuild your clustered index.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:E547F7AF-D4F5-4F3D-A8D1-634B8FE65513@.microsoft.com...
> Yes...
> "Kalen Delaney" wrote:
>> Are you ever updating any of the rows?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
>> > Kalen,
>> > If the clustered index is built on a indentity column which is is
>> > always
>> > in
>> > a sequence and ordered, will there be any need to rebuild the clustered
>> > index
>> > ?
>> >
>> > Thanks,
>> > Ranga
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Greg
>> >>
>> >> Thanks for the detailed response. These are some very interesting
>> >> points
>> >> to
>> >> think about. However, you said:
>> >>
>> >> > Re> Since clustered indexes are the table storage, any scan or
>> >> > partial
>> >> > scan of the data is impacted by the fragmentation.
>> >> > Not if the ncix covers the query.
>> >>
>> >> But...if the ncix covers the query, then you don't have a scan of the
>> >> data
>> >> level.
>> >> Which is what I was referring to.
>> >> :-)
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> >> > Hi Kalen
>> >> >
>> >> > Re> "and you're using the nc index to find just a few rows". This
>> >> > isn't
>> >> > a
>> >> > good assumption b/c in most OLTPs, ncix's are range-scanned as much
>> >> > as
>> >> > they're seek'd. Any range or full scan should ideally occur within a
>> >> > ncix
>> >> > where page density is higher & read io is therefore more efficient
>> >> > than
>> >> > it
>> >> > can ever be in a cix.. Any range or full scan that occurs within a
>> >> > cix
>> >> > will always be less efficient other than in the obscure scenario
>> >> > where
>> >> > all
>> >> > columns in a table are actually required by the query.
>> >> >
>> >> > Re> Since clustered indexes are the table storage, any scan or
>> >> > partial
>> >> > scan of the data is impacted by the fragmentation.
>> >> > Not if the ncix covers the query. In this case, the fragmentation of
>> >> > the
>> >> > ncix is all that matters & fragmentation in the cix is immaterial.
>> >> > Ideally, all performance critical queries should be covered by
>> >> > ncixs,
>> >> > so
>> >> > this is fairly important.
>> >> >
>> >> > Re>How exactly are you finding that rebuilding helps with the
>> >> > performance
>> >> > support of nonclustered indexes?
>> >> > We have empirically measured proof and documented user feedback that
>> >> > rebuilding ncixs alone usually improves the overall performance of
>> >> > otherwise well configured oltp system. Happy to show you the data
>> >> > next
>> >> > time you're out here too (c:
>> >> >
>> >> > Regards,
>> >> > Greg Linwood
>> >> > SQL Server MVP
>> >> >
>> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> >> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> >> >> Hi Greg
>> >> >>
>> >> >> Can you elaborate on this?
>> >> >>
>> >> >> While I agree that nonclustered index have more of a performance
>> >> >> support
>> >> >> role in more cases, I don't see what that has to do with rebuilding
>> >> >> them.
>> >> >>
>> >> >> How exactly are you finding that rebuilding helps with the
>> >> >> performance
>> >> >> support of nonclustered indexes?
>> >> >>
>> >> >> In particular, if the statistics are up to date, and you're using
>> >> >> the
>> >> >> nc
>> >> >> index to find just a few rows, why is rebuilding a necessary thing?
>> >> >>
>> >> >> Since clustered indexes are the table storage, any scan or partial
>> >> >> scan
>> >> >> of the data is impacted by the fragmentation of the clustered
>> >> >> index,
>> >> >> making it imperative that the clustered index be rebuilt.
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >>
>> >> >>
>> >> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> >> >> Hi Ranga
>> >> >>
>> >> >> It is usually more important for the non-clustered indexes to be
>> >> >> rebuilt
>> >> >> than clustered indexes. The non-clustered indexes purely provide
>> >> >> support
>> >> >> for query performance whilst clustered indexes are really the
>> >> >> table
>> >> >> storage structure, so non-clustered indexes always play a
>> >> >> performance
>> >> >> role whilst clustered indexes only provide performance support
>> >> >> sometimes. Given the specialised role of non-clustered indexes,
>> >> >> it's
>> >> >> critical that they be re-built if you're doing this for
>> >> >> performance
>> >> >> reasons. We often rebuild our non-clustered indexes many times
>> >> >> between
>> >> >> clustered index rebuilds..
>> >> >>
>> >> >> Regards,
>> >> >> Greg Linwood
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> >> >> Thanks Kalen...
>> >> >> I am using SQL 2000
>> >> >> My questions is will my non-clustered indexes if I rebuild my
>> >> >> clustered
>> >> >> index ?
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Kalen Delaney" wrote:
>> >> >>
>> >> >> On SQL 2000 and 2005 these two should be the same.
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >>
>> >> >>
>> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> >> >> > Working on a vendor database upgrade. They want the clustered
>> >> >> > index
>> >> >> > to be
>> >> >> > rebuilt. I have huge table with more than 10 million
>> >> >> > records....have a
>> >> >> > clustered index and 10 non-clustered indexes...
>> >> >> >
>> >> >> > what are my options,
>> >> >> >
>> >> >> > As an example lets take Orders table and CIX_Orders is the
>> >> >> > clustered
>> >> >> > index.
>> >> >> >
>> >> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >> >> >
>> >> >> > 2.create clustered index [CIX_Orders] on
>> >> >> > [dbo].[Orders_Temp]([OrderID])
>> >> >> > with drop_existing
>> >> >> >
>> >> >> > How does the above two differ?
>> >> >> >
>> >> >> > Thanks very much
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||I'm really trying to point out to Ranga that he's on the wrong path in
trying to avoid rebuilding his non-clustered indexes whilst rebuilding his
clustered indexes. This is a very common trap for inexperienced DBAs or
software vendors (as appears to be the case this time) who don't work with
indexes a lot - they often miss the point that performace is mainly governed
by non-clustered indexes, not clustered indexes & that there's usually far
less to be gained from rebuilding CIXs than NCIXs. You'll usually get far
more performance improvement from rebuilding your NCIXs than your CIXs -
there are very good reasons for rebuildinig NCIXs without rebuilding CIXs,
but usually not the other way around..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u05NTXGyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hi Greg
> Thanks for the detailed response. These are some very interesting points
> to think about. However, you said:
>> Re> Since clustered indexes are the table storage, any scan or partial
>> scan of the data is impacted by the fragmentation.
>> Not if the ncix covers the query.
> But...if the ncix covers the query, then you don't have a scan of the data
> level.
> Which is what I was referring to.
> :-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> Hi Kalen
>> Re> "and you're using the nc index to find just a few rows". This isn't a
>> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
>> they're seek'd. Any range or full scan should ideally occur within a ncix
>> where page density is higher & read io is therefore more efficient than
>> it can ever be in a cix.. Any range or full scan that occurs within a cix
>> will always be less efficient other than in the obscure scenario where
>> all columns in a table are actually required by the query.
>> Re> Since clustered indexes are the table storage, any scan or partial
>> scan of the data is impacted by the fragmentation.
>> Not if the ncix covers the query. In this case, the fragmentation of the
>> ncix is all that matters & fragmentation in the cix is immaterial.
>> Ideally, all performance critical queries should be covered by ncixs, so
>> this is fairly important.
>> Re>How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> We have empirically measured proof and documented user feedback that
>> rebuilding ncixs alone usually improves the overall performance of
>> otherwise well configured oltp system. Happy to show you the data next
>> time you're out here too (c:
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg
>> Can you elaborate on this?
>> While I agree that nonclustered index have more of a performance support
>> role in more cases, I don't see what that has to do with rebuilding
>> them.
>> How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> In particular, if the statistics are up to date, and you're using the nc
>> index to find just a few rows, why is rebuilding a necessary thing?
>> Since clustered indexes are the table storage, any scan or partial scan
>> of the data is impacted by the fragmentation of the clustered index,
>> making it imperative that the clustered index be rebuilt.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be
>> rebuilt than clustered indexes. The non-clustered indexes purely
>> provide support for query performance whilst clustered indexes are
>> really the table storage structure, so non-clustered indexes always
>> play a performance role whilst clustered indexes only provide
>> performance support sometimes. Given the specialised role of
>> non-clustered indexes, it's critical that they be re-built if you're
>> doing this for performance reasons. We often rebuild our non-clustered
>> indexes many times between clustered index rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my
>> clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million
>> > records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the
>> > clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>>
>>
>