How to Maintain your Azure SQL Database

Maintaining your SQL database can be crucial to the performance of your Rock install. Rock's "Database Maintenance" job runs nightly and is set to cover most of what you need. But, there are further times you can and should take action to keep things running smoothly. Azure's health advisors will prompt you to create new indexes or do other things to assist in DB performance... but I don't recommend doing those as Rock maintains its indexes as needed. However, that doesn't mean your SQL indexes or statistics don't require maintenance! Over time they become fragmented or outdated and updating statistics and/or reorganizing/rebuilding indexes (defragmenting) can breathe new life into slow databases! But if Rock's "Database Maintenance" job already does this, why write an article about it? Two reasons.

First, understanding what is happening under the hood and being able to manually do some of those tasks can be helpful (if not required) at times. The "Database Maintenance" task has great options you can set for thresholds and such, but it also has a limited time it runs before timing out. Yes, this time is adjustable in the Job settings and usually this is plenty, but what if you do a lot of work, such as... Shrinking Your Database and now you have multiple hours of DB maintenance to do? It helps to know how to do this on your own free of timeout constraints and with the transparency provided by running it manually (where you can see progress and stats).

Second, knowing how to do this gives you control over your data. Want to do maintenance on the DB while your site is offline for some other reason? No problem. Want to double check the "Database Maintenance" job is... well... doing it's job? No problem. Want to see fragmentation stats and know more info about your data? No problem.

So with all that said, we know the built-in "Database Maintenance" job runs nightly and handles most of what this covers, so this is a job that is optional an can be used on an as-needed basis (large DB size changes), or can be done as a "tune-up" or an "oil-check" type job you want to look at every 3-6 months if you want to get eyes on your fragmentation amount.

1. Download/Install Microsoft SQL Server Management Studio (SSMS) from the following link (this will require a reboot): https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

2. On your Rock web server, navigate to C:\inetpub\wwwroot and right click/edit "web.ConnectionStrings.config" to view the contents of that file. You should see something like:

add name="RockContext" connectionstring="Data Source=YOURSERVER.database.windows.net;Initial Catalog=YOURDATABASE; User Id=YOURUSERNAME; password=YOURPASSWORD;MultipleActiveResultSets=true" providername="System.Data.SqlClient"

Don't change anything in there, but copy down the text after "Data Source=" as this is your server name. It will usually be something like "myserver.database.windows.net" where "myserver" is whatever you named your SQL server on Azure. Also grab what follows the "User ID=" and the "password=" items... and paste all of those into the "Connect to Server" box that appears when you run SSMS. You'll need to change the "Authentication" drop down to be "SQL Server Authentication" to put in the login/password.

3. After you click "Connect" you will see your SQL server on the left bar. Expand the "Databases" and you should see your database name in the list.

4. Select your rock database on the left and click "New Query".

5. Paste the following script into the new window on the right. This will install a maintenance script (saved as a Stored Procedure you can call later) written by Yochanan Rachamim, a Microsoft Engineer, that will intelligently help maintain your DB when run. It will update statistics and then evaluate each index of your DB and determine fragmentation amounts. The guidance from Microsoft is that indexes be rebuilt if the fragmentation is over 30% and simply reorganized if the fragmentation is between 5 and 30%. It will handle all of that automatically! After you paste the script, double check the correct DB is listed in the box next to the Execute button, then click Execute.

