SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reindexing for fragmented indexes


Reindexing for fragmented indexes

Author
Message
mssqlsrv
mssqlsrv
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 585
Comments posted to this topic are about the item Reindexing for fragmented indexes
guptaprashant1982 19780
guptaprashant1982 19780
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 156
Very Useful script
Thanks :-)
aleksey donskoy
aleksey donskoy
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 533
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
Steven Willis
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4793 Visits: 1721
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





 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search