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;

Answer 3

Turn db auto shrink off. It seems to have a side effect of fragmenting indexes.

Answer 4

You probably don't have enough data in the index to merit defragmenting. After you get a couple thousand rows in there, the rebuilds and reorgs will get rid of the fragmentation.

Until the index page count gets up to 100 or so, the fragmentation won't affect performance.

You'll also want to verify that your query is covered by the index and that the index is being used.

Run the query from the management studio (you can capture the query with sql profiler or the Activity Monitor if it is dynamic) and click "Include actual execution plan". This will tell you what index is being used and if the query is covered.