--To View index fragmentation: UsedbnameSELECTSchema_name (t.schema_id) asSchema_name,t.name asTable_name,i.name asIndex_name,i.type asIndex_type,d.avg_fragmentation_in_percent asAvg_fragmentation_in_percent,page_count into#t_index fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) asDINNER JOINSys.indexes asI with(NOLOCK) ond.index_id=i.index_id andD.object_id=I.object_idINNER JOINSys.tables asT with(NOLOCK) onT.object_id=D.object_idWHEREI.type>0 andt.is_ms_shipped=0 andD.avg_fragmentation_in_percent>= - andPage_count>= +Order byD.avg_fragmentation_in_percentdesc--View index fragmentation and generate rebuild index code UsedbnameSELECTSchema_name (t.schema_id) asSchema_name,t.name asTable_name,i.name asIndex_name,i.type asIndex_type,d.avg_fragmentation_in_percent asAvg_fragmentation_in_percent,page_count into#t_index fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) asDINNER JOINSys.indexes asI with(NOLOCK) ond.index_id=i.index_id andD.object_id=I.object_idINNER JOINSys.tables asT with(NOLOCK) onT.object_id=D.object_idWHEREI.type>0 andt.is_ms_shipped=0 andD.avg_fragmentation_in_percent>= - andPage_count>= +Order byD.avg_fragmentation_in_percentdescSELECT 'ALTER INDEX' +Index_name+ ' on' +Schema_name+ '.'+table_name+ 'REBUILD with (ONLINE = ON)' from#t_index--ALTER INDEX Ix_userid on Dbo.user_service REBUILD with (ONLINE = ON)Drop Table#t_index
View index fragmentation and generate rebuild index code