SQLServer  Index Fragmentation Query and Processing of Tables

1.2

1. 2.reorganize 3.rebuild 4.rebuild 5.rebuild 1. SELECTobject_name(ips.object_id)ASTableName,ips.index_id,nameASIndexName,avg_fragmentation_in_percent,db_name(ips.database_id)ASDatabaseNameFROMsys.dm_db_index_physical_stats(Db_id(DB_NAME()),NULL,NULL,NULL,NULL)ASipsINNERJOINsys.indexesASSIONips.object_id=SI.object_idANDips.index_id=SI.index_idWHEREips.avg_fragmentation_in_percent>5ANDSI.index_id<>0 5%1000 5%-30%reorganize 30%rebuild 2.reorganize alterindex[]on[dbo].[]reorganize; 3.rebuild alterindex[]on[dbo].[]rebuild; 4.rebuild alterindexallon[dbo].[]rebuild; 5.rebuild USE[]GODECLARE@NoOfPartitionsBIGINT;DECLARE@objectidINT;DECLARE@indexidINT;DECLARE@idxnameNVARCHAR(255);DECLARE@objnameNVARCHAR(255);DECLARE@partitionnumBIGINT;DECLARE@schemanameNVARCHAR(255);DECLARE@partitionsBIGINT;DECLARE@fragFLOAT;DECLARE@statementVARCHAR(8000);--checkingexistanceofthetablethatwecreatefortemporarypurposeIFOBJECT_ID('defrag_work','U')ISNOTNULLDROPTABLEdefrag_work;--Copythefragmentedindexesdataintodefrag_worktable--Alltheindexesthathasfragmentation<5aregettingstoredintoourworktableSELECT[object_id]ASobjectid,index_idASindexid,partition_numberASpartition_no,avg_fragmentation_in_percentASfragINTOdefrag_workFROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED')WHEREavg_fragmentation_in_percent>5.0andindex_id>0;--cursortoprocessthelistofpartitionsDECLAREpartitionsCURSORFORSELECT*FROMdefrag_work;--Openthecursor.OPENpartitions;--LoopingthroughthepartitionsFETCHNEXTFROMpartitionsINTO@objectid,@indexid,@partitionnum,@frag;WHILE@@FETCH_STATUS=0BEGIN;SELECT@objname=QUOTENAME(so.name),@schemaname=QUOTENAME(ss.name)FROMsys.objectsASsoJOINsys.schemasASssONss.schema_id=so.schema_idWHEREso.object_id=@objectid;SELECT@idxname=QUOTENAME(name)FROMsys.indexesWHEREobject_id=@objectidANDindex_id=@indexid;SELECT@NoOfPartitions=COUNT(*)FROMsys.partitionsWHEREobject_id=@objectidANDindex_id=@indexid;/*Let'ssayN=fragmentationpercentageN<=5=IGNORE5<N<30=REORGANIZEN>30=REBUILD*/IF(@frag<30.0)--@frag>5isalreadyfilteredinourfirstquery,soweneedthatconditionhereBEGIN;SELECT@statement='ALTERINDEX'+@idxname+'ON'+@schemaname+'.'+@objname+'REORGANIZE';IF@NoOfPartitions>1SELECT@statement=@statement+'PARTITION='+CONVERT(CHAR,@partitionnum);EXEC(@statement);END;IF@frag>=30.0BEGIN;SELECT@statement='ALTERINDEX'+@idxname+'ON'+@schemaname+'.'+@objname+'REBUILD';IF@NoOfPartitions>1SELECT@statement=@statement+'PARTITION='+CONVERT(CHAR,@partitionnum);EXEC(@statement);END;PRINT'Executed'+@statement;FETCHNEXTFROMpartitionsINTO@objectid,@indexid,@partitionnum,@frag;END;--Closeanddeallocatethecursor.CLOSEpartitions;DEALLOCATEpartitions;--dropthetableIFOBJECT_ID('defrag_work','U')ISNOTNULLDROPTABLEdefrag_work; SQLServer,SQLServer