Rebuild does not appear to change % index fragmentation

I have been testing methods to maintain indexes in a SQL Server 2005 database which has been migrated from SQL Server 2000. The compatibility level is still set to 80. I used the query below to inspect the degree of fragmentation amongst other things.

SELECT a.index_id

, name

, database_id

, avg_fragmentation_in_percent

,index_type_desc

,fragment_count

,page_count

FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

Some of the indexes in the database had a high degree of fragmentation based on the avg_fragmentation_in_percent value. I tried drop+create, rebuild and reorganise commands on those indexes. Predictably, drop + create was the most effective, but even that did not always reduce fragmentation much. Sometimes the fragmentation was the same no matter what method I used. Other times drop+create helped, rebuild made it worse.

What is going on?

Hi Helicopter,

could you please post the output of the above command, so that we can get a better idea of your indexes.

regards

Jag

|||

How many rows do your tables contain?

Chris

|||

Jag, Chris,

Thanks for the responses. Here are the details.

Fragmentation

IDX ID Name DBID Fragmentation % Index type Fragment count Page count

15 tblChargeRows_iJobID 7 85.71428571 NONCLUSTERED INDEX 7 7 1 PK_tblContent 7 83.33333333 CLUSTERED INDEX 6 6 1 PK_tblStaffTimeSettingsHistory 7 80 CLUSTERED INDEX 5 5 0 NULL 7 77.6119403 HEAP 60 343 1 PK_tblStaffTimeSettings 7 66.66666667 CLUSTERED INDEX 3 3 14 tblStaffTimeSettingsHistory_sPrimaryPersonality 7 66.66666667 NONCLUSTERED INDEX 3 3 1 PK_tblBillingPeriods 7 66.66666667 CLUSTERED INDEX 3 3 0 NULL 7 66.66666667 HEAP 8 17 1 PK_tblSystemNotifications 7 60 CLUSTERED INDEX 5 5 1 PK_tblJoinSupplierQuotes 7 53.84615385 CLUSTERED INDEX 8 13 0 NULL 7 50 HEAP 2 8 1 PK__tblJobCostSum 7 50 CLUSTERED INDEX 2 2 1 PK_tblOvertimeDefinitions 7 50 CLUSTERED INDEX 2 2 1 PK_tblBusinessUnits 7 50 CLUSTERED INDEX 2 2 1 PK__tblCostSummary 7 50 CLUSTERED INDEX 2 2 1 PK_tblPublicHolidays 7 50 CLUSTERED INDEX 2 2 8 tblTeamRows_sSiteUser 7 33.33333333 NONCLUSTERED INDEX 7 18 1 PK_tblRFQs 7 31.81818182 CLUSTERED INDEX 9 22 1 PK_tblJoinRFQAndSupplier 7 31.81818182 CLUSTERED INDEX 8 22 0 NULL 7 29.41176471 HEAP 9 109 31 TJM_iBusinessUnitID 7 25.27472527 NONCLUSTERED INDEX 32 91 1 PK_tblPageMeta 7 25 CLUSTERED INDEX 2 4 0 NULL 7 23.07692308 HEAP 5 81 1 PK_tblChargeRowsArchive 7 20 CLUSTERED INDEX 4 10 1 PK_tblTopics 7 18.51851852 CLUSTERED INDEX 4 27 10 tblMessages_sTopicID 7 18.18181818 NONCLUSTERED INDEX 7 22 1 PK_tblJoinSupplierWorkSites 7 16.66666667 CLUSTERED INDEX 2 6 0 NULL 7 16.12903226 HEAP 9 231 0 NULL 7 14.28571429 HEAP 6 270 0 NULL 7 14.28571429 HEAP 4 98 26 TJM_IWSID_ncidx 7 12.79069767 NONCLUSTERED INDEX 13 86 16 tblInvoiceChargeRows_iJobID 7 8.133971292 NONCLUSTERED INDEX 36 209 29 TJM_iJobStatus 7 7.228915663 NONCLUSTERED INDEX 14 83 12 QUOTEID_ncidx 7 7.142857143 NONCLUSTERED INDEX 15 98 14 TBQ_JOBID_ncidx 7 5.952380952 NONCLUSTERED INDEX 12 84 15 JOBID_ncidx 7 5.323193916 NONCLUSTERED INDEX 35 263 1 PK_tblAuth 7 5.102040816 CLUSTERED INDEX 15 98 1 PK_tblArchiveRecords 7 4.294478528 CLUSTERED INDEX 37 326 1 PK_tbl_SUM_JobTotals 7 3.694581281 CLUSTERED INDEX 55 406 30 TJM_iProjectID 7 3.658536585 NONCLUSTERED INDEX 12 82 1 PK_tblContentMeta 7 2.985074627 CLUSTERED INDEX 6 67 1 PK_tblTopics 7 1.604938272 CLUSTERED INDEX 278 2430 9 tblTeamRows_iJobID 7 1.570680628 NONCLUSTERED INDEX 21 191 8 tblTeamRows_sSiteUser 7 0.355366027 NONCLUSTERED INDEX 108 1407 1 PK_tblJobMeta 7 0.350087522 CLUSTERED INDEX 78 3999 10 tblMessages_sTopicID 7 0.264830508 NONCLUSTERED INDEX 117 1888 1 PK_tblTeamRows 7 0.209292591 CLUSTERED INDEX 127 2389 1 PK_tblQuotes 7 0.178970917 CLUSTERED INDEX 241 2235 1 PK_tblMessages 7 0.157521659 CLUSTERED INDEX 486 7618 1 PK_tblChargeRowsArchive 7 0.132604011 CLUSTERED INDEX 505 6033 1 PK_tblQuoteRows 7 0.129785853 CLUSTERED INDEX 118 1541 1 PK_tblInvoices 7 0.10655301 CLUSTERED INDEX 116 1877 1 PK_tblInvoiceChargeRows 7 0.085306035 CLUSTERED INDEX 454 4689 1 PK_tblInvoiceChargeRows 7 0 CLUSTERED INDEX 2 8 1 PK_tblInvoiceChargeRows 7 0 CLUSTERED INDEX 1 1 16 tblInvoiceChargeRows_iJobID 7 0 NONCLUSTERED INDEX 1 1 15 JOBID_ncidx 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblChargeRowsArchive 7 0 CLUSTERED INDEX 1 1 1 PK_tblAuth 7 0 CLUSTERED INDEX 2 2 1 PK_tblAuth 7 0 CLUSTERED INDEX 1 1 1 PK_tblRoles 7 0 CLUSTERED INDEX 1 1 1 PK_tblSystemNotifications 7 0 CLUSTERED INDEX 1 1 1 PK_tblMessages 7 0 CLUSTERED INDEX 4 14 1 PK_tblMessages 7 0 CLUSTERED INDEX 1 1 1 PK_tblMessages 7 0 CLUSTERED INDEX NULL 8111 1 PK_tblTopics 7 0 CLUSTERED INDEX 1 1 1 PK_tblBusinessUnits 7 0 CLUSTERED INDEX 1 1 1 PK_tblPageMeta 7 0 CLUSTERED INDEX 1 1 1 PK__tblSpend 7 0 CLUSTERED INDEX 33 331 1 PK__tblSpend 7 0 CLUSTERED INDEX 1 1 1 PK_tblJobTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblJobStatusTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblTeamMemberTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblOvertimeDefinitions 7 0 CLUSTERED INDEX 1 1 1 PK_tblWorkSites 7 0 CLUSTERED INDEX 1 1 1 PK_tblBillingPeriods 7 0 CLUSTERED INDEX 1 1 1 PK_tblPublicHolidays 7 0 CLUSTERED INDEX 1 1 1 PK_tbl_SUM_JobTotals 7 0 CLUSTERED INDEX 1 1 1 PK_tblRFQs 7 0 CLUSTERED INDEX 1 1 1 PK_tblRFQs 7 0 CLUSTERED INDEX NULL 99 10 tblMessages_sTopicID 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblTEMPPersonalTime 7 0 CLUSTERED INDEX 1 1 1 PK_tblTEMPPersonalTime 7 0 CLUSTERED INDEX 1 1 1 PK_tblJoinSupplierLocations 7 0 CLUSTERED INDEX 1 1 1 PK_tblBusinessRegistrationDetails 7 0 CLUSTERED INDEX 1 1 0 NULL 7 0 HEAP 4 127 1 PK_tblWorkSiteTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblJobMeta 7 0 CLUSTERED INDEX 2 7 1 PK_tblJobMeta 7 0 CLUSTERED INDEX 1 1 26 TJM_IWSID_ncidx 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblJoinSupplierWorkSites 7 0 CLUSTERED INDEX 1 1 1 PK_tblSuppliers 7 0 CLUSTERED INDEX 1 1 1 PK_tblContent 7 0 CLUSTERED INDEX 1 1 1 PK_tblContent 7 0 CLUSTERED INDEX NULL 128 30 TJM_iProjectID 7 0 NONCLUSTERED INDEX 1 1 29 TJM_iJobStatus 7 0 NONCLUSTERED INDEX 1 1 1 PK__tblCostSummary 7 0 CLUSTERED INDEX 1 1 1 PK_tblTextTempates 7 0 CLUSTERED INDEX 1 1 1 PK_tblTextTempates 7 0 CLUSTERED INDEX NULL 1 1 PK_tblInvoices 7 0 CLUSTERED INDEX 2 4 1 PK_tblInvoices 7 0 CLUSTERED INDEX 1 1 1 PK__tblSeedTotals 7 0 CLUSTERED INDEX 1 1 1 PK_tblJobMonitors 7 0 CLUSTERED INDEX 1 1 1 PK_tblRoleKeys 7 0 CLUSTERED INDEX 1 1 1 PK_tblLocations 7 0 CLUSTERED INDEX 1 55 1 PK_tblLocations 7 0 CLUSTERED INDEX 1 1 11 TBL_IWSID_ncidx 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblArchiveRecords 7 0 CLUSTERED INDEX 1 1 1 PK_tblInfoCategories 7 0 CLUSTERED INDEX 1 1 1 PK__Calendar 7 0 CLUSTERED INDEX 8 64 1 PK__Calendar 7 0 CLUSTERED INDEX 1 1 1 PK_tblGSOMeta 7 0 CLUSTERED INDEX 12 113 1 PK_tblGSOMeta 7 0 CLUSTERED INDEX 1 1 1 PK_tblEmergencyContacts 7 0 CLUSTERED INDEX 20 224 1 PK_tblEmergencyContacts 7 0 CLUSTERED INDEX 1 1 1 PK_tblContentMeta 7 0 CLUSTERED INDEX 1 1 1 PK_tblSiteUser 7 0 CLUSTERED INDEX 2 216 1 PK_tblSiteUser 7 0 CLUSTERED INDEX 2 3 1 PK_tblSiteUser 7 0 CLUSTERED INDEX 1 1 19 tblSiteUser_sPrimaryPersonality 7 0 NONCLUSTERED INDEX 7 93 19 tblSiteUser_sPrimaryPersonality 7 0 NONCLUSTERED INDEX 1 2 19 tblSiteUser_sPrimaryPersonality 7 0 NONCLUSTERED INDEX 1 1 20 tblSiteUser_iWorkSiteID 7 0 NONCLUSTERED INDEX 4 51 20 tblSiteUser_iWorkSiteID 7 0 NONCLUSTERED INDEX 1 1 21 tblSiteUser_iLocationID 7 0 NONCLUSTERED INDEX 5 51 21 tblSiteUser_iLocationID 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblJobCostSumCtrl 7 0 CLUSTERED INDEX 1 1 1 PK_tblFileTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblAddressTypes 7 0 CLUSTERED INDEX 1 1 31 TJM_iBusinessUnitID 7 0 NONCLUSTERED INDEX 1 1 1 PK__tblJobCostSum 7 0 CLUSTERED INDEX 1 1 1 PK_tblGSOSysOptions 7 0 CLUSTERED INDEX 1 1 1 PK_tblProcReqStatusTypes 7 0 CLUSTERED INDEX 1 1 1 PK_tblProcurementRequests 7 0 CLUSTERED INDEX 3 20 1 PK_tblProcurementRequests 7 0 CLUSTERED INDEX 1 1 0 NULL 7 0 HEAP 1 1 1 PK_tblChildSites 7 0 CLUSTERED INDEX 1 1 1 PK_tblQuoteRows 7 0 CLUSTERED INDEX 2 3 1 PK_tblQuoteRows 7 0 CLUSTERED INDEX 1 1 15 tblPageMatrix_iSortOrderTopMenu 7 0 NONCLUSTERED INDEX 1 1 16 tblPageMatrix_iSortOrder 7 0 NONCLUSTERED INDEX 1 1 12 QUOTEID_ncidx 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblJoinSupplierQuotes 7 0 CLUSTERED INDEX 1 1 1 PK_tblQuotes 7 0 CLUSTERED INDEX 2 4 1 PK_tblQuotes 7 0 CLUSTERED INDEX 1 1 14 TBQ_JOBID_ncidx 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblStaffTimeSettings 7 0 CLUSTERED INDEX 1 1 1 PK_tblStaffTimeSettingsHistory 7 0 CLUSTERED INDEX 1 1 1 PK_tblTeamRows 7 0 CLUSTERED INDEX 2 4 1 PK_tblTeamRows 7 0 CLUSTERED INDEX 1 1 8 tblTeamRows_sSiteUser 7 0 NONCLUSTERED INDEX 1 1 14 tblStaffTimeSettingsHistory_sPrimaryPersonality 7 0 NONCLUSTERED INDEX 1 1 15 tblStaffTimeSettingsHistory_dStart 7 0 NONCLUSTERED INDEX 1 1 16 tblStaffTimeSettingsHistory_dEnd 7 0 NONCLUSTERED INDEX 1 1 9 tblTeamRows_iJobID 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblAddresses 7 0 CLUSTERED INDEX 13 135 1 PK_tblAddresses 7 0 CLUSTERED INDEX 1 1 1 PK_tblGroupedJobs 7 0 CLUSTERED INDEX 19 245 1 PK_tblGroupedJobs 7 0 CLUSTERED INDEX 1 1 1 PK_tblChargeProperties 7 0 CLUSTERED INDEX 2 9 1 PK_tblChargeProperties 7 0 CLUSTERED INDEX 1 1 1 PK_tblExtranetProperties 7 0 CLUSTERED INDEX 1 1 15 tblChargeRows_iJobID 7 0 NONCLUSTERED INDEX 1 1 1 PK_tblChargeRows 7 0 CLUSTERED INDEX 6 85 1 PK_tblChargeRows 7 0 CLUSTERED INDEX 1 1 1 PK_tblWorkCategories 7 0 CLUSTERED INDEX 1 8 1 PK_tblWorkCategories 7 0 CLUSTERED INDEX 1 1 1 pk_dtproperties 7 0 CLUSTERED INDEX 1 1 1 pk_dtproperties 7 0 CLUSTERED INDEX NULL 9 1 PK_tblJoinRFQAndSupplier 7 0 CLUSTERED INDEX 1 1 1 PK_tblGSORows 7 0 CLUSTERED INDEX 2 12 1 PK_tblGSORows 7 0 CLUSTERED INDEX 1 1 0 NULL 7 0 HEAP 1 1 0 NULL 7 0 HEAP NULL 2 1 PK_tblChargeCategories 7 0 CLUSTERED INDEX 1 1 1 PK_tblSupplierCategories 7 0 CLUSTERED INDEX 1 1

