인덱스를 리빌드 + 통계 업데이트를 한꺼번에^^

master.sql

 

DBA의 통상점검 작업 중 하나인 인덱스 리빌드와 통계 업데이트를 한번에 처리할 수 있는 프로시져입니다.

1. 기능 
가. Fragmentation >=30 AND PAGES>1000 일때 rebuild
나. Fragmentation between 15 to 29 AND PAGES>1000 일때 reorganize&updatestatistics
다. 가와 나의 조건에 들어가지 있는 다면, update the statistics
  
2. 원본 소스
   http://www.sqlservercentral.com/scripts/automated+index+defragmentation+script/68802/

3. Version
   SQL2005, SQL2008

4. Source

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_indexdefragmentation]
	@p_dbname SYSNAME = 'NL'
	, @p_Fillfactor	TINYINT = 80
/*
	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*************************
	
	참고 URL : http://www.sqlservercentral.com/scripts/automated+index+defragmentation+script/68802/
	최종 수정일 : 2011-04-28
	최종 수정자 : 주디아줌마
*/
AS
BEGIN
	SET NOCOUNT ON;

	IF @p_dbname = 'NL'
		SET @p_dbname = DB_NAME();

	DECLARE @a_Fillfactor VARCHAR(30)
	SET @a_Fillfactor = CONVERT(VARCHAR(30), @p_Fillfactor);


	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 OBJECT_NAME(F.OBJECT_ID) obj,i.name ind,
		f.avg_fragmentation_in_percent,
		f.page_count,table_schema
		FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F
		JOIN SYS.INDEXES I
		ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id
		JOIN INFORMATION_SCHEMA.TABLES S
		ON (s.table_name=OBJECT_NAME(F.OBJECT_ID))
		AND f.database_id=DB_ID()
		AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0
		WHERE F.index_id > 0 AND F.index_id <= 10000	'	
	)

	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 WITH ( FILLFACTOR = ' + @a_Fillfactor + ' ) ')
				--INSERT INTO msdb.dbo.dba_defrag_maintenance_history
				--VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
				
				PRINT 'USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD WITH ( FILLFACTOR = ' + @a_Fillfactor + ' ) '
			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())

				PRINT 'USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE '
				PRINT 'USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) '
			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
				BEGIN
					EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD WITH ( FILLFACTOR = ' + @a_Fillfactor + ' ) ')
					--INSERT INTO msdb.dbo.dba_defrag_maintenance_history
					--VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',GETDATE())
					PRINT 'USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD WITH ( FILLFACTOR = ' + @a_Fillfactor + ' ) '
				END
			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()) 		
		PRINT 'USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' 
	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()) 
		PRINT 'USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']';
	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

5. Test Query

use master
go

exec dbo.[sp_indexdefragmentation]  AdventureWorks2008
go

use AdventureWorks2008
go

exec dbo.[sp_indexdefragmentation]