· 5 years ago · Nov 11, 2020, 06:22 PM
1--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
2/*
3
4@ViewOnly = 1 allows you to run this script as a test only and review proposed actions.
5
6@ViewOnly = 0 will perform either a reorg or rebuild, based on range of fragmentation value.
7
8*/
9
10 -- Specify your Database Name
11 --USE [databasename]
12 --GO
13
14 -- Declare variables
15 SET NOCOUNT ON DECLARE @tablename VARCHAR(128) DECLARE @execstr VARCHAR(255) DECLARE @objectid int DECLARE @indexid int DECLARE @frag decimal DECLARE @maxreorg decimal DECLARE @maxrebuild decimal DECLARE @IdxName varchar(128) DECLARE @ViewOnly bit DECLARE @ReorgOptions varchar(255) DECLARE @RebuildOptions varchar(255)
16 -- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
17 SET @ViewOnly = 1
18 -- Decide on the maximum fragmentation to allow for a reorganize.
19 -- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
20 SET @maxreorg = 20 .0 SET @ReorgOptions = 'LOB_COMPACTION=ON'
21 -- Decide on the maximum fragmentation to allow for a rebuild.
22 SET @maxrebuild = 30 .0
23 --NOTE: SQL Server will retain existing options if they are not specified.
24 --If you are running SQL Server Enterprise or Developer then you may inlude the ONLINE = ON option above.
25 SET @RebuildOptions = 'SORT_IN_TEMPDB=OFF, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'
26 -- Declare a cursor.
27 DECLARE tables CURSOR FOR SELECT '[' + CAST(TABLE_SCHEMA AS VARCHAR(100)) + ']' + '.' + '[' + CAST(TABLE_NAME AS VARCHAR(100)) + ']' AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
28 --You may use the line below to specify a table.
29 --AND Table_Name = 'Results'
30
31 -- Create the temporary table.
32 if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%') drop table #fraglist CREATE TABLE #fraglist( ObjectName CHAR(255), ObjectId int, IndexId int, LogicalFrag NVARCHAR(255), IndexName CHAR(255) )
33 -- Open the cursor.
34 OPEN tables
35 -- Loop through all the tables in the database.
36 FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN
37 -- Display the dmv info of all indexes of the table
38 INSERT INTO #fraglist SELECT @tablename
39 , cast(o.Object_Id as numeric) as ObjectId
40 , cast(ips.Index_Id as numeric) as IndexId
41 , avg_fragmentation_in_percent as LogicalFrag
42 ,i.name as IndexName FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('' + @tablename + ''), NULL, NULL, NULL) ips JOIN sys.objects o on o.object_id = ips.object_id JOIN sys.indexes i on ips.index_id = i.index_id and ips.object_id = i.object_id ORDER BY ips.index_id FETCH NEXT FROM tables INTO @tablename END
43 -- Close and deallocate the cursor.
44 CLOSE tables DEALLOCATE tables
45 -- Declare the cursor for the list of indexes to be defragged.
46 DECLARE indexes CURSOR FOR SELECT ObjectName
47 ,ObjectId
48 ,IndexId
49 ,LogicalFrag
50 ,IndexName FROM #fraglist WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild)) AND INDEXPROPERTY(ObjectId, IndexName, 'IndexDepth') > 0
51 -- Open the cursor.
52 OPEN indexes
53 -- Loop through the indexes.
54 FETCH NEXT FROM indexes INTO @tablename
55 , @objectid
56 , @indexid
57 , @frag
58 , @IdxName WHILE @@FETCH_STATUS = 0 BEGIN
59
60IF (@frag >= @maxrebuild) BEGIN
61 IF (@ViewOnly = 1) BEGIN PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15), @frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )' END ELSE BEGIN PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15), @frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )' SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )' EXEC (@execstr) END END
62 -- Determine if fragmentation surpasses the defined threshold for reorganizing:
63 ELSE IF(@frag >= @maxreorg) BEGIN
64 IF (@ViewOnly = 1) BEGIN PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15), @frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )' END ELSE BEGIN PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15), @frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )' SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )' EXEC (@execstr) END END FETCH NEXT FROM indexes INTO @tablename
65 , @objectid
66 , @indexid
67 , @frag
68 , @IdxName END
69 -- Close and deallocate the cursor.
70 CLOSE indexes DEALLOCATE indexes
71 -- Delete the temporary table.
72 DROP TABLE #fraglist GO