SQL Server 2005 Index rebuild/Reorganize

Question

SQL Server 2005 Index rebuild/Reorganize

sqlserver_newbie-1128231

Newbie

Points: 8
More actions
July 13, 2009 at 7:43 pm

#133756

Hi,
I'm trying to determine when to rebuild/reorganize indexes. I ran the select statements below to check the fragmentation of the indexes in a database and tried to rebuils/reorganize based on the value returned by avg_fragmentation_in_percent for the index. However, 50% of the times rebuild/reorganize does not change the value of avg_fragmentation_in_percent
and hence I am not sure if there are other values I should look at in the sys.dm_db_index_physical_stats view when determining whether an index is a candidate for a rebuild/reorganise. I am fairly new to sql server and would really appreciate some insight into this topic.
for example :
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('HelpBox'), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 30;
Returns: (tHE ATTACHMENT HAS MUCH BETTER FORMATING)
Database_Id Object_id depth level avg_fragmentation Fragmentation_count Frag. Size in pages Page_Count
12 69575286 2 0 50 2 1 2
12 277576027 2 0 50 2 1 2
12 437576597 2 0 92.59259259 27 1 27
12 562101043 2 0 50 3 1.333333333 4
12 597577167 2 0 94.28571429 35 1 35
12 789577851 2 0 37.83783784 122 2.12295082 259
12 1042102753 2 0 95.83333333 24 1 24
12 1733581214 2 0 80 5 1 5
The index_type for all indexes is CLUSTERED_INDEX
The allocation_unit_type_desc is IN_ROW_DATA

Viewing 7 posts - 1 through 7 (of 7 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

The Size in pages column shows that your tables are very small. For such small tables you will not see much difference after rebuilding the index.

What you are trying to do is correct. You are on the right path. Try it on big tables.

sqlserver_newbie-1128231 Newbie Points: 8 More actions · Answer 2

Thanks v.much for your reply. What would be considered a big table?

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

BOL

Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it. For more information about mixed extents, see Understanding Pages and Extents.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Anything under about 1000 pages, you don't need to worry about fragmentation .

http://www.sqlservercentral.com/Forums/Topic639788-146-1.aspx

MJ

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Anything under about 1000 pages, you don't need to worry about fragmentation .

http://www.sqlservercentral.com/Forums/Topic639788-146-1.aspx

MJ

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

Anything under about 1000 pages, you don't need to worry about fragmentation .

http://www.sqlservercentral.com/Forums/Topic639788-146-1.aspx

MJ