Reindexing for fragmented indexes

• mssqlsrv

  SSCrazy

  Points: 2375

  More actions

  June 4, 2013 at 2:51 pm

  #394909

  Comments posted to this topic are about the item Reindexing for fragmented indexes

• guptaprashant1982 19780

  SSC Rookie

  Points: 36

  More actions

  June 7, 2013 at 12:13 pm

  #1622151

  Very Useful script

  Thanks

• aleksey donskoy

  Mr or Mrs. 500

  Points: 565

  More actions

  June 21, 2013 at 8:07 am

  #1626333

  Good script. Runs relatively fast.

  Very useful for a single database index maintenance.

  Missing index size analysis. Should skip low rowcount indexes. Should not attempt rebuild/reorganize indexes below 1000 pages as they have low impact on performance.

  Missing statistics maintenance.

  Thanks.

  Alex Donskoy

  Greenberg Trauriq PA

  Miami FL

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  June 21, 2013 at 2:18 pm

  #1626493

  Nice script. I'll let others worry about any additions/modifications such as mentioned by Aleksey. But I took the liberty of turning your script into two stored procedures. The code also requires an inline table-valued function for producing a virtual tally table which is posted below. That could easily be swapped out for a traditional tally table. Either way, the tally table gets rid of the WHILE loop.

  CREATE PROCEDURE dbo.UTIL_IndexFragmentation

  @isReorganize BIT

  ,@isRebuild BIT

  ,@lowerBound INT = 5 -- percent fragmentation

  ,@upperBound INT = 30 -- percent fragmentation

  AS

  BEGIN

  /*

  Usage examples:

  EXEC dbo.UTIL_IndexFragmentation 0,0,5,30 -- returns the results only

  EXEC dbo.UTIL_IndexFragmentation 1,0,5,30 -- REORGANIZE and show pre- and post-results

  EXEC dbo.UTIL_IndexFragmentation 0,1,5,30 -- REBUILD and show pre- and post-results

  EXEC dbo.UTIL_IndexFragmentation 1,1,5,30 -- REORGANIZE only (has precedence if both selected)

  EXEC dbo.UTIL_IndexFragmentation 0,0,10,75

  EXEC dbo.UTIL_IndexFragmentation 1,0,10,75

  EXEC dbo.UTIL_IndexFragmentation 0,1,10,75

  */

  SET NOCOUNT ON

  DECLARE

  @cmd NVARCHAR(MAX)

  ,@rowCount INT

  IF @isReorganize <> 1

  SET @isReorganize = 0

  IF @isRebuild <> 1

  OR @isReorganize = 1

  SET @isRebuild = 0

  SET @lowerBound = ISNULL(NULLIF(@lowerBound,0),5)

  SET @upperBound = ISNULL(NULLIF(@upperBound,100),30)

  --Tables to Hold Fragmented Objects

  IF OBJECT_ID('tempdb..#Reorganize') IS NOT NULL

  DROP TABLE #Reorganize

  CREATE TABLE #Reorganize

  (

  ID INT IDENTITY(1,1) NOT NULL

  ,Schemaname VARCHAR(50) NULL

  ,tablename VARCHAR(50) NULL

  ,Indexname VARCHAR(150) NULL

  ,Fragmentation FLOAT NULL

  ,PRIMARY KEY(ID)

  )

  IF OBJECT_ID('tempdb..#Rebuild') IS NOT NULL

  DROP TABLE #Rebuild

  CREATE TABLE #Rebuild

  (

  ID INT IDENTITY(1,1) NOT NULL

  ,Schemaname VARCHAR(50) NULL

  ,tablename VARCHAR(50) NULL

  ,Indexname VARCHAR(150) NULL

  ,Fragmentation FLOAT NULL

  ,PRIMARY KEY(ID)

  )

  IF @isReorganize = 0 AND @isRebuild = 0

  BEGIN

  INSERT INTO #Reorganize

  EXEC dbo.UTIL_IndexFragmentationStatus 1,@lowerBound,@upperBound

  INSERT INTO #Rebuild

  EXEC dbo.UTIL_IndexFragmentationStatus 2,@lowerBound,@upperBound

  SELECT * FROM #Reorganize

  SELECT * FROM #Rebuild

  END

  ELSE IF @isReorganize = 1

  BEGIN

  SET @cmd = ''

  SET @rowCount = 0

  INSERT INTO #Reorganize

  EXEC dbo.UTIL_IndexFragmentationStatus 1,5,30 --@lowerBound,@upperBound

  SELECT * FROM #Reorganize

  SELECT @rowCount = @@ROWCOUNT

  IF @rowCount > 0

  BEGIN

  SELECT

  @cmd =

  (SELECT

  N'ALTER INDEX [' + r.indexname + '] ON [' + r.[SCHEMANAME] + '].[' + r.tablename + '] REORGANIZE'+CHAR(10)

  FROM

  #Reorganize AS r

  INNER JOIN

  dbo.itvfTally(1,@rowCount) AS t

  ON r.ID = t.N

  FOR XML PATH(''))

  END

  PRINT @cmd

  EXEC(@cmd)

  --display the updated results

  TRUNCATE TABLE #Reorganize

  INSERT INTO #Reorganize

  EXEC dbo.UTIL_IndexFragmentationStatus 1,@lowerBound,@upperBound

  SELECT * FROM #Reorganize

  END

  ELSE IF @isRebuild = 1

  BEGIN

  SET @cmd = ''

  SET @rowCount = 0

  INSERT INTO #Rebuild

  EXEC dbo.UTIL_IndexFragmentationStatus 2,@lowerBound,@upperBound

  SELECT * FROM #Rebuild

  SELECT @rowCount = @@ROWCOUNT

  IF @rowCount > 0

  BEGIN

  SELECT

  @cmd =

  (SELECT

  N'ALTER INDEX [' + r.indexname + '] ON [' + r.[SCHEMANAME] + '].[' + r.tablename + '] REBUILD'+CHAR(10)

  FROM

  #Rebuild AS r

  INNER JOIN

  dbo.itvfTally(1,@rowCount) AS t

  ON r.ID = t.N

  FOR XML PATH(''))

  END

  PRINT @cmd

  EXEC(@cmd)

  --display the updated results

  TRUNCATE TABLE #Rebuild

  INSERT INTO #Rebuild

  EXEC dbo.UTIL_IndexFragmentationStatus 2,@lowerBound,@upperBound

  SELECT * FROM #Rebuild

  END

  ELSE

  BEGIN

  SELECT * FROM #Reorganize

  SELECT * FROM #Rebuild

  END

  END

  GO

  This second procedure is called by the first procedure.

  CREATE PROCEDURE dbo.UTIL_IndexFragmentationStatus

  @queryType INT -- 1 = reorganize; 2 = rebuild

  ,@lowerBound INT = 5 -- percent fragmentation

  ,@upperBound INT = 30 -- percent fragmentation

  AS

  BEGIN

  SET NOCOUNT ON

  SET @lowerBound = ISNULL(NULLIF(@lowerBound,0),5)

  SET @upperBound = ISNULL(NULLIF(@upperBound,100),30)

  IF @queryType = 1

  BEGIN

  SELECT

  s.name AS Schemaname

  ,o.name AS tablename

  ,i.name AS Indexname

  ,ips.avg_fragmentation_in_percent AS Fragmentation

  FROM

  sys.objects o

  LEFT OUTER JOIN sys.schemas s

  ON o.schema_id = s.schema_id

  LEFT OUTER JOIN sys.indexes i

  ON o.object_id = i.object_id

  LEFT OUTER JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS IPS

  ON i.object_id = IPS.object_id

  AND i.index_id = ips.index_id

  WHERE

  o.type = 'U'

  AND i.index_id > 0

  AND avg_fragmentation_in_percent BETWEEN @lowerBound AND @upperBound

  END

  ELSE IF @queryType = 2

  BEGIN

  SELECT

  s.name AS Schemaname

  ,o.name AS tablename

  ,i.name AS Indexname

  ,ips.avg_fragmentation_in_percent AS Fragmentation

  FROM

  sys.objects o

  LEFT OUTER JOIN sys.schemas s

  ON o.schema_id = s.schema_id

  LEFT OUTER JOIN sys.indexes i

  ON o.object_id = i.object_id

  LEFT OUTER JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) AS IPS

  ON i.object_id = IPS.object_id

  AND i.index_id = ips.index_id

  WHERE

  o.type = 'U'

  AND i.index_id > 0

  AND avg_fragmentation_in_percent > @lowerBound

  END

  ELSE

  RETURN

  END

  GO

  Tally table itvf

  CREATE FUNCTION [dbo].[itvfTally]

  (

  @pMin BIGINT

  ,@pMax BIGINT

  )

  RETURNS TABLE

  WITH SCHEMABINDING

  AS

  RETURN

  WITH E1(N)

  AS (

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  UNION ALL

  SELECT

  1

  ), --10E+1 or 10 rows

  E2(N)

  AS (

  SELECT

  1

  FROM

  E1 a

  ,E1 b

  ), --10E+2 or 100 rows

  E4(N)

  AS (

  SELECT

  1

  FROM

  E2 a

  ,E2 b

  ), --10E+4 or 10,000 rows max

  E8(N)

  AS (

  SELECT

  1

  FROM

  E4 a

  ,E4 b

  ), --10E+8 or 100,000,000 rows max

  cteTally(N)

  AS (

  SELECT

  RowNum AS N

  FROM

  (

  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum

  FROM E8

  ) R

  WHERE R.RowNum BETWEEN @pMin AND @pMax

  )

  SELECT N FROM cteTally

   

• alexander.parakhnevich 27977

  Old Hand

  Points: 329

  More actions

  December 23, 2017 at 2:07 am

  #1973321

  Hi all,
  I had a simmilar approach and published my query hier