SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index rebuild and fragmentation level


Index rebuild and fragmentation level

Author
Message
MW-309385
MW-309385
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 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)
Elisabeth Rédei
Elisabeth Rédei
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 788
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
MW-309385
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 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 ).
Rajaa
Rajaa
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 24
Did you try DBCC and check for more information on fragments?
MW-309385
MW-309385
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 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 ?
The_SQL_DBA
The_SQL_DBA
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 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."
Rajaa
Rajaa
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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)
SQL_DBA_3
SQL_DBA_3
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 658
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)
Matt Miller (4)
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27784 Visits: 18995
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
MW-309385
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 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 ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search