Top 15 tables by row count

NAME ROWS RESERVED DATA INDEX SIZE UNUSED tblChargeRowsArchive 188044 50.61 48.21 2.23 0.17 tblMessages 186377 61.08 60.87 0.16 0.05 tblInvoiceChargeRows 147573 37.64 37.48 0.08 0.08 tblTeamRows 138847 19.21 19.09 0.05 0.07 tblQuoteRows 69279 13.34 12.31 0.86 0.18 tblQuotes 59661 18.78 17.86 0.75 0.18 tblJobMeta 58482 32.8 31.9 0.77 0.14 tbl_INV_JobTotals 57178 3.34 3.22 0.02 0.1 tblInvoices 57022 15.11 15 0.05 0.06 tblTopics 47820 19.59 19.28 0.22 0.1 tblApprovedQuoteTotals 43272 3.99 2.74 0 1.26 tblPostCodes 16434 1.03 1.02 0.01 0.01 tblArchiveRecords 15893 2.63 2.54 0.02 0.08 PwCJobProjSpend 15437 2.18 2.16 0.01 0.02

|||

Chris,

There are numerous tables in the database ranging from just a few rows up to 188,000. I posted some row count and fragmentation data in another message.

The tables that I can't seem to defragment are all between 450 - 1500 rows. I realise that having an index in that case is almost academic but it seems wrong to have 30-100% fragmentation on any of them.

