1. Is index fragmentation generated?
The index page splits because of a large number of insertions, modifications, and deletions in the table. If the index has a high fragmentation, there are two cases, one is that scanning the index takes a lot of time, and the other is that the index does not use the index at all at the time of the query, which can result in performance degradation.
2. The fragment type is divided into:
2.1 Internal Crushing
Due to data insertion or modification operations in the index page, the data is terminated as a distribution of sparse matrices, resulting in an increase in data pages, which increases query time.
2.2 External crushing
Due to data insertion or modification of index/data pages, ending with page number separation and the allocation of new index pages that are incoherent in the file system, the database server cannot take advantage of the read-ahead operation because: the next associated data page is not near, and the following page numbers for these related links may be anywhere in the data file.
Automatic rebuilding of fragmented indexes
Create a new defragmentation stored procedure in your data
SQL code
- -- ================================================
- --Template GENERATED from template EXPLORER USING:
- --CREATE PROCEDURE (NEW MENU). Sql
- --
- --Use the Specify VALUES for TEMPLATE PARAMETERS
- --COMMAND (CTRL-SHIFT-M) to FILL in the PARAMETER
- --VALUES BELOW.
- --
- --This BLOCK of COMMENTS is not being INCLUDED in
- -The DEFINITION of the PROCEDURE.
- -- ================================================
- SET ANSI_NULLS on
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- --AUTHOR: <AUTHOR,,WUXIANGQIAN>
- --CREATE DATE: <create date,2014-05-16>
- --DESCRIPTION: <description, rebuilding the fragmented index >
- -- =============================================
- ALTER PROCEDURE usp_ims_defragment_indexes
- As
- --Declaring variables
- SET NOCOUNT on
- DECLARE @TABLENAME VARCHAR (128)-table name (index fragmentation has occurred)
- DECLARE @EXECSTR VARCHAR (255)--the statement that performs rebuilding the index
- DECLARE @INDEXNAME CHAR (255)--Index name
- DECLARE @DBNAME SYSNAME--database name
- DECLARE @DBNAMECHAR VARCHAR (20)--Database name
- DECLARE @TABLEIDCHAR VARCHAR (255)-table name (for traversing index fragmentation)
- --Check whether the user database is running
- SELECT @DBNAME = db_name ()
- IF @DBNAME in (' Master ', ' msdb ', ' model ', ' tempdb ')
- BEGIN
- PRINT ' This PROCEDURE should is RUN in SYSTEM DATABASES. '
- RETURN
- END ELSE
- BEGIN
- SET @DBNAMECHAR = ' DBNAME '
- END
- --1th stage: detecting fragments
- --Declaring cursors
- DECLARE TABLES CURSOR for
- SELECT CONVERT (varchar,so.id)
- From SYSOBJECTS so
- JOIN sysindexes SI
- On so.id = Si.id
- WHERE so. TYPE = ' U '
- and SI. Indid < 2
- and SI. ROWS > 0
- --Create a temporary table to store fragmentation information
- CREATE TABLE #FRAGLIST (
- TABLENAME CHAR (255),
- IndexName CHAR (255))
- --Open cursor
- OPEN TABLES
- --Executes the DBCC SHOWCONTIG command on all table loops of the database
- FETCH NEXT
- From TABLES
- Into @TABLEIDCHAR
- While @ @FETCH_STATUS = 0
- BEGIN
- --Statistics on all indexes of the table
- INSERT into #FRAGLIST
- EXEC (' SELECT object_name (DT. OBJECT_ID) as tablename,si.name as IndexName from ' +
- ' (SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent ' +
- ' From SYS. Dm_db_index_physical_stats (db_id (' [email protected]+ '), object_id (' [email protected]+ ') ' +
- ', Null,null, ' detailed ') WHERE index_id<>0) as DT INNER JOIN SYS. INDEXES SI ' +
- ' On SI. Object_id=dt. OBJECT_ID and SI. Index_id=dt. index_id and ' +
- ' DT. Avg_fragmentation_in_percent>10 ' +
- ' and DT. Avg_page_space_used_in_percent<75 ORDER by DT. Avg_fragmentation_in_percent DESC ')
- FETCH NEXT
- From TABLES
- Into @TABLEIDCHAR
- END
- --Close the release cursor
- CLOSE TABLES
- Deallocate TABLES
- --to check, report statistical results
- SELECT * from #FRAGLIST
- --2nd Stage: (defragment) declares a cursor for each index to defragment
- DECLARE INDEXES CURSOR for
- SELECT TABLENAME, IndexName
- From #FRAGLIST
- --Output start time
- SELECT ' STARTED defragmenting INDEXES at ' + CONVERT (varchar,getdate ())
- --Open cursor
- OPEN INDEXES
- --Loop all the indexes
- FETCH NEXT
- From INDEXES
- Into @TABLENAME, @INDEXNAME
- While @ @FETCH_STATUS = 0
- BEGIN
- SET QUOTED_IDENTIFIER ON
- SELECT @EXECSTR = ' ALTER INDEX ' [email protected]dexname+ ' on ' [e-mail protected]+ ' REBUILD with (fillfactor=90,online=on )‘
- SELECT ' Now executing: '
- SELECT (@EXECSTR)
- EXEC (@EXECSTR)
- SET QUOTED_IDENTIFIER OFF
- FETCH NEXT
- From INDEXES
- Into @TABLENAME, @INDEXNAME
- END
- --Close the release cursor
- CLOSE INDEXES
- Deallocate INDEXES
- --Report End time
- SELECT ' finished defragmenting INDEXES at ' + CONVERT (varchar,getdate ())
- --Delete temporary tables
- DROP TABLE #FRAGLIST
- GO
- GO
Set up timed execution steps
(1) Start SQL Server Management Studio and select Manage-maintenance plan option in the Object Explorer window.
(2) Right click on "Maintenance Plan", select "Maintenance Plan Wizard" in the Popup shortcut menu, the Maintenance Plan Wizard dialog box pops up, click "Next" button
(3) Popup "Select Target Server" dialog box, in the "Name" text box you can enter the name of the maintenance plan, in the "description" text box you can enter the description of the maintenance plan, "in the Server" text box you can enter the name of the server to use, and finally select the correct ID information, click the "Next" button.
(4) The Select Maintenance Task dialog box appears, in which you can choose to perform a SQL maintenance task, insert the Execute stored procedure statement
SQL code
- Use [DBNAME]
- GO
- EXEC [dbo]. [Usp_ims_defragment_indexes]
(5) Specify task execution plan
