Generation and processing of SQL index fragment.

Reference in this article

https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html

https://www.jb51.net/softjc/126055.html

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

This article needs to“ Indexes ”And“ Storage mode ”Have a certain understanding.

Software often gets stuck for no reason after a period of use, which is caused by paging and fragmentation in the process of frequent database (MSSQL) insertion and update. Therefore, the fragments need to be processed regularly. Basically, all methods are based on the reconstruction and collation of indexes, but in different ways.

  1. Delete index and rebuild
  2. Rebuild the index with drop ﹣ existing statement
  3. Use ALTER INDEX REBUILD statement to rebuild index
  4. Use ALTER INDEX REORGANIZE

The above methods have their own advantages and disadvantages. The following stored procedures mainly use 3, 4

First look at a defragmented stored procedure, and then use the way of jobs to execute regularly.

Create PROCEDURE [dbo].[proc_rebuild_index]
    @ret    INT OUTPUT
AS
SET NOCOUNT ON
BEGIN
    DECLARE @fldDefragFragment INT = 10;
    DECLARE @fldRebuildFragment INT = 30;
    DECLARE @fldMinPageCount INT = 1000;
    DECLARE @fldTable VARCHAR(256);
    DECLARE @fldIndex VARCHAR(256);
    DECLARE @fldPercent INT;
    DECLARE @Sql       VARCHAR(256);
    declare @DBID  int;
    BEGIN TRY
        SET @ret = -1;
        set @DBID = db_id();
        -- Get index fragment status
        DECLARE curIndex CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
            SELECT 
                 TBL.NAME TABLE_NAME
                ,IDX.NAME INDEX_NAME
                ,AVGP.AVG_FRAGMENTATION_IN_PERCENT
            FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(@DBID, NULL,NULL, NULL, 'LIMITED') AS AVGP 
            INNER JOIN SYS.INDEXES AS IDX 
             ON AVGP.OBJECT_ID = IDX.OBJECT_ID 
            AND AVGP.INDEX_ID = IDX.INDEX_ID 
            INNER JOIN SYS.TABLES AS TBL 
             ON AVGP.OBJECT_ID = TBL.OBJECT_ID
            INNER JOIN SYS.DM_DB_PARTITION_STATS PS
             ON AVGP.OBJECT_ID = PS.OBJECT_ID
            AND AVGP.INDEX_ID = PS.INDEX_ID 
            WHERE
                AVGP.INDEX_ID >= 1 
            AND AVGP.AVG_FRAGMENTATION_IN_PERCENT >= @fldDefragFragment
            AND PS.RESERVED_PAGE_COUNT >= @fldMinPageCount;
        -- Open cursor
        OPEN curIndex;
        -- Get cursor
        FETCH NEXT FROM curIndex
        INTO @fldTable,@fldIndex,@fldPercent;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                --Fragment rate greater than 30, re index
                IF @fldPercent >= @fldRebuildFragment
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REBUILD';
                        EXEC(@Sql);
                    END
                ELSE
                --Fragment rate less than 30, index reorganized
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REORGANIZE';
                        EXEC(@Sql);
                    END
                -- Get cursor
                FETCH NEXT FROM curIndex
                INTO @fldTable,@fldIndex,@fldPercent;
            END
        -- Close cursor
        CLOSE curIndex;
        DEALLOCATE curIndex;
        SET @ret = 0;
    END TRY
    BEGIN CATCH
        SET @ret = -1;
        DECLARE @ErrorMessage    nvarchar(4000);
        DECLARE @ErrorSeverity    int;
        DECLARE @ErrorState        int;
        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorSeverity  = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE();
        RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN;
    END CATCH;
END

Let's take a look at the process of debris generation

--Create test table
if object_id('test') is not null 
  drop table test
go
create table test
(
  col1 int, 
  col2 char(985),
  col3 varchar(10)
)
Go
--Create focus index
create CLUSTERED index cix on test(col1);
go
--insert data
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, 'xxx', '')
  set @var=@var+100
end;
--View page storage
select page_count, avg_page_space_used_in_percent, record_count,
       avg_record_size_in_bytes, avg_fragmentation_in_percent, fragment_count,
       * from [master].sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('test'), null, null, 'sampled')

 

--After the update operation, continue to view the page storage.

update test set col3='Update test' where col1=100

--View page storage after inserting data again
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, 'Insertion test', '')
  set @var=@var+100
end;

 

--Let's take a look at the data before and after defragmentation IO
set statistics io on 
select * from test
alter index cix on test rebuild
select * from test 
set statistics io off

 

Obvious reduction in logical reads. This improves performance

Tags: SQL Sever SQL Fragment Database Stored Procedure

Posted on Thu, 28 Nov 2019 09:27:54 -0800 by gsv2com