Index rebuild and fragmentation level

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Index rebuild and fragmentation level

26 posts, Page 1 of 3

1 2 3 »»»

Index rebuild and fragmentation level

Rate Topic

Display Mode

Topic Options

Author

Message

MW-309385

Posted Thursday, November 06, 2008 11:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45

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)

Post #598410

Elisabeth Rédei

Posted Thursday, November 06, 2008 12:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:56 AM
Points: 175, Visits: 676

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

Post #598448

MW-309385

Posted Thursday, November 06, 2008 12:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45

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

Post #598466

Rajaa

Posted Thursday, November 06, 2008 12:44 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 3:30 PM
Points: 15, Visits: 24

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

Post #598470

MW-309385

Posted Thursday, November 06, 2008 12:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45

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 ?

Post #598476

The_SQL_DBA

Posted Thursday, November 06, 2008 1:13 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:23 AM
Points: 794, Visits: 901

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

Post #598503

Rajaa

Posted Thursday, November 06, 2008 2:09 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 3:30 PM
Points: 15, Visits: 24

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)

Post #598553

SQL_DBA_3

Posted Thursday, November 06, 2008 3:27 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 21, 2012 9:26 AM
Points: 1,861, Visits: 652

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.

Post #598589

Matt Miller (#4)

Posted Thursday, November 06, 2008 5:43 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 6,997, Visits: 13,941

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?

Post #598623

MW-309385

Posted Thursday, November 06, 2008 7:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:37 AM
Points: 27, Visits: 45

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

Post #598638

« Prev Topic | Next Topic »

26 posts, Page 1 of 3

1 2 3 »»»

Permissions