June 4, 2013 at 2:51 pm
Comments posted to this topic are about the item Reindexing for fragmented indexes
June 7, 2013 at 12:13 pm
Very Useful script
Thanks 🙂
June 21, 2013 at 8:07 am
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
June 21, 2013 at 2:18 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply