Dynamics 365 CE On Premises: SQL Server Index Fragmentation Management

SQL Server Index Fragmentation Management

Regarding index fragmentation and rebuilding indexes, a general recommendation to start with is to implement a Rebuild index task for objects having a fragmentation higher than 30%.

If there is a time window, you can also implement as a second step a Reorganize index task for indexes having a fragmentation less than 30% and higher than 15%. You need to be aware that a Reorganize index task will burn a lot of transaction log and the performance improvement will be minor.

To perform the index defragmentation, SQL script was also provided and can be used as a maintenance plan.

USE MASTER
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- REPLACE ORGANIZATION DATABASE NAME BELOW
USE [OrganizationName_MSCRM]
GO

SET NOCOUNT ON
DECLARE
@DB_NAME SYSNAME,
@TAB_NAME SYSNAME,
@IND_NAME VARCHAR(5000),
@SCHEMA_NAME SYSNAME,
@FRAG FLOAT,
@PAGES INT

SET @DB_NAME=DB_NAME()
CREATE TABLE #TEMPFRAG
(
TABLE_NAME SYSNAME,
INDEX_NAME VARCHAR(5000),
FRAG FLOAT,
PAGES INT,
SCHEM_NAME SYSNAME
)
EXEC ('USE ' @DB_NAME ';
INSERT INTO #TEMPFRAG
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))
--WHERE INDEX_ID<> 0
AND F.DATABASE_ID=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0'
)

DECLARE CUR_FRAG CURSOR FOR
SELECT * FROM #TEMPFRAG
OPEN CUR_FRAG
FETCH NEXT FROM CUR_FRAG INTO
@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME
WHILE @@FETCH_STATUS=0

	BEGIN
	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 ')
			END

		ELSE IF((@FRAG BETWEEN 15 AND 30 ) AND @PAGES>1000 )
		BEGIN
		--IF PAGE LEVEL LOCKING IS DISABLED (PLLD) THEN REBUILD 
			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 ']) ' )
			END TRY
			BEGIN CATCH
			IF ERROR_NUMBER()=2552
			EXEC ('USE [' @DB_NAME '];ALTER INDEX [' @IND_NAME '] ON [' @SCHEMA_NAME '].[' @TAB_NAME  '] REBUILD ')
			END CATCH
		END
	ELSE
		BEGIN
		EXEC ('USE [' @DB_NAME '];UPDATE STATISTICS [' @SCHEMA_NAME '].[' @TAB_NAME '] ([' @IND_NAME ']) ' )
		END
	END

FETCH NEXT FROM CUR_FRAG INTO
@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME
END

DROP TABLE #TEMPFRAG
CLOSE CUR_FRAG
DEALLOCATE CUR_FRAG

You could regularly run this script for any database that needs index management, if it is not in place already. Consider that rebuilding indexes can be a costly operation, so it should be carried out during off-peak hours. Also, note that one of the maintenance jobs performed by the Asynchronous Service is indeed index management. However, the script provided above does a much better job, so it is advisable to schedule it as part of the regular maintenance plans and disable the operation performed by the asynchronous service by setting it to run in year 3000 (the job itself cannot be disabled).

Walter Carlin - MBA, MCSE, MCSA, MCT, MCTS, MCPS, MBSS, MCITP, MS

Senior Customer Engineer - Dynamics 365 - Microsoft - Brazil