February 4, 2013 at 6:49 pm
Comments posted to this topic are about the item Script to Rebuild/Reorganise Indexes
February 19, 2013 at 2:52 am
Hi,
I found this sometime ago. I apologize to the author that I do not remember him or where I got it from.
I have made som changes to it to get the fillfactor at the same time. But it really works and it uses ms_foreachdb so all databases are covered.
Considerations must be taken on allow_row_locks and allow page_locks.
If the are not allowed than exclude those databases in the script and change the rebuild syntax in the code
----------- CODE -------------
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL
,@PctCount INT
,@PctFactor INT
DECLARE @Defrag NVARCHAR(MAX)
CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment FLOAT
,pCount INT
,fFactor INT)
EXEC sp_msforeachdb 'INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment,
pCount,
fFactor
) SELECT ''?'' AS ''DBName''
,t.Name AS ''TableName''
,sc.Name AS ''SchemaName''
,i.name AS ''IndexName''
,s.avg_fragmentation_in_percent
,s.page_count
,i.fill_factor
FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL, NULL, ''Sampled'') AS s
JOIN ?.sys.indexes i
ON s.Object_Id = i.Object_id AND s.Index_id = i.Index_id
JOIN ?.sys.tables t
ON i.Object_id = t.Object_Id
JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent >= 5
AND s.page_count >= 500
AND t.TYPE = ''U''
ORDER BY TableName,IndexName'
SELECT * FROM #Frag
DECLARE cList CURSOR
FOR SELECT * FROM #Frag
OPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount,@PctFactor
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctCount > 499
BEGIN
IF @PctFrag BETWEEN 5.0 AND 30.0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
ELSE IF @PctFrag > 30.0
BEGIN
IF @PctFactor = 0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
-- change above REBUILD to what you need
-- these settings will set fillfactor to 90 and allow for row and page locks
END
ELSE
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
END
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
END
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount,@PctFactor
END
CLOSE cList
DEALLOCATE cList
DROP TABLE #Frag
February 19, 2013 at 6:53 am
There are a thousand ways peal an onion, as long as in the end you have diced onion.
I would add an sp_recompile of the table after the reindex. This makes sure all associated objects are recompiled, using the most recent distribution page information, to generate the most efficient (hopefully) execution plan.
February 19, 2013 at 7:02 am
Hi,
I thought of that but I run this as a separate task as this goes thru all databases. I'll end up with a table of indexes and where the are after the run.
February 19, 2013 at 7:12 am
If it works for you then you have diced onions.
best,
February 19, 2013 at 8:10 am
I agree with Scott Rankin that there are many ways to peel this onion.
What's I've posted below has actually been ripped out of a more complex and permanent solution that I wrote so if you see variables that are hard-coded and only used once that's because those variables would have been set by configuration tables that my system runs off of.
Either way, the positives about this solution are:
-- This version is done with temporary stored procedures so no permanent code needs to be added to your server if you don't want to. (can just as easy remove the ##'s if you want them permanent)
-- Two procs are offered here, one to gather statistics and another to selectively perform maintenance based on flexible standards.
-- Fragmentation level is not the only metric here, this solution also includes page density and will check for old statistics (in case you don't do auto update statistics or if the stats are old but haven't hit your fragmentation thresholds yet).
-- The proc will decide (based on the passed in thresholds) whether to use REORGANIZE or REBUILD.
-- At the end of the script it will return the stats table for you which shows what the indexes looked like before maintenance and there is a IndexStatus table which lets you know which ones had maintenance performed on them. This gives you a glimpse of how bad things were and how much work was accomplished.
/*Author: Derik Hammer - http://www.sqlhammer.com - 01/01/2013*/
IF OBJECT_ID('tempdb..##usp_UpdateIndexStatistics') IS NOT NULL
DROP PROCEDURE ##usp_UpdateIndexStatistics
GO
CREATE PROCEDURE ##usp_UpdateIndexStatistics
(
@FragLimit TINYINT,
@PageSpaceLimit TINYINT,
@StatsExpiration tinyint
)
As
BEGIN
--Set NOCOUNT to improve performance
SET NOCOUNT ON;
--Declare variables
DECLARE @ForceCheckBIT
DECLARE @IndexMaintOptionsID INT
DECLARE @DatabaseName varchar (128)
DECLARE @CheckPeriodicity tinyint
IF OBJECT_ID('tempdb..##Statistics') IS NOT NULL
DROP TABLE ##Statistics
CREATE TABLE ##Statistics
(
[IndexStatisticID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](128) NOT NULL,
[SchemaName] [sysname] NOT NULL,
[TableName] [nvarchar](128) NOT NULL,
[IndexName] [sysname] NOT NULL,
[IndexID] [int] NOT NULL,
[IndexDepth] [tinyint] NULL,
[IndexLevel] [tinyint] NULL,
[PartitionNumber] [int] NULL,
[IndexTypeDesc] [nvarchar](60) NULL,
[AllocUnitTypeDesc] [nvarchar](60) NULL,
[AvgFragmentationPercent] [float] NULL,
[AvgPageSpaceUsedPercent] [float] NULL,
[FillFactor] [tinyint] NOT NULL,
[IsDisabled] [bit] NULL,
[PageCount] [bigint] NULL,
[RecordCount] [bigint] NULL,
[IndexStatus] [varchar](50) NOT NULL,
[InsertDate] [datetime] NULL,
[UpdateDate] [datetime] NULL
)
ALTER TABLE ##Statistics ADD CONSTRAINT PK_IndexMaint_Statistics_IndexStatisticID PRIMARY KEY CLUSTERED
(
[IndexStatisticID] ASC
)
ALTER TABLE ##Statistics ADD CONSTRAINT [DF_IndexMaint_Statistics_IndexStatus] DEFAULT ('Index Maintenance Required') FOR [IndexStatus]
ALTER TABLE ##Statistics ADD CONSTRAINT [DF_IndexMaint_Statistics_InsertDate] DEFAULT (GETDATE()) FOR [InsertDate]
DECLARE Databases_cur CURSOR FAST_FORWARD
FOR
SELECT sysDBs.name
FROM sys.databases sysDBs
WHERE sysDBs.[state] = 0 --Ensure the database is ONLINE to continue.
OPEN Databases_cur
FETCH NEXT FROM Databases_cur INTO @DatabaseName
WHILE ( SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'Databases_cur' ) = 0
BEGIN
--Retrieve index check periodicity settings
SELECT @CheckPeriodicity = NULL
IF @CheckPeriodicity > 0 AND @CheckPeriodicity IS NOT NULL
BEGIN
--Set the periodicity to a negative number for use in the DATEADD function.
SET @CheckPeriodicity = 0 - @CheckPeriodicity
END
IF @CheckPeriodicity IS NULL
BEGIN
--Set ForceCheck when the check periodicity is left NULL indicating a complete recheck on each run.
SET @ForceCheck = 1
END
-- Remove the temp table used to store the index info during the run
IF OBJECT_ID('tempdb..##FragmentationInfo') IS NOT NULL
Begin
EXEC ('Drop Table ##FragmentationInfo')
End
-- Switch to the database to pull the index info from
EXEC ('USE [' + @DatabaseName + ']')
-- Create the table to store the dm_db_index_physical_stats results
EXEC ('CREATE TABLE ##FragmentationInfo
(
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [sysname] NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[IndexID] [int] NULL,
[IndexDepth] [tinyint] NULL,
[IndexLevel] [tinyint] NULL,
[PartitionNumber] [int] NULL,
[IndexTypeDesc] [nvarchar](60) NULL,
[AllocUnitTypeDesc] [nvarchar](60) NULL,
[AvgFragmentationPercent] [float] NULL,
[AvgPageSpaceUsedPercent] [float] NULL,
[FillFactor] [tinyint] NOT NULL,
[IsDisabled] [bit] NULL,
[PageCount] [bigint] NULL,
[RecordCount] [bigint] NULL
)')
-- Load stats for current database from sys.dm_db_index_physical_stats
--- Ensure only base tables are scanned
EXEC ('USE [' + @DatabaseName + '];
Insert Into ##FragmentationInfo
SELECTDB_NAME(INX_Stat.Database_ID) AS [DatabaseName],
INFO_Schema.TABLE_SCHEMA AS [SchemaName],
OBJECT_NAME(INX_Stat.Object_Id) AS [TableName],
INX.name AS [IndexName],
INX_Stat.index_id AS [IndexID],
INX_Stat.index_depth AS [IndexDepth],
INX_Stat.index_level AS [IndexLevel],
INX_Stat.partition_number AS [PartitionNumber],
INX_Stat.index_type_desc AS [IndexTypeDesc],
INX_Stat.alloc_unit_type_desc AS [AllocUnitTypeDesc],
INX_Stat.avg_fragmentation_in_percent AS [AvgFragmentationPercent],
INX_Stat.avg_page_space_used_in_percent AS [AvgPageSpaceUsedPercent],
INX.fill_factor AS [FillFactor],
INX.[is_disabled] AS [IsDisabled],
INX_Stat.page_count AS [PageCount],
INX_Stat.record_count AS [RecordCount]
FROM sys.dm_db_index_physical_stats(DB_ID(''' + @DatabaseName + '''), NULL, NULL, NULL, ''SAMPLED'') INX_Stat
INNER JOIN sys.indexes INX ON (INX.object_id = INX_Stat.object_id) AND (INX.index_id = INX_Stat.index_id)
INNER JOIN INFORMATION_SCHEMA.TABLES INFO_Schema ON OBJECT_ID(INFO_Schema.TABLE_SCHEMA + ''.'' + INFO_Schema.TABLE_NAME) = INX_Stat.object_id
WHERE INX.name IS NOT NULL
AND INFO_Schema.TABLE_TYPE = ''BASE TABLE''
AND INFO_Schema.TABLE_NAME <> ''dtproperties''
ORDER BY INX_Stat.avg_fragmentation_in_percent DESC, INX_Stat.avg_page_space_used_in_percent ASC')
-- Migrate the scan results into the [IndexMaint].[Statistics] table
EXEC ('Insert Into ##Statistics ([DatabaseName],[SchemaName],[TableName],[IndexName],[IndexID]
,[IndexDepth],[IndexLevel],[PartitionNumber],[IndexTypeDesc]
,[AllocUnitTypeDesc],[AvgFragmentationPercent],[AvgPageSpaceUsedPercent]
,[FillFactor],[IsDisabled],[PageCount],[RecordCount])
Select INFO.[DatabaseName],INFO.[SchemaName],INFO.[TableName],INFO.[IndexName],INFO.[IndexID]
,INFO.[IndexDepth],INFO.[IndexLevel],INFO.[PartitionNumber],INFO.[IndexTypeDesc],INFO.[AllocUnitTypeDesc]
,INFO.[AvgFragmentationPercent],INFO.[AvgPageSpaceUsedPercent],INFO.[FillFactor],INFO.[IsDisabled],INFO.[PageCount],INFO.[RecordCount]
From ##FragmentationInfo INFO
-- Only add new indexes to the table
Left Join ##Statistics INDX
On (INDX.DatabaseName = ''' + @DatabaseName + '''
And INFO.TableName = INDX.TableName
And INFO.IndexName = INDX.IndexName)
Where LEN(INFO.IndexName) > 0
And (INFO.IndexID > 0 And INFO.IndexID < 255)
And INDX.IndexStatisticID IS NULL')
-- Cleanup the temp table used to store the information
EXEC ('Drop Table ##FragmentationInfo')
-- Check the indexes and set the IndexStatus based on the settings, set the good indexes
Update ##Statistics
Set IndexStatus = 'Index is Optimal'
WHERE DatabaseName = @DatabaseName
And (
(
AvgFragmentationPercent <= @FragLimit --Flag for optimal if the fragmentation is below the limit.
And
((AvgPageSpaceUsedPercent / (CASE [FillFactor] WHEN 0 THEN 100 ELSE [FillFactor] END) * 100) >= @PageSpaceLimit) -- Only flag as optimal if the space used percentage is above the limit adjusted for fill factor.
AND
AvgPageSpaceUsedPercent <= (CASE [FillFactor] WHEN 0 THEN 100 ELSE [FillFactor] END) --REBUILD if the space used is above the fill factor. This is the only way to allocate the space.
)
OR RecordCount = 0-- If there are no rows, there''s no need to reindex
OR
(
AvgFragmentationPercent <= @FragLimit
AND
[PageCount] <= 5
) --Don't perform maintenance on extremely small tables unless they are fragmented. AvgPageSpaceUsedPercent is not a useful metric with such small tables.
)
And IndexStatus = 'Index Maintenance Required'
-- Check the statistics last updated date and set the IndexStatus based on the settings
EXEC ('USE [' + @DatabaseName + '];
Update ##Statistics
Set IndexStatus = ''Statistic Maintenance Required''
WHERE DatabaseName = ''' + @DatabaseName + '''
AND IndexName IN (SELECTINDX.name AS IndexName
FROMsys.indexes INDX
INNER JOIN sys.objects OBJS ON INDX.object_id = OBJS.object_id
INNER JOIN ##Statistics IXSTAT ON IXSTAT.IndexName = INDX.name
AND OBJS.object_id = OBJECT_ID(( QUOTENAME(LTRIM(RTRIM(IXSTAT.SchemaName)))
+ ''.''
+ QUOTENAME(LTRIM(RTRIM(IXSTAT.TableName)))))
WHEREOBJS.type = ''U'' --Ensure they are user tables only.
AND DATEDIFF(dd, ISNULL(STATS_DATE(INDX.OBJECT_ID, index_id), OBJS.create_date), GETDATE()) > ' + @StatsExpiration + ') --Ensure that their stats are out of date based on configuration setting.
AND IndexStatus != ''Index Maintenance Required'' --Do not mark for stats update when already marked for index maintenance because we use auto-update stats as a policy
--and index maintenance will do a better job updating the statistics than UPDATESTATISTICS will due to the sample size.
AND RecordCount != 0');
FETCH NEXT FROM Databases_cur INTO @DatabaseName
END
CLOSE Databases_cur
DEALLOCATE Databases_cur
SET NOCOUNT OFF
End
GO
/**************************************************************************************/
/**************************************************************************************/
/**************************************************************************************/
EXEC ##usp_UpdateIndexStatistics
@FragLimit = 20
, @PageSpaceLimit = 80
, @StatsExpiration = 7
--SELECT * FROM ##Statistics
/**************************************************************************************/
/**************************************************************************************/
/**************************************************************************************/
IF OBJECT_ID('tempdb..##usp_SelectiveReindex') IS NOT NULL
DROP PROCEDURE ##usp_SelectiveReindex
GO
CREATE PROCEDURE ##usp_SelectiveReindex
(
@ExecuteWindowEnd TINYINT = NULL -- End hour, for when to stop processing
, @MaxDefrag TINYINT -- Maximum fragmentation before defraging will become rebuild
, @FragLimit TINYINT
, @PageSpaceLimit TINYINT
)
As
Begin
SET NOCOUNT ON
DECLARE @DatabaseIDint
DECLARE @ExecStatementvarchar(4000)
DECLARE @ExecMessagevarchar(4000)
DECLARE @IndexStatisticIDint
DECLARE @ObjectIDint
DECLARE @TableNamesysname
DECLARE @IndexIDint
DECLARE @IndexNamesysname
DECLARE @FillFactortinyint
DECLARE @Fragmentationtinyint
DECLARE @PageSpaceUsedtinyint
DECLARE @LastErrorint
DECLARE@DatabaseNamesysname
DECLARE @IndexMaintOptionsID INT
DECLARE @IndexStatusVARCHAR(50)
DECLARE Databases_cur CURSOR FAST_FORWARD
FOR
SELECT sysDBs.name
FROM sys.databases sysDBs
WHERE sysDBs.[state] = 0 --Ensure the database is ONLINE to continue.
OPEN Databases_cur
FETCH NEXT FROM Databases_cur INTO @DatabaseName
WHILE ( SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'Databases_cur' ) = 0
BEGIN
-- delete the temp table if it still exists
IF OBJECT_ID('tempdb..#IndexCheck') IS NOT NULL
Begin
DROP TABLE #IndexCheck
End
-- Create the table to store the results of checking to see if the index exists
CREATE TABLE #IndexCheck ([id] int, [type] varchar(50))
--Loop through each record to perform maintenance, until there are no more indexes needing maintenance or the execution window ends.
-- -- A while loop was added instead of a cursor because a cursor's data set is static and in this case a single index would have
-- -- been reorged or rebuilt more than once. In this case when maintenance has been conducted on an index all of it's associated
-- -- records are updated with 'Index Optimized' and the WHILE loop will do another SELECT TOP 1 which won't include the duplicate rows.
-- -- The duplicate rows weren't removed from the result set because each index level has different frag levels and page density.
WHILE EXISTS(
Select TOP 1 ISTAT.IndexStatisticID
From ##Statistics ISTAT
--Inner Join (Select MAX(IndexStatisticID) As IndexStatisticID
--From [IndexMaint].[Statistics]
--Where DatabaseName = @DatabaseName
--Group By OBJECT_ID(TableName), TableName, IndexID, IndexName) UIDX
--On (ISTAT.IndexStatisticID = UIDX.IndexStatisticID)
Where DatabaseName = @DatabaseName
And (IndexStatus = 'Index Maintenance Required'
OR IndexStatus = 'Statistic Maintenance Required')
And LEN(LTRIM(RTRIM(IndexName))) > 0 -- Needs to have an index name...
And (IndexID > 0 And IndexID < 255)
And RecordCount > 0-- If there are no rows, there's no need to reindex
)
Begin
-- clear the index check table
Delete From #IndexCheck
Select TOP 1
@IndexStatisticID = ISTAT.IndexStatisticID
, @ObjectID = OBJECT_ID(TableName), @TableName = (QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName))))
, @IndexName = LTRIM(RTRIM(IndexName)), @FillFactor = [FillFactor]
, @Fragmentation = [AvgFragmentationPercent], @PageSpaceUsed = AvgPageSpaceUsedPercent
, @IndexStatus = IndexStatus
From ##Statistics ISTAT
--Inner Join (Select MAX(IndexStatisticID) As IndexStatisticID
--From [IndexMaint].[Statistics]
--Where DatabaseName = @DatabaseName
--Group By OBJECT_ID((QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName))))), TableName, IndexID, IndexName) UIDX
--On (ISTAT.IndexStatisticID = UIDX.IndexStatisticID)
Where DatabaseName = @DatabaseName
And (IndexStatus = 'Index Maintenance Required'
OR IndexStatus = 'Statistic Maintenance Required')
And LEN(LTRIM(RTRIM(IndexName))) > 0 -- Needs to have an index name...
And (IndexID > 0 And IndexID < 255)
And RecordCount > 0-- If there are no rows, there's no need to reindex
-- Verify the index exists by retrieving the ids from the source database
Insert Into #IndexCheck ([id], [type])
EXEC ('USE [' + @DatabaseName + '];
-- verify the table exists (Row 1)
Select [id], ''Table''
From dbo.sysobjects obj
Where id = OBJECT_ID(RTRIM(''' + @TableName + '''))
And OBJECTPROPERTY(id, N''IsUserTable'') = 1
Union All
-- verify the index exists (row 2)
Select [object_id]
, Case When allow_page_locks = 0 Then ''Index - No Page Lock'' Else ''Index'' End
From sys.indexes
Where name = RTRIM(''' + @IndexName + ''')
And [object_id] = OBJECT_ID(RTRIM(''' + @TableName + '''));')
-- Verify the IndexCheck table has two records
IF (Select COUNT([id]) From #IndexCheck) = 2
BEGIN
IF @IndexStatus = 'Statistic Maintenance Required'
BEGIN
SET @ExecStatement = 'UPDATE STATISTICS ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' ' + QUOTENAME(@IndexName) + ' WITH FULLSCAN;'
SET @ExecMessage = '-- Executing UPDATE STATISTICS ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' ' + QUOTENAME(@IndexName) + ' WITH FULLSCAN;'
END
IF @IndexStatus = 'Index Maintenance Required'
BEGIN
-- Determine if the index should be defragmented or reindexed
IF (@Fragmentation > @MaxDefrag) OR (((@PageSpaceUsed / (CASE @FillFactor WHEN 0 THEN 100 ELSE @FillFactor END) * 100) > 100))
Or EXISTS(Select 1 From #IndexCheck Where [type] = 'Index - No Page Lock')
Begin
SET @ExecStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REBUILD;';
SET @ExecMessage = '-- Executing ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REBUILD;'
End
ELSE
Begin
SET @ExecStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REORGANIZE;';
SET @ExecMessage = '-- Executing ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REORGANIZE;'
End
END
-- See if the statement should be displayed or executed
--IF @Debug = 1
--Begin
--Select @ExecMessage As ExecMessage, @ExecStatement As ExecStatement
--End
--ELSE
--Begin
-- Execute the statment for the index
EXEC (@ExecStatement)
Set @LastError = @@ERROR
-- Update the Index Statistics table with the new status.
-- -- Update all rows for the given index so that maintenance isn't performed excessively.
Update ##Statistics Set
IndexStatus = CASE When Not @LastError = 0 Then 'Error: ' + CAST(@LastError AS NVARCHAR(8))
Else 'Index Optimized' End
, UpdateDate = GETDATE()
WHERE IndexName = @IndexName
AND DatabaseName = @DatabaseName
AND (QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName)))) = @TableName
--End
End
ELSE
Begin
-- print a message explaining the current index counldn't be found
print 'Index doesn''t exist!'
-- Log that the index couldn't be found
Update ##Statistics Set
IndexStatus = 'Index doesn''t exist!'
Where IndexStatisticID = @IndexStatisticID
End
-- Check to see if the process should exit due to time
IF Not @ExecuteWindowEnd = Null
Begin
IF DATEPART(hour, GETDATE()) >= @ExecuteWindowEnd
Begin
Goto EXECUTION_WINDOW_END
End
End
END -- While Loop
-- End of execution
EXECUTION_WINDOW_END:
-- delete the temp table if it still exists
IF OBJECT_ID('tempdb..#IndexCheck') IS NOT NULL
Begin
DROP TABLE #IndexCheck
End
FETCH NEXT FROM Databases_cur INTO @DatabaseName
END
CLOSE Databases_cur
DEALLOCATE Databases_cur
SET NOCOUNT OFF
End
GO
/**************************************************************************************/
/**************************************************************************************/
/**************************************************************************************/
EXEC ##usp_SelectiveReindex
@ExecuteWindowEnd = NULL -- End hour, for when to stop processing
, @MaxDefrag = 50 -- Maximum fragmentation before defraging will become rebuild
, @FragLimit = 20
, @PageSpaceLimit = 80
SELECT * FROM ##Statistics
/**************************************************************************************/
/**************************************************************************************/
/**************************************************************************************/
IF OBJECT_ID('tempdb..##Statistics') IS NOT NULL
DROP TABLE ##Statistics
IF OBJECT_ID('tempdb..##usp_SelectiveReindex') IS NOT NULL
DROP PROC ##usp_SelectiveReindex
IF OBJECT_ID('tempdb..##usp_UpdateIndexStatistics') IS NOT NULL
DROP PROCEDURE ##usp_UpdateIndexStatistics
Best Regards,
Derik Hammer
www.sqlhammer.com
February 19, 2013 at 11:23 am
Likewise I appreciate Scott's comments. I see a lot of value in Derik's approach, this looks like 'diced onions' to me - thanks all 🙂
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 1:24 am
I use the scripts provided by Ola Hallengren.
It can be found at http://ola.hallengren.com/
It knocks the socks off any other attempt...
Best regards,
Henrik Staun Poulsen
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply