Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Index rebuild and fragmentation level Expand / Collapse
Author
Message
Posted Thursday, November 6, 2008 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 5, 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
Posted Thursday, November 6, 2008 12:14 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 175, Visits: 744
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
Posted Thursday, November 6, 2008 12:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 5, 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
Posted Thursday, November 6, 2008 12:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 2008 3:30 PM
Points: 15, Visits: 24
Did you try DBCC and check for more information on fragments?

Post #598470
Posted Thursday, November 6, 2008 12:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 5, 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
Posted Thursday, November 6, 2008 1:13 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:15 PM
Points: 804, Visits: 935
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
Posted Thursday, November 6, 2008 2:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 6, 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
Posted Thursday, November 6, 2008 3:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Thursday, November 6, 2008 5:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 7,122, Visits: 15,031
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
Posted Thursday, November 6, 2008 7:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 5, 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse