Rebuilding dimensions with huge fact tables

Hi,

I have a dimension which is used in two Measure Groups. It has many levels (let's say 10). One fact table handles level 1-8, the other 8-10.

The facts are huge, about 2 billion records for the first table (for level 1-8) and 100 million records for 8-10.

We discussed a fitting partitioning schema and this will not be the big issue.

Our problem is, that in the last levels (9-10) members can move from one parent to another. That means that the dimension structure changes, it has to be updated which also means a reprocession of all partitions related to this dimension.

This is really a problem because of the first fact table (2 billion records). I shouldn't be that big deal for the second table.

My question is: Since the structure of level 1-8 is quite fixed is there any way to get around reprocessing the partitions of the first fact table? Their aggregations will not change! It's only the aggregations of the lower level which are not stored in that fact table!

Any idea?

You have a classic case of slowly changing dimension. I dont remember which number 1, 2, or 3 given to this case. You can search for a term "slowly changing dimensions" you should get a lot of information for this design.

To make a story short: You can avoid re-processing your partitions by doing Update to dimension instead of Full Process. When issuing ProcessUpdate for your dimension, you can still get your members moving from one parent to another, but the data in the partitions will not be lost.

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

|||

Edward,

thanks for your reply. This is a SCD Type 1 scenario since I don't save any history...

Are you sure that an update of a dimension doen't affect the aggregations? That's what BOL says:

Process Update

Forces a re-read of data and an update of dimension attributes. Flexible aggregations and indexes on related partitions will be dropped. For example, this processing option can add new members to a dimension and force a complete re-read of the data to update object attributes. This processing option is supported for dimensions and mining models.

So the question might be what "flexible aggregations" are?! But I guess that all aggregations are flexible...

Regards,

|||

That correct. Updating of dimension will cause Analysis Server dropping flexible aggregations.

But that is way different from what your initial concern of having fully to re-process all the partitions in your cube.
Processing of aggregations will not require Analysis Server to read data again from relational database. Aggregations are built based on the data that already exists in your partitions.

If you think of it. It is logical that Analysis Server drops aggregations. Aggregations are being pre-calculated totals are no longer valid as soon as you move memeber from one parent to another.

Now about difference between flexible and rigid aggregations. It has to do with exactly the topic of this discussion. When defining relationship between attributes as flexible, you're telling the server that members of the child attribute could move from one parent to another and therefore any aggregaton build based on these attribures will be dropped in case of incremental update. You have an option to mark relationship as ridid. In this case if you try to move the member, you will get an error during processing.

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

|||

Edward,

thanks for your reply, things are getting clearer now...

So if you have a processed partition with aggregations defined and you do a "process update" of one or more dimensions, you'll still have the data in the partitions but then without aggregations and indices. I guess you can still query the cube but it will be slow. To speed it up again you have to do a "process index" to recreate aggregations (I still get a litte bit confused about idecies and aggregations, I understand that you always have indeces but you can add aggregations in a partition if you want to).

How big is the performance inpact on having flexible dimensions (if there is any)?

So it's right that aggregations on rigid relationships are not dropped in an update?! So having them i.e. on a time dimension keeps aggregations on time even when you "process update" the dimension?! What about other dimensions? If you "process update" your product dimension are aggregations on the region dimension also dropped? You only wrote that you get an error when you move a member in a rigid relationship. But there has to be an advantage as well ;-)

I'm really missing something like a "incremental" update of a dimension... This would mean that keys have to keep the same, only member names can be change. New members can be added but existing member couldn't move. That would be perfect for lots of cases, because you can add new members to a dimension without doing any processing of aggregations, just because any existing data will not be changed. But you can i.e. process the partition with the "actual" data and then you could take advantage of the new added members...

Thanks,

|||

So if you have a processed partition with aggregations defined and you do a "process update" of one or more dimensions, you'll still have the data in the partitions but then without aggregations and indices. I guess you can still query the cube but it will be slow. To speed it up again you have to do a "process index" to recreate aggregations (I still get a litte bit confused about idecies and aggregations, I understand that you always have indeces but you can add aggregations in a partition if you want to).

<E.M> You got this one right.

How big is the performance inpact on having flexible dimensions (if there is any)?

<E.M> There is no performance difference per-se between flexible and rigid aggregations. It is just that flexible once are being dropped during update of the dimension.

So it's right that aggregations on rigid relationships are not dropped in an update?! So having them i.e. on a time dimension keeps aggregations on time even when you "process update" the dimension?!

<E.M> Right here.

What about other dimensions? If you "process update" your product dimension are aggregations on the region dimension also dropped? You only wrote that you get an error when you move a member in a rigid relationship. But there has to be an advantage as well ;-)

<E.M> The aggregations are not per dimension. A single aggregation could will be defined to have aggregates across several dimensions. For instance totals for Product category and Year could be one aggregation. For aggregation to be rigid, it should be based on attributes with Rigid relationships across all dimensions "participating" in it.

I'm really missing something like a "incremental" update of a dimension... This would mean that keys have to keep the same, only member names can be change. New members can be added but existing member couldn't move. That would be perfect for lots of cases, because you can add new members to a dimension without doing any processing of aggregations, just because any existing data will not be changed. But you can i.e. process the partition with the "actual" data and then you could take advantage of the new added members...

<E.M> You are correct here again. Many users find "incremental" update of the dimension very useful. But in many cases you still need to have ability to move memeber from one parent to another, delete a member. Sometimes you have a lot of historical data in many partitions and cannot afford to re-process.

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

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](
1;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](&
#91;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...
>
>|||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...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||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...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||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...
>|||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...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||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...
>|||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...
>|||Thanks everyone for their feedback..this is a very interesting topic...I hav
e
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 wil
l
> 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 sca
n
> 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 otherwi
se
> well configured oltp system. Happy to show you the data next time you're o
ut
> 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...
>
>

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
>>
>>
>>
>>
>

Rebuilding a huge clustered index

I have a client that has a 800GB table. The current clustered index on this table is of low selectivity and is causing index scans on queries. I wish to drop the current index and create a new one that is of more use. What i really want to know is, what is the fastest method of rebuilding such a huge index? What would be the storage requirements to process this?

That is indeed a very big table. Perhaps, you want to use index defrag (dbcc indexdefrag) instead of a full rebuild.

A good article on index defrag (sql2k) is here:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

If you're on sql2k5, take a look at this:

http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

|||

Defragmenting the current index won't help, since you want a new index on different column(s).

If you are running SQL Server 2005 Enterprise Edition, you might try creating a new, non-clustered index with the ONLINE option and with MAXDOP set to something like 25% of your number of available CPU's (so it does not peg all the CPU's while building the index). This would be a lot less disruptive than dropping the clustered index and building a new one. This is assuming you need to stay online while you are doing this.

Without knowing the table schema, its hard to estimate the storage requirements for this.

|||The fastest way to rebuild a new index or create a new clustered index is to build/rebuild it in OFFLINE mode. If you rebuildin OFFLINE mode, we do minimally logging in the engine (we only log complete pages instead of individual inserts).

The space requirements for ONLINE / OFFLINE are simular: you need space for the original index, you need space for the new index (simular size to the original index), and if you build the clustered index on new columns, you need space in tempdb as SQL Server will be doing a SORT before creating the new indexes. This totals on about 2.2 times the size of the original index (but could be larger, the 2.5 is just a rough guess)

So if perf is your only concern , use OFFLINE. The drawback is the table is offline, so other clients cannot access the table while the rebuild is in progress.

Also, the MAXDOP option will work for OFFLINE rebuild as well, so you can specify the number of CPU's you want to use during the rebuild.

Thanks,