Automatic maintenance of SQL Server Index < 13th >

In a database with a large number of transactions, tables and indexes are fragmented over time. Therefore, to improve performance, you should periodically check the fragmentation of tables and indexes, and defragment them with a lot of fragmentation.

1. Determine all the tables in the current database that need to be analyzed for fragmentation.

2. Determine the fragmentation of all tables and indexes.

3. Consider the factors to determine the tables and indexes that need to be defragmented.

• High fragmentation level-avg_fragmentation_in_percent greater than 20%;
• is not a very small table or index-that is, Page_count is greater than 8;

4, the collation of a large number of fragmented tables and indexes;

Here is a sample SQL stored procedure, which performs the following operations;

• Traverse all the databases on the system and confirm the indexes on the tables in each database that meet the fragmentation criteria, and save them to a temporary table;
• Defragment fewer indexes and rebuild many indexes based on fragmentation levels.

CREATE PROCEDUREIndexdefrag asDECLARE @DBName NVARCHAR(255)    ,@TableName NVARCHAR(255)    ,@SchemaName NVARCHAR(255)    ,@IndexName NVARCHAR(255)    ,@PctFrag DECIMALDECLARE @Defrag NVARCHAR(MAX)IF EXISTS(SELECT *  fromSys.objectsWHERE object_id = object_id(N'#Frag'))    DROP TABLE#FragCREATE TABLE#Frag (DBNameNVARCHAR(255), TableNameNVARCHAR(255), SchemaNameNVARCHAR(255), IndexNameNVARCHAR(255), AvgfragmentDECIMAL)EXECSp_msforeachdb'INSERT into #Frag (DBName, TableName, SchemaName, IndexName, avgfragment) SELECT"'?"'As DBName, t.name as TableName, SC. Name as SchemaName, i.name as IndexName, s.avg_fragmentation_in_percent from?. Sys.dm_db_index_physical_stats (db_id ("'?"'), NULL, NULL , NULL,"'Sampled"') as S JOIN?. sys.indexes i on s.object_id = i.object_id and s.index_id = i.index_id JOIN?. Sys.tables T on i.object_id = t.object_id JOIN?. Sys.schemas sc on t.schema_id = sc. Schema_idwhere s.avg_fragmentation_in_percent > 20AND t.type ="'U"'and s.page_count > 8ORDER by Tablename,indexname'DECLARECListCURSOR for SELECT *  from#FragOPENcListFETCH NEXT  fromcList into @DBName,@TableName,@SchemaName,@IndexName,@PctFrag while @ @FETCH_STATUS = 0BEGIN    IF @PctFrag between 20.0  and 40.0    BEGIN        SET @Defrag =N'ALTER INDEX' + @IndexName + ' on' + @DBName + '.' + @SchemaName + '.' + @TableName + 'REORGANIZE'        EXECsp_executesql@Defrag                PRINT 'Reorganize Index:' + @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName    END    ELSE IF @PctFrag > 40.0    BEGIN        SET @Defrag =N'ALTER INDEX' + @IndexName + ' on' + @DBName + '.' + @SchemaName + '.' + @TableName + 'REBUILD'        EXECsp_executesql@Defrag        PRINT 'Rebuild Index:'+ @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName    END            FETCH NEXT  fromcList into @DBName,@TableName,@SchemaName,@IndexName,@PctFragENDCLOSEcListdeallocatecListDROP TABLE#Frag

Automatic maintenance of SQL Server Index < 13th >