v50fCB8q

1/*
2@ViewOnly = 1 allows you to run this script as a test only and review proposed actions.
3@ViewOnly = 0 will perform either a reorg or rebuild, based on range of fragmentation value.
4*/
5 
6-- Specify your Database Name
7--USE [databasename]
8--GO
9 
10 
11-- Declare variables
12SET NOCOUNT ON
13DECLARE @tablename VARCHAR(128)
14DECLARE @execstr VARCHAR(255)
15DECLARE @objectid  int
16DECLARE @indexid int
17DECLARE @frag decimal
18DECLARE @maxreorg decimal
19DECLARE @maxrebuild decimal
20DECLARE @IdxName varchar(128)
21DECLARE @ViewOnly bit
22DECLARE @ReorgOptions varchar(255)
23DECLARE @RebuildOptions varchar(255)
24 
25-- Set to 1 to view proposed actions, set to 0 to Execute proposed actions:
26SET @ViewOnly=0
27 
28-- Decide on the maximum fragmentation to allow for a reorganize.
29-- AVAILABLE OPTIONS: http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
30SET @maxreorg = 20.0
31SET @ReorgOptions = 'LOB_COMPACTION=ON'
32-- Decide on the maximum fragmentation to allow for a rebuild.
33SET @maxrebuild = 30.0
34 
35 
36 
37--NOTE: SQL Server will retain existing options if they are not specified.
38--If you are running SQL Server Enterprise or Developer then you may inlude the ONLINE = ON option above.
39SET @RebuildOptions = 'SORT_IN_TEMPDB=OFF, STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON'
40 
41 
42-- Declare a cursor.
43DECLARE tables CURSOR FOR
44SELECT '[' + CAST(TABLE_SCHEMA AS VARCHAR(100)) + ']'
45+'.'+ '[' + CAST(TABLE_NAME AS VARCHAR(100)) + ']'
46AS Table_Name
47FROM INFORMATION_SCHEMA.TABLES
48WHERE TABLE_TYPE = 'BASE TABLE'
49--You may use the line below to specify a table.
50--AND Table_Name = 'Results'
51 
52 
53-- Create the temporary table.
54if exists (select name from tempdb.dbo.sysobjects where name like '#fraglist%')
55drop table #fraglist
56 
57CREATE TABLE #fraglist (
58ObjectName CHAR(255),
59ObjectId int,
60IndexId int,
61LogicalFrag nvarchar(255),
62IndexName CHAR(255)
63)
64 
65-- Open the cursor.
66OPEN tables
67 
68-- Loop through all the tables in the database.
69FETCH NEXT
70FROM tables
71INTO @tablename
72 
73WHILE @@FETCH_STATUS = 0
74BEGIN
75-- Display the dmv info of all indexes of the table
76INSERT INTO #fraglist
77SELECT @tablename, cast(o.Object_Id as numeric) as ObjectId, cast(ips.Index_Id as numeric) as IndexId,
78avg_fragmentation_in_percent as LogicalFrag, i.name as IndexName
79FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('' + @tablename + ''), NULL,NULL,NULL)ips
80JOIN sys.objects o on o.object_id = ips.object_id
81JOIN sys.indexes i on ips.index_id = i.index_id and ips.object_id = i.object_id
82ORDER BY ips.index_id
83 
84 
85 
86 
87 
88FETCH NEXT
89FROM tables
90INTO @tablename
91END
92 
93-- Close and deallocate the cursor.
94CLOSE tables
95DEALLOCATE tables
96 
97 
98-- Declare the cursor for the list of indexes to be defragged.
99DECLARE indexes CURSOR FOR
100SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
101FROM #fraglist
102WHERE ((LogicalFrag >= @maxreorg) OR (LogicalFrag >= @maxrebuild))
103AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
104 
105-- Open the cursor.
106OPEN indexes
107 
108-- Loop through the indexes.
109FETCH NEXT
110FROM indexes
111INTO @tablename, @objectid, @indexid, @frag, @IdxName
112 
113WHILE @@FETCH_STATUS = 0
114BEGIN
115IF (@frag >= @maxrebuild)
116BEGIN
117IF (@ViewOnly=1)
118BEGIN
119PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
120END
121ELSE
122BEGIN
123PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
124SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REBUILD WITH ( ' + @RebuildOptions + ' )'
125EXEC (@execstr)
126END
127END
128-- Determine if fragmentation surpasses the defined threshold for reorganizing:
129ELSE IF (@frag >= @maxreorg)
130BEGIN
131IF (@ViewOnly=1)
132BEGIN
133PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'WOULD be executing ALTER INDEX ' + '[' + RTRIM(@IdxName)+ ']' + ' ON '  + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
134END
135ELSE
136BEGIN
137PRINT 'Fragmentation at ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%' + ' ' + 'Now executing ALTER INDEX ' + '[' + RTRIM(@IdxName)+ ']' + ' ON ' +  RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
138SELECT @execstr = 'ALTER INDEX ' + '[' + RTRIM(@IdxName) + ']' + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH ( ' + @ReorgOptions + ' )'
139EXEC (@execstr)
140END
141END
142 
143FETCH NEXT
144FROM indexes
145INTO @tablename, @objectid, @indexid, @frag, @IdxName
146END
147 
148-- Close and deallocate the cursor.
149CLOSE indexes
150DEALLOCATE indexes
151 
152-- Delete the temporary table.
153DROP TABLE #fraglist
154GO