· 10 years ago · Sep 22, 2016, 01:50 PM
1CREATE PROCEDURE [dbo].[DBA_indexMaintenance]
2 @dbname SYSNAME = NULL
3 , @minFragReorganize FLOAT = 10.0 -- min Fragmentation to consider reorganizing an index
4 , @minFragRebuild FLOAT = 30.0 -- min Fragmentation to consider rebuilding an index
5 , @minPageCount BIGINT = 1000
6 , @maxdop TINYINT = 0
7 , @batchNo TINYINT = NULL
8 , @weekDayOverride TINYINT = NULL
9 , @debugging BIT = 0 -- SET TO 0 FOR THE SP TO DO ITS JOB, OTHERWISE WILL JUST PRINT OUT THE STATEMENTS!!!!
10AS
11BEGIN
12
13 SET NOCOUNT ON
14
15 -- Adjust parameters
16 SET @minFragReorganize = ISNULL(@minFragReorganize, 10.0)
17 SET @minFragRebuild = ISNULL(@minFragRebuild, 30.0)
18 SET @minPageCount = ISNULL(@minPageCount, 1000)
19 SET @maxdop = ISNULL(@maxdop, 0)
20 SET @batchNo = CASE WHEN @dbname IS NULL THEN @batchNo ELSE NULL END -- when @dbname is provided it does not matter the batchNo
21 SET @debugging = ISNULL(@debugging, 0)
22
23 DECLARE @db TABLE(
24 ID INT IDENTITY PRIMARY KEY
25 , database_name SYSNAME)
26
27 IF ISNULL(@weekDayOverride, DATEPART(WEEKDAY, GETDATE())) NOT BETWEEN 1 AND 7 BEGIN
28 RAISERROR ('The value specified for @weekDayOverride is not valid, please specify a value between 1 and 7', 16, 0)
29 RETURN -50
30 END
31
32 DECLARE @sqlString NVARCHAR(MAX)
33 , @dayOfTheWeek INT = ISNULL(@weekDayOverride, DATEPART(WEEKDAY, GETDATE()))
34
35 DECLARE @num_dbs SMALLINT
36 , @count_dbs SMALLINT = 1
37 , @time DATETIME
38
39 DECLARE @SERVERNAME SYSNAME = @@SERVERNAME
40
41 DECLARE @numCores INT = ( SELECT ( cpu_count / hyperthread_ratio ) -- number of CPU's * number of physical cores per CPU
42 *
43 CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
44 ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
45 END
46 FROM sys.dm_os_sys_info )
47
48 -- MAXDOP to be <= than the number of physical cores
49 IF @maxdop > @numCores BEGIN
50 RAISERROR ('The MAXDOP specified exceeds the number of physical cores in the server, MAXDOP will be set to 0', 0, 0)
51 SET @maxdop = 0
52 END
53
54 INSERT INTO @db (database_name)
55 -- Scheduled databases
56 SELECT TOP 100 PERCENT db.[name]
57 FROM sys.databases AS db
58 INNER JOIN DBA.dbo.DatabaseInformation AS d
59 ON d.name = db.name COLLATE DATABASE_DEFAULT
60 AND d.server_name = @SERVERNAME
61 LEFT JOIN sys.dm_hadr_availability_replica_states AS s
62 ON s.replica_id = d.replica_id
63 WHERE db.state = 0 -- Online databases only
64 AND db.is_read_only = 0 -- exclude read_only databases
65 AND db.source_database_id IS NULL -- exclude snapshots
66 AND (s.replica_id IS NULL OR s.role_desc = N'PRIMARY')
67 AND ISNULL(backupBatchNo, 0) = ISNULL(@batchNo, 0)
68 AND SUBSTRING(IndexMaintenanceSchedule, @dayOfTheWeek, 1) <> '-' -- Remove one day from @dayOfTheWeek to read the actual position and not from that
69 --AND ISNULL(RebuildIndexesWeekNo, @weekNumber) = @weekNumber
70 AND db.name NOT IN ('model', 'tempdb')
71 AND @dbname IS NULL
72 UNION
73 -- Specified database if any
74 SELECT TOP 100 PERCENT [name]
75 FROM sys.databases AS db
76 WHERE db.state = 0 -- Online databases only
77 AND db.is_read_only = 0 -- exclude read_only databases
78 AND db.source_database_id IS NULL -- exclude snapshots
79 AND db.name NOT IN ('model', 'tempdb')
80 AND db.name LIKE @dbname
81 ORDER BY [name]
82
83 SELECT * FROM @db ORDER BY database_name ASC
84
85 SELECT @num_dbs = MAX(ID)
86 , @count_dbs = MIN(ID)
87 FROM @db
88
89 WHILE @count_dbs <= @num_dbs BEGIN
90 SELECT @dbname = database_name
91 FROM @db
92 WHERE ID = @count_dbs
93
94 SET @time = GETDATE()
95 PRINT REPLICATE ( CHAR(10), 3 ) + 'Processing database: ' + QUOTENAME(@dbname) + ' @ ' + CONVERT(VARCHAR,GETDATE(),120)
96
97 SET @sqlString = N'
98
99 USE ' + QUOTENAME(@dbname) + N'
100
101 DECLARE @oneTab CHAR(1) = CHAR(9)
102 DECLARE @oneLine CHAR(1) = CHAR(10)
103
104 DECLARE @object_id INT
105 DECLARE @index_id INT
106 DECLARE @db_id INT = DB_ID()
107 DECLARE @dbname SYSNAME = DB_NAME()
108 DECLARE @action VARCHAR(20)
109 DECLARE @command NVARCHAR(MAX)
110 DECLARE @timestamp DATETIME = GETDATE()
111 DECLARE @ftCatalogName SYSNAME
112
113 DECLARE @isEnterprise BIT = (SELECT CASE WHEN SERVERPROPERTY(''EngineEdition'') = 3 THEN 1 ELSE 0 END)
114 -- If the server engine is not Enterprise, the index cannot be REBUILT ONLINE
115
116 DECLARE @SQLNumericVersion INT = (DBA.dbo.getNumericSQLVersion(NULL))
117
118 IF OBJECT_ID(''tempdb..#work_to_do'') IS NOT NULL DROP TABLE #work_to_do
119
120 PRINT @oneLine + N''Finding Fragmentation... ''
121
122 SELECT IDENTITY(INT,1,1) AS ID
123 , @@SERVERNAME AS [server_name]
124 , CONVERT(VARCHAR(15), '''') AS [Action]
125 , 0 AS [isOnlineOperation]
126 , QUOTENAME(DB_NAME()) AS [database_name]
127 , @db_id AS [database_id]
128 , fix.[object_id] AS [object_id]
129 , QUOTENAME(OBJECT_SCHEMA_NAME(fix.object_id)) AS [schema_name]
130 , QUOTENAME(OBJECT_NAME(fix.object_id)) AS [object_name]
131 , fix.index_id AS [index_id]
132 , fix.partition_number AS [partition_number]
133 , pc.partition_count AS [partition_count]
134 , ix.ignore_dup_key AS [ignore_dup_key]
135 , ix.is_padded AS [is_padded]
136 , CASE WHEN ix.fill_factor = 0 THEN 100 ELSE ix.fill_factor END AS [fill_factor]
137 , p.data_compression_desc COLLATE DATABASE_DEFAULT AS [data_compression_desc]
138 , fix.avg_fragmentation_in_percent AS [avg_fragmentation_in_percent]
139 , fix.page_count AS [page_count]
140 , QUOTENAME(ix.name) AS [index_name]
141 , ix.[type] AS [type]
142 , ix.[type_desc] COLLATE DATABASE_DEFAULT AS [type_desc]
143 , ix.is_primary_key AS [is_primary_key]
144 , OBJECTPROPERTY(ix.[object_id], ''IsView'') AS [IsView]
145 , OBJECTPROPERTY(fix.[object_id], ''IsIndexed'') AS [IsIndexed]
146 , OBJECTPROPERTY(fix.[object_id], ''ExecIsAnsiNullsOn'') AS [ExecIsAnsiNullsOn] -- check what happen
147 , ix.allow_row_locks AS [allow_row_locks]
148 , ix.allow_page_locks AS [allow_page_locks]
149 ,
150 CASE
151 WHEN ix.[type] = 1 -- Clustered
152 AND EXISTS (SELECT 1
153 FROM sys.all_columns c
154 INNER JOIN sys.types t
155 ON c.system_type_id = t.system_type_id
156 WHERE c.object_id = @object_id
157 AND (
158 (@SQLNumericVersion < 11
159 AND ( c.system_type_id IN (34, 35, 99, 241) -- LOBs
160 OR (c.system_type_id IN (165, 167, 231) AND c.max_length = -1 ) ) ) -- (MAX) datatypes
161
162 -- From 2012 MAX types are allowed for online rebuild
163 OR (@SQLNumericVersion >= 11
164 AND c.system_type_id IN (34, 35, 99, 241)) -- LOBs
165 )
166 )
167 THEN 1
168 WHEN ix.[type] = 2 -- Non clustered
169 AND EXISTS (SELECT 1
170 FROM sys.index_columns as ixc
171 INNER JOIN sys.all_columns as c
172 ON c.object_id = ix.object_id
173 and c.column_id = ixc.column_id
174 WHERE ixc.object_id = @object_id
175 AND ixc.index_id = @index_id
176 -- From 2012 (MAX) types are allowed to be included in NCIX for online rebuilds,
177 -- LOB types are not allowed in NCIX either as key or included, hence there is no check
178 AND @SQLNumericVersion < 11
179 AND c.system_type_id IN (165, 167, 231) AND c.max_length = -1 -- MAX data types
180 )
181
182 THEN 1
183 ELSE 0
184 END AS HasLobData
185
186 , @timestamp AS DataCollectionTime
187 , CONVERT(INT, 0) AS Duration_seconds
188
189 INTO #work_to_do
190 FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, ''LIMITED'') AS fix
191
192 INNER JOIN sys.indexes AS ix
193 ON ix.object_id = fix.object_id
194 AND ix.index_id = fix.index_id
195 INNER JOIN sys.partitions AS p
196 ON p.object_id = ix.object_id
197 AND p.index_id = ix.index_id
198 AND p.partition_number = fix.partition_number
199
200 OUTER APPLY (SELECT COUNT(*) AS partition_count FROM sys.partitions AS p WHERE p.object_id = ix.object_id AND p.index_id = ix.index_id) AS pc
201
202 WHERE fix.index_id > 0
203 AND avg_fragmentation_in_percent IS NOT NULL
204 AND avg_fragmentation_in_percent > @minFragReorganize
205 AND fix.page_count > @minPageCount
206
207 --SELECT * FROM #work_to_do
208 PRINT N''Time taken: '' + [DBA].[dbo].[formatSecondsToHR](DATEDIFF(ss, @timestamp, GETDATE()))
209
210 -- Update Action and
211 UPDATE #work_to_do
212 SET Action = CASE
213 WHEN (avg_fragmentation_in_percent BETWEEN @minFragReorganize AND @minFragRebuild)
214 OR (HasLobData = 1)
215 OR (IsView = 1 AND IsIndexed = 1 AND ExecIsAnsiNullsOn = 0)
216
217 THEN ''REORGANIZE''
218 ELSE
219 ''REBUILD''
220 END
221 , isOnlineOperation = CASE
222 -- Reorganize is always online operation
223 WHEN (avg_fragmentation_in_percent BETWEEN @minFragReorganize AND @minFragRebuild)
224 OR (HasLobData = 1)
225 OR (IsView = 1 AND IsIndexed = 1 AND ExecIsAnsiNullsOn = 0)
226
227 THEN ''true''
228 -- When is rebuild, it depends on the engine
229 ELSE
230 @isEnterprise
231 END
232
233 IF @debugging = 1 BEGIN
234 SELECT * FROM #work_to_do
235 END
236
237 DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FAST_FORWARD FOR
238 SELECT
239 i.Action
240 -- Actual statement we will apply to the index
241 , N''ALTER INDEX '' + i.index_name + N'' ON '' + i.schema_name + N''.'' + i.object_name +
242 CASE
243 WHEN (i.avg_fragmentation_in_percent BETWEEN @minFragReorganize AND @minFragRebuild)
244 OR (i.HasLobData = 1)
245 OR (i.IsView = 1 AND i.IsIndexed = 1 AND i.ExecIsAnsiNullsOn = 0)
246
247 THEN N'' REORGANIZE'' + CASE WHEN [partition_count] > 1 THEN N'' PARTITION = '' + CONVERT(NVARCHAR, [partition_number]) ELSE '''' END
248
249 WHEN i.[partition_count] > 1
250 THEN
251 -- Rebuild partition
252 N'' REBUILD PARTITION = '' + CONVERT(NVARCHAR, [partition_number]) + N'' WITH (SORT_IN_TEMPDB = ON'' +
253 N'', MAXDOP = '' + CONVERT(NVARCHAR, @maxdop) +
254 N'', DATA_COMPRESSION = '' + data_compression_desc +
255 N'', ONLINE = '' + CASE WHEN isOnlineOperation = 1 THEN N''ON'' ELSE N''OFF'' END + '')''
256
257 ELSE
258 -- Rebuild index
259 N'' REBUILD WITH (PAD_INDEX = '' + CASE WHEN is_padded = 1 THEN N''ON'' ELSE N''OFF'' END +
260 N'', FILLFACTOR = '' + CONVERT(NVARCHAR, i.fill_factor) +
261 N'', SORT_IN_TEMPDB = ON'' +
262 --------------------Pointless... you cannot add this parameter for primary keys or unique indexes...
263 --------------------CASE WHEN i.[is_primary_key] = 1 THEN '''' ELSE -- this option cannot be specified for primary keys, regardless is ON or OFF
264 -------------------- N'', IGNORE_DUP_KEY = '' + CASE WHEN ignore_dup_key = 1 THEN N''ON'' ELSE N''OFF'' END
265 --------------------END +
266 N'', ONLINE = '' + CASE WHEN isOnlineOperation = 1 THEN N''ON'' ELSE N''OFF'' END +
267 N'', ALLOW_ROW_LOCKS = '' + CASE WHEN [allow_row_locks] = 1 THEN N''ON'' ELSE N''OFF'' END +
268 N'', ALLOW_PAGE_LOCKS = '' + CASE WHEN [allow_page_locks] = 1 THEN N''ON'' ELSE N''OFF'' END +
269 N'', MAXDOP = '' + CONVERT(NVARCHAR, @maxdop) +
270 N'', DATA_COMPRESSION = '' + data_compression_desc +
271 N'', FILLFACTOR = '' + CONVERT(NVARCHAR, i.fill_factor) +'')''
272
273 END AS [REORGANIZE_REBUILD]
274
275 , i.object_id
276 , i.index_id
277 FROM #work_to_do AS i
278
279 OPEN cur
280 FETCH NEXT FROM cur INTO @action, @command, @object_id, @index_id
281
282 WHILE @@FETCH_STATUS = 0 BEGIN
283
284 SET @timestamp = GETDATE()
285
286 PRINT N''Executing: '' + @command
287
288 BEGIN TRY
289
290 -- Persist index usage information as some SQL versions might lose it
291 EXECUTE DBA.dbo.DBA_indexUsageStatsPersistsHistory
292 @dbname = @dbname
293 , @object_id = @object_id
294 , @index_id = @index_id
295 , @debugging = @debugging
296
297 IF @debugging = 0 BEGIN
298 EXECUTE sp_executesql @command
299 END
300
301 UPDATE #work_to_do
302 SET Duration_seconds = DATEDIFF(SECOND,@timestamp, GETDATE())
303 WHERE database_id = @db_id
304 AND object_id = @object_id
305 AND index_id = @index_id
306
307
308 PRINT N''Executed - Time taken: '' + [DBA].[dbo].[formatSecondsToHR](DATEDIFF(ss, @timestamp, GETDATE()))
309
310 -- Backup the transaction log (if required)
311 EXECUTE DBA.dbo.DBA_runLogBackup @dbname = @dbname, @skipUsageValidation = 0, @debugging = @debugging
312
313 END TRY
314 BEGIN CATCH
315 SELECT ERROR_MESSAGE(), ERROR_NUMBER()
316
317 END CATCH
318
319 FETCH NEXT FROM cur INTO @action, @command, @object_id, @index_id
320
321 END
322
323 CLOSE cur
324 DEALLOCATE cur
325
326 -- Check for fulltext catalogs
327 DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FAST_FORWARD FOR
328 SELECT c.Name AS FullTextCatalogName
329 FROM sys.fulltext_catalogs c
330 INNER JOIN sys.fulltext_indexes i
331 ON i.fulltext_catalog_id = c.fulltext_catalog_id
332 INNER JOIN sys.fulltext_index_fragments f
333 ON f.table_id = i.object_id
334 GROUP BY c.Name
335 HAVING COUNT(*) > 1
336
337 OPEN cur
338 FETCH NEXT FROM cur INTO @ftCatalogName
339
340 WHILE @@FETCH_STATUS = 0 BEGIN
341
342 SET @command = ''ALTER FULLTEXT CATALOG '' + QUOTENAME(@ftCatalogName) + '' REBUILD''
343 SELECT @timestamp = GETDATE()
344
345 BEGIN TRY
346
347 PRINT @oneTab + N''Executing: '' + @command
348
349 IF @debugging = 0 BEGIN
350 EXECUTE sp_executesql @command
351 END
352
353 PRINT @oneTab + N''Executed - Time taken: '' + [DBA].[dbo].[formatSecondsToHR](DATEDIFF(ss, @timestamp, GETDATE()))
354
355 EXECUTE DBA.dbo.DBA_runLogBackup @dbname = @dbname, @skipUsageValidation = 0, @debugging = @debugging
356
357 END TRY
358 BEGIN CATCH
359 END CATCH
360
361 FETCH NEXT FROM cur INTO @ftCatalogName
362
363 END
364
365 CLOSE cur
366 DEALLOCATE cur
367
368 IF @debugging = 0 BEGIN
369 -- Save
370 INSERT INTO DBA.dbo.IndexFragmentationHistory
371 ([Action],[isOnlineOperation],[server_name],[database_name],[database_id],[object_id]
372 ,[schema_name],[object_name],[index_id]
373 ,[partition_number],[partition_count],[ignore_dup_key],[is_padded],[fill_factor],[data_compression_desc]
374 ,[avg_fragmentation_in_percent],[page_count],[index_name],[type],[type_desc],[is_primary_key],[IsView],[IsIndexed]
375 ,[ExecIsAnsiNullsOn],[allow_row_locks],[allow_page_locks],[HasLobData],[DataCollectionTime],[Duration_seconds])
376
377 SELECT [Action],[isOnlineOperation],[server_name],DBA.dbo.UNQUOTENAME([database_name]),[database_id],[object_id]
378 ,DBA.dbo.UNQUOTENAME([schema_name]),DBA.dbo.UNQUOTENAME([object_name]),[index_id]
379 ,[partition_number],[partition_count],[ignore_dup_key],[is_padded],[fill_factor],[data_compression_desc]
380 ,[avg_fragmentation_in_percent],[page_count],DBA.dbo.UNQUOTENAME([index_name]),[type],[type_desc],[is_primary_key],[IsView],[IsIndexed]
381 ,[ExecIsAnsiNullsOn],[allow_row_locks],[allow_page_locks],[HasLobData],[DataCollectionTime],[Duration_seconds]
382 FROM #work_to_do
383 END
384
385 '
386 IF @debugging = 1 BEGIN
387 SELECT CONVERT(XML, '<?query --' + @sqlString + '--?>')
388 END
389
390 EXECUTE sp_executesql
391 @sqlString
392 , @params = N'@debugging BIT, @maxdop INT, @minFragReorganize FLOAT, @minFragRebuild FLOAT, @minPageCount BIGINT'
393 , @debugging = @debugging
394 , @maxdop = @maxdop
395 , @minFragReorganize = @minFragReorganize
396 , @minFragRebuild = @minFragRebuild
397 , @minPageCount = @minPageCount
398
399 PRINT REPLICATE ( CHAR(10), 3 ) + 'Finishing Database : ' + QUOTENAME(@dbname) + ' @ ' + CONVERT(VARCHAR,GETDATE(),120)
400 PRINT REPLICATE ( CHAR(10), 3 ) + 'Time Taken : ' + DBA.dbo.formatSecondsToHR(DATEDIFF(SECOND, @time, GETDATE() ))
401
402 SET @count_dbs = (SELECT MIN(ID) FROM @db WHERE ID > @count_dbs)
403
404 END
405END