Change to indexdefragmentation script
My take on Muthukkumaran Kaliyamoorhty’s script, deals with schemas for indexes and tables.
Ran from stored job to rebuild or reorganize and update stats depending on if they need it or not.
Build history table:
USE [msdb]
GO
/****** Object: Table [dbo].[dba_defrag_maintenance_history] Script Date: 05/13/2014 10:43:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dba_defrag_maintenance_history](
[db_name] [sysname] NOT NULL,
[table_name] [sysname] NOT NULL,
[index_name] [sysname] NOT NULL,
[frag] [float] NULL,
[page] [int] NULL,
[action_taken] [varchar](35) NULL,
[date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[dba_defrag_maintenance_history] ADD DEFAULT (getdate()) FOR [date]
GO
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[indexdefragmentation] Script Date: 05/13/2014 09:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary: Remove the Index Fragmentation to improve the query performance
Contact: Muthukkumaran Kaliyamoorhty SQL DBA
Description: This Sproc will take the fragmentation details and do four kinds of work.
1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
3. Check the fragmentation between 15% to 29% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the three conditions is false
ChangeLog:
Date Coder Description
2011-11-23 Muthukkumaran Kaliyamoorhty created
*************************All the SQL keywords should be written in upper case*************************
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT
SET @db_name=@p_dbname
--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schema_name SYSNAME
)
EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schema_name)
SELECT
T.Name AS obj,
I.[name] AS ind,
f.avg_fragmentation_in_percent,
f.page_count,
SCHEMA_NAME(T.Schema_ID) AS table_schema
FROM sys.tables T WITH (NOLOCK)
LEFT OUTER JOIN sys.indexes I
ON T.object_id = I.object_id
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) F
On F.object_id=i.object_id
WHERE T.type = ''U''
ORDER BY T.Name
'
)
SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag
TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history
WHILE (@min_id<=@max_id)
BEGIN
SELECT
@tab_name=table_name,
@schema_name=schema_name,
@ind_name=index_name ,
@frag=frag ,
@pages=pages
FROM #tempfrag WHERE id = @min_id
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
BEGIN
IF (@frag>=30 AND @pages>1000)
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',GETDATE())
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',GETDATE())
END CATCH
END
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
ELSE
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',GETDATE())
END
END
ELSE
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',GETDATE())
END
SET @min_id=@min_id+1
END
DROP TABLE #tempfrag
INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history
END