Parçalanmış Indexleri Düzeltir.(Sql Server 2007 için)

Parçalanmış Indexleri  Düzeltir.(Sql Server 2007 için). Scripteri ve sonuçlarını çalıştırın!

(Fixes Fragmented Indexes):

 

 

----------1.

 

create table _FragmentedIndex

(

index_id int,name nvarchar(max),

avg_fragmentation_in_percent decimal

)

 

 

----------2.

 

select

                'insert into _FragmentedIndex SELECT a.index_id, name, avg_fragmentation_in_percent

                FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('''+name+'''),

                                NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

where avg_fragmentation_in_percent>20;'

 from sys.tables

 

---------------3.

 

select

case

when avg_fragmentation_in_percent <30

then

'ALTER INDEX '+name+' ON [dbo].'+

(select name from sys.tables where object_id = (select object_id from sys.indexes where name=_FragmentedIndex.name))

+ ' REORGANIZE WITH ( LOB_COMPACTION = ON );'

when avg_fragmentation_in_percent >29

then

'ALTER INDEX '+ name +' ON [dbo].'+(select name from sys.tables where object_id = (select object_id from sys.indexes where name=_FragmentedIndex.name))

+' REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF );'

end as test,

 

 *,

(select name from sys.tables where object_id = (select object_id from sys.indexes where name=_FragmentedIndex.name))

 from

_FragmentedIndex

where avg_fragmentation_in_percent>5

---rebuilt

order by 2