Microsoft SQL Server 2008 - 99% fragmentation on non-clustered, non-unique index
- by user550441
I have a table with several indexes (defined below). One of the indexes (IX_external_guid_3) has 99% fragmentation regardless of rebuilding/reorganizing the index. Anyone have any idea as to what might cause this, or the best way to fix it?
We are using Entity Framework 4.0 to query this, the EF queries on the other indexed fields about 10x faster on average then the external_guid_3 field, however an ADO.Net query is roughly the same speed on both (though 2x slower than the EF Query to indexed fields).
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) -- usually 2-4K
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)
