MSSQL method for automatically rebuilding fragmented indexes

Time:2021-11-29

1. Generation of index fragments?

The index page is split due to a large number of insertion, modification and deletion operations in the table. If the index has high fragmentation, there are two situations. One is that it takes a lot of time to scan the index, and the other is that the index does not use the index at all during query, which will lead to performance degradation.

2. Debris types are divided into:

2.1 internal crushing

Due to the data insertion or modification operation in the index page, the distribution of data as a sparse matrix ends, which will lead to the increase of data pages and increase the query time.

2.2 external crushing

Due to the data insertion or modification of the index / data page, which ends with page 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 adjacent, and the associated following page numbers may be anywhere in the data file.

Automatically rebuild fragmented indexes

Create a new defragmentation stored procedure in the data

Copy codeThe code is as follows:
— ================================================
— 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 WILL NOT BE 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, rebuild the fragmented index >
— =============================================
ALTER PROCEDURE USP_IMS_DEFRAGMENT_INDEXES
AS
–Declare variable
SET NOCOUNT ON
Declare @ tablename varchar (128) — table name (index fragmentation occurred)
Declare @ execstr varchar (255) — execute the statement to rebuild 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 (used to traverse index fragments)

–Check whether it runs in the user database
SELECT @DBNAME = DB_NAME()
IF @DBNAME IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)
BEGIN
PRINT ‘THIS PROCEDURE SHOULD NOT BE RUN IN SYSTEM DATABASES.’
RETURN
END ELSE
BEGIN
SET @DBNAMECHAR = ‘DBNAME’
END

–Phase 1: detection of debris
–Declare cursor
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 fragment information
CREATE TABLE #FRAGLIST (
TABLENAME CHAR (255),
INDEXNAME CHAR (255))

–Open cursor
OPEN TABLES

–Execute the DBCC showconnection command cyclically on all tables in the database
FETCH NEXT
FROM TABLES
INTO @TABLEIDCHAR

WHILE @@FETCH_STATUS = 0
BEGIN
–Perform 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 release cursor
CLOSE TABLES
DEALLOCATE TABLES

–To check, report the statistical results
SELECT * FROM #FRAGLIST

–Phase 2: (defragment) declare cursors for each index to be defragmented
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 indexes
FETCH NEXT
FROM INDEXES
INTO @TABLENAME, @INDEXNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @EXECSTR = ‘ALTER INDEX ‘[email protected]+’ ON ‘[email 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 release cursor
CLOSE INDEXES
DEALLOCATE INDEXES

–Report end time
SELECT ‘FINISHED DEFRAGMENTING INDEXES AT ‘ + CONVERT(VARCHAR,GETDATE())

–Delete temporary table
DROP TABLE #FRAGLIST
GO

GO

Set timing execution steps

(1) Start SQL Server Management Studio and select management – maintenance plan in the object Explorer window.

(2) Right click maintenance plan and select Maintenance Plan Wizard in the pop-up shortcut menu to open the maintenance plan Wizard dialog box as shown in the figure. Click next

(3) The select target server dialog box as shown in the figure pops up. You can enter the name of the maintenance plan in the name text box; In the description text box, you can enter the description text of the maintenance plan; Enter the server name to be used in the [server] text box; Finally, select the correct ID information and click next.

(4) The select maintenance task dialog box as shown in the figure pops up. In this dialog box, you can select to execute SQL maintenance tasks and insert execution stored procedure statements

Copy codeThe code is as follows:
USE [DBNAME]

GO

EXEC [dbo].[USP_IMS_DEFRAGMENT_INDEXES]

(5) Specify task execution plan

Recommended Today

On the mutation mechanism of Clickhouse (with source code analysis)

Recently studied a bit of CH code.I found an interesting word, mutation.The word Google has the meaning of mutation, but more relevant articles translate this as “revision”. The previous article analyzed background_ pool_ Size parameter.This parameter is related to the background asynchronous worker pool merge.The asynchronous merge and mutation work in Clickhouse kernel is completed […]