Real Time DSS: upgrade

Showing posts with label upgrade. Show all posts

Friday, March 23, 2012

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

Friday, March 9, 2012

Re-attaching databases after upgrading to SQL Server Enterprise Edition

Since Upgrading from SQL Standard Edition to SQL Server EE we have been unable to re-attach the database files that we detached before the upgrade, to SQL Server Management studio. We get an error message that reads:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file

Any help with this matter would be truly appreciated. Thank you.

You need to reset the NTFS permisions on the physical files. SQL 2005 locks out the physical files NTFS permissions when the databases are detached.

Reasons to go SQL 2005 over SQL 2000

Hi all,

I am working with an outside consultant to upgrade our SQL 7 instance. He wants to upgrade to SQL 2000 and I want to go to SQL 2005. I want to have concrete reasons backed up with documentation as to why we should go SQL 2005 as opposed to SQL 2000.

For eg. support (why 2005 is better, ie. longer support), security(More secure, documentation), management, stability (number of patches released last year) etc.

I'd really appreciate it if someone could point me to an appropriate resource.

Thanks all,

SA.

Online Articles Regarding 2005 Enhancements over 2000

http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx|||Derek thank you for the informative post.

SA.|||

Hi,

This message was a useful one.

Can u throw some light on the cost as well.

regards
Shiv

|||One more thing to note is the product life cycle of Microsoft products. Check out http://www.microsoft.com/sql/support/lifecycle.mspx for more information. Initial cost outlay may be frightening for some but operations and maintenance cost have to be considered as part of your overall cost|||

Bass_player,

Thanks for the info on the lifecycle of Microsoft products.

By mentioning operation and maintenance costs did u mean that it would cost more to support SQL 2000 in the long run since the mainstream support ends much sooner than SQL 2005.

SA3446.

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...[vbcol=seagreen]
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Hi
I'm trying to put a case together to encourage my employer to upgrade from
its SQL 2000 Enterprise farm to SQL Server 2005.
We're interested in Enterprise functionality, such as:
- Improvement in backups
- Monitoring improvements
- Resilience improvements (mirroring, replication, failover etc).
- Cost / payback case.
Can anyone point me in the right direction to be able to put together such a
case.
Thanks"Joe Spears" <joespears@.hotmail.co.uk> wrote in message
news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such
> a case.
>
What's New in SQL Server 2005
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
There's lots of cool stuff:
Mirroring.
Monitoring performance with Server reports and DMV's.
Transactional Replication.
The new database maintence plan desiner is great.
Database Mail.
SSIS.
But what does your organization struggle with? Stories that start "Remember
when we had that problem ...", help motivate the upgrade. Heve a concrete
vision for how SQL Server 2005 fits in your environment. That's the
business case.
David|||And do your COTS or applications support SQL 2005 yet?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23n5sSKQeGHA.3948@.TK2MSFTNGP03.phx.gbl...
> "Joe Spears" <joespears@.hotmail.co.uk> wrote in message
> news:u4mag.156498$tc.56861@.fe2.news.blueyonder.co.uk...
>> Hi
>> I'm trying to put a case together to encourage my employer to upgrade
>> from its SQL 2000 Enterprise farm to SQL Server 2005.
>> We're interested in Enterprise functionality, such as:
>> - Improvement in backups
>> - Monitoring improvements
>> - Resilience improvements (mirroring, replication, failover etc).
>> - Cost / payback case.
>> Can anyone point me in the right direction to be able to put together
>> such a case.
>
> What's New in SQL Server 2005
> http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
> There's lots of cool stuff:
> Mirroring.
> Monitoring performance with Server reports and DMV's.
> Transactional Replication.
> The new database maintence plan desiner is great.
> Database Mail.
> SSIS.
> But what does your organization struggle with? Stories that start
> "Remember when we had that problem ...", help motivate the upgrade. Heve
> a concrete vision for how SQL Server 2005 fits in your environment.
> That's the business case.
> David
>|||If you ask 10 people this question, you may get 10 different answers because
SQL2005 offers a wide array of new features and improvement. Personally, my
current reasons for upgrade are the following VLDB-related features (no
considerations given to rank these or other features systematically):
-- More robust DBCC
-- More granular backup/recovery
-- Table partitioning
-- x64 support
Linchi
"Joe Spears" wrote:
> Hi
> I'm trying to put a case together to encourage my employer to upgrade from
> its SQL 2000 Enterprise farm to SQL Server 2005.
> We're interested in Enterprise functionality, such as:
> - Improvement in backups
> - Monitoring improvements
> - Resilience improvements (mirroring, replication, failover etc).
> - Cost / payback case.
> Can anyone point me in the right direction to be able to put together such a
> case.
> Thanks
>
>
>|||Actually you'd probably get 40 different answers. :)
Start the process with all of the things that you struggle with currently.
1. Does reindexing lock up your tables and make applications unavailble? -
Online reindexing in 2005 Enterprise Edition
2. Do you have to do large scale data loads or archives that cause massive
contention making the data unavailable? - Table partitioning
3. Have you ever had a production issue where you simply scratched your head
because the engine is just a great big black box that you can't get detailed
diagnostics from? - Dynamic management views and functions
4. Have you ever needed to create an index in order to improve performance
at a critical point, but couldn't because it would lock up the entire
table? - Online index creation in the Enterprise Edition
5. Have you ever had an index corrupt causing everything to be
unavailable? - online index creation in Enterprise Edition
6. Have you ever had a table corrupted that required restoring the entire
database? - page level restores
7. Have you ever needed to restore just a single filegroup into a database
without taking everything else offline? - online restores in Enterprise
Edition
8. Have you ever needed to encrypt data within a table? - encryption
features
The list goes on and on and on. You'll note that I didn't pick on a single
feature that is classified as "cool". Management doesn't care about "cool".
They care about the databases being online, always. Each one of the items
above hit very definite problems that many people have run across in
production that caused databases to be unavailable and management to be
unhappy. 2005 eliminates all of the issues above. (You can keep filling in
the blanks with additional issues from your enviroment.)
If you need to make a case to management, talking about "cool" features is
going to get the request denied. Talking about things that happened in your
environment (and will likely happen again) which will no longer occur by
deploying SQL Server 2005 is going to get management to listen.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E309B075-01DC-4E40-B4E0-7FBA6F4272F3@.microsoft.com...
> If you ask 10 people this question, you may get 10 different answers
> because
> SQL2005 offers a wide array of new features and improvement. Personally,
> my
> current reasons for upgrade are the following VLDB-related features (no
> considerations given to rank these or other features systematically):
> -- More robust DBCC
> -- More granular backup/recovery
> -- Table partitioning
> -- x64 support
> Linchi
> "Joe Spears" wrote:
>> Hi
>> I'm trying to put a case together to encourage my employer to upgrade
>> from
>> its SQL 2000 Enterprise farm to SQL Server 2005.
>> We're interested in Enterprise functionality, such as:
>> - Improvement in backups
>> - Monitoring improvements
>> - Resilience improvements (mirroring, replication, failover etc).
>> - Cost / payback case.
>> Can anyone point me in the right direction to be able to put together
>> such a
>> case.
>> Thanks
>>
>>

Friday, March 23, 2012

Rebuilding clustered index

Rebuilding clustered index

Friday, March 9, 2012

Re-attaching databases after upgrading to SQL Server Enterprise Edition

Reasons to go SQL 2005 over SQL 2000

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Reasons for an Enterprise to upgrade from SQL 2000 to SQL 2005

Real Time DSS

Blog Archive

About Me