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


Script to Rebuild/Reorganise Indexes


Script to Rebuild/Reorganise Indexes

Author
Message
DBA From The Cold
DBA From The Cold
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4744 Visits: 1763
Comments posted to this topic are about the item Script to Rebuild/Reorganise Indexes
webtomte
webtomte
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 226
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
Scott Rankin
Scott Rankin
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 34
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.
webtomte
webtomte
SSC-Addicted
SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

Group: General Forum Members
Points: 435 Visits: 226
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.
Scott Rankin
Scott Rankin
Mr or Mrs. 500
Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)Mr or Mrs. 500 (593 reputation)

Group: General Forum Members
Points: 593 Visits: 34
If it works for you then you have diced onions.

best,
sqlhammer 72186
sqlhammer 72186
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 147
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 - 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 @ForceCheck BIT
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
SELECT DB_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 ( SELECT INDX.name AS IndexName
FROM sys.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)))))
WHERE OBJS.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 @DatabaseID int
DECLARE @ExecStatement varchar(4000)
DECLARE @ExecMessage varchar(4000)
DECLARE @IndexStatisticID int
DECLARE @ObjectID int
DECLARE @TableName sysname
DECLARE @IndexID int
DECLARE @IndexName sysname
DECLARE @FillFactor tinyint
DECLARE @Fragmentation tinyint
DECLARE @PageSpaceUsed tinyint
DECLARE @LastError int
DECLARE @DatabaseName sysname
DECLARE @IndexMaintOptionsID INT
DECLARE @IndexStatus VARCHAR(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
Tom Hamilton
Tom Hamilton
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 794
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/
henrik staun poulsen
henrik staun poulsen
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3137 Visits: 1239
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
www.stovi.com



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