Comma delimited info for patients should not allow for ", jr" or ", sr". This messed up my night. Ugh, 5 records had this type of data in the lastname column. When code saw this it shifted the column to value mapping to the right by one. The mapping was being done in a SQL DTS package.
Deductive reasoning:
Code does what you tell it. You tell it what you think it should do. If the code fails they you didn't think hard enough.
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: MK
-- 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 Max(A.RunID) DESC, MAX(B.RunID) DESC, 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.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE 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
USE [scheduler]
GO
/****** Object: Table [dbo].[TrackFragmentation] Script Date: 04/01/2008 19:06:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrackFragmentation](
[DBName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TableName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataStoreType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[partition_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_density_percentage] [float] NULL,
[current_fragmentation_percentage] [float] NULL,
[MBUsed] [numeric](29, 7) NULL,
[Rows] [int] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[RunOnline] [bit] NULL CONSTRAINT [DF_TrackFragmentation_RunOnline] DEFAULT ((1)),
[DateCreated] [datetime] NULL,
[RunID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Oh my!!! A sense of accomplishment. Now I can do just about anything with my indexes, schedule rebuild, monitor changes, create an alert if the delta is out of line.
DECLARE @return_value int,
@ErrorMsg varchar(4000)
EXEC @return_value = [dbo].[sp_IndexMaintenance]
@DBName = N'MyDB',
@ViewType = N'',
@Rebuild_CIX = 0,
@Rebuild_NCIX = 1,
@Rebuild_ViewExecuteLoad = 'LOAD',
@Rebuild_UsingLast = 0,
@MinRows = 1,
@MaxRows = 1000000,
@MinFragPercent = 3,
@MaxFragPercent = 90,
@MinDensity = 1,
@MaxDensity = 90,
@ErrorMsg = @ErrorMsg OUTPUT
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
CREATE PROCEDURE 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_ViewOrExecute = 'View' --// View | Execute
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output
