Am back to SQL Server performance turning after a year of a lot of things, as always I love sharing what I am doing to help someone in need and to create a point of reference to my work.
Below is a SQL script that will allow SQL tables Index Rebuilding or Re-Organization, I added the Online flag to allow my tables to be accessible during the re-indexing process. You can reference BOL to see how the ONLINE flag works and the limitations that it brings along.
Remember for ONLINE feature to work you have to have enterprise, evaluation or the developer editions. For the purpose of my process, I needed to have the online flag enabled.
CREATE PROCEDURE [dbo].[usp_dbmaint_Reindexing_Statistics]
/*
This procedure determines the level of fragmentation on a database, based on fragmentation if your table is below 40%
the process reorganizes the indexes and if it’s over 40% the process rebuilds the indexes. At the end of the process,
I am updating statistics because Re-indexing will only update statistics regarding that specific Index but not the
Stats on the rest of your database.
Created by Leonard Mwangi www.etekglobalinc.com
*/
AS
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);
DECLARE @i int, @n int
SET @i = 0
IF OBJECT_ID('tempdb..#Reindexing','u') IS NOT NULL
BEGIN
drop table #Reindexing
END
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
,page_count
,0 as t_flag
INTO #Reindexing
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 20.0 AND index_id > 0;
SET @n = (SELECT COUNT(*) FROM #Reindexing)
WHILE @i < @n
BEGIN
SET @objectid = (SELECT TOP 1 objectid from #Reindexing where t_flag = 0);
SET @indexid = (SELECT TOP 1 indexid from #Reindexing where t_flag = 0);
SET @frag = (SELECT TOP 1 frag from #Reindexing where t_flag = 0);
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;
IF @frag > 20.0 and @frag < 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 40.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE = ON)';
EXEC (@command);
PRINT N'Executed: ' + @command;
UPDATE top (1) #Reindexing
SET t_flag = 1
WHERE t_flag = 0
SET @i = @i + 1
END