How to identify and solve the fragmentation in SQL Server?

How does fragmentation occur in SQL Server?

If there is an index on the object, the data is ordered in the page using the slot array. When a new insert comes, SQL engine will search an available page to keep the data by using the index. The data should be saved in the specific page because it should be ordered. The data should be in the specific page but if there is no enough space in the page, a new page will be allocated and the original page will be split and previous, next page numbers will be updated. This process will cause fragmentation. It can be monitored using the “page split/sec” counter.

Another reason is because of the data type. Let’s think about that we have one table with a column which is varchar(100) data type. If 10 characters are inserted, it will allocate 10 bytes and if the page is full and if the data is updated to 20 characters it will look for 10 more bytes in the same page. But the page is full so it will place a pointer to forward the date to a new page. This process will also cause fragmentation. It can be monitored using the “forwarded records/sec” counter.

If the pages are fragmented, it will increase the disk search time and it will cause the performance issue.

How can we monitor the fragmentation and How can we fix the fragmentation issue?

Maintenance Plans can be used like below.

Fragmentation_1 Fragmentation_2
Create a maintenance plan Maintenance Tasks

If the fragmentation level is greater than 25 or 30 percent, Rebuild Index Task is recommended otherwise Reorganize Index Task can be used. But there is no any option to give the threshold in those tasks.

Rebuild Index Task also updates the statistics but Reorganize Index Task does not.

Rebuild Index Task can take longer time to be completed. There may be some big indexes on read-only tables so it is not useful to rebuild them. It will only cause longer time. How can we eliminate those indexes from the plan? The answer is custom script.

You can check the script below and implement it on your environment. You can make some customization on that. Please let us know if you have any improvement on that

 

 

 --part 1: create the objects 
SET NOCOUNT ON;

if not exists (select name from sys.databases where name = 'SQLAdmin')
    create database SQLAdmin
go
use SQLAdmin
go
if not exists (select name from sys.objects where name = 'fragmentation_history')
   create table fragmentation_history(
        history_id int identity(1,1), 
        database_id smallint NULL,
        database_name sysname NULL,
        schema_id int null,
        schema_name sysname null,
        object_id int NULL,
        object_name sysname NULL,
        index_id int NULL,
        index_name sysname NULL,
        partition_number int NULL,
        avg_fragmentation_in_percent_before float NULL,
        avg_fragmentation_in_percent_after float NULL,
        alter_start datetime NULL,
        alter_end datetime NULL,
        progress datetime NULL
    ) ON [PRIMARY]
if not exists (select name from sys.objects where name = 'sql_errors')
    create table sql_errors(
        error_id int identity(1,1),
        command varchar(4000) null,
        error_number int null,
        error_severity smallint null,
        error_state smallint null,
        error_line int null,
        error_message varchar(4000) null,
        error_procedure varchar(200) null,
        time_stamp datetime null,
        primary key clustered 
        (
            error_id asc
        ) ON [PRIMARY]
    ) ON [PRIMARY]
GO
if exists (select name from sys.objects where name = 'p_error_handling')
    drop procedure p_error_handling
go
create procedure p_error_handling 
    @command varchar(4000)
as
DECLARE @error_number int
DECLARE @error_severity int
DECLARE @error_state int
DECLARE @error_line int
DECLARE @error_message varchar(4000)
DECLARE @error_procedure varchar(200)
DECLARE @time_stamp datetime

SELECT @error_number = isnull(error_number(),0),
        @error_severity = isnull(error_severity(),0),
        @error_state = isnull(error_state(),1),
        @error_line = isnull(error_line(), 0),
        @error_message = isnull(error_message(),'NULL Message'),
        @error_procedure = isnull(error_procedure(),''),
        @time_stamp = GETDATE();

INSERT INTO dbo.sql_errors (command, error_number, error_severity, error_state, error_line, error_message, error_procedure, time_stamp)
SELECT @command, @error_number, @error_severity, @error_state, @error_line, @error_message, @error_procedure, @time_stamp

GO

--part 2: Collect the fragmentation data
SET NOCOUNT ON;
DECLARE @command varchar(8000);
DECLARE @databaseid int;
DECLARE @databasename sysname;

DECLARE database_list CURSOR FOR 
    SELECT database_id, name 
    FROM sys.databases 
    where database_id > 4 and state not in (1)
    order by name

-- Open the cursor.
OPEN database_list

-- Loop through the partitions.
FETCH NEXT FROM database_list 
   INTO @databaseid, @databasename

WHILE @@FETCH_STATUS = 0
    BEGIN
        --set @databasename = 'AdventureWorks2008'
        set @command = 'use [' + @databasename + '];'
        set @command = @command + '
                                    insert into SQLAdmin.dbo.fragmentation_history (database_id, database_name, schema_id, schema_name, object_id, object_name, index_id, index_name, partition_number, avg_fragmentation_in_percent_before)
                                    SELECT     D.database_id, D.name, O.schema_id, s.name, IPS.object_id, O.name, IPS.index_id, I.name, partition_number, avg_fragmentation_in_percent
                                    FROM sys.dm_db_index_physical_stats (' + convert(varchar(3), @databaseid) + ', NULL, NULL , NULL, ''LIMITED'') IPS
                                    join sys.databases D on IPS.database_id=D.database_id
                                    join sys.objects O on IPS.object_id = O.object_id
                                    join sys.schemas as s ON s.schema_id = O.schema_id
                                    join sys.indexes I on IPS.object_id = I.object_id and IPS.index_id = I.index_id
                                    WHERE D.state not in (1) and avg_fragmentation_in_percent > 5.0 AND IPS.index_id > 0
                                    and page_count>1000                                    
                                    '
        exec (@command)
        FETCH NEXT FROM database_list 
           INTO @databaseid, @databasename
    END;
