Index rebuild and fragmentation level

Question

Post reply

Index rebuild and fragmentation level

MW-309385

Old Hand

Points: 341
More actions
November 6, 2008 at 11:27 am

#195766

I ran the following command to rebuild an index.
alter index inx_charges1 on charges
rebuild
Then I ran the following command to check the statistics.
Why do I get a fragmentation percentage of 100% ( which is bad ) after rebuilding the index ?
select index_id,avg_fragmentation_in_percent, page_count,
index_level, index_depth,
avg_page_space_used_in_percent,object_id,*
FROM
sys.dm_db_index_physical_stats( DB_ID('Ngprod'), Object_ID('Charges'),
NULL,NULL, 'DETAILED' )
WHERE
index_id <> 0
I just copied a portion of the output for index_id=2
Question: So why is it at index_level 2 we have a
fragmentation percent of 100% ( very bad )
index_id avg_fragmentation page_count index_level
---------------------------------- ------------------
2 4.33088369903497 38237 0
2 100 233 1
2 0 1 2
(3 row(s) affected)

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

Because that sounds so very strange I just have to ask you; is inx_charges1 really indid=2 of that table?

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

MW-309385 Old Hand Points: 341 More actions · Answer 2

Yes

This table has about 18 indexes. I tried several of them ( Tried the Alter Index command using other indexes ) and some of them returned the same result. That is, at the INDEX LEVEL 2 the fragmentation percent is 100% ?

By th way, we do use a raid system However, the table is not a partiioned table ( for sure ).

Rajaa SSC Enthusiast Points: 183 More actions · Answer 3

Did you try DBCC and check for more information on fragments?

MW-309385 Old Hand Points: 341 More actions · Answer 4

Not sure how to use the DBCC command. Any advice ?

Also this is a large table I don't want to slow down the system

if this command is resource intensive ?

Anyway send us the command please ?

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

use this : DBCC SHOWCONTIG(tablename)

In the results check for the logical scan fragmentation, which ideally should be 0.01%

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Rajaa SSC Enthusiast Points: 183 More actions · Answer 6

Yes it may slow down the performance, as it uses parallel sessions to execute the DBCC.

So execute it after peak hours. It took 5 mins in my system for the big table.

DBCC SHOWCONTIG (tablename)

SQL_DBA_3 SSCarpal Tunnel Points: 4772 More actions · Answer 7

use DBCC SHOWCONTIG(tablename) WITH FAST .

This will not lock the table while execution and also gives you results quicker(in seconds).

Then check logical scan fragmentation .It should be less than 1% for an index which was just rebuilt.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

It may be that your indexes are so small, the rebuild process doesn't do anything to help with fragmentation. Below a certain point, fragmentation is essentially disregarded because the entire index will end up being loadd in memeory anyway.

Paul Randal (who's the resident authority on the matter) mentioned something like anything less than 1,000 pages in the index probably wouldn't defrag.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MW-309385 Old Hand Points: 341 More actions · Answer 9

#1 The table is very large ( well above a million rows ).

#2 If the page count in small ( less than 1000 ) you said it would not fragment. So why does it then give a 100% ?

Take a look at the output. At the index level 1 it is 0%

This is clear since there is only one page involved.

If you say that since the page count is small ( less than 1000 ) why did it say 100% ? It could have said 0% as well?

Also before I ran the "Alter index command... " I didn't see 100% on any of the indexes at any level ?

This is confusing ?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

1. If I'm reading your results correctly - your index is only 233 pages big. So - while the table might be large - your index is apparently not.

2. I said that if it's below 1000, it would not DEfragment. Meaning - it will ALWAYS show as fragmented. The way the algorithm works (according to Paul again), it will figure out that there will be no benefit in defragmenting it, so it won't.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

MW-309385 Old Hand Points: 341 More actions · Answer 11

Hello

The index is not small and not just 233 pages. Take a look. Only level 2 of the index has 233 pages. Level 3 has 38237 pages.

So it is a large index ( because it has several levels )

Take a look at the OUTPUT that is pasted below.

Ok, so you are saying when the # pages is less than 1000, the algorithm does not even bother about defragmenting.

Fine! Well then, before I ran the "Alter index command.." why did it

show a value between 0 and 100 ( can't remember the exact value ) ?

Here is the output

index_id avg_fragmentation page_count index_level

---------------------------------- ------------------

2 4.33088369903497 38237 0

2 100 233 1

2 0 1 2

(3 row(s) affected)

------------------------------------------------------

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 12

Hi,

I can see how it would be true for a handful of pages (less than 8) but since the index is created from scratch, I for one cannot see why the pages would be allocated from all over the place.

You mentioned it happens for ALL tables (with more or equal than 3 levels) and always index level 2. Is it so that if there are more levels, it is still just level 2? I suppose some of them have clustered indexes and some are heaps, or?

I am still very puzzled, do you get the same result if you do CREATE INDEX .... WITH DROP_EXISTING?

Which service pack level are you on?

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

MW-309385 Old Hand Points: 341 More actions · Answer 13

Hello

This is a production database. I am not allowed to drop indexes and create them from scratch.

I guess the previous reply is correct to assume. He said when the

number of pages is less than 1000, SQl server does not bother to defragment the index ( I mean that part of the index that sits on level 2 )

I am going to do a few more ( I mean index rebuilds ) today and find out whether the results are consistent with what he says.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 14

Look below for the difference in the way you use the dbcc showcontig command

--DBCC SHOWCONTIG(TABLE_PROD_RES)

DBCC SHOWCONTIG scanning 'TABLE_PROD_RES' table...

Table: 'TABLE_PROD_RES' (1241211622); index ID: 1, database ID: 5

TABLE level scan performed.

- Pages Scanned................................: 748041

- Extents Scanned..............................: 93513

- Extent Switches..............................: 93526

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.98% [93506:93527]

- Logical Scan Fragmentation ..................: 0.01%

- Extent Scan Fragmentation ...................: 10.43%

- Avg. Bytes Free per Page.....................: 351.9

- Avg. Page Density (full).....................: 95.65%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--DBCC SHOWCONTIG(TABLE_PROD_RES) WITH FAST