Minimizing Fragmentation of table having Non-clustered Index.

Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.