-- Close and deallocate the cursor.
CLOSE database_list;
DEALLOCATE database_list;

--part 3: fix the fragmentation with rebuild or reorganize according to the threshold
SET NOCOUNT ON;
DECLARE @historyid int;
DECLARE @command varchar(8000);
DECLARE @command1 varchar(7950);
DECLARE @command2 varchar(50);
DECLARE @databaseid int;
DECLARE @databasename sysname;
DECLARE @schemaid int;
DECLARE @schemaname sysname;
DECLARE @objectid int;
DECLARE @objectname sysname;
DECLARE @indexid int;
DECLARE @indexname sysname;
DECLARE @partitionnumber bigint;
DECLARE @frag_before float;
DECLARE @frag_after float;
DECLARE @alterstart datetime;
DECLARE @alterend datetime;
DECLARE @progress datetime;

set @progress = getdate()

DECLARE fragmentation_list CURSOR FOR 
    SELECT history_id, database_id, database_name, schema_id, schema_name, object_id, object_name, index_id, index_name, partition_number, avg_fragmentation_in_percent_before 
    FROM SQLAdmin.dbo.fragmentation_history 
    where progress is null;

-- Open the cursor.
OPEN fragmentation_list;

-- Loop through the partitions.
FETCH NEXT
   FROM fragmentation_list
   INTO @historyid, @databaseid, @databasename, @schemaid, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnumber, @frag_before;

WHILE @@FETCH_STATUS = 0

    BEGIN;

        -- 25 is an arbitrary decision point at which to switch between reorganizing and rebuilding
        IF @frag_before < 25.0
            BEGIN;
                set @command1 = 'use ' + @databasename + ';'
                set @command1 = @command1 + '
                                            DECLARE @partitioncount bigint;
                                            SELECT @partitioncount = count (*)
                                            FROM sys.partitions
                                            WHERE object_id = ' + convert(varchar(10), @objectid) + ' AND index_id = ' + convert(varchar(10), @indexid) + ';
                                            IF @partitioncount > 1
                                                ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' + ' PARTITION=' + CONVERT (CHAR, @partitionnumber) + ';
                                            else
                                                ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' + ';
                                            UPDATE STATISTICS ' + @schemaname + '.[' + @objectname + '] [' + @indexname + '];
                                            '
                set @command = @command1
            END;

        IF @frag_before >= 25.0
            BEGIN;
                set @command1 = 'use ' + @databasename + ';'
                set @command1 = @command1 + '
                                            DECLARE @partitioncount bigint;
                                            SELECT @partitioncount = count (*)
                                            FROM sys.partitions
                                            WHERE object_id = ' + convert(varchar(10), @objectid) + ' AND index_id = ' + convert(varchar(10), @indexid) + ';
                                            IF @partitioncount > 1
                                                ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD' + ' PARTITION=' + CONVERT (CHAR, @partitionnumber) + '
                                            else
                                                ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD' + '
                                            '
                set @command2 = 'with (online=ON)'
                set @command = @command1 + ' ' + @command2
            END;

            begin try
                    set @alterstart = getdate() 
                    EXEC (@command);
                    set @alterend = getdate()
    
                    select @frag_after = avg_fragmentation_in_percent  
                    from sys.dm_db_index_physical_stats(@databaseid, @objectid, @indexid, @partitionnumber,  'LIMITED')

                    update SQLAdmin.dbo.fragmentation_history
                    set alter_start = @alterstart,
                        alter_end = @alterend,
                        avg_fragmentation_in_percent_after = @frag_after,
                        progress = @progress
                    where history_id = @historyid
            end try    
            begin catch
                    if error_number() = 2275 or error_number() = 153
                        begin
                            set @alterstart = getdate() 
                            EXEC (@command1);
                            set @alterend = getdate()
    
                            select @frag_after = avg_fragmentation_in_percent  
                            from sys.dm_db_index_physical_stats(@databaseid, @objectid, @indexid, @partitionnumber,  'LIMITED')

                            update SQLAdmin.dbo.fragmentation_history
                            set alter_start = @alterstart,
                                alter_end = @alterend,
                                avg_fragmentation_in_percent_after = @frag_after,
                                progress = @progress
                            where history_id = @historyid
                        end
                    --select @command command, @databasename database_name, @schemaname schema_name, @objectname object_name, @indexname index_name, @partitionnumber partition_number, 
                        --    error_number() error_number , ERROR_SEVERITY() error_severity, ERROR_STATE() error_state, ERROR_LINE() error_line, ERROR_MESSAGE() error_message
                    exec p_error_handling  @command
            end catch

            FETCH NEXT
               FROM fragmentation_list
               INTO @historyid, @databaseid, @databasename,  @schemaid, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnumber, @frag_before;
    END;

-- Close and deallocate the cursor.
CLOSE fragmentation_list;
DEALLOCATE fragmentation_list;

--part 4: get the most expensive index maintenance by duration
select datediff(ms, alter_start, alter_end) duration_ms, alter_start, alter_end,
database_name, schema_name, object_name, index_name, avg_fragmentation_in_percent_before, 
avg_fragmentation_in_percent_after from SQLAdmin.dbo.fragmentation_history
order by duration_ms desc