Data World

SQL Server Tuning – De-fragment Indexes

Posted by Pramod Singla on March 10, 2017


Problem Statement: Script to generate the scripts used to de-fragment indexes.

Solution: Use this script to find the fragmented indexes and following script can be used to de-fragment them.

SELECT ‘Alter index ‘ + i.NAME + ‘ on ‘
+ Quotename(s.NAME) + ‘.’ + Quotename(o.NAME) + CASE WHEN
stats.avg_fragmentation_in_percent >=60 THEN ‘ rebuild ‘ ELSE
‘ reorganize’ END
FROM   sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE  o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER  BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC

Advertisements

One Response to “SQL Server Tuning – De-fragment Indexes”

  1. […] SQL Server Tuning – De-fragment Indexes March 10, 2017 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: