How does MSSQL automatically rebuild the index with fragments?

1. What is the generation of index fragmentation?

Index pages are split due to a large number of insert, modify, and delete 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 during the query, performance will be reduced.

2. Fragment types include:

2.1 internal fragmentation

The data insertion or modification operation on the index page ends with the distribution of data as a sparse matrix, which increases the data page and the query time.

2.2 External fragmentation

Data insertion or modification of indexes/Data Pages ends with distribution of new index pages separated by page numbers and inconsistent in the file system, the database server cannot use the advantages of the pre-read operation, because the next associated data page is not approaching, and the page numbers of these related connections may be anywhere in the data file.

Auto-rebuild index with fragmentation

Create a storage process for fragment in 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 supported 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 index with fragments>
-- ===================================================== ======
Alter procedure USP_IMS_DEFRAGMENT_INDEXES
AS
-- Declare Variables
SET NOCOUNT ON
DECLARE @ tablename varchar (128) -- table name (index fragmentation occurred)
DECLARE @ execstr varchar (255) -- execute the statement of re-Indexing
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 the database is running
SELECT @ DBNAME = DB_NAME ()
IF @ dbname in ('master', 'msdb', 'model', 'tempdb ')
BEGIN
PRINT 'This PROCEDURE shocould not be run in system databases .'
RETURN
END ELSE
BEGIN
SET @ DBNAMECHAR = 'dbname'
END

-- Stage 1: Detection of fragments
-- Declare a cursor
DECLARE TABLES CURSOR
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 the cursor
OPEN TABLES

-- Execute the dbcc showcontig command cyclically for all tables in the database
FETCH NEXT
FROM TABLES
INTO @ TABLEIDCHAR

WHILE @ FETCH_STATUS = 0
BEGIN
-- Statistics on all indexes of a 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 (''' + @ DBNAMECHAR + '''), object_id (''' + @ TABLEIDCHAR + ''') '+
', NULL, NULL, ''detailled'') 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

-- Report statistical results for inspection
SELECT * FROM # FRAGLIST

-- Stage 2: (fragment) declare a cursor for each index to fragment
DECLARE INDEXES CURSOR
Select tablename, INDEXNAME
FROM # FRAGLIST
-- Output Start Time
SELECT 'started defragmenting indexes at' + CONVERT (VARCHAR, GETDATE ())
-- Open the 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' + @ INDEXNAME + 'on' + @ TABLENAME + '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 a temporary table
Drop table # FRAGLIST
GO

GO

Set scheduled execution steps

(1) start SQL server Management Studio and select Manage> maintenance plan in the object Resource Manager window.

(2) Right-click maintenance plan and select the maintenance plan wizard option from the shortcut menu. In the displayed maintenance plan wizard dialog box, click Next.

(3) The select target server dialog box is displayed. In the Name text box, enter the name of the maintenance plan. In the description text box, enter the description of the maintenance plan; in the server text box, enter the name of the server you want to use. Finally, select the correct ID card and click Next.

(4) The select maintenance task dialog box is displayed. In this dialog box, you can select to execute an SQL maintenance task and insert and execute stored procedure statements.

Copy codeThe Code is as follows:
USE [DBNAME]

GO

EXEC [dbo]. [USP_IMS_DEFRAGMENT_INDEXES]

(5) Specify the task execution plan