Geek Answers Handbook

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

I have a table with multiple indexes (defined below). One of the indexes (IX_external_guid_3) has 99% fragmentation, regardless of the restructuring / reorganization of the index. Can anyone think of what might cause this, or a better way to fix it?

We use Entity Framework 4.0 to query this EF query in other indexed fields, about 10 times faster than the external_guid_3 field, however the ADO.Net query is approximately equal to the speed on both (although 2 times slower than EF Query for 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 zero)
  • FK_id (FK, int, null)
  • lock_guid (uniqueidentifer, null)
  • locked_time (datetime, null)
  • external_guid_4 (UniqueIdentifier, null)
  • corrected_time (datetime, null)
  • is_add (bit, but not null) evaluation (int, null)
  • row_version (timestamp, null)

Indices

  • PK_table (cluster)
  • 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)
+6
sql-server entity-framework-4
source share
4 answers

Actually, it just looks like guid indexing can be the culprit here: http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/

Recently, I have found several links that seem to support this.

+3
source share

Note. SQL Server Best Practices recommends that indexes with less than 10,000 pages typically do not benefit from performance improvements.

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

Here is a small snippet to determine when your database needs defragmentation. In our product, we reduced this to 2000 and selected> 20% fragmentation. You can easily modify the script to tell you which individuals 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;
+3
source share

You probably don’t have enough data in the index to deserve defragmentation. After you get a couple of thousand lines, regenerators and reorganis will get rid of fragmentation.

Until the number of index pages reaches 100 or so, fragmentation will not affect performance.

You will also want to check that your query is covered by the index and that the index is being used.

Run the request from the management studio (you can capture the request using the sql profiler or Activity Activity, if it is dynamic) and click "Enable the actual execution plan". This will tell you which index is being used, and if the query is included.

0
source share

Enable automatic shrink db . It appears to have a side effect of index fragmentation.

0
source share

More articles:


All Articles