-
Notifications
You must be signed in to change notification settings - Fork 65
Expand file tree
/
Copy pathAzureSQLMaintenance.sql
More file actions
821 lines (715 loc) · 37.8 KB
/
AzureSQLMaintenance.sql
File metadata and controls
821 lines (715 loc) · 37.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
/* Azure SQL Maintenance - Maintenance script for Azure SQL Database */
/* This script provided AS IS, Please review the code before executing this on production environment */
/* For any issue or suggestion please email to: yocr@microsoft.com */
/*
***********************************************
Current Version Date: 2026-03-22
***********************************************
Change Log:
2026-03-22 - Fix collation conflict if user database does not use the default collation (thanks to Naseem Abubaker, Sofia Silva and Sabrin Alsahsah for your help on this update)
2025-11-04 - Add User Override options. (exclue or override the command or add extra command to maintenance operation)
2024-09-23 - Avoid rebuil heaps on external tables as this is not needed and not possible.
2024-09-18 - Preserve xml compression in case this was used for the index.
2024-04-18 - Add internal variable to control SORT_IN_TEMPDB, change the way alter index command is being build to make it more flexible.
2023-07-13 - KB4551220 - skip resumable operation for indexes that has filter
2022-11-08 - Ignore table value functions for index maintenance Thanks to https://github.com/Mitch-Wheat for suggesting that
2022-10-06 - Fix help text that was mixed up, thanks Holger for pointing that out.
2022-10-03 - Add [whatif] as the first debug option, fix - do not skip stats for reorganize.
2022-04-25 - Set data type for @debug to nvarchar(10) as per comment I got from Paul McMillan - note that @debug is not in use at the moment.
2022-01-30 - As per comment from Tariq, removing dbo schema name from procedure and use user default.
2021-12-08 - Fix issue #19 on GitGub
2021-01-07
+ some bug fixes - see GitHub for more information
*/
if object_id('AzureSQLMaintenance') is null
exec('create procedure AzureSQLMaintenance as /*dummy procedure body*/ select 1;')
GO
ALTER PROCEDURE [AzureSQLMaintenance]
(
@operation nvarchar(10) = null,
@mode nvarchar(10) = 'smart',
@ResumableIndexRebuild bit = 0,
@RebuildHeaps bit = 0,
@LogToTable bit = 0,
@debug nvarchar(10) = 'off'
)
as
begin
set nocount on;
---------------------------------------------
--- Varialbles and pre conditions check
---------------------------------------------
set quoted_identifier on;
declare @idxIdentifierBegin char(1), @idxIdentifierEnd char(1);
declare @statsIdentifierBegin char(1), @statsIdentifierEnd char(1);
declare @msg nvarchar(max);
declare @minPageCountForIndex int = 40;
declare @OperationTime datetime2 = sysdatetime();
declare @KeepXOperationInLog int =3;
declare @ScriptHasAnError int = 0;
declare @ResumableIndexRebuildSupported int;
declare @indexStatsMode sysname;
declare @LowFragmentationBoundry int = 5;
declare @HighFragmentationBoundry int = 30;
declare @SORT_IN_TEMPDB bit = 1; /* 1=Activate SORT_IN_TEMPDB , 0= do not activate SORT_IN_TEMPDB while rebuilding indexes */
/*
Add your manual settings here
*/
/* make sure parameters selected correctly */
set @operation = lower(@operation)
set @mode = lower(@mode)
set @debug = lower(@debug)
if @mode not in ('smart','dummy') or @mode is null
set @mode = 'smart'
---------------------------------------------
--- Begin
---------------------------------------------
if @operation not in ('index','statistics','all') or @operation is null
begin
raiserror('@operation (varchar(10)) [mandatory]',0,0)
raiserror(' Select operation to perform:',0,0)
raiserror(' "index" to perform index maintenance',0,0)
raiserror(' "statistics" to perform statistics maintenance',0,0)
raiserror(' "all" to perform indexes and statistics maintenance',0,0)
raiserror(' ',0,0)
raiserror('@mode(varchar(10)) [optional]',0,0)
raiserror(' optionaly you can supply second parameter for operation mode: ',0,0)
raiserror(' "smart" (Default) using smart decision about what index or stats should be touched.',0,0)
raiserror(' "dummy" going through all indexes and statistics regardless thier modifications or fragmentation.',0,0)
raiserror(' ',0,0)
raiserror('@ResumableIndexRebuild(bit) [optional]',0,0)
raiserror(' Optionaly you can choose to rebuild indexes as resumable operation: ',0,0)
raiserror(' "0" (Default) using non resumable index rebuild.',0,0)
raiserror(' "1" using resumable index rebuild when it is supported.',0,0)
raiserror(' ',0,0)
raiserror('@RebuildHeaps(bit) [optional]',0,0)
raiserror(' Rebuild HEAPS to fix forwarded records issue on tables with no clustered index',0,0)
raiserror(' 0 - (Default) do not rebuild heaps',0,0)
raiserror(' 1 - Rebuild heaps based on @mode parameter, @mode=dummy will rebuild all heaps',0,0)
raiserror(' ',0,0)
raiserror('@LogToTable(bit) [optional]',0,0)
raiserror(' Optionaly allows you to turn on logging ',0,0)
raiserror(' 0 - (Default) do not log operation to table',0,0)
raiserror(' 1 - log operation to table',0,0)
raiserror(' for logging option only 3 last execution will be kept by default. this can be changed by easily in the procedure body.',0,0)
raiserror(' Log table will be created automatically if not exists.',0,0)
raiserror(' ',0,0)
raiserror('@debug [optional]',0,0)
raiserror(' Allows debugging feature.',0,0)
raiserror(' off - (Default) debug option is off',0,0)
raiserror(' whatif - Remark all commands so it will not be executed, helps with understanding the commands to be executed',0,0)
raiserror(' * in any case debug is used there will be user tables created to help with reviewing the process. cmdQueue, idxBefore and statsBefore ',0,0)
raiserror(' ',0,0)
raiserror('User Override settings:',0,0)
raiserror(' You are allowed to averride the default behavior as follows ',0,0)
raiserror(' - add command to be executed in addition to the maintenance script',0,0)
raiserror(' - Exclude either whole schema, table, index or statistics from an operation',0,0)
raiserror(' - force command of your own, such as sample percent for stats update or specific index',0,0)
raiserror(' for more information about it open the code and go to line 145 to get some samples and available options.',0,0)
raiserror(' ',0,0)
raiserror('Example:',0,0)
raiserror(' exec AzureSQLMaintenance ''all'', @LogToTable=1',0,0)
end
else
begin
---------------------------------------------
--- Prepare log table
---------------------------------------------
/* Prepare Log Table */
if object_id('AzureSQLMaintenanceLog') is null and @LogToTable=1
begin
create table AzureSQLMaintenanceLog (id bigint primary key identity(1,1), OperationTime datetime2, command varchar(4000),ExtraInfo varchar(4000), StartTime datetime2, EndTime datetime2, StatusMessage varchar(1000));
end
/* Prepare User override table if not exists */
if object_id('AzureSQLMaintenanceOverride') is null
begin
create table AzureSQLMaintenanceOverride(
PK int identity primary key,
ApplyOnObjectType nvarchar(10) null, /* schema / table / index / statistics */
TableSchema sysname null,
TableName sysname null,
IndexName sysname null,
StatisticsName sysname null,
Operation nvarchar(10) null, /* index / statistics / Null */
OverrideName varchar(100) not null, /* Exclude / Manual / AddCommand */
AdditionalSettings varchar(max) null,
IsSample bit not null,
Remark varchar(1000) null
,constraint Add_Command_Cannot_Be_Applied_On_Object check( (lower(OverrideName) =lower('AddCommand') and ApplyOnObjectType is null ) or (ApplyOnObjectType is not null and lower(OverrideName) !=lower('AddCommand')) )
,constraint Cannot_Apply_Index_Operation_On_Statistics check(lower(Operation)!=lower('Index') OR (lower(Operation)=lower('Index') and StatisticsName is null ))
,constraint Cannot_Apply_Statistics_Operation_On_Index check(lower(Operation)!=lower('Statistics') OR (lower(Operation)=lower('Statistics') and IndexName is null ))
,constraint At_Schema_Level_Can_Only_Exclue check(lower(ApplyOnObjectType) != lower('Schema') or (lower(ApplyOnObjectType)=lower('Schema') and lower(OverrideName) =lower('Exclude')))
)
/*
Some sample configuration for user override.
* note, a mistake in applying the override most likely to just be ignored, please make sure you test the settings to confirm it has been applied correctly.
below there are some samples you can use.
insert into AzureSQLMaintenanceOverride
select NULL,NULL,NULL,NULL,NULL,NULL,'AddCommand','-- ReadMe',1,'Please read the below samples to understand better the options you can override '
union all select null,NULL,NULL,NULL,NULL,NULL,'AddCommand','SELECT 1',1,NULL
union all select 'Schema','dbo',NULL,NULL,NULL,'Index','exclude',NULL,1,NULL
union all select 'Schema','dbo',NULL,NULL,NULL,'Statistics','exclude',NULL,1,NULL
union all select 'Table','dbo','tab1',NULL,NULL,'Index','exclude',NULL,1,NULL
union all select 'Table','dbo','tab1',NULL,NULL,'Statistics','exclude',NULL,1,NULL
union all select 'Table','dbo','tab1',NULL,'stats1','Statistics','exclude',NULL,1,NULL
union all select 'Index','dbo','tab1','idx1',NULL,'Index','exclude',NULL,1,NULL
union all select 'Statistics','dbo','tab1',NULL,'stats1','Statistics','exclude',NULL,1,NULL
union all select 'Table','dbo','tab2',NULL,NULL,'Index','Manual',NULL,1,NULL
union all select 'Table','dbo','tab2',NULL,NULL,'Statistics','Manual',NULL,1,NULL
union all select 'Table','dbo','tab2',NULL,'stats2','Statistics','Manual',NULL,1,NULL
union all select 'Index','dbo','tab2','idx2',NULL,'Index','Manual',NULL,1,'Use commant template with place holders in curly braces such as: ALTER INDEX [{IndexName}] ON [{SchemaName}].[{TableName}] rebuild; '
union all select 'Statistics','dbo','tab2',NULL,'stats2','Statistics','Manual',NULL,1,NULL
insert into AzureSQLMaintenanceOverride values('Table','SalesLT','Customer',null,null,'Statistics','Manual','UPDATE STATISTICS [{SchemaName}].[{TableName}] ([{StatisticsName}]) WITH FULLSCAN; --manual row2',1,null)
insert into AzureSQLMaintenanceOverride values('Statistics','dbo','tab1',null,'idx1','Statistics','Manual','UPDATE STATISTICS [{SchemaName}].[{TableName}] ([{StatisticsName}]) WITH FULLSCAN; -- manual row 1',1,null)
insert into AzureSQLMaintenanceOverride values('Table','SalesLT','Customer',null,null,'Index','Manual','Alter index [{IndexName}] on [{SchemaName}].[{TableName}] rebuild;',1,null)
insert into AzureSQLMaintenanceOverride values('Index','dbo','tab1','idx1',null,'Index','Manual','Alter index [{IndexName}] on [{SchemaName}].[{TableName}] rebuild;',1,null)
insert into AzureSQLMaintenanceOverride values(NULL,NULL,NULL,NULL,NULL,NULL,'AddCommand','-- Adding command 1 out of 2 to execution ',1,null)
insert into AzureSQLMaintenanceOverride values(NULL,NULL,NULL,NULL,NULL,NULL,'AddCommand','-- Adding command 2 out of 2 to execution ',1,null)
*/
end
---------------------------------------------
--- Resume operation
---------------------------------------------
/*Check is there is operation to resume*/
if OBJECT_ID('AzureSQLMaintenanceCMDQueue') is not null
begin
if
/*resume information exists*/ exists(select * from AzureSQLMaintenanceCMDQueue where ID=-1)
begin
/*resume operation confirmed*/
set @operation='resume' -- set operation to resume, this can only be done by the proc, cannot get this value as parameter
-- restore operation parameters
select top 1
@LogToTable = JSON_VALUE(ExtraInfo,'$.LogToTable')
,@mode = JSON_VALUE(ExtraInfo,'$.mode')
,@ResumableIndexRebuild = JSON_VALUE(ExtraInfo,'$.ResumableIndexRebuild')
from AzureSQLMaintenanceCMDQueue
where ID=-1
raiserror('-----------------------',0,0)
set @msg = 'Resuming previous operation'
raiserror(@msg,0,0)
raiserror('-----------------------',0,0)
end
else
begin
-- table [AzureSQLMaintenanceCMDQueue] exist but resume information does not exists
-- this might happen in case execution intrupted between collecting index & ststistics information and executing commands.
-- to fix that we drop the table now, it will be recreated later
DROP TABLE [AzureSQLMaintenanceCMDQueue];
end
end
---------------------------------------------
--- Report operation parameters
---------------------------------------------
/*Write operation parameters*/
raiserror('-----------------------',0,0)
set @msg = 'set operation = ' + @operation;
raiserror(@msg,0,0)
set @msg = 'set mode = ' + @mode;
raiserror(@msg,0,0)
set @msg = 'set ResumableIndexRebuild = ' + cast(@ResumableIndexRebuild as varchar(1));
raiserror(@msg,0,0)
set @msg = 'set RebuildHeaps = ' + cast(@RebuildHeaps as varchar(1));
raiserror(@msg,0,0)
set @msg = 'set LogToTable = ' + cast(@LogToTable as varchar(1));
raiserror(@msg,0,0)
set @msg = 'set debug = ' + @debug;
raiserror(@msg,0,0)
raiserror('-----------------------',0,0)
end
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),'Starting operation: Operation=' +@operation + ' Mode=' + @mode + ' Keep log for last ' + cast(@KeepXOperationInLog as varchar(10)) + ' operations' )
-- create command queue table, if there table exits then we resume operation in earlier stage.
if @operation!='resume'
create table AzureSQLMaintenanceCMDQueue (ID int identity primary key,txtCMD nvarchar(max),ExtraInfo varchar(max))
---------------------------------------------
--- Check if engine support resumable index operation
---------------------------------------------
if @ResumableIndexRebuild=1
begin
if cast(SERVERPROPERTY('EngineEdition')as int)>=5 or cast(SERVERPROPERTY('ProductMajorVersion')as int)>=14
begin
set @ResumableIndexRebuildSupported=1;
end
else
begin
set @ResumableIndexRebuildSupported=0;
set @msg = 'Resumable index rebuild is not supported on this database'
raiserror(@msg,0,0)
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),@msg)
end
end
---------------------------------------------
--- Index maintenance
---------------------------------------------
if @operation in('index','all')
begin
/**/
if @mode='smart' and @RebuildHeaps=1
set @indexStatsMode = 'SAMPLED'
else
set @indexStatsMode = 'LIMITED'
raiserror('Get index information...(wait)',0,0) with nowait;
/* Get Index Information */
/* using inner join - this eliminates indexes that we cannot maintain such as indexes on functions */
select
idxs.[object_id]
,ObjectSchema = OBJECT_SCHEMA_NAME(idxs.object_id) COLLATE database_default
,ObjectName = object_name(idxs.object_id) COLLATE database_default
,IndexName = idxs.name COLLATE database_default
,idxs.type
,idxs.type_desc
,idxs.has_filter
,p.xml_compression
,i.avg_fragmentation_in_percent
,i.page_count
,i.index_id
,i.partition_number
,i.avg_page_space_used_in_percent
,i.record_count
,i.ghost_record_count
,i.forwarded_record_count
,null as OnlineOpIsNotSupported
,0 as ObjectDoesNotSupportResumableOperation
,cast(0 as bit) as SortInTempDB
,case when ps.data_space_id IS NULL then 0 else 1 end as IsPartitioned
,case when et.object_id is NULL then 0 else 1 end as IsExternalTable
,0 as SkipIndex
,replicate(' ',20) as OperationToTake
,replicate(' ',128) as SkipReason
,0 as UserOverride
,replicate(' ',1024) as UserOverrideTemplate
into #idxBefore
from sys.indexes idxs
left join sys.partition_schemes ps ON idxs.data_space_id = ps.data_space_id
inner join sys.objects obj on idxs.object_id = obj.object_id
left join sys.partitions p on p.object_id = obj.object_id and p.index_id = idxs.index_id
left join sys.external_tables et on obj.object_id = et.object_id
inner join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,@indexStatsMode) i on i.object_id = idxs.object_id and i.index_id = idxs.index_id and p.partition_number=i.partition_number
where idxs.type in (0 /*HEAP*/,1/*CLUSTERED*/,2/*NONCLUSTERED*/,5/*CLUSTERED COLUMNSTORE*/,6/*NONCLUSTERED COLUMNSTORE*/)
and (alloc_unit_type_desc = 'IN_ROW_DATA' /*avoid LOB_DATA or ROW_OVERFLOW_DATA*/ or alloc_unit_type_desc is null /*for ColumnStore indexes*/)
and OBJECT_SCHEMA_NAME(idxs.object_id) != 'sys'
and idxs.is_disabled=0
and obj.type_desc != 'TF' /* Ignore table value functions */
and not exists (select * from sys.external_tables as et where et.object_id = obj.object_id) /* as added by alasdaircs to avoid external tables */
order by i.avg_fragmentation_in_percent desc, i.page_count desc
-- mark indexes XML,spatial and columnstore not to run online update
update #idxBefore set OnlineOpIsNotSupported=1 where [object_id] in (select [object_id] from #idxBefore where [type]=3 /*XML Indexes*/)
-- mark clustered indexes for tables with 'text','ntext','image' to rebuild offline
update #idxBefore set OnlineOpIsNotSupported=1
where index_id=1 /*clustered*/ and [object_id] in (
select object_id
from sys.columns c join sys.types t on c.user_type_id = t.user_type_id
where t.name in ('text','ntext','image')
)
-- do all as offline for box edition that does not support online
update #idxBefore set OnlineOpIsNotSupported=1
where /* Editions that does not support online operation in case this has been used with on-prem server */
convert(varchar(100),serverproperty('Edition')) like '%Express%'
or convert(varchar(100),serverproperty('Edition')) like '%Standard%'
or convert(varchar(100),serverproperty('Edition')) like '%Web%'
-- Do non resumable operation when index contains computed column or timestamp data type
update idx set ObjectDoesNotSupportResumableOperation=1
from #idxBefore idx join sys.index_columns ic on idx.object_id = ic.object_id and idx.index_id=ic.index_id
join sys.columns c on ic.object_id=c.object_id and ic.column_id=c.column_id
where c.is_computed=1 or system_type_id=189 /*TimeStamp column*/
-- Disable resumable operation for indexes that has filter (filtered indexes) (KB4551220)
update idx set ObjectDoesNotSupportResumableOperation=1
from #idxBefore idx
where idx.has_filter=1
-- set SkipIndex=1 if conditions for maintenance are not met
-- this is used to idntify if stats need to be updated or not.
-- Check#1 - if table is too small
update #idxBefore set SkipIndex=1,SkipReason='Maintenance is not needed as table is too small'
where (
/*Table is small*/
(page_count<=@minPageCountForIndex)
)
and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */
-- Check#2 - if table is not small and fragmentation % is too low
update #idxBefore set SkipIndex=1,SkipReason='Maintenance is not needed as fragmentation % is low'
where (
/*Table is big enough - but fragmentation is less than 5%*/
(page_count>@minPageCountForIndex and avg_fragmentation_in_percent<@LowFragmentationBoundry)
)
and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */
-- Skip columnstore indexes
update #idxBefore set SkipIndex=1,SkipReason='Columnstore index'
where (
type in (
5/*Clustered columnstore index*/,
6/*Nonclustered columnstore index*/
)
)
and @mode != 'dummy' /*for Dummy mode we do not want to skip anything */
/***/
update #idxBefore set OperationToTake =
case when
(
avg_fragmentation_in_percent between @LowFragmentationBoundry and @HighFragmentationBoundry and @mode = 'smart')/* index fragmentation condition */
or
(@mode='dummy' and type in (5,6))/* Columnstore indexes in dummy mode -> reorganize them */
then
'REORGANIZE'
else
'REBUILD'
end
-- Choose when to do SORT_IN_TEMPDB, based on variable and if resumable operation is used as SORT_IN_TEMPDB is not supported for resumable operations.
update idx set SortInTempDB=1
from #idxBefore idx
where
(
/* Internal variable instrusts to use SORT_IN_TEMPDB and resumable operation was not activated*/
/* Resumable operation cannot use sort in tempDB as tempdb is nor persisted*/
@SORT_IN_TEMPDB=1 and @ResumableIndexRebuild = 0
)
-- Apply User override settings for indexes at schema level. ***test***
update idx set SkipIndex=1, SkipReason='User override at schema level'
from #idxBefore idx
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='Schema' and ovr.IsSample=0 and ovr.Operation='Index' and ovr.OverrideName='Exclude'
and ovr.TableSchema = idx.ObjectSchema
-- Apply User override settings for indexes at table level. ***test***
update idx set SkipIndex=1, SkipReason='User override at table level'
from #idxBefore idx
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='Table' and ovr.IsSample=0 and ovr.Operation='Index' and ovr.OverrideName='Exclude'
and ovr.TableSchema = idx.ObjectSchema
and ovr.TableName = idx.ObjectName
-- Apply User override settings for indexes at index level. ***test***
update idx set SkipIndex=1, SkipReason='User override at index level'
from #idxBefore idx
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='Index' and ovr.IsSample=0 and ovr.Operation='Index' and ovr.OverrideName='Exclude'
and ovr.TableSchema = idx.ObjectSchema
and ovr.TableName = idx.ObjectName
and ovr.IndexName = idx.IndexName
-- Tag rows that has manual overrride so we can handle it differently later - This is for table level. ***test***
update idx set UserOverride=1, UserOverrideTemplate = ovr.AdditionalSettings
from #idxBefore idx
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='table' and ovr.IsSample=0 and ovr.Operation='Index' and ovr.OverrideName='Manual'
and ovr.TableSchema = idx.ObjectSchema
and ovr.TableName = idx.ObjectName
-- Tag rows that has manual overrride so we can handle it differently later - This is for index level. ***test***
update idx set UserOverride=1, UserOverrideTemplate = ovr.AdditionalSettings
from #idxBefore idx
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='Index' and ovr.IsSample=0 and ovr.Operation='Index' and ovr.OverrideName='Manual'
and ovr.TableSchema = idx.ObjectSchema
and ovr.TableName = idx.ObjectName
and ovr.IndexName = idx.IndexName
raiserror('---------------------------------------',0,0) with nowait
raiserror('Index Information:',0,0) with nowait
raiserror('---------------------------------------',0,0) with nowait
select @msg = count(*) from #idxBefore
set @msg = 'Total Indexes: ' + @msg
raiserror(@msg,0,0) with nowait
select @msg = avg(avg_fragmentation_in_percent) from #idxBefore where page_count>@minPageCountForIndex
set @msg = 'Average Fragmentation: ' + @msg
raiserror(@msg,0,0) with nowait
select @msg = sum(iif(avg_fragmentation_in_percent>=@LowFragmentationBoundry and page_count>@minPageCountForIndex,1,0)) from #idxBefore
set @msg = 'Fragmented Indexes: ' + @msg
raiserror(@msg,0,0) with nowait
raiserror('---------------------------------------',0,0) with nowait
/* Choose the identifier to be used based on existing object name
this came up from object that contains '[' within the object name
such as "EPK[export].[win_sourceofwealthbpf]" as index name
if we use '[' as identifier it will cause wrong identifier name
*/
if exists(
select 1
from #idxBefore
where IndexName like '%[%' or IndexName like '%]%'
or ObjectSchema like '%[%' or ObjectSchema like '%]%'
or ObjectName like '%[%' or ObjectName like '%]%'
)
begin
set @idxIdentifierBegin = '"'
set @idxIdentifierEnd = '"'
end
else
begin
set @idxIdentifierBegin = '['
set @idxIdentifierEnd = ']'
end
/*Handle User Override for indexes*/
insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
select
txtCMD = REPLACE(REPLACE(REPLACE('/*User Override*/'+UserOverrideTemplate,'{TableName}',idx.ObjectName),'{SchemaName}',idx.ObjectSchema),'{IndexName}',idx.IndexName)
,ExtraInfo = 'User Override command'
from #idxBefore idx
where SkipIndex=0 and type != 0 /*Avoid HEAPS*/ and UserOverride=1 /*User Override requested*/
/* create queue for indexes */
insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
select
txtCMD = 'ALTER INDEX ' + @idxIdentifierBegin + IndexName + @idxIdentifierEnd + ' ON '+ @idxIdentifierBegin + ObjectSchema + @idxIdentifierEnd +'.'+ @idxIdentifierBegin + ObjectName + @idxIdentifierEnd + ' ' +
OperationToTake+ ' ' +
case when IsPartitioned = 1 then 'PARTITION=' + CAST(partition_number AS varchar(10)) + ' ' else '' end +
case when OperationToTake='REBUILD'
then 'WITH(MAXDOP=1' +
case when OnlineOpIsNotSupported=1 then ',ONLINE=OFF' else ',ONLINE=ON' end +
case when @ResumableIndexRebuild=1 and @ResumableIndexRebuildSupported=1 and ObjectDoesNotSupportResumableOperation=0 then ',RESUMABLE=ON' else '' end /*Default resumable is off so nothing is off - Thanks to mperdeck */ +
case when SortInTempDB=1 then ',SORT_IN_TEMPDB=ON' else ',SORT_IN_TEMPDB=OFF' end +
case when xml_compression=1 then ',XML_COMPRESSION=ON' else '' end +
')'
else /* Operation is reoranize*/ '' end +
';'
, ExtraInfo =
'Taking Action: ' + OperationToTake + ' ' +
case when type in (5,6) then
'Dummy mode therefore reorganize columnstore indexes'
else
'Current fragmentation: ' + format(avg_fragmentation_in_percent/100,'p')+ ' with ' + cast(page_count as nvarchar(20)) + ' pages'
end
from #idxBefore
where SkipIndex=0 and type != 0 /*Avoid HEAPS*/ and UserOverride=0 /*No User Override*/
---------------------------------------------
--- Index - Heaps
---------------------------------------------
/* create queue for heaps */
if @RebuildHeaps=1
begin
insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
select
txtCMD = 'ALTER TABLE ' + @idxIdentifierBegin + ObjectSchema + @idxIdentifierEnd +'.'+ @idxIdentifierBegin + ObjectName + @idxIdentifierEnd + ' REBUILD ' +
case when IsPartitioned = 1 then 'PARTITION=' + CAST(partition_number AS varchar(10)) + ' ' else '' end + ';'
, ExtraInfo = 'Rebuilding heap - forwarded records ' + cast(forwarded_record_count as varchar(100)) + ' out of ' + cast(record_count as varchar(100)) + ' record in the table'
from #idxBefore
where
type = 0 /*heaps*/
and IsExternalTable=0 /*cannot rebuild external tables*/
and
(
@mode='dummy'
or
(forwarded_record_count/nullif(record_count,0)>0.3) /* 30% of record count */
or
(forwarded_record_count>105000) /* for tables with > 350K rows dont wait for 30%, just run yje maintenance once we reach the 100K forwarded records */
)
end /* create queue for heaps */
end
---------------------------------------------
--- Statistics maintenance
---------------------------------------------
if @operation in('statistics','all')
begin
/*Gets Stats for database*/
raiserror('Get statistics information...',0,0) with nowait;
select
ObjectSchema = OBJECT_SCHEMA_NAME(s.object_id) COLLATE database_default
,ObjectName = object_name(s.object_id) COLLATE database_default
,s.object_id
,s.stats_id
,StatsName = s.name COLLATE database_default
,sp.last_updated
,sp.rows
,sp.rows_sampled
,sp.modification_counter
, i.type
, i.type_desc
,0 as SkipStatistics /* 0=do not skip, 1=skip unless user override, 2=skip anyway*/
,replicate(' ',128) as SkipReason
,0 as UserOverride
,replicate(' ',1024) as UserOverrideTemplate
into #statsBefore
from sys.stats s cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
left join sys.indexes i on sp.object_id = i.object_id and sp.stats_id = i.index_id
where OBJECT_SCHEMA_NAME(s.object_id) != 'sys' and /*Modified stats or Dummy mode*/(isnull(sp.modification_counter,0)>0 or @mode='dummy')
order by sp.last_updated asc
/*Remove statistics if it is handled by index rebuild
When index is rebuild we already update stats as part of the rebuild -> therefore I am skipping this index
for reorganize or for indexes with low fragmentation we do not update stats*/
if @operation= 'all'
update _stats set SkipStatistics=1
from #statsBefore _stats
join #idxBefore _idx
on _idx.ObjectSchema = _stats.ObjectSchema
and _idx.ObjectName = _stats.ObjectName
and _idx.IndexName = _stats.StatsName
where _idx.SkipIndex=0 and _idx.OperationToTake='REBUILD'
/*Skip statistics for Columnstore indexes*/
update #statsBefore set SkipStatistics=2
where type in (5,6) /*Column store indexes*/
/*Skip statistics if resumable operation is pause on the same object*/
if @ResumableIndexRebuildSupported=1
begin
update _stats set SkipStatistics=2
from #statsBefore _stats join sys.index_resumable_operations iro on _stats.object_id=iro.object_id and _stats.stats_id=iro.index_id
end
-- Apply User override settings for Statistics at schema level. ***test***
update _stats set SkipStatistics=2, SkipReason='User override at schema level'
from #statsBefore _stats
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='schema' and ovr.IsSample=0 and ovr.Operation = 'Statistics' and ovr.OverrideName='exclude'
and ovr.TableSchema = _stats.ObjectSchema
-- Apply User override settings for Statistics at table level. ***test***
update _stats set SkipStatistics=2, SkipReason='User override at table level'
from #statsBefore _stats
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='table' and ovr.IsSample=0 and ovr.Operation = 'Statistics' and ovr.OverrideName='exclude'
and ovr.TableSchema = _stats.ObjectSchema
and ovr.TableName = _stats.ObjectName
-- Apply User override settings for Statistics at index level. ***test***
update _stats set SkipStatistics=2, SkipReason='User override at Statistics level'
from #statsBefore _stats
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='statistics' and ovr.IsSample=0 and ovr.Operation = 'Statistics' and ovr.OverrideName='exclude'
and ovr.TableSchema = _stats.ObjectSchema
and ovr.TableName = _stats.ObjectName
and ovr.StatisticsName = _stats.StatsName
-- Tag rows that has manual overrride so we can handle it differently later - This is for table level. ***test***
update _stats set UserOverride=1, UserOverrideTemplate = ovr.AdditionalSettings
from #statsBefore _stats
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='table' and ovr.IsSample=0 and ovr.Operation='Statistics' and ovr.OverrideName='Manual'
and ovr.TableSchema = _stats.ObjectSchema
and ovr.TableName = _stats.ObjectName
-- Tag rows that has manual overrride so we can handle it differently later - This is for index level. ***test***
update _stats set UserOverride=1, UserOverrideTemplate = ovr.AdditionalSettings
from #statsBefore _stats
join AzureSQLMaintenanceOverride ovr
on ovr.ApplyOnObjectType='Statistics' and ovr.IsSample=0 and ovr.Operation='Statistics' and ovr.OverrideName='Manual'
and ovr.TableSchema = _stats.ObjectSchema
and ovr.TableName = _stats.ObjectName
and ovr.StatisticsName = _stats.StatsName
raiserror('---------------------------------------',0,0) with nowait
raiserror('Statistics Information:',0,0) with nowait
raiserror('---------------------------------------',0,0) with nowait
select @msg = sum(modification_counter) from #statsBefore
set @msg = 'Total Modifications: ' + @msg
raiserror(@msg,0,0) with nowait
select @msg = sum(iif(modification_counter>0,1,0)) from #statsBefore
set @msg = 'Modified Statistics: ' + @msg
raiserror(@msg,0,0) with nowait
raiserror('---------------------------------------',0,0) with nowait
/* Choose the identifier to be used based on existing object name */
if exists(
select 1
from #statsBefore
where StatsName like '%[%' or StatsName like '%]%'
or ObjectSchema like '%[%' or ObjectSchema like '%]%'
or ObjectName like '%[%' or ObjectName like '%]%'
)
begin
set @statsIdentifierBegin = '"'
set @statsIdentifierEnd = '"'
end
else
begin
set @statsIdentifierBegin = '['
set @statsIdentifierEnd = ']'
end
/*Handle User Override for Statistics objects*/
insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
select
txtCMD = REPLACE(REPLACE(REPLACE('/*User Override*/'+UserOverrideTemplate,'{TableName}',_stats.ObjectName),'{SchemaName}',_stats.ObjectSchema),'{StatisticsName}',_stats.StatsName)
,ExtraInfo = 'User Override statistics update command'
from #statsBefore _stats
where SkipStatistics in (0,1 /*exclude 2*/) and UserOverride=1 /*User Override requested*/
/* create queue for update stats */
insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
select
txtCMD = 'UPDATE STATISTICS '+ @statsIdentifierBegin + ObjectSchema + +@statsIdentifierEnd + '.'+@statsIdentifierBegin + ObjectName + @statsIdentifierEnd +' (' + @statsIdentifierBegin + StatsName + @statsIdentifierEnd + ') WITH FULLSCAN;'
, ExtraInfo = '#rows:' + cast([rows] as varchar(100)) + ' #modifications:' + cast(modification_counter as varchar(100)) + ' modification percent: ' + format((1.0 * modification_counter/ rows ),'p')
from #statsBefore
where SkipStatistics=0;
end
if @operation in('statistics','index','all','resume')
begin
declare @SQLCMD nvarchar(max);
declare @ID int;
declare @ExtraInfo nvarchar(max);
/*handle debug options*/
if @debug!='off'
begin
/*When whatif is used remark all commands*/
if @debug='whatif'
begin
update AzureSQLMaintenanceCMDQueue set txtCMD = '--' + txtCMD
end
/*keep debug table snapshot*/
drop table if exists idxBefore
drop table if exists statsBefore
drop table if exists cmdQueue
if object_id('tempdb..#idxBefore') is not null select * into idxBefore from #idxBefore
if object_id('tempdb..#statsBefore') is not null select * into statsBefore from #statsBefore
if object_id('AzureSQLMaintenanceCMDQueue') is not null select * into cmdQueue from AzureSQLMaintenanceCMDQueue
end
/*Save current execution parameters in case resume is needed */
if @operation!='resume'
begin
set @ExtraInfo = (select top 1 LogToTable = @LogToTable, operation=@operation, operationTime=@OperationTime, mode=@mode, ResumableIndexRebuild = @ResumableIndexRebuild from sys.tables for JSON path, WITHOUT_ARRAY_WRAPPER)
set identity_insert AzureSQLMaintenanceCMDQueue on
insert into AzureSQLMaintenanceCMDQueue(ID,txtCMD,ExtraInfo) values(-1,'parameters to be used by resume code path',@ExtraInfo)
set identity_insert AzureSQLMaintenanceCMDQueue off
end
if @operation in('statistics','index','all')
begin
/*Add extra command from User override */
insert into AzureSQLMaintenanceCMDQueue
select AdditionalSettings, 'User Added Command' from AzureSQLMaintenanceOverride where OverrideName='AddCommand' and IsSample=0
end
---------------------------------------------
--- Executing commands
---------------------------------------------
/*
needed to rebuild indexes on comuted columns
if ANSI_WARNINGS is set to OFF we might get the followin exception:
Msg 1934, Level 16, State 1, Line 2
ALTER INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
*/
SET ANSI_WARNINGS ON;
raiserror('Start executing commands...',0,0) with nowait
declare @T table(ID int, txtCMD nvarchar(max),ExtraInfo nvarchar(max));
while exists(select * from AzureSQLMaintenanceCMDQueue where ID>0)
begin
update top (1) AzureSQLMaintenanceCMDQueue set txtCMD=txtCMD output deleted.* into @T where ID>0;
select top (1) @ID = ID, @SQLCMD = txtCMD, @ExtraInfo=ExtraInfo from @T
raiserror(@SQLCMD,0,0) with nowait
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,@SQLCMD,@ExtraInfo,sysdatetime(),null,'Started')
begin try
exec(@SQLCMD)
if @LogToTable=1 update AzureSQLMaintenanceLog set EndTime = sysdatetime(), StatusMessage = 'Succeeded' where id=SCOPE_IDENTITY()
end try
begin catch
set @ScriptHasAnError=1;
set @msg = 'FAILED : ' + CAST(ERROR_NUMBER() AS VARCHAR(50)) + ERROR_MESSAGE();
raiserror(@msg,0,0) with nowait
if @LogToTable=1 update AzureSQLMaintenanceLog set EndTime = sysdatetime(), StatusMessage = @msg where id=SCOPE_IDENTITY()
end catch
delete from AzureSQLMaintenanceCMDQueue where ID = @ID;
delete from @T
end
drop table AzureSQLMaintenanceCMDQueue;
end
---------------------------------------------
--- Clean old records from log table
---------------------------------------------
if @LogToTable=1
begin
delete from AzureSQLMaintenanceLog
from
AzureSQLMaintenanceLog L join
(select distinct OperationTime from AzureSQLMaintenanceLog order by OperationTime desc offset @KeepXOperationInLog rows) F
ON L.OperationTime = F.OperationTime
insert into AzureSQLMaintenanceLog values(@OperationTime,null,cast(@@rowcount as varchar(100))+ ' rows purged from log table because number of operations to keep is set to: ' + cast( @KeepXOperationInLog as varchar(100)),sysdatetime(),sysdatetime(),'Cleanup Log Table')
end
if @ScriptHasAnError=0 raiserror('Done',0,0)
if @LogToTable=1 insert into AzureSQLMaintenanceLog values(@OperationTime,null,null,sysdatetime(),sysdatetime(),'End of operation')
if @ScriptHasAnError=1 raiserror('Script has errors - please review the log.',16,1)
end
GO
print 'Execute AzureSQLMaintenance to get help'
/*
Examples
1. run through all indexes and statistic and take smart decision about steps taken for each object
exec AzureSQLMaintenance 'all'
1.1 add log to table
exec AzureSQLMaintenance 'all', @LogToTable=1, @ResumableIndexRebuild=1
2. run through all indexes and statistic with no limitation (event non modified object will be rebuild or updated)
exec AzureSQLMaintenance 'all','dummy'
3. run smart maintenance only for statistics
exec AzureSQLMaintenance 'statistics'
4. run smart maintenance only for indexes
exec AzureSQLMaintenance 'index'
*/