Maintain SQL Indexes

Rebuild and Reorganize Database Indexes

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stp_MaintainIndexesInDatabase]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[stp_MaintainIndexesInDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Daniel Petersen
-- Create date: 2/18/2014
-- Description:	Maintains Indexes in the current database
-- =============================================
CREATE PROCEDURE dbo.stp_MaintainIndexesInDatabase
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @QueryResult nvarchar(4000);
	SET @QueryResult = '';
 
	WITH Tables AS 
	(
		SELECT OBJECT_Name(ps.OBJECT_ID) as TableName
			,i.name as IndexName
			,MAX(ps.avg_fragmentation_in_percent) as Fragmentation
			,MAX(ps.page_count) as [PageCount]
		FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
			INNER JOIN sys.indexes i ON i.OBJECT_ID = ps.OBJECT_ID
			INNER JOIN sys.objects o ON ps.OBJECT_ID = o.OBJECT_ID
		WHERE ps.index_id > 0 -- Excludes heap indexes
			AND o.is_ms_shipped = 0 -- Excludes any objects created as a part of SQL Server installation
			AND i.name IS NOT NULL
			AND i.name <> '<Name of Missing Index, sysname,>'
        GROUP BY ps.OBJECT_ID, i.name
	)
    ,IndexesInNeedOfMaintenance AS
    (
        SELECT TableName
            ,IndexName
            ,Fragmentation
            ,[PageCount]
            ,CASE 
                WHEN Fragmentation > 30 THEN 'Rebuild' 
                WHEN Fragmentation <= 30 AND Fragmentation > 5 THEN 'Reorganize' 
                ELSE NULL 
             END as Action
        FROM Tables
        WHERE TableName NOT LIKE 'vw%'
			AND Fragmentation > 5
			AND PageCount >= 200 -- Threshold where it actually helps (Can be up to 1000 if performance is hurting)
    )
	SELECT @QueryResult = @QueryResult + 
		CASE 
			WHEN Action = 'Rebuild' THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME('dbo') + '.' + QUOTENAME(TableName) + ' REBUILD;'
			WHEN Action = 'Reorganize' THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME('dbo') + '.' + QUOTENAME(TableName) + ' REORGANIZE;'
		END
	FROM IndexesInNeedOfMaintenance
 
	IF @QueryResult <> '' 
		EXEC sp_executesql @QueryResult
END
GO

Rebuild and Reorganize Table Indexes

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stp_MaintainIndexesOnTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[stp_MaintainIndexesOnTable]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Daniel Petersen
-- Create date: 2/18/2014
-- Description:	Maintains Indexes on a particular table
-- =============================================
CREATE PROCEDURE dbo.stp_MaintainIndexesOnTable
	@table_name varchar(50) 
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @QueryResult nvarchar(500);
	SET @QueryResult = '';
 
	WITH Tables AS 
	(
		SELECT @table_name as TableName
			,i.name as IndexName
			,MAX(ps.avg_fragmentation_in_percent) as Fragmentation
			,MAX(ps.page_count) as [PageCount]
		FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@table_name), NULL, NULL, NULL) AS ps
			INNER JOIN sys.indexes i ON i.OBJECT_ID = ps.OBJECT_ID
			INNER JOIN sys.objects o ON ps.OBJECT_ID = o.OBJECT_ID
		WHERE ps.index_id > 0 -- Excludes heap indexes
			AND o.is_ms_shipped = 0 -- Excludes any objects created as a part of SQL Server installation
			AND i.name IS NOT NULL
			AND i.name <> '<Name of Missing Index, sysname,>'
		GROUP BY i.name
	)
	,IndexesInNeedOfMaintenance AS
    (
        SELECT TableName
            ,IndexName
            ,Fragmentation
            ,[PageCount]
            ,CASE 
                WHEN Fragmentation > 30 THEN 'Rebuild' 
                WHEN Fragmentation <= 30 AND Fragmentation > 5 THEN 'Reorganize' 
                ELSE NULL 
             END as Action
        FROM Tables
        WHERE TableName NOT LIKE 'vw%'
			AND Fragmentation > 5
			AND PageCount >= 200 -- Threshold where it actually helps (Can be up to 1000 if performance is hurting)
    )
	SELECT @QueryResult = @QueryResult + 
		CASE 
			WHEN Action = 'Rebuild' THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME('dbo') + '.' + TableName + ' REBUILD;'
			WHEN Action = 'Reorganize' THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME('dbo') + '.' + TableName + ' REORGANIZE;'
		END
	FROM IndexesInNeedOfMaintenance
 
	IF @QueryResult <> '' 
		EXEC sp_executesql @QueryResult
END
GO