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.

CREATEPROCEDUREIndexdefragAsDECLARE@DBNameNVARCHAR (255) ,@TableNameNVARCHAR (255) ,@SchemaNameNVARCHAR (255) ,@IndexNameNVARCHAR (255) ,@PctFragDECIMALDECLARE@DefragNVARCHAR (MAX)IFEXISTS (SELECT*From sys.objectsWHEREobject_id=OBJECT_ID (N‘#Frag‘))DROPTABLE#FragCREATETABLE#Frag (DBNameNVARCHAR (255), TableNameNVARCHAR (255), SchemaNameNVARCHAR (255), IndexNameNVARCHAR (255), AvgfragmentDECIMAL)EXEC Sp_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‘DECLARE cListCURSORForSELECT*From#FragOPENCListFETCHNEXTFromCListInto@DBName,@TableName,@SchemaName,@IndexName,@PctFragWhile@ @FETCH_STATUS=0BEGINIF@PctFragBetween20.0and40.0BEGINSET@Defrag= N‘ALTER INDEX‘+@IndexName+‘On‘+@DBName+‘.‘+@SchemaName+‘.‘+@TableName+‘REORGANIZE‘EXEC sp_executesql@DefragPRINT‘Reorganize Index:‘+@DBName+‘.‘+@SchemaName+‘.‘+@TableName+‘.‘+@IndexNameENDELSEIF@PctFrag>40.0BEGINSET@Defrag= N‘ALTER INDEX‘+@IndexName+‘On‘+@DBName+‘.‘+@SchemaName+‘.‘+@TableName+‘REBUILD‘EXEC sp_executesql@DefragPRINT‘Rebuild Index:‘+@DBName+‘.‘+@SchemaName+‘.‘+@TableName+ ' . +  @IndexName end fetch next from CList into  @DBName,  @TableName,  @SchemaName,  @IndexName,  @PctFrag endclose< Span style= "color: #000000;" > Clistdeallocate cList drop table #Frag        

In order to automate the fragmentation analysis process, you can create a SQL Server task from SQL Server Enterprise Manager with the following simple steps.

1, open SQL Server Agent;

　　

2, open management Studio, right-click, select new = "task;

　　

3. In the General page of the New Task dialog box, enter the task name and other details:

　　

4. On the steps page of the New Task dialog box, click New and enter the SQL command for the user database.

　　

5. On the Advanced page of the New Task Step dialog box, enter the name of the output file that reports the fragmentation analysis results:

　　

6. Click the OK button to return to the new Job dialog box;

7. On the schedule page of the New Task dialog box, click New Schedule and enter the appropriate plan for running the SQL Server task:

　　

Schedule this stored procedure to execute at non-peak. To determine the database schema of the database, record the full day of SQL Server:sql statistics\batch requests/sec performance counters, which will show the fluctuations in database load.

8. Click the OK button to return to the New Task dialog box.

9. After you have entered all the information, click the OK button in the New Task dialog box to create the SQL Server task. Create a SQL Server task that schedules the Sp_indexdefrag stored procedure to run at a fixed interval (weekly).

10. Ensure that SQL Server Agent runs so that the SQL Server task will automatically run according to the set schedule.

This SQL task will parse each database and defragment it every Sunday at 1 o'clock in the morning.

Automatic maintenance of SQL Server Index < 13th >