/* 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: 2021-01-07
***********************************************

Change Log: 
	2020-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 [dbo].[AzureSQLMaintenance]
	(
		@operation nvarchar(10) = null,
		@mode nvarchar(10) = 'smart',
		@ResumableIndexRebuild bit = 0,
		@RebuildHeaps bit = 0,
		@LogToTable bit = 0,
		@debug nvarchar = 'none'
	)
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;

	/* make sure parameters selected correctly */
	set @operation = lower(@operation)
	set @mode = lower(@mode)
	set @debug = lower(@debug) /* any value at this time will produce the temp tables as permanent tables */
	
	if @mode not in ('smart','dummy')
		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(' Logging option: @LogToTable(bit)',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('@LogToTable(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('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

		---------------------------------------------
		--- 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)
		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 */
		select 
			idxs.[object_id]
			,ObjectSchema = OBJECT_SCHEMA_NAME(idxs.object_id)
			,ObjectName = object_name(idxs.object_id) 
			,IndexName = idxs.name
			,idxs.type
			,idxs.type_desc
			,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
			,null as ObjectDoesNotSupportResumableOperation
			,0 as SkipIndex
			,replicate('',128) as SkipReason
		into #idxBefore
		from sys.indexes idxs
		left 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
		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
		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*/
		
		-- set SkipIndex=1 if conditions for maintenance are not met
		-- this is used to idntify is 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<5)
				)
				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 */

		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>=5 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

			
		/* create queue for indexes */
		insert into AzureSQLMaintenanceCMDQueue(txtCMD,ExtraInfo)
		select 
		txtCMD = 'ALTER INDEX ' + @idxIdentifierBegin + IndexName + @idxIdentifierEnd + ' ON '+ @idxIdentifierBegin + ObjectSchema + @idxIdentifierEnd +'.'+ @idxIdentifierBegin + ObjectName + @idxIdentifierEnd + ' ' +
		case when (
					avg_fragmentation_in_percent between 5 and 30 and @mode = 'smart')/* index fragmentation condition */ 
					or 
					(@mode='dummy' and type in (5,6)/* Columnstore indexes in dummy mode -> reorganize them */
				) then
			 'REORGANIZE;'
			when OnlineOpIsNotSupported=1 then
			'REBUILD WITH(ONLINE=OFF,MAXDOP=1);'
			when ObjectDoesNotSupportResumableOperation=1 or @ResumableIndexRebuildSupported=0 or @ResumableIndexRebuild=0 then
			'REBUILD WITH(ONLINE=ON,MAXDOP=1);'
			else
			'REBUILD WITH(ONLINE=ON,MAXDOP=1, RESUMABLE=ON);'
		end
		, ExtraInfo = 
			case when type in (5,6) then
				'Dummy mode, 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*/


		---------------------------------------------
		--- 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;' 
			, 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 
					(
						@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)
			,ObjectName = object_name(s.object_id) 
			,s.object_id
			,s.stats_id
			,StatsName = s.name
			,sp.last_updated
			,sp.rows
			,sp.rows_sampled
			,sp.modification_counter
			, i.type
			, i.type_desc
			,0 as SkipStatistics
		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 / reorginize 
		I am removing statistics based on existance on the index in the list because for indexes with <5% changes we do not apply
		any action - therefore we might decide to update statistics */
		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

		/*Skip statistics for Columnstore indexes*/
		update #statsBefore set SkipStatistics=1
		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=1
			from #statsBefore _stats join sys.index_resumable_operations iro on _stats.object_id=iro.object_id and _stats.stats_id=iro.index_id
		end
		
		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
		
		/* 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);
	
		/*Print debug information in case debug is activated */
		if @debug!='none'
		begin
			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('tempdb..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
	
		---------------------------------------------
		--- 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'

*/

6. Now if you right click and "refresh" the database, then expand down into the following, you should see the new stored procedure we saved called "dbo.AzureSQLMaintenance". Congrats, you've installed a Stored Procedure! I think it's a good time for a coffee break. Treat yourself!

7. Back? Ok, moving on. Now that we have the Stored Procedure, we can easily do complex maintenance on hundreds of statistics and indexes with as little as three words. Pretty cool, eh? So is now a good time? You have a few hours where you can potentially slow things down on the server with a data intensive operation? Great! Let's do this. Open a New Query and paste in:

Confirm you are looking at the correct DB name and click Execute.

8. Now if you click on the "Messages" tab at the bottom, you can see what it is doing in realtime.You can see it is "Executing query..." at the bottom left and the time it is taking to run at the bottom right. When complete, this timer remains with the final execution time listed. Depending on how fragmented your DB is, the speed of your server, and how often you run this script, the time will vary. It could be as little as a few seconds or as great as many hours.

9. When complete, oddly the "results" tab disappears, but in the "messages" tab, you should see that the query executed successfully and that your completion time is listed.

The initial run likely took a long time. If you were to run it again by clicking Execute a second time, it would complete in about 5%-10% of the time the first run took... and a third run would complete even faster as items are further defragged with each additoinal pass through.

- - - - - - - - - - - -

Bonus Content 1:

What if you want to just check how fragmented your indexes are but not do any maintenance? Run this Query instead. It returns a list of the indexes in the database, with the most fragmented first. This query is resource intensive and may take some time to run as it is polling 1,500+ indexes and analyzing each.

SELECT
OBJECT_SCHEMA_NAME(ips.OBJECT_ID) 'Schema',
OBJECT_NAME(ips.OBJECT_ID) 'Table',
i.NAME,
ips.index_id,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN
sys.indexes i
ON (ips.object_id = i.object_id)
AND
(
ips.index_id = i.index_id
)
ORDER BY
avg_fragmentation_in_percent DESC

It will return something like:

Bonus Content 2:

What if you don't want to run a full maintenance set of both statistics and index reorganize/rebuilds, rather you'd like to just update one or the other? The Stored Procedure we installed allows for this with the following commands:

As listed above, you can run through all indexes and statistics and it will auto maintain everything with: exec AzureSQLMaintenance 'all'
If you only want to run maintenance for statistics: exec AzureSQLMaintenance 'statistics'
If you only want to run maintenance for indexes: exec AzureSQLMaintenance 'index'

Bonus Content 3:

What if you want to automate this Stored Procedure to run monthly or with some other frequency, it can be done by creating a Scheduled Job in Rock that looks something like this:

Note that I set the job to not be active. This allows a manual run or you can enable it to run on whatever cron schedule you want. But this is a somewhat odd thing to do as the regular Database Maintenance job really already covers this and can be manually run by itself. The point is, yes, you CAN call a Stored Procedure from a SQL job you schedule in Rock.

Alternatively you can schedule it with Azure Automation outside of Rock's purview. I haven't dug into this yet, but here are some tutorials I found where I would start the process.

https://5minutebi.com/2018/02/08/sql-azure-stored-procedure-automation-using-runbooks-email/

https://global.hitachi-solutions.com/blog/azure-sql-databases-stored-procedure

Reference: https://www.beyondtrust.com/docs/privilege-management/reporting/install/db-maintenance/rebuild-indexes-sql-azure.htm

Yochanan Rachamim's Stored Procedure: https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt