We often come across Developers / DBAs talking about the Job runs taking really long or the queries are taking very long to provide output. The First thing everyone thinks about is to check if there are any blocks / look at the fragmentation level / How long does the query take to pull the data.
Although it is simple to run some queries and fetch the results, but, it needs some manual efforts on understanding the fragmentation levels and which indexes needs to be Reorganized / Rebuilt.
Hence, I have come up with a simple stored procedure that identifies fragmentation level and then Reorganizes or Rebuilds them based on a certain percentage.
Create a SQL Agent Job to execute this SP based on whatever frequency suitable for your database system.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spDefragmentIndexes]
@readonly BIT = 0,
@RunDefragmentIndexes bit = 1,
@maxfrag DECIMAL=30
AS
BEGIN
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
IF ISNULL(@RunDefragmentIndexes,0) = 1
BEGIN
-- CREATE THE TEMPORARY TABLE TO HOLD FRAGMENTATION INFORMATION
CREATE TABLE #FragmentedIndexes (
DBName CHAR (255)
,TableName CHAR (255)
,IndexName CHAR (255)
,ObjectID INT
,index_type_desc CHAR (255)
,IndexSpaceUsedBytes DECIMAL
,avg_fragmentation_in_percent DECIMAL
,fragment_count INT
,avg_fragment_size_in_pages DECIMAL
,page_count INT
)
INSERT INTO #FragmentedIndexes (DBName,TableName,IndexName,ObjectID,index_type_desc,IndexSpaceUsedBytes,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count)
SELECT
DB_NAME() AS DBName
,'[' + s.Name + '].[' + OBJECT_NAME(ps.object_id) + ']' AS TableName
,i.name AS IndexName
,i.object_ID As ObjectID
,ips.index_type_desc
,ps.reserved_page_count*8.0*1024 AS IndexSpaceUsedBytes
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,ips.avg_fragment_size_in_pages
,ips.page_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
LEFT JOIN sys.Tables t on t.object_id = ps.object_id
LEFT JOIN sys.schemas s on s.schema_id = t.schema_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ips.avg_fragmentation_in_percent desc, ps.object_id, ps.index_id
IF @readonly = 1
BEGIN
-- WRITE TO OUTPUT START TIME FOR INFORMATION PURPOSES
--SELECT 'CURRENTLY FRAGMENTED INDEXES'
SELECT TableName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, avg_fragmentation_in_percent
FROM #FragmentedIndexes f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE avg_fragmentation_in_percent >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
Order by avg_fragmentation_in_percent desc
END
-- BEGIN STAGE 2: (DEFRAG) DECLARE CURSOR FOR LIST OF INDEXES TO BE DEFRAGGED
DECLARE indexes CURSOR FOR
SELECT TableName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, avg_fragmentation_in_percent
FROM #FragmentedIndexes f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE avg_fragmentation_in_percent >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- OPEN THE CURSOR
OPEN INDEXES
-- LOOP THROUGH THE INDEXES
FETCH NEXT
FROM INDEXES
INTO @tablename, @objectowner, @objectid, @indexname, @frag
IF @readonly = 1
BEGIN
-- WRITE TO OUTPUT START TIME FOR INFORMATION PURPOSES
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
--SELECT 'Now executing on ' + RTRIM(@indexname) + ' from table ' + RTRIM(@tablename) + ' with fragmentation of ' + CAST(@frag AS Varchar(10))
--SELECT(@execstr)
BEGIN TRY
IF (@frag < 30)
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REORGANIZE '
END
ELSE
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE = ON)'
END
IF (@readonly = 0)
BEGIN
EXEC (@execstr)
END
END TRY
BEGIN CATCH
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE = OFF)'
IF (@readonly = 0)
BEGIN
EXEC (@execstr)
END
END CATCH
--UPDATE STATS AFTER INDEX REBUILD / REORGANIZE
SELECT @execstr = 'UPDATE STATISTICS ' + RTRIM(@tablename)
EXEC (@execstr)
SET QUOTED_IDENTIFIER OFF
FETCH NEXT
FROM INDEXES
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- CLOSE AND DEALLOCATE THE CURSOR
CLOSE INDEXES
DEALLOCATE INDEXES
IF @readonly = 1
BEGIN
-- Report on finish time for information purposes
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
END
select *from #FragmentedIndexes
drop table #FragmentedIndexes
END
END