One of the indexes is a 450 row PK int identity with 66.666... % fragmentation which totally does my head in. It doesn't change that much, maybe 1-5 new rows added per week.

|||

Hi Helicopter,

Looking at the output, your tables are not big enough to worry about fragmentation.

The large indexes are not fragmented as per the output. You have some heap tables, you might want create clustered index to replace those heaps.

Which index in your output (with 450 rows) are you worried about?

regards

Jag

|||

Hi Jag,

It is PK_tblBillingPeriods. I can't understand why a table like that should be so fragmented, and be impossible to defragment, when another table with a PK int indentity, and only 32 rows, has 0% fragmentation.

I know that since both tables are still small the index fragmentation won't be affecting performance... right now. If this is deliberate normal behavior for SQL Server then I can sleep peacefully at night. I don't want to leave a fundamental flaw festering in the DB for years - if there is one.

I don't understand why using the three types of method to drop-create, rebuild, and reorganize the index has no effect on the index fragmentation for this table. Does SQL server treat indexing of the smaller tables differently because the whole table will be scanned anyway? Has the table actually been organised optimally and % fragmentation is not the correct way to assessing such a table? I am about to make a maintenance script which will choose different indexing methods based criteria such as fragmentation so I am trying to understand. Should I implement some decision making to ignore the fragmentation on the smaller tables, or assess them differently?

