Index Fragmentation in Microsoft SQL Server [modified]
-
I have a database table that consists of an int primary key, several unique identifiers (most of which are used to reference data in other databases), an xml field and an infoset. I have non-unique, non-clustered indexes that I am trying to use to speed up the queries to the table, all of the indexes work well except for a single index on a uniqueidentifier field that retains a 99% fragmentation rate regardless of rebuild/reorganizing the index. Below is the details of the table, IX_external_guid_3 is the index with the high fragmentation. Can anyone tell me what might cause the high fragmentation, or what I can do to reduce the fragmentation on this guid field and speed up queries based on it? Thanks, Table
id(PK, int, not null) guid(uniqueidentifier, null, rowguid) external_guid_1(uniqueidentifier, not null) external_guid_2(uniqueidentifier, null) state(varchar(32), null) value(varchar(max), null) infoset(XML(.), null) created_time(datetime, null) updated_time(datetime, null) external_guid_3(uniqueidentifier, not null) FK_id(FK, int, null) locking_guid(uniqueidentifer, null) locked_time(datetime, null) external_guid_4(uniqueidentifier, null) corrected_time(datetime, null) is_add(bit, not null) score(int, null) row_version(timestamp, null) Indexes
PK_table(Clustered) IX_created_time(Non-Unique, Non-Clustered) IX_external_guid_1(Non-Unique, Non-Clustered) IX_guid(Non-Unique, Non-Clustered) IX_external_guid_3(Non-Unique, Non-Clustered) IX_state(Non-Unique, Non-Clustered)John
modified on Tuesday, December 21, 2010 2:57 PM
-
I have a database table that consists of an int primary key, several unique identifiers (most of which are used to reference data in other databases), an xml field and an infoset. I have non-unique, non-clustered indexes that I am trying to use to speed up the queries to the table, all of the indexes work well except for a single index on a uniqueidentifier field that retains a 99% fragmentation rate regardless of rebuild/reorganizing the index. Below is the details of the table, IX_external_guid_3 is the index with the high fragmentation. Can anyone tell me what might cause the high fragmentation, or what I can do to reduce the fragmentation on this guid field and speed up queries based on it? Thanks, Table
id(PK, int, not null) guid(uniqueidentifier, null, rowguid) external_guid_1(uniqueidentifier, not null) external_guid_2(uniqueidentifier, null) state(varchar(32), null) value(varchar(max), null) infoset(XML(.), null) created_time(datetime, null) updated_time(datetime, null) external_guid_3(uniqueidentifier, not null) FK_id(FK, int, null) locking_guid(uniqueidentifer, null) locked_time(datetime, null) external_guid_4(uniqueidentifier, null) corrected_time(datetime, null) is_add(bit, not null) score(int, null) row_version(timestamp, null) Indexes
PK_table(Clustered) IX_created_time(Non-Unique, Non-Clustered) IX_external_guid_1(Non-Unique, Non-Clustered) IX_guid(Non-Unique, Non-Clustered) IX_external_guid_3(Non-Unique, Non-Clustered) IX_state(Non-Unique, Non-Clustered)John
modified on Tuesday, December 21, 2010 2:57 PM