index fragmentation and how to deal with it, Sql Server

index fragmentation and how to deal with it

--suppose you have a table with index
--as the table gets larger index gets "fragmented"
--which means that it takes longer to search it on some values

--if fragmentation gets realy high you might observe that your code works fast on some values
--and slow on others (or slow on all values)
--fragmented indexes need to be rebuild manualy. Here is the proc that should be called every once in a while, 
--to ensure indexes are ok

--(another symptom that indexes are fragmented is when you look at the execution plan and actual number of rows is significantly (say by an order of magnitude)
--different than estimated number of rows)

CREATE PROCEDURE [dbo].[Optimize_Indexes] 
	-- Add the parameters for the stored procedure here
    --	@DBName nvarchar(100)
AS
BEGIN
	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 @dbid smallint;
	
	-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
	-- and convert object and index IDs to names.
	SET @dbid = DB_ID();
	--SET @dbid = DB_ID(@DBName);

SELECT
		[object_id] AS objectid,
		index_id AS indexid,
		partition_number AS partitionnum,
		avg_fragmentation_in_percent AS frag, page_count
	INTO #work_to_do
	FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
	WHERE avg_fragmentation_in_percent > 5.0  -- Allow limited fragmentation
	  AND index_id > 0 -- Ignore heaps
	  AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
	DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag 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;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
			IF @frag < 30.0
				SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
			IF @frag >= 30.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;
END


run \| edit \| history \| help	0