Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Logical Fragmentation in SQL Server Indexes

DZone's Guide to

Logical Fragmentation in SQL Server Indexes

· Performance Zone
Free Resource

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

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

Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:

Published at DZone with permission of Merrick Chaffer, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}