How to maintain Azure SQL Indexes and Statistics

[Yochanan_Rachamim]

First published on MSDN on Jul 03, 2016

[2020-11-09]Note: New version with major update is now available include resume functionality, resumable index rebuild, fix forwarded records in heaps.

check the download link below

[2017-07-16]Note: New version is now available, added option to log operation to table including automatic retention of 3 last operation (can be modified)

There are a lot of work that Azure SQL saves from you, and most of the users we meet seem to believe that maintain the database indexes and statistics is one the missions you can forget when you migrate to Azure SQL.

Statistics objects, like in the box version of SQL have by default "Auto update" set to ON.

the condition to run the auto update is updating for at least 20% + 500 rows in the table. if the condition is met, the update will occur when the optimizer needs to generate execution plan against that table. so except that the update will take some time and use IO resources (which is very important information if you are on the cloud PAAS database) the update will read only 30% of the rows to generate the updated statistics, therefore, the statistic might not be 100% accurate.

• You can use QDS to force the best plan for the statistics, but this is your responsibility to keep it up to date.

so, this maintenance still needs to be done by the DBA on a regular basis, and if this has not been done, your database can suffer from poor performance just because of that.

I created a maintenance solution (Yes, I was inspired by Ola Hallengren's solution ) just that this one is lighter and suitable with Azure SQL DB (it also compatible with SQL Server 2012 and higher)

Now, all you have to do is download the T-SQL script, execute it on your Azure SQL DB and execute the maintenance procedure.

A quick remark about the options you have:

exec  AzureSQLMaintenance @operation,@mode

@operation: {all, index, statistics} (no default)

statistics : will run only statistics update

index : will run only index maintenance

@mode: {smart, dummy} (Default: smart)

smart : will touch only modified statistics and choose index maintenance by % of fragmentation

dummy : will run through all statistics or indexes.

@logtotable: {0, 1} (Default: 0)

0 : this feature is off.

1 : will log the operation to table [AzureSQLMaintenanceLog] (if the table does not exist it will be created automatically) the log will update ongoing with any progress so you can monitor the progress with this log. for every operation, you can find detailed information about the object before it was maintained (fragmentation percent for indexes, and modification counter for statistics) by default only 3 last operations will be kept in the log table, older execution log will be automatically removed, this can be changed in the procedure code.

More parameters is available, check the procedure for more details.

Download procedure code:

AzureSQLMaintenance

Scheduling and automation:



Automating Azure SQL DB index and statistics maintenance using Azure Automation



! updated: 2016-10-27 - fixed performance issue while updating statistics.

! updated: 2016-11-14 - fixed issue with the double rebuild of indexes. (thank you JPelttari for the comment)

! updated: 2016-11-29 - adding information about scheduling and automation.

! updated: 2017-07-16 - adding an option to log operation to table with a rotation of 3 last operations.

! updated: 2018-01-15 - added reference to another post that describes how to automate this maintenance task.

! updated: 2020-11-09 - New version release. 

[marcselman]

You will need to use 

REPLACE(IndexName, '].', ']].')

and

REPLACE(StatsName, '].', ']].')

 for indexes or stats with brackets and a dot in the name.

[EXEABLEBP]

Is this script recommended to run (at regular basis) when Azure SQL Autotuning is enabled?  

Thanks.

[Yochanan_Rachamim]

@EXEABLEBP  - yes. auto tuning is doing something else.. it's not maintaining from that aspect. 

[cwalshe]

Hi

We are on Pricing Tier -> Premium P4: 500 DTUs

Manually ran Ola Hallengrens script modified as below

@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 50,
@FragmentationLevel2 int = 80,

@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample int = NULL

This should be less intensive than a fullscan option scanning all rows in a table or doing an index rebuild at lower fragmentation levels

Despite this the Compute Utilization quickly reaches 100% - even thought this was running out of hours i stopped the process after 7 minutes

Is this expected behaviour at this Pricing Tier?

We would like to schedule this to run weekly.

Thanks

[Yochanan_Rachamim]

Hi @cwalshe

rebuilding and reorganizing indexes as well as updating statistics with full scan is an intensive IO operation.

this can explain the high resource usage you can see. 

however, if this ran when the system is idle - this is good, and it uses as much resource as it can to finish the maintenance task. 

this is the reason for why we recommend to run the maintenance during night / weekend / system idle time. 

if that;s not possible we should be more granular with the maintenance or maybe use different time or different service tier. 

note that even if you see high resource usage that does not necessarily means that your application cannot work at the same time. 

it really depend on the workload. 

HTH, 

Yochanan. 

[Sergey_F]

Hi @Yochanan_Rachamim ,
 Thank you for the detailed explanation!
 Can you guide me what is the best way to split Azure SQL (Indexes and Statistics) workloads, as we do not really have an idle time for system?

Regards,

 SergeyF

[Yochanan_Rachamim]

Hi @Sergey_F 

There is no magic formula, this will be decided based on tests only. 

it worth to know that the script is running an analysis at first step and then starts by maintaining the database objects. 

if you stop that and run it again after a while, it will do the assessment again and if an object already maintained and there is no need to re-run the maintenance it will just skip that resource. 

so, it is safe to stop it when your maintenance window ends and restart whenever this is possible. 

For indexed we have the resumable operation, unfortunately this maintenance script is not using that ability yet. 

HTH,

Yochanan. 

[reminvestor]

@Yochanan_Rachamim can you tell me how to modify the script for this? I can't seem to get past this error.

-----------------------
set operation = all
set mode = smart
set LogToTable = 1
-----------------------
Get index information...(wait)
---------------------------------------
Index Information:
---------------------------------------
Total Indexes: 737
Average Fragmentation: 1.22889
Fragmented Indexes: 1
---------------------------------------
Get statistics information...
---------------------------------------
Statistics Information:
---------------------------------------


---------------------------------------
Start executing commands...
ALTER INDEX [PK_gnEmailPrev] ON [dbo].[gnEmailPrev] REBUILD WITH(ONLINE=ON,MAXDOP=3);
FAILED : 2725An online operation cannot be performed for index 'PK_gnEmailPrev' because the index contains column 'RecipList' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.
Msg 50000, Level 16, State 1, Procedure AzureSQLMaintenance, Line 269 [Batch Start Line 284]
Script has errors - please review the log.

Completion time: 2020-09-15T12:00:21.5813261-05:00

[reminvestor]

I had to set the script to do offline only for it to run. The script completed but it didn't defragment. Current fragmentation is still at 99.13%.

id OperationTime command ExtraInfo StartTime EndTime StatusMessage
2 2020-09-15 12:06:33.7869014 ALTER INDEX [PK_gnEmailPrev] ON [dbo].[gnEmailPrev] REBUILD WITH(ONLINE=ON,MAXDOP=3); Current fragmentation: 99.13% 2020-09-15 12:06:33.8706769 2020-09-15 12:06:33.8717064 FAILED : 2725An online operation cannot be performed for index 'PK_gnEmailPrev' because the index contains column 'RecipList' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

[Yochanan_Rachamim]

Hi @reminvestor 

You have an old and deprecated data type for one or some of the columns in your table. 

it is recommended to use supported datatype for your columns to get more efficiency and supportability

for instance, if you are using ntext datatype you may use nvarchar(max) instead.

another option is to modify the script to do offline operation for all indexes, in this case you will not face any issue with these data types however concurrency might be impacted during index maintenance operation.

you may file an issue in the github page and I will try to fix that in the next cycle for this maintenance script. 

I hope that helps, 

Yochanan [MSFT]

[CrocJon]

Hello @Yochanan_Rachamim 

We are looking to use your scripts as we currently have no maint plan in place and are facing high fragmentation.

One thing that concerns us slightly is that one of our key tables has a UNIQUEIDENTIFIER primary key and index

Will this solution work ok with this, or should we look to do something different.

Thanks so much for your help.

Jon

[Yochanan_Rachamim]

Hi @CrocJon 

yes, it will work perfectly with UNIQUEIDENTIFIER column (UIC). 

one recommendation I can share with you to avoid high fragmentation on this PK column is to use increasing value for this UIC

for instance, you may generate new UIC with newid() T-SQL function, however it will generate randomized value. 

if you change that to use NEWSEQUENTIALID() function it will generate increasing values and prevent page-split and index fragmentation. 

HTH, 

Yochanan.

[GregPurkis]

Hi @Yochanan_Rachamim 

Thank you for this script......just what I've been looking for.

1 problem I've found, the param @debug doesn't have a length specified so it's defaulting to nvarchar(1) which means the check at the end of the script, where you check its value is not evaluating correctly.  Generates tables as a result.

/*Print debug information in case debug is activated */
if @debug!='None'

Thanks

Greg

[Yochanan_Rachamim]

Hi @GregPurkis  - Thank you for your feedback. 

its true, the debug option is not really implemented yet, it was used for a specific purpose during the last development cycle. 

Yochanan.

[Peter-MSFT]

@Yochanan_Rachamim - Could you add to the functionality of the script to take into account fill factor? Thanks 

[Yochanan_Rachamim]

Hi @Peter-MSFT - Thank you for you feedback, luckily for us fill factor is kept on rebuild

When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. To view the current index option settings, use sys.indexes.

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-ver15...

[proesler]

Hello, we want to use Elastic Jobs in Azure to do that. We found that instruction on the web:

Automating Azure SQL Database index maintenance using Elastic Job Agents (sqlshack.com)

How do we need to adjust your script so it would work with the instructions above? We want to run many jobs parallel.

Br,
Pascal

[marcselman]

Hi @Yochanan_Rachamim 

Thanks for your work! The updated script works perfectly!

[Yochanan_Rachamim]

Hi @proesler 

Thank you for reaching out, you may use the instructions to create the Elastic Jobs setup. 

you can then use any procedure you like to be automated with the Job. 

when you create the job step using "sp_add_jobstep" use your preferred Maintenance procedure in the @command parameter. 

just make sure the procedure is created with the most recent version on every database in the target group. 

Thank you, 

Yochanan.

[proesler]

Hi @Yochanan_MSFT 

When our stored procedure is in elasticjob db but not in target database. We get the following error:

Command failed: Could not find stored procedure 'dbo.AzureSQLMaintenance'. (Msg 2812, Level 16, State 62, Line 1)

What do we need to adjust to run the stored procedure from the elastic job agent database in the target database? 
Br

[Yochanan_Rachamim]

Hi @proesler, 

With Azure SQL DB we cannot use cross database reference, therefore the procedure needs to be created in each destination database. 

you may include another step in your elastic job to create / update the maintenance procedure in each destination database just before executing it.

please note that some fixes has been included in the recent version, so please make sure you are using the recent version in your environment. 

Regards, 

Yochanan. 

[langstonj61]

Running the latest version, it oddly is trying to do an index rebuild on a Table-valued function.

[Yochanan_Rachamim]

@langstonj61  - I cannot repro the same, can you share more details about your specific scenario? 

[langstonj61]

The error in the log table is:  FAILED : 1914Index cannot be created on object 'dbo.fnTableDate' because the object is not a user table or view.

[ToddElliott]

We have a managed instance.  Any chance you have a sample on how to run against all user db's in the managed instance instead of putting it in each db and executing one at a time?

[Yochanan_Rachamim]

Hi @ToddElliott - at the moment the maintenance script is designed to work on the current database only.

We might consider adding cross database access to support managed instance in future versions. 

[rozeboosje]

We have a few Table Valued functions in which return an "at"TableName table variable with a PRIMARY KEY defined in it. This script will try to include these, and then fails...

"FAILED : 1914Index cannot be created on object 'dbo.functionnamehere' because the object is not a user table or view."

[rozeboosje]

CREATE FUNCTION
       [dbo].[xxxxxxxxxxxxxxx]
      ()
RETURNS @yyyyyyyyyyyyyyyyyyyyyyyyyyyy TABLE
       ([id]                 INT             IDENTITY(1, 1)       PRIMARY KEY          NOT NULL,
        <etcetera>)

[rozeboosje]

I added the following to address this:

<your code>

		into #idxBefore
		from sys.indexes idxs
         inner join sys.objects [SO]
                 on [SO].object_id = [idxs].object_id
                AND [SO].[type] IN ('U', 'V')
		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

<more of your code>

[Jeff_Moden]

The supposed "Best Practices" are not best practices and we're never meant to be taken as "Best Practices". The recommendation of the original author of the MS docs and creator of REORGANIZE and REBUILD is to, and I quote... "take those numbers with a pinch of salt and don’t treat them as absolute."

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-f...

I'll also tell you that REORGANIZE does NOT work the way most people think it does. It does NOT "follow the Fill Factor" in any way ever close to a REBUILD. Instead, all it does is it tries to shrink the index by combining "some" pages UP TO the Fill Factor. It does NOT create any Free Space above the Fill Factor. The bad part about that is that 99.99% of the people in the world use 5 or 10% to 30% LOGICAL fragmentation to identify when REORGANIZE should be done and they do a REBUILD only if the LOGICAL fragmentation goes over 30%. Since REORGANIZE DOES remove LOGICAL fragmentation, it very quickly gets "stuck" on indexes and they frequently never be rebuilt ever again. That means that no Free Space is created above the Fill Factor. That means that virtually every index you lowered the Fill Factor to prevent fragmentation is NOT working correctly and the fragmentation such an action was supposed to prevent is now perpetual and actually gets worse after each REORGANIZE because all the critical space above the Fill Factor is permanently full because REORGANIZE removes free space below the Fill Factor and compresses pages.

The phenomena prevents perfect wide or random distribution indexes from working correctly. For example, a correctly maintained Random GUID index (and I have the proof in multiple repeatable tests) can withstand 100,000 per day inserts for 58 days (that's a total of 5.8 MILLION rows, folks!) with less than 1% logical fragmentation and absolutely no index maintenance during that entire period. The way I did that was to STOP using REORGANIZE. In a parallel test, REORGANIZE cause fairly massive and perpetual page splits all day every day during that same 58 day period along with fragmentation going over 5% and needing another REORGANIZE every 4 days.

During that same testing, it was proven that it's actually better to do NO index maintenance than it is to ever use REORGANIZE. The only 2 places that you should use reorganize is to compress LOBs (it even sucks at that and needs a rebuild right afterwards) and to remove physical fragmentation on an Insert Hot Spot cause by an Insert/Update pattern on ever increasing indexes. And, other fragmenting indexes are also affected in a similar manner... not just Random GUIDs.

For anyone interested (and, no.... this is NOT meant to be any form of spam), I'm giving the hour long version of my 2.5 hour presentation on the subject on the 28th of July at "EightKB". You can register to attend at the following URL...

https://eightkb.online/

--Jeff Moden

[sreedxc]

got error  when running as  exec dbo.AzureSQLMaintenance 'all', @LogToTable=0, @ResumableIndexRebuild=1

-----------------------
set operation = all
set mode = smart
set ResumableIndexRebuild = 1
set RebuildHeaps = 0
set LogToTable = 0
-----------------------
Get index information...(wait)
---------------------------------------
Index Information:
---------------------------------------
Total Indexes: 7
Average Fragmentation: 97.6471
Fragmented Indexes: 1
---------------------------------------
Get statistics information...
---------------------------------------
Statistics Information:
---------------------------------------
Total Modifications: 128
Modified Statistics: 2
---------------------------------------
Start executing commands...
ALTER INDEX [CORRESPONDENCE_REQUEST_PK] ON [UA3_CORRESPONDENCE_MGMT].[CORRESPONDENCE_REQUEST] REBUILD WITH(ONLINE=ON,MAXDOP=1, RESUMABLE=ON);
FAILED : 574RESUMABLE INDEX statement cannot be used inside a user transaction.
UPDATE STATISTICS [UA3_CORRESPONDENCE_MGMT].[CORRESPONDENCE_RESPONSE] ([CORRESPONDENCE_RESPONSE_PK]) WITH FULLSCAN;
UPDATE STATISTICS [UA3_CORRESPONDENCE_MGMT].[HPP_TENANT] ([HPP_TENANT_PK]) WITH FULLSCAN;
Msg 50000, Level 16, State 1, Procedure AzureSQLMaintenance, Line 527 [Batch Start Line 570]
Script has errors - please review the log.

Completion time: 2021-07-02T12:51:01.8000450-05:00   

[sreedxc]

i am executing admin user .  when i try exec dbo.AzureSQLMaintenance 'all' seems to work but takes long time

[sreedxc]

this is error when used with @ResumableIndexRebuild=1

Msg 50000, Level 16, State 1, Procedure dbo.AzureSQLMaintenance, Line 527 [Batch Start Line 572]
Script has errors - please review the log. 

[sreedxc]

previous code still works

[Yochanan_Rachamim]

@Jeff_Moden  - Appreciate your detailed feedback. I tend to agree with the points you mentioned. 

The script never meant to be the best solution to every scenario, however it still fit to most of the scenarios. 

I will take your inputs and will consider the best way to incorporate it with the maintenance procedure.

Again - thank you very much for your valuable feedback. 

Yochanan.

[Jeff_Moden]

Step one would be to simply stop using REORGANIZE.  Step two would be to stop using the numbers 5 and 30% for logical fragmentation. ;)

Take a look at the EightKB presentation I'm putting on tomorrow at 14:30UTC and make adaptations to your code.  Let me know how it works out.

[Tim Braes]

@Yochanan_Rachamim 

Thanks for this code! ;

A question : many options exists to run this script. Is automation account still the best suitable way?

Azure Job Scheduling Options for Azure SQL DB - Bernard Lim | Cloud | Data | Software (thebernardlim...

We have a situation at a customer that requires multiple Azure SQL DB to be maintained.

Wondering what your view is ; elastic jobs sounds promising too, but still preview...

Thanks,

Tim

[Yochanan_Rachamim]

Hi @Tim Braes , I'm very happy to see this code is helping you. 

as per executing the maintenance task, I guess that Elastic jobs (considering the fact that you can run the maintenance after if any issue happen) it does make sense to use preview feature. 

yes, it is not recommended for production workload as this is still considered as beta product, however this is more suitable for your needs and you can monitor and act if needed so I think it does make sense to use Elastic jobs although it is still in preview. 

many customers already had some jobs systems, of automation tool they use for their own implementation - this maintenance can be incorporated there as well if it is existed.

Bottom line, my vote goes to Elastic Jobs.

HTH,

Yochanan.

[ECAretonet]

hello, i been advised by azure support to run this but I seem to be getting an issue where it wants to rebuild an index again and again on the same table, I think it is because of the partitions so it's detecting them but not specifying partition number is there any fixes for this?

e.g. when run I get 

txtCMD ExtraInfo
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.37% with 184 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.96% with 675 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.87% with 177 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 99.20% with 624 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.82% with 762 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.32% with 179 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 98.88% with 179 pages
ALTER INDEX [CI_Q_Net_xx] ON [dbo].[Q_Net_xx] REBUILD WITH(ONLINE=ON,MAXDOP=1); Current fragmentation: 99.02% with 916 pages

[Yochanan_Rachamim]

Hi @ECAretonet , 

Thank you for your feedback. 

handling partitioned tables is one of the items I'm currently have in my backlog. 

there is an issue about it in my git hub page - based on your request I will try to do my best to prioritize that. 

Please stay tuned with my github page. 

Yochanan.

[Dino3000]

Hello @Yochanan_Rachamim 

Am testing your script.

I have a question regarding the lines  below:

<<--------------------------------

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..dbo.AzureSQLMaintenanceCMDQueue') is not null select * into cmdQueue from dbo.AzureSQLMaintenanceCMDQueue
end

-------------------------------->>

I see your script create 3 new tables (idxBefore ,statsBefore  & cmdQueue ).

do they phisically remain even after the execution of the SP: [dbo].[AzureSQLMaintenance] ?

btw: for table creation of AzureSQLMaintenanceCMDQueue, I noticed that schema has to be taken into consideration 

dbo.AzureSQLMaintenanceCMDQueue.

Is it required to create these 3 tables ?  if so, i will need to ad dbo schema to each one.

Thanks for your feedback.

[Yochanan_Rachamim]

Hi @Dino3000 

this code path is only used when you set Debug to 1

this is intended only for troubleshooting and development purposes

those table will not be created on regular usage

HTH, please reach out for any other questions

[Yevgen585]

Hello @Yochanan_Rachamim 

Thanks for your code.

We tested your script for our  Azure DBs.  One of our databases contains table functions with the primary key.   For example

CREATE FUNCTION [dbo].[Function]
(
@Parametr1 CHAR,
@Parametr2 TEXT
)
RETURNS @Result TABLE (Column1 SMALLINT IDENTITY(1, 1) PRIMARY KEY, Coulmn2 VARCHAR(8000))
AS
--Calculation

When we ran your script on this DB we had the next error:

Alter index [PK_Function_FFEE232423697868] on [dbo].[Function] REBUILD  WITH (ONLINE=ON,MAXDOP=1,RESUMABLE=ON);

Failed: 1914 Index cannot be created on object '[dbo].[Function]' because the object is not  a user table or view

After this error, I checked the code of procedure and  found the next  actions of procedure:

1 Procedure found this index in the table sys.indexes, but this index doesn't have any properties from sys.dm_db_index_physical_stats. I mean this index has all properties with value NULL( for example page_count, percent_fragmentationa and etc)

2 Next code should set SkipIndex=1 for  it, but it doesn't work  because page_count= null

-- 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 */
		

Is this mistake or am I wrong? Also, could you explain why did you use left join in this part of the code? 

from sys.indexes idxs
		left join sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,@indexStatsMode)

 Thanks

[Yochanan_Rachamim]

Hi @Yevgen585 - Thank you for reaching out. 

This behavior was mentioned in issue #19 which I believe is relatively rare. 

in response to your request, I checked that and it does seem that using inner join would make sense. 

I changed the join from left to inner and it fixed the issue -> it's now committed to the main branch and is available in the link here in this page. 

again - Thank you for your feedback and for using the maintenance script. 

[ianstirk]

Hi,

thanks for creating this utility, it's very useful.

Looking at the code, I can see it says "Remove statistics if it is handled by index rebuild / reorginize...", please note that an index reorg does not update the statistics e.g. https://dba.stackexchange.com/questions/48991/does-a-re-index-update-statistics

thanks

Ian

[Yochanan_Rachamim]

Hi @ianstirk 

Thank you for your comment, this is absolutely true and I am creating now work item to fix this.

Thank you very much for your feedback and for using the script. 

Thanks,

Yochanan

[langstonj61]

This exec dbo.dbaAzureSQLMaintenanceNew 'index','smart',1,0,1

results in the error Msg 0, Level 11, State 0, Line 6
A severe error occurred on the current command. The results, if any, should be discarded.

when executing an ALTER INDEX REORGANIZE command

If this were traditonal SQL Server I would have run a DBCC CHECKDB but what I read about Azure SQL Database that is not necessary as it is done behind the scenes.

Recommendations?

[langstonj61]

Following up on my last post, a subsequent running of exec dbo.dbaAzureSQLMaintenanceNew 'index','smart',1,0,1 ran to successful conclusion, so I concluded I must have encountered a bit of "cloudiness" on the prior tries.

[Hakan67]

You say this solution is "more suitable with Azure SQL DB" than Ola Hallengren's solution. In what way?

@Yochanan_Rachamim

[AlexOnData]

Thanks for the excellent blog and script, @Yochanan_Rachamim!
I’m looking at making a version of this script that could be compatible with Synapse SQL Pools, and while there are plenty of differences between the two platforms, a fair amount of the maintenance T-SQL is the same. Have you heard from any users of the script applying this to Synapse?

thanks,

Alex