.NET Zone is brought to you in partnership with:

Merrick Chaffer is a Software Developer with Russell Reynolds Associates, with over 16 years experience in areas relating to Microsoft Application Development. Merrick is a DZone MVB and is not an employee of DZone and has posted 37 posts at DZone. You can read more from them at their website. View Full User Profile

Logical Fragmentation in SQL Server Indexes

03.17.2013
| 3076 views |
  • submit to reddit

My colleague Lawrence today discovered that a query we had that was taking over 2hrs to run (and still timing out), was due to a non clustered index being badly fragmented on the table in question.

To discover this fact he used the following queries, before rebuilding all indexes on the table, which then fixed the issue, and got the query coming back in 2 seconds.

1. find index names

sp_helpindex 'dbo.MyTableName'

2. show index logical fragmentation. Scan density should be above 97%

dbcc showcontig (MyTableName) WITH TABLERESULTS, ALL_INDEXES

3. show when indexes were last rebuilt

Declare @dbid int
Select @dbid = db_id('Beacon')
Select objectname=object_name(i.object_id)
, indexname=i.name, i.index_id
, o.create_date, o.modify_date
from sys.indexes i, sys.objects o
where objectproperty(o.object_id,'IsUserTable') = 1
--and i.index_id NOT IN
--(select s.index_id
--from sys.dm_db_index_usage_stats s
--where s.object_id=i.object_id and
--i.index_id=s.index_id and
--database_id = @dbid )
and o.object_id = i.object_id
and object_name(i.object_id)= 'MyTableName'
order by o.modify_date desc

Published at DZone with permission of Merrick Chaffer, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)