Für Reorganization

Mit diesem Script kann man herausfinden welcher Index eine Reorganisation benötigt.
Zusätzlich erhält man einen Output (Fertiges Script zum ausführen der Reorganization)

use yourdb
SET NOCOUNT ON;
SELECT

'ALTER INDEX [' + b.name + '] ON ['+ db.name +'].[' + OBJECT_NAME(b.[object_id]) + ']
REORGANIZE WITH ( LOB_COMPACTION = OFF ) GO'

FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as ps INNER JOIN
sys.indexes as b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id INNER JOIN
sys.partitions as p on b.OBJECT_ID = p.OBJECT_ID and b.index_id = p.index_id INNER JOIN
sys.allocation_units as a on p.partition_id = a.container_id INNER JOIN
sys.databases as db ON ps.database_id = db.database_id

WHERE

ps.index_depth > 0 and ps.avg_fragmentation_in_percent between 10 and 30
order by p.rows desc