-- ============================================= -- Author: Rollin Burr -- Create date: 8 Jun 09 -- Description: Captures Index Fragmentation 20%> on all dbs -- ============================================= CREATE PROCEDURE [dbo].[sGetIndxFrag] AS BEGIN SET NOCOUNT ON; INSERT INTO IndexFragmentation ( DBName, objectID, indexTypeDesc, FragPerc, EvalDateTime) SELECT (SELECT [name] FROM sys.databases WHERE sys.databases.database_id = sys.dm_db_index_physical_stats.database_id) AS DBName, CAST([object_id] AS BIGINT), index_type_desc, avg_fragmentation_in_percent AS FragPerc, GETDATE() FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) WHERE object_id IS NOT NULL AND index_type_desc <> 'HEAP' AND database_id <> 1 and database_id <> 4 and avg_fragmentation_in_percent > 20 ORDER BY FragPerc DESC -- Update table with ave frag perc for this day UPDATE IndexFragmentation SET AvgFragByDay = (SELECT TOP (1) AVG(FragPerc) AS AvgFragPerc FROM dbo.IndexFragmentation WHERE (YEAR(GETDATE()) = YEAR(EvalDateTime)) AND (DATEPART(dy, GETDATE()) = DATEPART(dy, EvalDateTime)) ) WHERE (YEAR(GETDATE()) = YEAR(EvalDateTime)) AND (DATEPART(dy, GETDATE()) = DATEPART(dy, EvalDateTime)) END>I don't see anything in this script that checks for the fragmentation level.
>> >>SET ANSI_NULLS ON >>GO >>SET QUOTED_IDENTIFIER ON >>GO >>CREATE PROCEDURE [dbo].[sDefragmentIndices] >>AS >> SET NOCOUNT ON >> DECLARE @objectid int >> DECLARE @indexid int >> DECLARE @command varchar(8000) >> DECLARE @baseCommand varchar(8000) >> DECLARE @schemaname sysname >> DECLARE @objectname sysname >> DECLARE @indexname sysname >> DECLARE @currentDdbId int >> SELECT @currentDdbId = DB_ID() >> >> PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Starting' >> >> -- Loop over each of the indices >> DECLARE indexesToDefrag CURSOR FOR >> SELECT i.object_id, i.index_id, i.name >> FROM sys.indexes AS i INNER JOIN sys.objects AS o >> ON i.object_id = o.object_id >> WHERE i.index_id > 0 AND >> o.type = 'U' >> OPEN indexesToDefrag >> FETCH NEXT FROM indexesToDefrag -- Loop through the partitions. >> INTO @objectid, >> @indexid, >> @indexname >> WHILE @@FETCH_STATUS = 0 >> BEGIN >> -- Lookup the name of the index >> SELECT @schemaname = s.name >> FROM sys.objects AS o >> JOIN sys.schemas AS s >> ON s.schema_id = o.schema_id >> WHERE o.object_id = @objectid >> >> PRINT CONVERT(nvarchar, GETDATE(), 126) + ': ' + @schemaname + '.' + >> @indexname + ' is now being rebuilt.' >> >> -- Fragmentation is bad enough that it will be more efficient to rebuild the index >> SELECT @baseCommand = ' ALTER INDEX ' + @indexname + ' ON ' + >> @schemaname + '.' + object_name(@objectid) + >> ' REBUILD WITH (FILLFACTOR = 80, ONLINE = ' >> >> -- Use dynamic sql so this compiles in SQL 2000 >> SELECT @command = ' BEGIN TRY ' + @baseCommand + 'ON) ' + ' END TRY ' + >> ' BEGIN CATCH ' + @baseCommand + 'OFF) ' + -- Indices with image-like columns can't be rebuild online, so go offline >> ' END CATCH ' >> >> PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding' >> EXEC (@command) >> PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done' >> >> FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname >> END >> CLOSE indexesToDefrag >> DEALLOCATE indexesToDefrag >> >> RETURN 0 >> >>