TABLE SPEC

tblBillingPeriods

[iBillingPeriodID] [int] IDENTITY(1,1) NOT NULL,

[iWorkSiteID] [int] NOT NULL CONSTRAINT [DF_tblBillingPeriods_iWorkSiteID] DEFAULT (0),

[dStart] [datetime] NOT NULL,

[dEnd] [datetime] NULL,

[bOpen] [bit] NOT NULL,

[dClosed] [datetime] NULL,

CONSTRAINT [PK_tblBillingPeriods] PRIMARY KEY CLUSTERED

INDEX FRAGMENTATION

1 PK_tblBillingPeriods 7 66.66666667 CLUSTERED INDEX 3 3

|||

Hi Helicopter,

I see two entries in your output for index PK_tblBillingPeriods. The second one towards the end is :

1 PK_tblBillingPeriods 7 0 CLUSTERED INDEX 1 1

Could you please post the output of the following command:

Code Snippet

select * from sys.dm_db_index_physical_stats (7,object_id(tblBillingPeriods), NULL,NULL,NULL)

Jag

|||

Jag,

Query:

select * from sys.dm_db_index_physical_stats (7,230291880, NULL,NULL,NULL)

Output:

7 230291880 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 NULL NULL NULL NULL NULL NULL NULL NULL

I will be very happy if there is a logical explanation for this

|||

OK,

I will try and look for it.

Jag

|||

ok Helicopter,

Try the ALTER REINDEX with ONLINE option ON. and let me know if that reduced fragmentation. Also sepcify the fill factor of 100.

Jag

|||

Jag,

BEFORE:

select * from sys.dm_db_index_physical_stats (7,230291880, NULL,NULL,NULL)

7 230291880 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 NULL NULL NULL NULL NULL NULL NULL NULL

QUERY:

ALTER INDEX ALL ON dbo.tblBillingPeriods

REBUILD WITH (FILLFACTOR = 100, ONLINE = ON );

RESULT:

select * from sys.dm_db_index_physical_stats (7,230291880, NULL,NULL,NULL)

7 230291880 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 2 1.5 3 NULL NULL NULL NULL NULL NULL NULL NULL

After that I experimented with the fill factor a bit. I could repeatably control the amount of fragmentation with the fill factor. That means the page to index data size ratio is the problem?