not entirely an original work, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2865707&SiteID=1.
But I'm not very fond of just copy, paste, and pray. I wanted to ensure I fully understood what was going on. Also adapted it to fit my specific needs. But I would also hope this is a wee bit easier to grasp. Let me know if i'm missed something.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexMaintenance] Script Date: 04/01/2008 13:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: BOBMIHADA
-- Create date: 20080401
-- Description: Doesn't take partietions into account
Note create tabe "TrackFragmentation" and update
accordingly currently "[SCHEDULER].dbo.TrackFragmentation"
============================================= */
ALTER PROCEDURE [dbo].[sp_IndexMaintenance]
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewExecuteLoad VARCHAR(20) = 'View' --// View | Execute | Load
, @Rebuild_UsingLast BIT = 0
, @MinRows INT = 1 --// 1 - x
, @MaxRows INT = 500000000 --// 1 - x
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@DBID INT
, @DateCreated DateTime
, @IndexType VarChar(100)
, @MaintenanceAction VarChar(100)
, @RunID INT
, @Counter INT
, @Count INT
, @sql_Action VARCHAR(8000)
, @Use VARCHAR(255)
, @ErrorMessage NVARCHAR(4000)
, @Msg NVARCHAR(1000)
, @RC_SUCCESSFUL INT
, @RC_FAILED INT
, @RowsAffected INT
, @Error INT
DECLARE @tmp TABLE (idx int identity(1,1), sql_action VarChar(8000), TableName VarChar(255))
SELECT
@RC_SUCCESSFUL =320
,@RC_FAILED = -1
,@RowsAffected = 0
,@MaintenanceAction = 'NOTHING'
,@Msg = ''
/*
Perform validation
*/
SELECT @MaintenanceAction = 'LOAD' --//Default
--// What do you want to do
IF LEN(ISNULL(@ViewType,'~')) > 1
BEGIN
IF @ViewType NOT IN ('RECENT','CHANGE')
BEGIN
SELECT @Msg = 'Invalid @ViewType: ' + @ViewType
GOTO PROBLEM
END
SELECT @MaintenanceAction = 'VIEW'
END
ELSE IF (@Rebuild_CIX = 1 OR @Rebuild_NCIX = 1)
BEGIN
IF ISNULL(@Rebuild_ViewExecuteLoad,'~') NOT IN ('VIEW','EXECUTE','LOAD')
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid @Rebuild_ViewOrExecute: ' + @Rebuild_ViewExecuteLoad
IF NOT ISNULL(@MinFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinFragPercent' + LTRIM(str(@MinFragPercent))
IF NOT ISNULL(@MaxFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxFragPercent' + LTRIM(str(@MaxFragPercent))
IF NOT ISNULL(@MinDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinDensity' + LTRIM(str(@MinDensity))
IF NOT ISNULL(@MaxDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxDensity' + LTRIM(str(@MaxDensity))
IF @Rebuild_CIX = 1 AND @Rebuild_NCIX = 1
SELECT @Msg = @Msg + char(10) + char(13) + 'You can only Rebuild either NONCLUSTERED or CLUSTERED, not both'
IF ISNULL(@MinRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinRows' + LTRIM(str(@MinRows))
IF ISNULL(@MaxRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxRows' + LTRIM(str(@MaxRows))
IF LEN(ISNULL(@Msg,'')) > 1 GOTO PROBLEM
END
ELSE IF ISNULL(@Rebuild_ViewExecuteLoad,'~') <> 'LOAD'
BEGIN
SELECT @Msg = 'What do you want to do?'
GOTO PROBLEM
END
SELECT
@DBID = DB_ID(@DBName)
,@DateCreated = GetDate()
IF @DBID IS NULL
BEGIN
SELECT @Msg = 'Invalid : @DBName' + @DBName
GOTO PROBLEM
END
IF @MaintenanceAction = 'VIEW'
BEGIN
IF @ViewType = 'CHANGE'
BEGIN
-- View Frag change
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT) [Prev Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT) [Prev %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, CAST(ROUND(B.MBUsed,2) AS FLOAT) [Prev MB]
, A.Rows [Now Rows]
, B.Rows [Prev Rows]
,Max(A.RunID) [Now RunID]
,A.DateCreated [Now RunTime]
,Max(B.RunID) [Prev RunID]
,B.DateCreated [Prev RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
LEFT JOIN [SCHEDULER].dbo.TrackFragmentation B ON B.TableName = A.Tablename AND B.IndexName = A.IndexName
WHERE
A.RunID > B.RunID
AND A.DBName = @DBName
GROUP BY A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT)
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT)
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(A.MBUsed,2) AS FLOAT)
, CAST(ROUND(B.MBUsed,2) AS FLOAT)
, A.Rows
, B.Rows
, A.DateCreated
, B.DateCreated
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
ELSE
BEGIN
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, A.Rows [Now Rows]
, A.RunID [Now RunID]
,A.DateCreated [Now RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
WHERE
A.DBName =@DBName
AND A.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE A.DBName = @DBName)
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
GOTO EXIT_PROC
END
IF @Rebuild_UsingLast = 1
BEGIN
SELECT
@RunID = MAX(RunID)
FROM
[SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName
IF @@RowCount = 1 GOTO REBUILD_USINGLAST
SELECT @Msg = @Msg + char(10) + char(13) + 'There is not existing data for this DB ' + @DBName
GOTO PROBLEM
END
--// Load TrackFragmentation
SELECT
@RunID = MAX(RunID) + 1
FROM
[SCHEDULER].dbo.TrackFragmentation
IF @RunID IS NULL SELECT @RunID = 1
PRINT 'Loading TrackFragmentation'
SELECT @sql_action = '
USE ' + QUOTENAME(@DBName) + '
IF object_id(''tempdb..##tmpSysIndex'') IS NOT NULL
BEGIN
DROP TABLE ##tmpSysIndex
END
SELECT OBJECT_NAME(object_id) TableName, * INTO ##tmpSysIndex FROM ' + QUOTENAME(@DBName) + '.sys.indexes WHERE [Name] is not null
IF object_id(''tempdb..##tmp_dboSysindex'') IS NOT NULL
BEGIN
DROP TABLE ##tmp_dboSysindex
END
SELECT * INTO ##tmp_dboSysindex FROM ' + QUOTENAME(@DBName) + '.dbo.sysindexes WHERE groupid >0'
EXEC(@sql_action)
INSERT INTO [SCHEDULER].dbo.TrackFragmentation(
DBName
, TableName
, IndexName
, IndexType
, DataStoreType
, partition_number
, current_density_percentage
, current_fragmentation_percentage
, MBUsed
, Rows
, [object_id]
, index_id
, DateCreated
, RunID
)
SELECT
DB_NAME(@DBID) --// AS DBName
,I.TableName --// AS TableName
,I.name AS [IndexName]
,I.type_desc --// AS [IndexType] REPLACE(p.index_type_desc,' INDEX','')
,p.[alloc_unit_type_desc] --// AS DataStoreType
,CAST(p.[partition_number] AS varchar(10)) --// AS [partition_number]
,p.[avg_page_space_used_in_percent] --// AS [current_density_percentage]
,p.[avg_fragmentation_in_percent] --// AS current_fragmentation_percentage
,CASE WHEN page_count <= 8 THEN 0 ELSE (page_count * 8.0 / 1024.0) END --// AS [MBUsed]
,(SELECT top 1 rows FROM ##tmp_dboSysindex WHERE id = p.object_id AND indid < 2) --// [Rows]
,P.[object_id]
,P.[index_id]
,@DateCreated --// AS DateCreated
,@RunID --// AS RunID
--INTO TrackFragmentation --// If you need to re-create
FROM
sys.dm_db_index_physical_stats( @DBID, NULL, NULL, NULL, 'SAMPLED') p
JOIN ##tmpSysIndex i (NOLOCK) ON I.[object_id] = p.[object_id] AND I.[index_id] = p.[index_id]
WHERE
p.index_id > 0
PRINT 'Loaded: ' + LTRIM(STR(@@ROWCOUNT)) + ' records'
REBUILD_USINGLAST:
--// When I try to exclude this in main query it takes longer
UPDATE [SCHEDULER].dbo.TrackFragmentation
SET RunOnline = 0
WHERE [object_ID] IN (SELECT [object_id] FROM [SCHEDULER].dbo.TrackFragmentation T WHERE DataStoreType = 'LOB_DATA')
DELETE FROM [SCHEDULER].dbo.TrackFragmentation WHERE DataStoreType = 'LOB_DATA' AND RunID = @RunID
--// Drop Temp tables
IF object_id('tempdb..##tmpSysIndex') IS NOT NULL DROP TABLE ##tmpSysIndex
IF object_id('tempdb..##tmp_dboSysindex') IS NOT NULL DROP TABLE ##tmp_dboSysindex
IF @Rebuild_ViewExecuteLoad = 'LOAD' GOTO EXIT_PROC
IF @Rebuild_NCIX = 1
BEGIN
SET @IndexType = 'NONCLUSTERED'
END
ELSE
BEGIN
SET @IndexType = 'CLUSTERED'
END
SELECT TOP 1
@Use = 'USE ' + QUOTENAME(DBName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
RunID = @RunID
INSERT INTO @tmp
SELECT @Use, NULL
INSERT INTO @tmp
SELECT
'PRINT ''--//' + QUOTENAME(DBName) + '.' + QUOTENAME(TableName) + '..' + QUOTENAME(IndexName) +
' SIZE: ' + Ltrim(str([MBUsed])) + '(mb) ' +
' Rows: ' + Ltrim(str([Rows])) +
' %Frag: ' + Ltrim(str(Cast(round(current_fragmentation_percentage,0) as int))) +
' Density: ' + Ltrim(str(Cast(round(current_density_percentage,0) as int))) + '''' + CHAR(13) + CHAR(10)
+ CASE WHEN [Rows] > 1000000 THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REORGANIZE'
ELSE 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REBUILD WITH (ONLINE=' + CASE WHEN RunOnline = 0 THEN 'OFF' ELSE 'ON' END +', SORT_IN_TEMPDB=ON) ' END + '
UPDATE STATISTICS ' + QUOTENAME(TableName) + QUOTENAME(IndexName)
, QUOTENAME(TableName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
(
(current_fragmentation_percentage BETWEEN @MinFragPercent AND @MaxFragPercent)
OR
(current_density_percentage BETWEEN @MinDensity AND @MaxDensity)
)
AND Rows BETWEEN @MinRows AND @MaxRows
AND IndexType = @IndexType
AND RunID = @RunID
ORDER BY
IndexType DESC
,Rows ASC
,TableName
INSERT INTO @tmp
SELECT
'EXEC sp_recompile ''' + TableName + ''''+ CHAR(13) + CHAR(10),NULL
FROM
@tmp
GROUP BY
TableName
SELECT @Counter = 0, @Count = Count(*) FROM @tmp
WHILE ISNULL(@Counter,0) < @Count
BEGIN
SELECT @Counter = @Counter + 1
SELECT @sql_Action = sql_Action FROM @tmp WHERE IDX = @Counter
IF @Rebuild_ViewExecuteLoad = 'Execute'
BEGIN
SELECT @sql_Action = @Use + char(10) + char(13) + @sql_Action
EXEC(@sql_Action)
END
ELSE
BEGIN
PRINT @sql_Action
END
END
EXIT_PROC:
RETURN(@RC_SUCCESSFUL)
PROBLEM:
SELECT @ErrorMsg = 'Stored Procedure: sp_IndexMaintenance
Error Message: ' + @Msg + '
Input Parameter(s):
@DBName = ' + isnull( CONVERT(nvarchar,@DBName), '' ) + '
@ViewType = ' + isnull( CONVERT(nvarchar,@ViewType), '' ) + '
@Rebuild_CIX = ' + isnull( CONVERT(nvarchar,@Rebuild_CIX), '' ) + '
@Rebuild_NCIX = ' + isnull( CONVERT(nvarchar,@Rebuild_NCIX), '' ) + '
@Rebuild_ViewExecuteLoad = ' + isnull( CONVERT(nvarchar,@Rebuild_ViewExecuteLoad), '' ) + '
@MinFragPercent = ' + isnull( CONVERT(nvarchar,@MinFragPercent), '' ) + '
@MaxFragPercent = ' + isnull( CONVERT(nvarchar,@MaxFragPercent), '' ) + '
@MinDensity = ' + isnull( CONVERT(nvarchar,@MinDensity), '' ) + '
@MaxDensity = ' + isnull( CONVERT(nvarchar,@MaxDensity), '' )
SELECT @ErrorMsg
RAISERROR(@ErrorMsg, 16,1) WITH LOG
RETURN(@RC_FAILED)
END
