Index reorganization

With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
see witch index must be reorganized or reduilded (with
avg_fragmentation_in_perc).
Some indexes (clustured and nonclustered) are return many time, with a
different index_level and a different fragmentation for each level. Even
after a index reorganization, fragmentation stay high for a level and low for
others levels for this index.
I want to schedule a index maintenance, but in this case, some index are
always to reorganize ! Can I exclude these indexes by checking other fields
of sys.dm_db_index_physical_stats or anything else ?
Fillfactor for index is 90.
Thanks.Always go for the leaf level (visualize the index tree). I can't remember the value for that
reported by the DMV, but for a certain index it will be the one with most pages, like value 0. Also,
disregard indexes with less than say 100, 5000 or 1000 pages (MS recommendations is to not care if
fewer than 1000 pages). And remember when you have few pages, it is basically meaningless to talk
about fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> see witch index must be reorganized or reduilded (with
> avg_fragmentation_in_perc).
> Some indexes (clustured and nonclustered) are return many time, with a
> different index_level and a different fragmentation for each level. Even
> after a index reorganization, fragmentation stay high for a level and low for
> others levels for this index.
> I want to schedule a index maintenance, but in this case, some index are
> always to reorganize ! Can I exclude these indexes by checking other fields
> of sys.dm_db_index_physical_stats or anything else ?
> Fillfactor for index is 90.
> Thanks.
>|||1) don't bother rebuilding/defragging stuff with < 1000 pages or so.
2) If the database doesn't have lots of contiguous free space in it,
defragging won't do any good because the pages can't be laid down in
sequential order --> remain fragmented. Double the size of your db and try
some defragging.
3) Not so important on 2005, but I still like to defrag clustered index
first (if necessary) and then do NC indexes.
4) You may want to control your defrag especially the first time after
making a lot of free space, since you could cause a huge tlog.
TheSQLGuru
President
Indicium Resources, Inc.
"shwac" <shwac@.discussions.microsoft.com> wrote in message
news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> see witch index must be reorganized or reduilded (with
> avg_fragmentation_in_perc).
> Some indexes (clustured and nonclustered) are return many time, with a
> different index_level and a different fragmentation for each level. Even
> after a index reorganization, fragmentation stay high for a level and low
> for
> others levels for this index.
> I want to schedule a index maintenance, but in this case, some index are
> always to reorganize ! Can I exclude these indexes by checking other
> fields
> of sys.dm_db_index_physical_stats or anything else ?
> Fillfactor for index is 90.
> Thanks.
>|||Thanks for your help, all indexes with high fragmentation that I have are
less then 1000 pages. I will modify my script to take account of this
parameter.
"Tibor Karaszi" wrote:
> Always go for the leaf level (visualize the index tree). I can't remember the value for that
> reported by the DMV, but for a certain index it will be the one with most pages, like value 0. Also,
> disregard indexes with less than say 100, 5000 or 1000 pages (MS recommendations is to not care if
> fewer than 1000 pages). And remember when you have few pages, it is basically meaningless to talk
> about fragmentation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "shwac" <shwac@.discussions.microsoft.com> wrote in message
> news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
> > With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I can
> > see witch index must be reorganized or reduilded (with
> > avg_fragmentation_in_perc).
> >
> > Some indexes (clustured and nonclustered) are return many time, with a
> > different index_level and a different fragmentation for each level. Even
> > after a index reorganization, fragmentation stay high for a level and low for
> > others levels for this index.
> >
> > I want to schedule a index maintenance, but in this case, some index are
> > always to reorganize ! Can I exclude these indexes by checking other fields
> > of sys.dm_db_index_physical_stats or anything else ?
> >
> > Fillfactor for index is 90.
> >
> > Thanks.
> >
> >
>|||> 2) If the database doesn't have lots of contiguous free space in it,
> defragging won't do any good because the pages can't be laid down in
> sequential order --> remain fragmented. Double the size of your db and
> try some defragging.
Not true.
If you defrag the index, the pages will be ordered within the existing
extents allocated to the index - therefore allowing 8-page IOs during
readahead instead of single-page IOs. What you don't allow is 32-page IOs
during readahead because extents are not reordered to be contiguous.
If you rebuild the index, you'll at least get pages ordered within extents,
and the degree of extent contiguity will depend on how much contiguous free
space is available to be used in the various files in the database.
Adding extra space to a database does nothing for the defrag algorithm, only
for the rebuild algorithm.
Thanks
--
Paul S. Randal
Managing Director, www.SQLskills.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%23hkmcUp5HHA.4436@.TK2MSFTNGP03.phx.gbl...
> 1) don't bother rebuilding/defragging stuff with < 1000 pages or so.
> 2) If the database doesn't have lots of contiguous free space in it,
> defragging won't do any good because the pages can't be laid down in
> sequential order --> remain fragmented. Double the size of your db and
> try some defragging.
> 3) Not so important on 2005, but I still like to defrag clustered index
> first (if necessary) and then do NC indexes.
> 4) You may want to control your defrag especially the first time after
> making a lot of free space, since you could cause a huge tlog.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "shwac" <shwac@.discussions.microsoft.com> wrote in message
> news:2EC0728E-D3FE-4410-84CE-DA301D0FC6D0@.microsoft.com...
>> With dynamic view sys.dm_db_index_physical_stats in SQL SERVER 2005, I
>> can
>> see witch index must be reorganized or reduilded (with
>> avg_fragmentation_in_perc).
>> Some indexes (clustured and nonclustered) are return many time, with a
>> different index_level and a different fragmentation for each level. Even
>> after a index reorganization, fragmentation stay high for a level and low
>> for
>> others levels for this index.
>> I want to schedule a index maintenance, but in this case, some index are
>> always to reorganize ! Can I exclude these indexes by checking other
>> fields
>> of sys.dm_db_index_physical_stats or anything else ?
>> Fillfactor for index is 90.
>> Thanks.
>>
>