SQL 2005 Rebuild Index dosn't help fragmentation percent go down |
|
Author |
Message |
JeremyE
|
Posted: Fri Apr 06 14:22:17 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
I have a clustered index on a table with 594032 rows. Fill factor is 0.
avg_fragmentation_in_percent is 62 and the same even after rebuilding the
index.
I also tried UPDATE STATISTICS and no change. When I run the query below, I
get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
avg_page_space_used_in_percent is 91 and index_level 2 the
avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
Anyone out there who could explian what going on here. Thanks.
SELECT object_id, OBJECT_NAME(object_id), index_id,
avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
'DETAILED')
WHERE index_id <> 0
AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
AND object_id = 5575058
AND index_id = 1
SQL Server273
|
|
|
|
|
Aaron
|
Posted: Fri Apr 06 14:22:17 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
Can you describe the table with DDL (e.g. right-click, script table as,
create to new window).
>I have a clustered index on a table with 594032 rows. Fill factor is 0.
> avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> index.
> I also tried UPDATE STATISTICS and no change. When I run the query below,
> I
> get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> avg_page_space_used_in_percent is 91 and index_level 2 the
> avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> Anyone out there who could explian what going on here. Thanks.
>
> SELECT object_id, OBJECT_NAME(object_id), index_id,
> avg_fragmentation_in_percent, avg_page_space_used_in_percent
> FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> 'DETAILED')
> WHERE index_id <> 0
> AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent <
> 75)
> AND object_id = 5575058
> AND index_id = 1
>
>
|
|
|
|
|
SQL
|
Posted: Fri Apr 06 14:36:01 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
here is the DDL.
CREATE TABLE [dbo].[TestTable](
[TKey] [int] NOT NULL,
[CKey] [int] NOT NULL,
[EKey] [int] NOT NULL,
[IKey] [int] NOT NULL,
[AKey] [int] NOT NULL,
[ATKey] [int] NOT NULL,
[UpdateDate] [datetime] NULL,
[DeleteDate] [datetime] NULL,
CONSTRAINT [TestTable_PK] PRIMARY KEY CLUSTERED
(
[TKey] ASC,
[IKey] ASC,
[EKey] ASC,
[AKey] ASC,
[ATKey] ASC,
[CKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Also foreign keys on on keys. Didn't include those.
> Can you describe the table with DDL (e.g. right-click, script table as,
> create to new window).
>
>
>
>
>
> >I have a clustered index on a table with 594032 rows. Fill factor is 0.
> > avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> > index.
> > I also tried UPDATE STATISTICS and no change. When I run the query below,
> > I
> > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> > avg_page_space_used_in_percent is 91 and index_level 2 the
> > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> > Anyone out there who could explian what going on here. Thanks.
> >
> > SELECT object_id, OBJECT_NAME(object_id), index_id,
> > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> > 'DETAILED')
> > WHERE index_id <> 0
> > AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent <
> > 75)
> > AND object_id = 5575058
> > AND index_id = 1
> >
> >
>
>
>
|
|
|
|
|
AlejandroMesa
|
Posted: Fri Apr 06 15:28:46 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
SQL,
> When I run the query below, I get 2 rows
> - index_level 1 the
> avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent is 91
> and index_level 2 the
> avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
This is the information for non leaf pages (index_level 1 and 2) of the
clustered index.
AMB
> I have a clustered index on a table with 594032 rows. Fill factor is 0.
> avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> index.
> I also tried UPDATE STATISTICS and no change. When I run the query below, I
> get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> avg_page_space_used_in_percent is 91 and index_level 2 the
> avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> Anyone out there who could explian what going on here. Thanks.
>
> SELECT object_id, OBJECT_NAME(object_id), index_id,
> avg_fragmentation_in_percent, avg_page_space_used_in_percent
> FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> 'DETAILED')
> WHERE index_id <> 0
> AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
> AND object_id = 5575058
> AND index_id = 1
>
>
|
|
|
|
|
SQL
|
Posted: Fri Apr 06 15:40:00 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
Do I have to worry about avg_fragmentation_in_percent and
avg_page_space_used_in_percent for both index_levels or just the 1st level?
> SQL,
>
> > When I run the query below, I get 2 rows
> > - index_level 1 the
> > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent is 91
> > and index_level 2 the
> > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
>
> This is the information for non leaf pages (index_level 1 and 2) of the
> clustered index.
>
>
> AMB
>
>
> > I have a clustered index on a table with 594032 rows. Fill factor is 0.
> > avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> > index.
> > I also tried UPDATE STATISTICS and no change. When I run the query below, I
> > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> > avg_page_space_used_in_percent is 91 and index_level 2 the
> > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> > Anyone out there who could explian what going on here. Thanks.
> >
> > SELECT object_id, OBJECT_NAME(object_id), index_id,
> > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> > 'DETAILED')
> > WHERE index_id <> 0
> > AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
> > AND object_id = 5575058
> > AND index_id = 1
> >
> >
|
|
|
|
|
AlejandroMesa
|
Posted: Fri Apr 06 15:52:00 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
SQL,
I wish Kalen be close to help me with this question.
- avg_page_space_used_in_percent
Sure, but in this case SQL Server should be using the maximum from the page
because the index was created using (PAD_INDEX = OFF).
- avg_fragmentation_in_percent
Just to leaf pages.
AMB
> Do I have to worry about avg_fragmentation_in_percent and
> avg_page_space_used_in_percent for both index_levels or just the 1st level?
>
>
> > SQL,
> >
> > > When I run the query below, I get 2 rows
> > > - index_level 1 the
> > > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent is 91
> > > and index_level 2 the
> > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> >
> > This is the information for non leaf pages (index_level 1 and 2) of the
> > clustered index.
> >
> >
> > AMB
> >
> >
> > > I have a clustered index on a table with 594032 rows. Fill factor is 0.
> > > avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> > > index.
> > > I also tried UPDATE STATISTICS and no change. When I run the query below, I
> > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> > > avg_page_space_used_in_percent is 91 and index_level 2 the
> > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> > > Anyone out there who could explian what going on here. Thanks.
> > >
> > > SELECT object_id, OBJECT_NAME(object_id), index_id,
> > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> > > 'DETAILED')
> > > WHERE index_id <> 0
> > > AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
> > > AND object_id = 5575058
> > > AND index_id = 1
> > >
> > >
|
|
|
|
|
SQL
|
Posted: Fri Apr 06 16:10:01 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
Yes, I agree. When the index is created with (PAD_INDEX = OFF), sql server
uses the maximum in the page. But when changes happen to the database,
fragmentation happens, right?
So, are you telling that having a value of 62 for
avg_fragmentation_in_percent for index_level is ok???
How do I query just the leaf level pages and check for fragmentation?
Please explain.
> SQL,
>
> I wish Kalen be close to help me with this question.
>
> - avg_page_space_used_in_percent
>
> Sure, but in this case SQL Server should be using the maximum from the page
> because the index was created using (PAD_INDEX = OFF).
>
> - avg_fragmentation_in_percent
>
> Just to leaf pages.
>
>
> AMB
>
>
> > Do I have to worry about avg_fragmentation_in_percent and
> > avg_page_space_used_in_percent for both index_levels or just the 1st level?
> >
> >
> > > SQL,
> > >
> > > > When I run the query below, I get 2 rows
> > > > - index_level 1 the
> > > > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent is 91
> > > > and index_level 2 the
> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> > >
> > > This is the information for non leaf pages (index_level 1 and 2) of the
> > > clustered index.
> > >
> > >
> > > AMB
> > >
> > >
> > > > I have a clustered index on a table with 594032 rows. Fill factor is 0.
> > > > avg_fragmentation_in_percent is 62 and the same even after rebuilding the
> > > > index.
> > > > I also tried UPDATE STATISTICS and no change. When I run the query below, I
> > > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> > > > avg_page_space_used_in_percent is 91 and index_level 2 the
> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent is 6.
> > > > Anyone out there who could explian what going on here. Thanks.
> > > >
> > > > SELECT object_id, OBJECT_NAME(object_id), index_id,
> > > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> > > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL, NULL,
> > > > 'DETAILED')
> > > > WHERE index_id <> 0
> > > > AND (avg_fragmentation_in_percent > 10 OR avg_page_space_used_in_percent < 75)
> > > > AND object_id = 5575058
> > > > AND index_id = 1
> > > >
> > > >
|
|
|
|
|
Kalen
|
Posted: Fri Apr 06 16:32:46 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
SQL
Index levels 1 and 2 are upper levels of the index, and should not have that
many pages for only half a million rows. Can you tell us how many pages
there were? Page_count is another column in the output which you did not
include. If there are fewer than 8 pages, fragmentation is completely
meaningless, and if there are fewer than 100, fragmentation is mostly
meaningless.
Your concern should be with index level 0 which is the actual data. Since
your query only gave you these two rows, and not level 0, my assumption is
that the data itself did not meet your fragmentation filter.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
> Yes, I agree. When the index is created with (PAD_INDEX = OFF), sql
> server
> uses the maximum in the page. But when changes happen to the database,
> fragmentation happens, right?
> So, are you telling that having a value of 62 for
> avg_fragmentation_in_percent for index_level is ok???
> How do I query just the leaf level pages and check for fragmentation?
> Please explain.
>
>
>> SQL,
>>
>> I wish Kalen be close to help me with this question.
>>
>> - avg_page_space_used_in_percent
>>
>> Sure, but in this case SQL Server should be using the maximum from the
>> page
>> because the index was created using (PAD_INDEX = OFF).
>>
>> - avg_fragmentation_in_percent
>>
>> Just to leaf pages.
>>
>>
>> AMB
>>
>>
>> > Do I have to worry about avg_fragmentation_in_percent and
>> > avg_page_space_used_in_percent for both index_levels or just the 1st
>> > level?
>> >
>> >
>> > > SQL,
>> > >
>> > > > When I run the query below, I get 2 rows
>> > > > - index_level 1 the
>> > > > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent
>> > > > is 91
>> > > > and index_level 2 the
>> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
>> > > > is 6.
>> > >
>> > > This is the information for non leaf pages (index_level 1 and 2) of
>> > > the
>> > > clustered index.
>> > >
>> > >
>> > > AMB
>> > >
>> > >
>> > > > I have a clustered index on a table with 594032 rows. Fill factor
>> > > > is 0.
>> > > > avg_fragmentation_in_percent is 62 and the same even after
>> > > > rebuilding the
>> > > > index.
>> > > > I also tried UPDATE STATISTICS and no change. When I run the query
>> > > > below, I
>> > > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
>> > > > avg_page_space_used_in_percent is 91 and index_level 2 the
>> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
>> > > > is 6.
>> > > > Anyone out there who could explian what going on here. Thanks.
>> > > >
>> > > > SELECT object_id, OBJECT_NAME(object_id), index_id,
>> > > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
>> > > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL,
>> > > > NULL,
>> > > > 'DETAILED')
>> > > > WHERE index_id <> 0
>> > > > AND (avg_fragmentation_in_percent > 10 OR
>> > > > avg_page_space_used_in_percent < 75)
>> > > > AND object_id = 5575058
>> > > > AND index_id = 1
>> > > >
>> > > >
|
|
|
|
|
AlejandroMesa
|
Posted: Fri Apr 06 17:48:01 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
Kalen,
Thanks for jumping in.
AMB
> SQL
>
> Index levels 1 and 2 are upper levels of the index, and should not have that
> many pages for only half a million rows. Can you tell us how many pages
> there were? Page_count is another column in the output which you did not
> include. If there are fewer than 8 pages, fragmentation is completely
> meaningless, and if there are fewer than 100, fragmentation is mostly
> meaningless.
>
> Your concern should be with index level 0 which is the actual data. Since
> your query only gave you these two rows, and not level 0, my assumption is
> that the data itself did not meet your fragmentation filter.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
>
> > Yes, I agree. When the index is created with (PAD_INDEX = OFF), sql
> > server
> > uses the maximum in the page. But when changes happen to the database,
> > fragmentation happens, right?
> > So, are you telling that having a value of 62 for
> > avg_fragmentation_in_percent for index_level is ok???
> > How do I query just the leaf level pages and check for fragmentation?
> > Please explain.
> >
> >
> >> SQL,
> >>
> >> I wish Kalen be close to help me with this question.
> >>
> >> - avg_page_space_used_in_percent
> >>
> >> Sure, but in this case SQL Server should be using the maximum from the
> >> page
> >> because the index was created using (PAD_INDEX = OFF).
> >>
> >> - avg_fragmentation_in_percent
> >>
> >> Just to leaf pages.
> >>
> >>
> >> AMB
> >>
> >>
> >> > Do I have to worry about avg_fragmentation_in_percent and
> >> > avg_page_space_used_in_percent for both index_levels or just the 1st
> >> > level?
> >> >
> >> >
> >> > > SQL,
> >> > >
> >> > > > When I run the query below, I get 2 rows
> >> > > > - index_level 1 the
> >> > > > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent
> >> > > > is 91
> >> > > > and index_level 2 the
> >> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
> >> > > > is 6.
> >> > >
> >> > > This is the information for non leaf pages (index_level 1 and 2) of
> >> > > the
> >> > > clustered index.
> >> > >
> >> > >
> >> > > AMB
> >> > >
> >> > >
> >> > > > I have a clustered index on a table with 594032 rows. Fill factor
> >> > > > is 0.
> >> > > > avg_fragmentation_in_percent is 62 and the same even after
> >> > > > rebuilding the
> >> > > > index.
> >> > > > I also tried UPDATE STATISTICS and no change. When I run the query
> >> > > > below, I
> >> > > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> >> > > > avg_page_space_used_in_percent is 91 and index_level 2 the
> >> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
> >> > > > is 6.
> >> > > > Anyone out there who could explian what going on here. Thanks.
> >> > > >
> >> > > > SELECT object_id, OBJECT_NAME(object_id), index_id,
> >> > > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> >> > > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL,
> >> > > > NULL,
> >> > > > 'DETAILED')
> >> > > > WHERE index_id <> 0
> >> > > > AND (avg_fragmentation_in_percent > 10 OR
> >> > > > avg_page_space_used_in_percent < 75)
> >> > > > AND object_id = 5575058
> >> > > > AND index_id = 1
> >> > > >
> >> > > >
>
>
>
|
|
|
|
|
Kalen
|
Posted: Fri Apr 06 22:29:22 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
All you have to do is wish for it.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
> Kalen,
>
> Thanks for jumping in.
>
> AMB
>
>
>
>> SQL
>>
>> Index levels 1 and 2 are upper levels of the index, and should not have
>> that
>> many pages for only half a million rows. Can you tell us how many pages
>> there were? Page_count is another column in the output which you did not
>> include. If there are fewer than 8 pages, fragmentation is completely
>> meaningless, and if there are fewer than 100, fragmentation is mostly
>> meaningless.
>>
>> Your concern should be with index level 0 which is the actual data. Since
>> your query only gave you these two rows, and not level 0, my assumption
>> is
>> that the data itself did not meet your fragmentation filter.
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>>
>> > Yes, I agree. When the index is created with (PAD_INDEX = OFF), sql
>> > server
>> > uses the maximum in the page. But when changes happen to the database,
>> > fragmentation happens, right?
>> > So, are you telling that having a value of 62 for
>> > avg_fragmentation_in_percent for index_level is ok???
>> > How do I query just the leaf level pages and check for fragmentation?
>> > Please explain.
>> >
>> >
>> >> SQL,
>> >>
>> >> I wish Kalen be close to help me with this question.
>> >>
>> >> - avg_page_space_used_in_percent
>> >>
>> >> Sure, but in this case SQL Server should be using the maximum from the
>> >> page
>> >> because the index was created using (PAD_INDEX = OFF).
>> >>
>> >> - avg_fragmentation_in_percent
>> >>
>> >> Just to leaf pages.
>> >>
>> >>
>> >> AMB
>> >>
>> >>
>> >> > Do I have to worry about avg_fragmentation_in_percent and
>> >> > avg_page_space_used_in_percent for both index_levels or just the
>> >> > 1st
>> >> > level?
>> >> >
>> >> >
>> >> > > SQL,
>> >> > >
>> >> > > > When I run the query below, I get 2 rows
>> >> > > > - index_level 1 the
>> >> > > > avg_fragmentation_in_percent is 62,
>> >> > > > avg_page_space_used_in_percent
>> >> > > > is 91
>> >> > > > and index_level 2 the
>> >> > > > avg_fragmentation_in_percent is 0,
>> >> > > > avg_page_space_used_in_percent
>> >> > > > is 6.
>> >> > >
>> >> > > This is the information for non leaf pages (index_level 1 and 2)
>> >> > > of
>> >> > > the
>> >> > > clustered index.
>> >> > >
>> >> > >
>> >> > > AMB
>> >> > >
>> >> > >
>> >> > > > I have a clustered index on a table with 594032 rows. Fill
>> >> > > > factor
>> >> > > > is 0.
>> >> > > > avg_fragmentation_in_percent is 62 and the same even after
>> >> > > > rebuilding the
>> >> > > > index.
>> >> > > > I also tried UPDATE STATISTICS and no change. When I run the
>> >> > > > query
>> >> > > > below, I
>> >> > > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is
>> >> > > > 62,
>> >> > > > avg_page_space_used_in_percent is 91 and index_level 2 the
>> >> > > > avg_fragmentation_in_percent is 0,
>> >> > > > avg_page_space_used_in_percent
>> >> > > > is 6.
>> >> > > > Anyone out there who could explian what going on here. Thanks.
>> >> > > >
>> >> > > > SELECT object_id, OBJECT_NAME(object_id), index_id,
>> >> > > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
>> >> > > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL,
>> >> > > > NULL,
>> >> > > > 'DETAILED')
>> >> > > > WHERE index_id <> 0
>> >> > > > AND (avg_fragmentation_in_percent > 10 OR
>> >> > > > avg_page_space_used_in_percent < 75)
>> >> > > > AND object_id = 5575058
>> >> > > > AND index_id = 1
>> >> > > >
>> >> > > >
>>
>>
>>
|
|
|
|
|
SQL
|
Posted: Mon Apr 09 10:14:01 CDT 2007 |
Top |
SQL Server Developer >> SQL 2005 Rebuild Index dosn't help fragmentation percent go down
Thanks Kalen.
I looked at index level 0, avg_fragmentation_in_percent is 26. The
page_count is also 26 and as you said the number of rows are less that
fragmentation is meaningless. Thanks again.
> SQL
>
> Index levels 1 and 2 are upper levels of the index, and should not have that
> many pages for only half a million rows. Can you tell us how many pages
> there were? Page_count is another column in the output which you did not
> include. If there are fewer than 8 pages, fragmentation is completely
> meaningless, and if there are fewer than 100, fragmentation is mostly
> meaningless.
>
> Your concern should be with index level 0 which is the actual data. Since
> your query only gave you these two rows, and not level 0, my assumption is
> that the data itself did not meet your fragmentation filter.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
>
> > Yes, I agree. When the index is created with (PAD_INDEX = OFF), sql
> > server
> > uses the maximum in the page. But when changes happen to the database,
> > fragmentation happens, right?
> > So, are you telling that having a value of 62 for
> > avg_fragmentation_in_percent for index_level is ok???
> > How do I query just the leaf level pages and check for fragmentation?
> > Please explain.
> >
> >
> >> SQL,
> >>
> >> I wish Kalen be close to help me with this question.
> >>
> >> - avg_page_space_used_in_percent
> >>
> >> Sure, but in this case SQL Server should be using the maximum from the
> >> page
> >> because the index was created using (PAD_INDEX = OFF).
> >>
> >> - avg_fragmentation_in_percent
> >>
> >> Just to leaf pages.
> >>
> >>
> >> AMB
> >>
> >>
> >> > Do I have to worry about avg_fragmentation_in_percent and
> >> > avg_page_space_used_in_percent for both index_levels or just the 1st
> >> > level?
> >> >
> >> >
> >> > > SQL,
> >> > >
> >> > > > When I run the query below, I get 2 rows
> >> > > > - index_level 1 the
> >> > > > avg_fragmentation_in_percent is 62, avg_page_space_used_in_percent
> >> > > > is 91
> >> > > > and index_level 2 the
> >> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
> >> > > > is 6.
> >> > >
> >> > > This is the information for non leaf pages (index_level 1 and 2) of
> >> > > the
> >> > > clustered index.
> >> > >
> >> > >
> >> > > AMB
> >> > >
> >> > >
> >> > > > I have a clustered index on a table with 594032 rows. Fill factor
> >> > > > is 0.
> >> > > > avg_fragmentation_in_percent is 62 and the same even after
> >> > > > rebuilding the
> >> > > > index.
> >> > > > I also tried UPDATE STATISTICS and no change. When I run the query
> >> > > > below, I
> >> > > > get 2 rows - index_level 1 the avg_fragmentation_in_percent is 62,
> >> > > > avg_page_space_used_in_percent is 91 and index_level 2 the
> >> > > > avg_fragmentation_in_percent is 0, avg_page_space_used_in_percent
> >> > > > is 6.
> >> > > > Anyone out there who could explian what going on here. Thanks.
> >> > > >
> >> > > > SELECT object_id, OBJECT_NAME(object_id), index_id,
> >> > > > avg_fragmentation_in_percent, avg_page_space_used_in_percent
> >> > > > FROM sys.dm_db_index_physical_stats (DB_ID('Test'), NULL, NULL,
> >> > > > NULL,
> >> > > > 'DETAILED')
> >> > > > WHERE index_id <> 0
> >> > > > AND (avg_fragmentation_in_percent > 10 OR
> >> > > > avg_page_space_used_in_percent < 75)
> >> > > > AND object_id = 5575058
> >> > > > AND index_id = 1
> >> > > >
> >> > > >
>
>
>
|
|
|
|
|
|
|