For the standard version of SQL server the script is: (all credits to the author in the link provided above):
1: IF EXISTS(SELECT OBJECT_ID FROM sys.tables
2: WHERE [name] = N'dba_indexDefragLog')
3: BEGIN
4: 5: DROP TABLE dbo.dba_indexDefragLog;
6: 7: PRINT 'dba_indexDefragLog table dropped!';
8: 9: END
10: 11: CREATE TABLE dbo.dba_indexDefragLog
12: (13: indexDefrag_id INT IDENTITY(1,1) NOT NULL
14: , objectID INT NOT NULL
15: , objectName NVARCHAR(130) NOT NULL
16: , indexID INT NOT NULL
17: , indexName NVARCHAR(130) NOT NULL
18: , fragmentation FLOAT NOT NULL
19: , page_count INT NOT NULL
20: , dateTimeStart DATETIME NOT NULL
21: , durationSeconds INT NOT NULL
22: CONSTRAINT PK_indexDefragLog
23: PRIMARY KEY CLUSTERED (indexDefrag_id)
24: ); 25: 26: PRINT 'dba_indexDefragLog Table Created';
27: 28: IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefragStandard_sp'),
29: N'IsProcedure') IS Null
30: BEGIN
31: EXECUTE ('Create Procedure dbo.dba_indexDefragStandard_sp
32: As Print ''Hello World!''');
33: RAISERROR('Procedure dba_indexDefragStandard_sp created.'
34: , 10, 1);35: END;
36: Go
37: 38: SET ANSI_Nulls ON;
39: SET Ansi_Padding ON;
40: SET Ansi_Warnings ON;
41: SET ArithAbort ON;
42: SET Concat_Null_Yields_Null ON;
43: SET NOCOUNT ON;
44: SET Numeric_RoundAbort OFF;
45: SET Quoted_Identifier ON;
46: Go
47: 48: ALTER PROCEDURE dbo.dba_indexDefragStandard_sp
49: 50: /* Declare Parameters */
51: @minFragmentation FLOAT = 10.0
52: /* in percent, will not defrag if fragmentation
53: less than specified */
54: , @rebuildThreshold FLOAT = 30.0
55: /* in percent, greater than @rebuildThreshold
56: will result in rebuild instead of reorg */
57: , @executeSQL BIT = 1
58: /* 1 = execute; 0 = print command only */
59: , @tableName VARCHAR(4000) = Null
60: /* Option to specify a table name */
61: , @printCommands BIT = 0
62: /* 1 = print commands; 0 = do not print commands */
63: , @printFragmentation BIT = 0
64: /* 1 = print fragmentation prior to defrag;
65: 0 = do not print */
66: , @defragDelay CHAR(8) = '00:00:05'
67: /* time to wait between defrag commands */
68: AS
69: /******************************************************************** 70: Name: dba_indexDefragStandard_sp 71: 72: Author: Michelle F. Ufford 73: 74: Purpose: Defrags all indexes for the current database
75: 76: Notes: This script was designed for SQL Server 2005
77: Standard edition. 78: 79: CAUTION: Monitor transaction log if executing for the first time!
80: 81: @minFragmentation defaulted to 10%, will not defrag if
82: fragmentation if less than specified.
83: 84: @rebuildThreshold defaulted to 30% as recommended by
85: Microsoft in BOL;
86: > than 30% will result in rebuild instead
87: 88: @executeSQL 1 = execute the SQL generated by this proc;
89: 0 = print command only
90: 91: @tableName Specify if you only want to defrag indexes
92: for a specific table
93: 94: @printCommands 1 = print commands to screen;
95: 0 = do not print commands
96: 97: @printFragmentation 1 = print fragmentation to screen;
98: 0 = do not print fragmentation
99: 100: @defragDelay time to wait between defrag commands;
101: gives the server some time to catch up
102: 103: Called by: SQL Agent Job or DBA
104: 105: Date Initials Description
106: ----------------------------------------------------------------
107: 2008-10-27 MFU Initial Release108: 2008-11-17 MFU Added page_count to log table
109: , added @printFragmentation option
110: ********************************************************************111: Exec dbo.dba_indexDefragStandard_sp
112: @executeSQL = 1 113: , @printCommands = 1 114: , @minFragmentation = 0 115: , @printFragmentation = 1; 116: ********************************************************************/ 117: 118: SET NOCOUNT ON;
119: SET XACT_Abort ON;
120: 121: BEGIN
122: 123: /* Declare our variables */
124: DECLARE @objectID INT
125: , @indexID INT
126: , @schemaName NVARCHAR(130) 127: , @objectName NVARCHAR(130) 128: , @indexName NVARCHAR(130)129: , @fragmentation FLOAT
130: , @pageCount INT
131: , @sqlCommand NVARCHAR(4000) 132: , @rebuildCommand NVARCHAR(200) 133: , @dateTimeStart DATETIME 134: , @dateTimeEnd DATETIME135: , @containsLOB BIT;
136: 137: /* Just a little validation... */138: IF @minFragmentation Not Between 0.00 And 100.0
139: SET @minFragmentation = 10.0;
140: 141: IF @rebuildThreshold Not Between 0.00 And 100.0
142: SET @rebuildThreshold = 30.0;
143: 144: IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
145: SET @defragDelay = '00:00:05';
146: 147: /* Determine which indexes to defrag using our
148: user-defined parameters */
149: SELECT
150: OBJECT_ID AS objectID
151: , index_id AS indexID
152: , avg_fragmentation_in_percent AS fragmentation
153: , page_count154: , 0 AS 'defragStatus'
155: /* 0 = unprocessed, 1 = processed */156: INTO #indexDefragList
157: FROM sys.dm_db_index_physical_stats
158: (DB_ID(), OBJECT_ID(@tableName), NULL , NULL, N'Limited')
159: WHERE avg_fragmentation_in_percent > @minFragmentation
160: And index_id > 0
161: OPTION (MaxDop 1);
162: 163: /* Create a clustered index to boost performance a little */
164: CREATE CLUSTERED INDEX CIX_temp_indexDefragList
165: ON #indexDefragList(objectID, indexID);
166: 167: /* Begin our loop for defragging */
168: WHILE (SELECT COUNT(*) FROM #indexDefragList
169: WHERE defragStatus = 0) > 0
170: BEGIN
171: 172: /* Grab the most fragmented index first to defrag */
173: SELECT TOP 1
174: @objectID = objectID 175: , @fragmentation = fragmentation 176: , @indexID = indexID 177: , @pageCount = page_count178: FROM #indexDefragList
179: WHERE defragStatus = 0
180: ORDER BY fragmentation DESC;
181: 182: /* Look up index information */
183: SELECT @objectName = QUOTENAME(o.name)
184: , @schemaName = QUOTENAME(s.name)185: FROM sys.objects AS o
186: Inner Join sys.schemas AS s
187: ON s.schema_id = o.schema_id
188: WHERE o.OBJECT_ID = @objectID;
189: 190: SELECT @indexName = QUOTENAME(name)
191: FROM sys.indexes
192: WHERE OBJECT_ID = @objectID
193: And index_id = @indexID
194: And type > 0;
195: 196: /* Look for LOBs */
197: SELECT TOP 1
198: @containsLOB = column_id199: FROM sys.columns WITH (NOLOCK)
200: WHERE
201: [OBJECT_ID] = @objectID202: And (system_type_id In (34, 35, 99)
203: -- 34 = image, 35 = text, 99 = ntext
204: Or max_length = -1);
205: -- varbinary(max), varchar(max), nvarchar(max), xml
206: 207: /* See if we should rebuild or reorganize; handle thusly */
208: IF @fragmentation < @rebuildThreshold Or IsNull(@containsLOB, 0) > 0
209: -- Cannot rebuild if the table has one or more LOB
210: SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
211: + @schemaName + N'.' + @objectName + N' ReOrganize;'
212: ELSE
213: SET @sqlCommand = N'Alter Index ' + @indexName + N' On '
214: + @schemaName + N'.' + @objectName + ' Rebuild '
215: + 'With (MaxDop = 1)'; -- minimize impact on server
216: 217: /* Are we executing the SQL? If so, do it */
218: IF @executeSQL = 1
219: BEGIN
220: 221: /* Grab the time for logging purposes */
222: SET @dateTimeStart = GETDATE();
223: EXECUTE (@sqlCommand);
224: SET @dateTimeEnd = GETDATE();
225: 226: /* Log our actions */227: INSERT INTO dbo.dba_indexDefragLog
228: ( 229: objectID 230: , objectName 231: , indexID 232: , indexName 233: , fragmentation 234: , page_count 235: , dateTimeStart 236: , durationSeconds 237: )238: SELECT
239: @objectID 240: , @objectName 241: , @indexID 242: , @indexName 243: , @fragmentation 244: , @pageCount 245: , @dateTimeStart246: , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);
247: 248: /* Just a little breather for the server */
249: WAITFOR Delay @defragDelay;
250: 251: /* Print if specified to do so */
252: IF @printCommands = 1
253: PRINT N'Executed: ' + @sqlCommand;
254: END
255: ELSE
256: /* Looks like we're not executing, just print
257: the commands */ 258: BEGIN 259: IF @printCommands = 1 260: PRINT @sqlCommand; 261: END 262: 263: /* Update our index defrag list when we've264: finished with that index */
265: UPDATE #indexDefragList
266: SET defragStatus = 1
267: WHERE objectID = @objectID
268: And indexID = @indexID;
269: 270: END
271: 272: /* Do we want to output our fragmentation results? */
273: IF @printFragmentation = 1
274: SELECT idl.objectID
275: , o.name AS 'tableName'
276: , idl.indexID277: , i.name AS 'indexName'
278: , idl.fragmentation 279: , idl.page_count280: FROM #indexDefragList AS idl
281: JOIN sys.objects AS o
282: ON idl.objectID = o.OBJECT_ID
283: JOIN sys.indexes AS i
284: ON idl.objectID = i.OBJECT_ID
285: AND idl.indexID = i.index_id;
286: 287: /* When everything is done, make sure to get rid of
288: our temp table */
289: DROP TABLE #indexDefragList;
290: 291: SET NOCOUNT OFF;
292: RETURN 0
293: END
294: Go
No comments:
Post a Comment