Sql server maintenance - Index Rebuild, Reorganize, and Statistics in Maintenance Plans in SQL Server 2016

SQL Server Maintenance Plan Best Practices

• Check Database Integrity
• Shrink Database
• Rebuild Index
• Reorganize Index
• Update Statistics
• Execute SQL Server Agent Job
• History Cleanup
• Back Up Database (Full)
• Back Up Database (Differential)
• Backup Database (Transaction Log)
• Maintenance Cleanup Task

Symantec’s standard SQL maintenance includes:
- Shrink databases (optional step that, if used, must be performed before backing up the transaction logs as the shrink operation causes significant growth in those logs)
- Rebuild Indexes
- Update Statistics

Reorganize and Rebuild Indexes and Update Statistics

Stored Procedure :

This stored procedure can be divided into 4 parts:

1. Check database integrity
2. Reorganize Indexes
3. Rebuild Indexes
4. Updates Statistics

CREATE PROCEDURE [maintenance].[usp_IndexStatisticsMaintenance]
@DBName AS NVARCHAR(128)
AS

DECLARE @ERRORE INT
--Check Database Error
DBCC CHECKDB WITH NO_INFOMSGS
SET @ERRORE = @@ERROR
IF @ERRORE = 0 
BEGIN
 DECLARE @RC INT
 DECLARE @Messaggio VARCHAR(MAX)
 DECLARE @Rebild AS VARCHAR(MAX)
 DECLARE @Reorganize AS VARCHAR(MAX)

 SET @Reorganize = ''
 SET @Rebild = ''

 SELECT  @Reorganize = @Reorganize + ' ' + _
 'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
   REORGANIZE WITH ( LOB_COMPACTION = ON )'
 FROM sys.dm_db_index_physical_stats
     (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
   inner join sys.tables t
    on fi.[object_id] = t.[object_id]
   inner join sys.indexes i
    on fi.[object_id] = i.[object_id] and
    fi.index_id = i.index_id
 where t.[name] is not null and i.[name] is not null 
   and avg_fragmentation_in_percent > 10   
   and avg_fragmentation_in_percent <=35
 order by t.[name]

 EXEC (@Reorganize)

 SELECT  @Rebild = @Rebild + ' ' + _
 'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
   REBUILD WITH (ONLINE = OFF )'
 FROM sys.dm_db_index_physical_stats
     (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
   inner join sys.tables t
    on fi.[object_id] = t.[object_id]
   inner join sys.indexes i
    on fi.[object_id] = i.[object_id] and
    fi.index_id = i.index_id
 where avg_fragmentation_in_percent > 35 and t.[name] is not null and i.[name] is not null
 order by t.[name]

 EXEC (@Rebild)
END

-- if there are not error update statistics
SET @ERRORE = @@ERROR
IF @ERRORE = 0
 BEGIN
  EXEC sp_updatestats
 END
 
;

Index Fragmentation:

-- Ensure a USE <databasename> statement has been executed first. 

USE   DB_NAME 

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
   BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 10.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 10.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

STATISTICS:

How to determine last Update Statistics run: 

USE   DB_Name 

SELECT o.name 'Table Name', i.name 'Index Name', i.type 'Index Type'
,STATS_DATE(i.[object_id], i.[index_id]) 'Last Statistics Update'
FROM sys.indexes i, sys.objects o
WHERE i.[object_id]=o.[object_id]
AND o.type='U' AND i.name IS NOT NULL
--AND STATS_DATE(i.object_id, i.index_id) < DATEADD(DAY, -1, GETDATE())
ORDER BY 'Last Statistics Update' DESC

SQLServerReorganizeOrRebuildIndexFragmentation.ps1

################################################################################################################################
#
# Name: PowerShell Script to check and fix SQL Server Index Fragmentation on a Database
#
# Overview: Uses SQL Server SMO to determine index fragmentation. Then Reorganizes or Rebuilds depending on '%' of fragmentation
#
# Usage: Set the '$sqlserver' and '$database' variables to suit your environment
#
# Source: http://www.youdidwhatwithtsql.com/managing-index-fragmentation-with-powershell
# 
# Version History:
# 1.0 06/06/2011 - Initial release
#
################################################################################################################################
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;
 
# Set sql server and database name here
$sqlserver = "localhost\sql2005"; #Change your SQL Server Instance here
$database = "AdventureWorks"; #Change your Database name here
 
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $sqlserver;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");
$db = $srv.Databases[$database];
 
# Get table count
$table_count = $db.Tables.Count;
$i = 0;
 
# First script out the drops
foreach($table in $db.Tables)
{
    Write-Progress -Activity "Checking table $table" -PercentComplete (($i / $table_count) * 100) -Status "Processing indexes" -Id 1;
    $i++;
    foreach($index in $table.Indexes)
    {
        $index_name = $index.Name;
        Write-Progress -Activity "Checking table $table" -PercentComplete (($i / $table_count) * 100) -Status "Processing index $index_name" -Id 1;
        # Get the fragmentation stats
        $frag_stats = $index.EnumFragmentation();
 
        # Get the properties we need to work with the index
        $frag_stats | ForEach-Object {
                        $Index_Name = $_.Index_Name;
                        $Index_Type = $_.Index_Type;
                        $Average_Fragmentation = $_.AverageFragmentation;
                                    };
        Write-Host -ForegroundColor Green "$Index_Type $Index_Name has a fragmentation percentage of $Average_Fragmentation";
 
        # Here we decide what to do based on the level on fragmentation
        if ($Average_Fragmentation -gt 40.00) #Percentage Average Fragmentation to prompt an Index Rebuild
        {
            Write-Host -ForegroundColor Red "$Index_Name is more than 40% fragmented and will be rebuilt.";
            $index.Rebuild();
            Write-Host -ForegroundColor Green "$Index_Name has been rebuilt.";
        }
        elseif($Average_Fragmentation -ge 10.00 -and $Average_Fragmentation -le 40.00) #Percentage Average Fragmentation to prompt an Index Reorganize
        {
            Write-Host -ForegroundColor Red "$Index_Name is between 10-40% fragmented and will be reorganized.";
            $index.Reorganize();
            Write-Host -ForegroundColor Green "$Index_Name has been reorganized.";
        }
        else
        {
            Write-Host -ForegroundColor White "$Index_Name is healthy, with $Average_Fragmentation% fragmentation, and will be left alone.";
        }
 
    }
}
Write-Progress -Activity "Finished processing `"$database`" indexes." -PercentComplete 100 -Status "Done" -Id 1;
Start-Sleep -Seconds 2;