Microsoft SQL Server 2008 - 99% fragmentation on non-clustered, non-unique index

Question

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)

Answer 1

It actually looks simply like indexing on a guid might be the culprit here: http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/

Lately I've been finding a number of references that seem to support this.

Answer 2

Note: SQL Server Best Practices state that indexes with less than 10,000 pages do not usually benefit from performance gains.

See http://technet.microsoft.com/en-gb/library/cc966523.aspx http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog

Here's a little snippet to identify when your DB needs defragging.In our product, we reduced this to 2000 and chose >20% fragmentation. You can easily modify the script to tell you which indecies in particular.

SELECT COUNT(*) AS fragmented_indexes FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) as p WHERE p.avg_fragment_size_in_pages <= 1 AND p.avg_fragmentation_in_percent >= 20 AND p.page_count > 2000;