How to maintain Azure SQL Indexes and Statistics

Published Mar 13 2019 06:12 PM 46.4K Views
Microsoft

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. 

42 Comments
%3CLINGO-SUB%20id%3D%22lingo-sub-368787%22%20slang%3D%22en-US%22%3EHow%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368787%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jul%2003%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20%5B2017-07-16%5DNote%3A%20New%20version%20is%20now%20available%2C%20added%20option%20to%20log%20operation%20to%20table%20including%20automatic%20retention%20of%203%20last%20operation%20(can%20be%20modified)%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20There%20are%20a%20lot%20of%20work%26nbsp%3Bthat%20Azure%20SQL%20saves%20from%20you%2C%20and%20most%20of%20the%20users%20we%20meet%20seem%20to%20believe%20that%20maintain%20the%20database%20indexes%20and%20statistics%20is%20one%20the%20missions%20you%20can%20forget%20when%20you%20migrate%20to%20Azure%20SQL.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Statistics%20objects%2C%20like%20in%20the%20box%20version%20of%20SQL%20have%20by%20default%20%22Auto%20update%22%20set%20to%20ON.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20the%20condition%20to%20run%20the%20auto%20update%20is%20updating%20for%20at%20least%2020%25%20%2B%20500%20rows%20in%20the%20table.%20if%20the%20condition%20is%20met%2C%20the%20update%20will%20occur%20when%20the%20optimizer%20needs%20to%20generate%20execution%20plan%20against%20that%20table.%20so%20except%26nbsp%3Bthat%20the%20update%20will%20take%20some%20time%20and%20use%20IO%20resources%20(which%20is%20very%20important%20information%20if%20you%20are%20on%20the%20cloud%20PAAS%20database)%20the%20update%20will%20read%20only%2030%25%20of%20the%20rows%20to%20generate%20the%20updated%20statistics%2C%20therefore%2C%20the%20statistic%20might%20not%20be%20100%25%20accurate.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EYou%20can%20use%20QDS%20to%20force%20the%20best%20plan%20for%20the%20statistics%2C%20but%20this%20is%20your%20responsibility%20to%20keep%20it%20up%20to%20date.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20so%2C%20this%20maintenance%26nbsp%3Bstill%20needs%20to%20be%20done%20by%20the%20DBA%20on%20a%20regular%20basis%2C%20and%20if%20this%20has%20not%20been%20done%2C%20your%20database%20can%20suffer%20from%20poor%20performance%20just%20because%20of%20that.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20I%20created%20a%20maintenance%20solution%20(Yes%2C%20I%20was%20inspired%20by%20%3CA%20href%3D%22https%3A%2F%2Fola.hallengren.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Ola%20Hallengren's%20solution%20%3C%2FA%3E%20)%20just%20that%20this%20one%20is%20lighter%20and%20suitable%20with%20Azure%20SQL%20DB%20(it%20also%20compatible%20with%20SQL%20Server%202012%20and%20higher)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Now%2C%20all%20you%20have%20to%20do%20is%20download%20the%20T-SQL%20script%2C%20execute%20it%20on%20your%20Azure%20SQL%20DB%20and%20execute%20the%20maintenance%20procedure.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20A%20quick%20remark%20about%20the%20options%20you%20have%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20exec%20%26nbsp%3BAzureSQLMaintenance%20%40operation%2C%40mode%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20%40operation%3A%20%7Ball%2C%20index%2C%20statistics%7D%20(no%20default)%3C%2FEM%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Estatistics%20%3C%2FSTRONG%3E%20%3A%20will%20run%20only%20statistics%20update%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Eindex%20%3C%2FSTRONG%3E%20%3A%20will%20run%20only%20index%20maintenance%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CEM%3E%20%40mode%3A%20%7Bsmart%2C%20dummy%7D%20(Default%3A%20smart)%20%3C%2FEM%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Esmart%20%3C%2FSTRONG%3E%20%3A%20will%20touch%20only%20modified%20statistics%20and%20choose%20index%20maintenance%20by%20%25%20of%20fragmentation%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Edummy%20%3C%2FSTRONG%3E%20%3A%20will%20run%20through%20all%20statistics%20or%20indexes.%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CEM%3E%20%40logtotable%3A%20%7B0%2C%201%7D%20(Default%3A%200)%20%3C%2FEM%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3E0%20%3C%2FSTRONG%3E%20%3A%20this%20feature%20is%20off.%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3E1%20%3C%2FSTRONG%3E%20%3A%26nbsp%3Bwill%20log%20the%20operation%20to%20table%20%5BAzureSQLMaintenanceLog%5D%20(if%20the%20table%20does%20not%20exist%20it%20will%20be%20created%20automatically)%20the%20log%20will%20update%20ongoing%20with%20any%20progress%20so%20you%20can%20monitor%20the%20progress%20with%20this%20log.%20for%20every%20operation%2C%20you%20can%20find%20detailed%20information%20about%20the%20object%20before%20it%20was%20maintained%20(fragmentation%20percent%20for%20indexes%2C%20and%20modification%20counter%20for%20statistics)%20by%20default%20only%203%20last%20operations%20will%20be%20kept%20in%20the%20log%20table%2C%20older%20execution%20log%20will%20be%20automatically%20removed%2C%20this%20can%20be%20changed%20in%20the%20procedure%20code.%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1678996737%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%20id%3D%22toc-hId-1703842397%22%3E%3CSTRONG%3E%20Download%20procedure%20code%3A%20%3C%2FSTRONG%3E%3C%2FH2%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--873160224%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%20id%3D%22toc-hId--848314564%22%3E%3CA%20href%3D%22https%3A%2F%2Fraw.githubusercontent.com%2Fyochananrachamim%2FAzureSQL%2Fmaster%2FAzureSQLMaintenance.txt%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20AzureSQLMaintenance%20%3C%2FA%3E%3C%2FH2%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3EScheduling%26nbsp%3Band%20automation%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-functions%2Ffunctions-scenario-database-table-cleanup%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2018%2F01%2F15%2Fautomating-azure-sql-db-index-and-statistic-maintenance-using-azure-automation%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Automating%20Azure%20SQL%20DB%20index%20and%20statistics%20maintenance%20using%20Azure%20Automation%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20!%20updated%3A%202016-10-27%20-%20fixed%20performance%20issue%20while%20updating%20statistics.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20!%20updated%3A%202016-11-14%20-%20fixed%20issue%20with%20the%20double%20rebuild%20of%20indexes.%20(thank%20you%26nbsp%3BJPelttari%20for%20the%20comment)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20!%20updated%3A%202016-11-29%20-%20adding%20information%20about%20scheduling%20and%20automation.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20!%20updated%3A%202017-07-16%20-%20adding%20an%20option%20to%20log%20operation%20to%20table%20with%20a%20rotation%20of%203%20last%20operations.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20!%20updated%3A%202018-01-15%20-%20added%20reference%20to%20another%20post%20that%20describes%20how%20to%20automate%20this%20maintenance%20task.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368787%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2003%2C%202016%20%5B2017-07-16%5DNote%3A%20New%20version%20is%20now%20available%2C%20added%20option%20to%20log%20operation%20to%20table%20including%20automatic%20retention%20of%203%20last%20operation%20(can%20be%20modified)%26nbsp%3BThere%20are%20a%20lot%20of%20work%26nbsp%3Bthat%20Azure%20SQL%20saves%20from%20you%2C%20and%20most%20of%20the%20users%20we%20meet%20seem%20to%20believe%20that%20maintain%20the%20database%20indexes%20and%20statistics%20is%20one%20the%20missions%20you%20can%20forget%20when%20you%20migrate%20to%20Azure%20SQL.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-368787%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Emaintenance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ereindex%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eupdate%20statistics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2049302%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2049302%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%2C%20we%20want%20to%20use%20Elastic%20Jobs%20in%20Azure%20to%20do%20that.%20We%20found%20that%20instruction%20on%20the%20web%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlshack.com%2Fautomating-azure-sql-database-index-maintenance-using-elastic-job-agents%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EAutomating%20Azure%20SQL%20Database%20index%20maintenance%20using%20Elastic%20Job%20Agents%20(sqlshack.com)%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20do%20we%20need%20to%20adjust%20your%20script%20so%20it%20would%20work%20with%20the%20instructions%20above%3F%20We%20want%20to%20run%20many%20jobs%20in%20parallel.%3CBR%20%2F%3E%3CBR%20%2F%3EBr%2C%20%3CBR%20%2F%3E%20Pascal%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2078946%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2078946%22%20slang%3D%22de-DE%22%3E%3CDIV%3E%3CSPAN%3EHi%20%3C%2FSPAN%3E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3EWhen%20our%20stored%20procedure%20is%20%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20in%20elasticjob%20db%20but%20not%20in%20target%20database.%20We%20get%20the%20following%20error%3A%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ECommand%20failed%3A%20Could%20not%20find%20stored%20procedure%20'dbo.AzureSQLMaintenance'.%20(Msg%202812%2C%20Level%2016%2C%20State%2062%2C%20Line%201)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EWhat%20do%20we%20need%20to%20adjust%20to%20run%20the%20stored%20procedure%20from%20the%20elastic%20job%20agent%20database%20in%20the%20target%20database%3F%26nbsp%3B%3CBR%20%2F%3EBro%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819993%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819993%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20will%20need%20to%20use%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EREPLACE(IndexName%2C%20'%5D.'%2C%20'%5D%5D.')%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EREPLACE(StatsName%2C%20'%5D.'%2C%20'%5D%5D.')%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Bfor%20indexes%20or%20stats%20with%20brackets%20and%20a%20dot%20in%20the%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106788%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106788%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20error%20in%20the%20log%20table%20is%3A%26nbsp%3B%26nbsp%3BFAILED%20%3A%201914Index%20cannot%20be%20created%20on%20object%20'dbo.fnTableDate'%20because%20the%20object%20is%20not%20a%20user%20table%20or%20view.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2213983%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2213983%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20managed%20instance.%26nbsp%3B%20Any%20chance%20you%20have%20a%20sample%20on%20how%20to%20run%20against%20all%20user%20db's%20in%20the%20managed%20instance%20instead%20of%20putting%20it%20in%20each%20db%20and%20executing%20one%20at%20a%20time%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2225784%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225784%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F964178%22%20target%3D%22_blank%22%3E%40ToddElliott%3C%2FA%3E%26nbsp%3B-%20at%20the%20moment%20the%20maintenance%20script%20is%20designed%20to%20work%20on%20the%20current%20database%20only.%3C%2FP%3E%0A%3CP%3EWe%20might%20consider%20adding%20cross%20database%20access%20to%20support%20managed%20instance%20in%20future%20versions.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2256463%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256463%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20few%20Table%20Valued%20functions%20in%20which%20return%20an%20%22at%22TableName%20table%20variable%20with%20a%20PRIMARY%20KEY%20defined%20in%20it.%20This%20script%20will%20try%20to%20include%20these%2C%20and%20then%20fails...%3C%2FP%3E%3CP%3E%22FAILED%20%3A%201914Index%20cannot%20be%20created%20on%20object%20'dbo.functionnamehere'%20because%20the%20object%20is%20not%20a%20user%20table%20or%20view.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2256472%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256472%22%20slang%3D%22en-US%22%3E%3CPRE%3ECREATE%20FUNCTION%0A%20%20%20%20%20%20%20%5Bdbo%5D.%5Bxxxxxxxxxxxxxxx%5D%0A%20%20%20%20%20%20()%0ARETURNS%20%40yyyyyyyyyyyyyyyyyyyyyyyyyyyy%20TABLE%0A%20%20%20%20%20%20%20(%5Bid%5D%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INT%20%20%20%20%20%20%20%20%20%20%20%20%20IDENTITY(1%2C%201)%20%20%20%20%20%20%20PRIMARY%20KEY%20%20%20%20%20%20%20%20%20%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%26lt%3Betcetera%26gt%3B)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2256482%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256482%22%20slang%3D%22en-US%22%3E%3CP%3EI%20added%20the%20following%20to%20address%20this%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CYOUR%20code%3D%22%22%3E%3C%2FYOUR%3E%3C%2FP%3E%3CPRE%3E%20%20into%20%23idxBefore%0A%20%20from%20sys.indexes%20idxs%0A%3CSTRONG%3E%20%20%20%20%20%20%20%20%20inner%20join%20sys.objects%20%5BSO%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20on%20%5BSO%5D.object_id%20%3D%20%5Bidxs%5D.object_id%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20%5BSO%5D.%5Btype%5D%20IN%20('U'%2C%20'V')%3C%2FSTRONG%3E%0A%20%20left%20join%20sys.dm_db_index_physical_stats(DB_ID()%2CNULL%2C%20NULL%2C%20NULL%20%2C%40indexStatsMode)%20i%20%20on%20i.object_id%20%3D%20idxs.object_id%20and%20i.index_id%20%3D%20idxs.index_id%3C%2FPRE%3E%3CP%3E%3CMORE%20of%3D%22%22%20your%3D%22%22%20code%3D%22%22%3E%3C%2FMORE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1130857%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1130857%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20this%20script%20recommended%20to%20run%20(at%20regular%20basis)%20when%20Azure%20SQL%20Autotuning%20is%20enabled%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1320823%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1320823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F534153%22%20target%3D%22_blank%22%3E%40EXEABLEBP%3C%2FA%3E%26nbsp%3B%20-%20yes.%20auto%20tuning%20is%20doing%20something%20else..%20it's%20not%20maintaining%20from%20that%20aspect.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1461464%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1461464%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20on%20Pricing%20Tier%20-%26gt%3B%26nbsp%3B%3CSPAN%3EPremium%20P4%3A%20500%20DTUs%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EManually%20ran%20Ola%20Hallengrens%20script%20modified%20as%20below%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%40FragmentationMedium%20nvarchar(max)%20%3D%20'INDEX_REORGANIZE%2CINDEX_REBUILD_ONLINE'%2C%3CBR%20%2F%3E%40FragmentationHigh%20nvarchar(max)%20%3D%20'INDEX_REBUILD_ONLINE%2CINDEX_REBUILD_OFFLINE'%2C%3CBR%20%2F%3E%40FragmentationLevel1%20int%20%3D%2050%2C%3CBR%20%2F%3E%40FragmentationLevel2%20int%20%3D%2080%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%40UpdateStatistics%20%3D%20'ALL'%2C%3CBR%20%2F%3E%40OnlyModifiedStatistics%20%3D%20'Y'%2C%3CBR%20%2F%3E%40StatisticsSample%20int%20%3D%20NULL%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20should%20be%20less%20intensive%20than%20a%20fullscan%20option%20scanning%20all%20rows%20in%20a%20table%20or%20doing%20an%20index%20rebuild%20at%20lower%20fragmentation%20levels%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDespite%20this%20the%20Compute%20Utilization%20quickly%20reaches%20100%25%20-%20even%20thought%20this%20was%20running%20out%20of%20hours%20i%20stopped%20the%20process%20after%207%20minutes%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20expected%20behaviour%20at%20this%20Pricing%20Tier%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20would%20like%20to%20schedule%20this%20to%20run%20weekly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Azure_Compute_100.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198614iD049563968D2BEC5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Azure_Compute_100.jpg%22%20alt%3D%22Azure_Compute_100.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495480%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495480%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F698811%22%20target%3D%22_blank%22%3E%40cwalshe%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Erebuilding%20and%20reorganizing%20indexes%20as%20well%20as%20updating%20statistics%20with%20full%20scan%20is%20an%20intensive%20IO%20operation.%3C%2FP%3E%0A%3CP%3Ethis%20can%20explain%20the%20high%20resource%20usage%20you%20can%20see.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehowever%2C%20if%20this%20ran%20when%20the%20system%20is%20idle%20-%20this%20is%20good%2C%20and%20it%20uses%20as%20much%20resource%20as%20it%20can%20to%20finish%20the%20maintenance%20task.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethis%20is%20the%20reason%20for%20why%20we%20recommend%20to%20run%20the%20maintenance%20during%20night%20%2F%20weekend%20%2F%20system%20idle%20time.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20that%3Bs%20not%20possible%20we%20should%20be%20more%20granular%20with%20the%20maintenance%20or%20maybe%20use%20different%20time%20or%20different%20service%20tier.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Enote%20that%20even%20if%20you%20see%20high%20resource%20usage%20that%20does%20not%20necessarily%20means%20that%20your%20application%20cannot%20work%20at%20the%20same%20time.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20really%20depend%20on%20the%20workload.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1511230%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1511230%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%2C%3CBR%20%2F%3E%26nbsp%3BThank%20you%20for%20the%20detailed%20explanation!%3CBR%20%2F%3E%26nbsp%3BCan%20you%20guide%20me%20what%20is%20the%20best%20way%20to%20split%26nbsp%3BAzure%20SQL%20(Indexes%20and%20Statistics)%20workloads%2C%20as%20we%20do%20not%20really%20have%20an%20idle%20time%20for%20system%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3BSergeyF%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1518554%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1518554%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723323%22%20target%3D%22_blank%22%3E%40Sergey_F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20no%20magic%20formula%2C%20this%20will%20be%20decided%20based%20on%20tests%20only.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20worth%20to%20know%20that%20the%20script%20is%20running%20an%20analysis%20at%20first%20step%20and%20then%20starts%20by%20maintaining%20the%20database%20objects.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20stop%20that%20and%20run%20it%20again%20after%20a%20while%2C%20it%20will%20do%20the%20assessment%20again%20and%20if%20an%20object%20already%20maintained%20and%20there%20is%20no%20need%20to%20re-run%20the%20maintenance%20it%20will%20just%20skip%20that%20resource.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eso%2C%20it%20is%20safe%20to%20stop%20it%20when%20your%20maintenance%20window%20ends%20and%20restart%20whenever%20this%20is%20possible.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20indexed%20we%20have%20the%20resumable%20operation%2C%20unfortunately%20this%20maintenance%20script%20is%20not%20using%20that%20ability%20yet.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3EYochanan.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1670012%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1670012%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3Bcan%20you%20tell%20me%20how%20to%20modify%20the%20script%20for%20this%3F%20I%20can't%20seem%20to%20get%20past%20this%20error.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E-----------------------%3CBR%20%2F%3Eset%20operation%20%3D%20all%3CBR%20%2F%3Eset%20mode%20%3D%20smart%3CBR%20%2F%3Eset%20LogToTable%20%3D%201%3CBR%20%2F%3E-----------------------%3CBR%20%2F%3EGet%20index%20information...(wait)%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EIndex%20Information%3A%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3ETotal%20Indexes%3A%20737%3CBR%20%2F%3EAverage%20Fragmentation%3A%201.22889%3CBR%20%2F%3EFragmented%20Indexes%3A%201%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EGet%20statistics%20information...%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EStatistics%20Information%3A%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EStart%20executing%20commands...%3CBR%20%2F%3EALTER%20INDEX%20%5BPK_gnEmailPrev%5D%20ON%20%5Bdbo%5D.%5BgnEmailPrev%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D3)%3B%3CBR%20%2F%3EFAILED%20%3A%202725An%20online%20operation%20cannot%20be%20performed%20for%20index%20'PK_gnEmailPrev'%20because%20the%20index%20contains%20column%20'RecipList'%20of%20data%20type%20text%2C%20ntext%2C%20image%20or%20FILESTREAM.%20For%20a%20non-clustered%20index%2C%20the%20column%20could%20be%20an%20include%20column%20of%20the%20index.%20For%20a%20clustered%20index%2C%20the%20column%20could%20be%20any%20column%20of%20the%20table.%20If%20DROP_EXISTING%20is%20used%2C%20the%20column%20could%20be%20part%20of%20a%20new%20or%20old%20index.%20The%20operation%20must%20be%20performed%20offline.%3CBR%20%2F%3EMsg%2050000%2C%20Level%2016%2C%20State%201%2C%20Procedure%20AzureSQLMaintenance%2C%20Line%20269%20%5BBatch%20Start%20Line%20284%5D%3CBR%20%2F%3EScript%20has%20errors%20-%20please%20review%20the%20log.%3C%2FP%3E%3CP%3ECompletion%20time%3A%202020-09-15T12%3A00%3A21.5813261-05%3A00%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1670073%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1670073%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20to%20set%20the%20script%20to%20do%20offline%20only%20for%20it%20to%20run.%20The%20script%20completed%20but%20it%20didn't%20defragment.%20Current%20fragmentation%20is%20still%20at%2099.13%25.%3CBR%20%2F%3E%3CBR%20%2F%3Eid%20OperationTime%20command%20ExtraInfo%20StartTime%20EndTime%20StatusMessage%3CBR%20%2F%3E2%202020-09-15%2012%3A06%3A33.7869014%20ALTER%20INDEX%20%5BPK_gnEmailPrev%5D%20ON%20%5Bdbo%5D.%5BgnEmailPrev%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D3)%3B%20Current%20fragmentation%3A%2099.13%25%202020-09-15%2012%3A06%3A33.8706769%202020-09-15%2012%3A06%3A33.8717064%20FAILED%20%3A%202725An%20online%20operation%20cannot%20be%20performed%20for%20index%20'PK_gnEmailPrev'%20because%20the%20index%20contains%20column%20'RecipList'%20of%20data%20type%20text%2C%20ntext%2C%20image%20or%20FILESTREAM.%20For%20a%20non-clustered%20index%2C%20the%20column%20could%20be%20an%20include%20column%20of%20the%20index.%20For%20a%20clustered%20index%2C%20the%20column%20could%20be%20any%20column%20of%20the%20table.%20If%20DROP_EXISTING%20is%20used%2C%20the%20column%20could%20be%20part%20of%20a%20new%20or%20old%20index.%20The%20operation%20must%20be%20performed%20offline.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1673150%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1673150%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F794587%22%20target%3D%22_blank%22%3E%40reminvestor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20an%20old%20and%20deprecated%20data%20type%20for%20one%20or%20some%20of%20the%20columns%20in%20your%20table.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20is%20recommended%20to%20use%20supported%20datatype%20for%20your%20columns%20to%20get%20more%20efficiency%20and%20supportability%3C%2FP%3E%0A%3CP%3Efor%20instance%2C%20if%20you%20are%20using%20ntext%20datatype%20you%20may%20use%20nvarchar(max)%20instead.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eanother%20option%20is%20to%20modify%20the%20script%20to%20do%20offline%20operation%20for%20all%20indexes%2C%20in%20this%20case%20you%20will%20not%20face%20any%20issue%20with%20these%20data%20types%20however%20concurrency%20might%20be%20impacted%20during%20index%20maintenance%20operation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou%20may%20file%20an%20issue%20in%20the%20github%20page%20and%20I%20will%20try%20to%20fix%20that%20in%20the%20next%20cycle%20for%20this%20maintenance%20script.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20that%20helps%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan%20%5BMSFT%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1707960%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1707960%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20looking%20to%20use%20your%20scripts%20as%20we%20currently%20have%20no%20maint%20plan%20in%20place%20and%20are%20facing%20high%20fragmentation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20thing%20that%20concerns%20us%20slightly%20is%20that%20one%20of%20our%20key%20tables%20has%20a%20UNIQUEIDENTIFIER%20primary%20key%20and%20index%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20this%20solution%20work%20ok%20with%20this%2C%20or%20should%20we%20look%20to%20do%20something%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1709287%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1709287%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F807357%22%20target%3D%22_blank%22%3E%40CrocJon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyes%2C%20it%20will%20work%20perfectly%20with%20UNIQUEIDENTIFIER%20column%20(UIC).%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eone%20recommendation%20I%20can%20share%20with%20you%20to%20avoid%20high%20fragmentation%20on%20this%20PK%20column%20is%20to%20use%20increasing%20value%20for%20this%20UIC%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Efor%20instance%2C%20you%20may%20generate%20new%20UIC%20with%20newid()%20T-SQL%20function%2C%20however%20it%20will%20generate%20randomized%20value.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20change%20that%20to%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fnewsequentialid-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ENEWSEQUENTIALID%3C%2FA%3E()%20function%20it%20will%20generate%20increasing%20values%20and%20prevent%20page-split%20and%20index%20fragmentation.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1940244%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1940244%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20this%20script......just%20what%20I've%20been%20looking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%20problem%20I've%20found%2C%20the%20param%26nbsp%3B%40debug%20doesn't%20have%20a%20length%20specified%20so%20it's%20defaulting%20to%20nvarchar(1)%20which%20means%20the%20check%20at%20the%20end%20of%20the%20script%2C%20where%20you%20check%20its%20value%20is%20not%20evaluating%20correctly.%26nbsp%3B%20Generates%20tables%20as%20a%20result.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F*Print%20debug%20information%20in%20case%20debug%20is%20activated%20*%2F%0Aif%20%40debug!%3D'None'%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1940894%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1940894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885634%22%20target%3D%22_blank%22%3E%40GregPurkis%3C%2FA%3E%26nbsp%3B%20-%20Thank%20you%20for%20your%20feedback.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eits%20true%2C%20the%20debug%20option%20is%20not%20really%20implemented%20yet%2C%20it%20was%20used%20for%20a%20specific%20purpose%20during%20the%20last%20development%20cycle.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1994093%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994093%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B-%20Could%20you%20add%20to%20the%20functionality%20of%20the%20script%20to%20take%20into%20account%20fill%20factor%3F%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2006713%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2006713%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F506037%22%20target%3D%22_blank%22%3E%40Peter-MSFT%3C%2FA%3E%26nbsp%3B-%20Thank%20you%20for%20you%20feedback%2C%20luckily%20for%20us%20fill%20factor%20is%20kept%20on%20rebuild%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%3EWhen%20an%20option%20is%20not%20explicitly%20specified%2C%20the%20current%20setting%20is%20applied.%20For%20example%2C%20if%20a%20FILLFACTOR%20setting%20is%20not%20specified%20in%20the%20REBUILD%20clause%2C%20the%20fill%20factor%20value%20stored%20in%20the%20system%20catalog%20will%20be%20used%20during%20the%20rebuild%20process.%20To%20view%20the%20current%20index%20option%20settings%2C%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-catalog-views%2Fsys-indexes-transact-sql%3Fview%3Dsql-server-ver15%22%20data-linktype%3D%22relative-path%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Esys.indexes%3C%2FA%3E.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESource%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Falter-index-transact-sql%3Fview%3Dsql-server-ver15%23remarks%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Falter-index-transact-sql%3Fview%3Dsql-server-ver15%23remarks%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2052084%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2052084%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20work!%20The%20updated%20script%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2072573%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072573%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748794%22%20target%3D%22_blank%22%3E%40proesler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20reaching%20out%2C%20you%20may%20use%20the%20instructions%20to%20create%20the%20Elastic%20Jobs%20setup.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou%20can%20then%20use%20any%20procedure%20you%20like%20to%20be%20automated%20with%20the%20Job.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhen%20you%20create%20the%20job%20step%20using%20%22%3CSPAN%3Esp_add_jobstep%22%20use%20your%20preferred%20Maintenance%20procedure%20in%20the%26nbsp%3B%3CSPAN%20class%3D%22crayon-sy%22%3E%40%3C%2FSPAN%3E%3CSPAN%20class%3D%22crayon-i%22%3Ecommand%20parameter.%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22crayon-i%22%3Ejust%20make%20sure%20the%20procedure%20is%20created%20with%20the%20most%20recent%20version%20on%20every%20database%20in%20the%20target%20group.%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22crayon-i%22%3EThank%20you%2C%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22crayon-i%22%3EYochanan.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2081760%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2081760%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748794%22%20target%3D%22_blank%22%3E%40proesler%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20Azure%20SQL%20DB%20we%20cannot%20use%20cross%20database%20reference%2C%20therefore%20the%20procedure%20needs%20to%20be%20created%20in%20each%20destination%20database.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou%20may%20include%20another%20step%20in%20your%20elastic%20job%20to%20create%20%2F%20update%20the%20maintenance%20procedure%20in%20each%20destination%20database%20just%20before%20executing%20it.%3C%2FP%3E%0A%3CP%3Eplease%20note%20that%20some%20fixes%20has%20been%20included%20in%20the%20recent%20version%2C%20so%20please%20make%20sure%20you%20are%20using%20the%20recent%20version%20in%20your%20environment.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106611%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106611%22%20slang%3D%22en-US%22%3E%3CP%3ERunning%20the%20latest%20version%2C%20it%20oddly%20is%20trying%20to%20do%20an%20index%20rebuild%20on%20a%20Table-valued%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2106703%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2106703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F316545%22%20target%3D%22_blank%22%3E%40langstonj61%3C%2FA%3E%26nbsp%3B%20-%20I%20cannot%20repro%20the%20same%2C%20can%20you%20share%20more%20details%20about%20your%20specific%20scenario%3F%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22ms-editor-squiggler%22%20style%3D%22color%3A%20initial%3B%20font%3A%20initial%3B%20font-feature-settings%3A%20initial%3B%20font-kerning%3A%20initial%3B%20font-optical-sizing%3A%20initial%3B%20font-variation-settings%3A%20initial%3B%20text-orientation%3A%20initial%3B%20text-rendering%3A%20initial%3B%20-webkit-font-smoothing%3A%20initial%3B%20-webkit-locale%3A%20initial%3B%20-webkit-text-orientation%3A%20initial%3B%20-webkit-writing-mode%3A%20initial%3B%20writing-mode%3A%20initial%3B%20zoom%3A%20initial%3B%20place-content%3A%20initial%3B%20place-items%3A%20initial%3B%20place-self%3A%20initial%3B%20alignment-baseline%3A%20initial%3B%20animation%3A%20initial%3B%20appearance%3A%20initial%3B%20aspect-ratio%3A%20initial%3B%20backdrop-filter%3A%20initial%3B%20backface-visibility%3A%20initial%3B%20background%3A%20initial%3B%20background-blend-mode%3A%20initial%3B%20baseline-shift%3A%20initial%3B%20block-size%3A%20initial%3B%20border-block%3A%20initial%3B%20border%3A%20initial%3B%20border-radius%3A%20initial%3B%20border-collapse%3A%20initial%3B%20border-inline%3A%20initial%3B%20inset%3A%20initial%3B%20box-shadow%3A%20initial%3B%20box-sizing%3A%20initial%3B%20break-after%3A%20initial%3B%20break-before%3A%20initial%3B%20break-inside%3A%20initial%3B%20buffered-rendering%3A%20initial%3B%20caption-side%3A%20initial%3B%20caret-color%3A%20initial%3B%20clear%3A%20initial%3B%20clip%3A%20initial%3B%20clip-path%3A%20initial%3B%20clip-rule%3A%20initial%3B%20color-interpolation%3A%20initial%3B%20color-interpolation-filters%3A%20initial%3B%20color-rendering%3A%20initial%3B%20color-scheme%3A%20initial%3B%20columns%3A%20initial%3B%20column-fill%3A%20initial%3B%20gap%3A%20initial%3B%20column-rule%3A%20initial%3B%20column-span%3A%20initial%3B%20contain%3A%20initial%3B%20contain-intrinsic-size%3A%20initial%3B%20content%3A%20initial%3B%20content-visibility%3A%20initial%3B%20counter-increment%3A%20initial%3B%20counter-reset%3A%20initial%3B%20counter-set%3A%20initial%3B%20cursor%3A%20initial%3B%20cx%3A%20initial%3B%20cy%3A%20initial%3B%20d%3A%20initial%3B%20display%3A%20block%3B%20dominant-baseline%3A%20initial%3B%20empty-cells%3A%20initial%3B%20fill%3A%20initial%3B%20fill-opacity%3A%20initial%3B%20fill-rule%3A%20initial%3B%20filter%3A%20initial%3B%20flex%3A%20initial%3B%20flex-flow%3A%20initial%3B%20float%3A%20initial%3B%20flood-color%3A%20initial%3B%20flood-opacity%3A%20initial%3B%20grid%3A%20initial%3B%20grid-area%3A%20initial%3B%20height%3A%20initial%3B%20hyphens%3A%20initial%3B%20image-orientation%3A%20initial%3B%20image-rendering%3A%20initial%3B%20inline-size%3A%20initial%3B%20inset-block%3A%20initial%3B%20inset-inline%3A%20initial%3B%20isolation%3A%20initial%3B%20letter-spacing%3A%20initial%3B%20lighting-color%3A%20initial%3B%20line-break%3A%20initial%3B%20list-style%3A%20initial%3B%20margin-block%3A%20initial%3B%20margin%3A%20initial%3B%20margin-inline%3A%20initial%3B%20marker%3A%20initial%3B%20mask%3A%20initial%3B%20mask-type%3A%20initial%3B%20max-block-size%3A%20initial%3B%20max-height%3A%20initial%3B%20max-inline-size%3A%20initial%3B%20max-width%3A%20initial%3B%20min-block-size%3A%20initial%3B%20min-height%3A%20initial%3B%20min-inline-size%3A%20initial%3B%20min-width%3A%20initial%3B%20mix-blend-mode%3A%20initial%3B%20object-fit%3A%20initial%3B%20object-position%3A%20initial%3B%20offset%3A%20initial%3B%20opacity%3A%20initial%3B%20order%3A%20initial%3B%20origin-trial-test-property%3A%20initial%3B%20orphans%3A%20initial%3B%20outline%3A%20initial%3B%20outline-offset%3A%20initial%3B%20overflow-anchor%3A%20initial%3B%20overflow-wrap%3A%20initial%3B%20overflow%3A%20initial%3B%20overscroll-behavior-block%3A%20initial%3B%20overscroll-behavior-inline%3A%20initial%3B%20overscroll-behavior%3A%20initial%3B%20padding-block%3A%20initial%3B%20padding%3A%20initial%3B%20padding-inline%3A%20initial%3B%20page%3A%20initial%3B%20page-orientation%3A%20initial%3B%20paint-order%3A%20initial%3B%20perspective%3A%20initial%3B%20perspective-origin%3A%20initial%3B%20pointer-events%3A%20initial%3B%20position%3A%20initial%3B%20quotes%3A%20initial%3B%20r%3A%20initial%3B%20resize%3A%20initial%3B%20ruby-position%3A%20initial%3B%20rx%3A%20initial%3B%20ry%3A%20initial%3B%20scroll-behavior%3A%20initial%3B%20scroll-margin-block%3A%20initial%3B%20scroll-margin%3A%20initial%3B%20scroll-margin-inline%3A%20initial%3B%20scroll-padding-block%3A%20initial%3B%20scroll-padding%3A%20initial%3B%20scroll-padding-inline%3A%20initial%3B%20scroll-snap-align%3A%20initial%3B%20scroll-snap-stop%3A%20initial%3B%20scroll-snap-type%3A%20initial%3B%20shape-image-threshold%3A%20initial%3B%20shape-margin%3A%20initial%3B%20shape-outside%3A%20initial%3B%20shape-rendering%3A%20initial%3B%20size%3A%20initial%3B%20speak%3A%20initial%3B%20stop-color%3A%20initial%3B%20stop-opacity%3A%20initial%3B%20stroke%3A%20initial%3B%20stroke-dasharray%3A%20initial%3B%20stroke-dashoffset%3A%20initial%3B%20stroke-linecap%3A%20initial%3B%20stroke-linejoin%3A%20initial%3B%20stroke-miterlimit%3A%20initial%3B%20stroke-opacity%3A%20initial%3B%20stroke-width%3A%20initial%3B%20tab-size%3A%20initial%3B%20table-layout%3A%20initial%3B%20text-align%3A%20initial%3B%20text-align-last%3A%20initial%3B%20text-anchor%3A%20initial%3B%20text-combine-upright%3A%20initial%3B%20text-decoration%3A%20initial%3B%20text-decoration-skip-ink%3A%20initial%3B%20text-indent%3A%20initial%3B%20text-overflow%3A%20initial%3B%20text-shadow%3A%20initial%3B%20text-size-adjust%3A%20initial%3B%20text-transform%3A%20initial%3B%20text-underline-offset%3A%20initial%3B%20text-underline-position%3A%20initial%3B%20touch-action%3A%20initial%3B%20transform%3A%20initial%3B%20transform-box%3A%20initial%3B%20transform-origin%3A%20initial%3B%20transform-style%3A%20initial%3B%20transition%3A%20initial%3B%20user-select%3A%20initial%3B%20vector-effect%3A%20initial%3B%20vertical-align%3A%20initial%3B%20visibility%3A%20initial%3B%20-webkit-app-region%3A%20initial%3B%20border-spacing%3A%20initial%3B%20-webkit-border-image%3A%20initial%3B%20-webkit-box-align%3A%20initial%3B%20-webkit-box-decoration-break%3A%20initial%3B%20-webkit-box-direction%3A%20initial%3B%20-webkit-box-flex%3A%20initial%3B%20-webkit-box-ordinal-group%3A%20initial%3B%20-webkit-box-orient%3A%20initial%3B%20-webkit-box-pack%3A%20initial%3B%20-webkit-box-reflect%3A%20initial%3B%20-webkit-highlight%3A%20initial%3B%20-webkit-hyphenate-character%3A%20initial%3B%20-webkit-line-break%3A%20initial%3B%20-webkit-line-clamp%3A%20initial%3B%20-webkit-mask-box-image%3A%20initial%3B%20-webkit-mask%3A%20initial%3B%20-webkit-mask-composite%3A%20initial%3B%20-webkit-perspective-origin-x%3A%20initial%3B%20-webkit-perspective-origin-y%3A%20initial%3B%20-webkit-print-color-adjust%3A%20initial%3B%20-webkit-rtl-ordering%3A%20initial%3B%20-webkit-ruby-position%3A%20initial%3B%20-webkit-tap-highlight-color%3A%20initial%3B%20-webkit-text-combine%3A%20initial%3B%20-webkit-text-decorations-in-effect%3A%20initial%3B%20-webkit-text-emphasis%3A%20initial%3B%20-webkit-text-emphasis-position%3A%20initial%3B%20-webkit-text-fill-color%3A%20initial%3B%20-webkit-text-security%3A%20initial%3B%20-webkit-text-stroke%3A%20initial%3B%20-webkit-transform-origin-x%3A%20initial%3B%20-webkit-transform-origin-y%3A%20initial%3B%20-webkit-transform-origin-z%3A%20initial%3B%20-webkit-user-drag%3A%20initial%3B%20-webkit-user-modify%3A%20initial%3B%20white-space%3A%20initial%3B%20widows%3A%20initial%3B%20width%3A%20initial%3B%20will-change%3A%20initial%3B%20word-break%3A%20initial%3B%20word-spacing%3A%20initial%3B%20x%3A%20initial%3B%20y%3A%20initial%3B%20z-index%3A%20initial%3B%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2478980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2478980%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20supposed%20%22Best%20Practices%22%20are%20not%20best%20practices%20and%20we're%20never%20meant%20to%20be%20taken%20as%20%22Best%20Practices%22.%20The%20recommendation%20of%20the%20original%20author%20of%20the%20MS%20docs%20and%20creator%20of%20REORGANIZE%20and%20REBUILD%20is%20to%2C%20and%20I%20quote...%20%22take%20those%20numbers%20with%20a%20pinch%20of%20salt%20and%20don%E2%80%99t%20treat%20them%20as%20absolute.%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fwhere-do-the-books-online-index-fragmentation-thresholds-come-from%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlskills.com%2Fblogs%2Fpaul%2Fwhere-do-the-books-online-index-fragmentation-thresholds-come-from%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'll%20also%20tell%20you%20that%20REORGANIZE%20does%20NOT%20work%20the%20way%20most%20people%20think%20it%20does.%20It%20does%20NOT%20%22follow%20the%20Fill%20Factor%22%20in%20any%20way%20ever%20close%20to%20a%20REBUILD.%20Instead%2C%20all%20it%20does%20is%20it%20tries%20to%20shrink%20the%20index%20by%20combining%20%22some%22%20pages%20UP%20TO%20the%20Fill%20Factor.%20It%20does%20NOT%20create%20any%20Free%20Space%20above%20the%20Fill%20Factor.%20The%20bad%20part%20about%20that%20is%20that%2099.99%25%20of%20the%20people%20in%20the%20world%20use%205%20or%2010%25%20to%2030%25%20LOGICAL%20fragmentation%20to%20identify%20when%20REORGANIZE%20should%20be%20done%20and%20they%20do%20a%20REBUILD%20only%20if%20the%20LOGICAL%20fragmentation%20goes%20over%2030%25.%20Since%20REORGANIZE%20DOES%20remove%20LOGICAL%20fragmentation%2C%20it%20very%20quickly%20gets%20%22stuck%22%20on%20indexes%20and%20they%20frequently%20never%20be%20rebuilt%20ever%20again.%20That%20means%20that%20no%20Free%20Space%20is%20created%20above%20the%20Fill%20Factor.%20That%20means%20that%20virtually%20every%20index%20you%20lowered%20the%20Fill%20Factor%20to%20prevent%20fragmentation%20is%20NOT%20working%20correctly%20and%20the%20fragmentation%20such%20an%20action%20was%20supposed%20to%20prevent%20is%20now%20perpetual%20and%20actually%20gets%20worse%20after%20each%20REORGANIZE%20because%20all%20the%20critical%20space%20above%20the%20Fill%20Factor%20is%20permanently%20full%20because%20REORGANIZE%20removes%20free%20space%20below%20the%20Fill%20Factor%20and%20compresses%20pages.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20phenomena%20prevents%20perfect%20wide%20or%20random%20distribution%20indexes%20from%20working%20correctly.%20For%20example%2C%20a%20correctly%20maintained%20Random%20GUID%20index%20(and%20I%20have%20the%20proof%20in%20multiple%20repeatable%20tests)%20can%20withstand%20100%2C000%20per%20day%20inserts%20for%2058%20days%20(that's%20a%20total%20of%205.8%20MILLION%20rows%2C%20folks!)%20with%20less%20than%201%25%20logical%20fragmentation%20and%20absolutely%20no%20index%20maintenance%20during%20that%20entire%20period.%20The%20way%20I%20did%20that%20was%20to%20STOP%20using%20REORGANIZE.%20In%20a%20parallel%20test%2C%20REORGANIZE%20cause%20fairly%20massive%20and%20perpetual%20page%20splits%20all%20day%20every%20day%20during%20that%20same%2058%20day%20period%20along%20with%20fragmentation%20going%20over%205%25%20and%20needing%20another%20REORGANIZE%20every%204%20days.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDuring%20that%20same%20testing%2C%20it%20was%20proven%20that%20it's%20actually%20better%20to%20do%20NO%20index%20maintenance%20than%20it%20is%20to%20ever%20use%20REORGANIZE.%20The%20only%202%20places%20that%20you%20should%20use%20reorganize%20is%20to%20compress%20LOBs%20(it%20even%20sucks%20at%20that%20and%20needs%20a%20rebuild%20right%20afterwards)%20and%20to%20remove%20physical%20fragmentation%20on%20an%20Insert%20Hot%20Spot%20cause%20by%20an%20Insert%2FUpdate%20pattern%20on%20ever%20increasing%20indexes.%20And%2C%20other%20fragmenting%20indexes%20are%20also%20affected%20in%20a%20similar%20manner...%20not%20just%20Random%20GUIDs.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFor%20anyone%20interested%20(and%2C%20no....%20this%20is%20NOT%20meant%20to%20be%20any%20form%20of%20spam)%2C%20I'm%20giving%20the%20hour%20long%20version%20of%20my%202.5%20hour%20presentation%20on%20the%20subject%20on%20the%2028th%20of%20July%20at%20%22EightKB%22.%20You%20can%20register%20to%20attend%20at%20the%20following%20URL...%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Feightkb.online%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Feightkb.online%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E--Jeff%20Moden%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2511547%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2511547%22%20slang%3D%22en-US%22%3E%3CP%3Egot%20error%26nbsp%3B%20when%20running%20as%26nbsp%3B%20exec%20dbo.AzureSQLMaintenance%20'all'%2C%20%40LogToTable%3D0%2C%20%40ResumableIndexRebuild%3D1%3C%2FP%3E%3CP%3E-----------------------%3CBR%20%2F%3Eset%20operation%20%3D%20all%3CBR%20%2F%3Eset%20mode%20%3D%20smart%3CBR%20%2F%3Eset%20ResumableIndexRebuild%20%3D%201%3CBR%20%2F%3Eset%20RebuildHeaps%20%3D%200%3CBR%20%2F%3Eset%20LogToTable%20%3D%200%3CBR%20%2F%3E-----------------------%3CBR%20%2F%3EGet%20index%20information...(wait)%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EIndex%20Information%3A%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3ETotal%20Indexes%3A%207%3CBR%20%2F%3EAverage%20Fragmentation%3A%2097.6471%3CBR%20%2F%3EFragmented%20Indexes%3A%201%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EGet%20statistics%20information...%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EStatistics%20Information%3A%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3ETotal%20Modifications%3A%20128%3CBR%20%2F%3EModified%20Statistics%3A%202%3CBR%20%2F%3E---------------------------------------%3CBR%20%2F%3EStart%20executing%20commands...%3CBR%20%2F%3EALTER%20INDEX%20%5BCORRESPONDENCE_REQUEST_PK%5D%20ON%20%5BUA3_CORRESPONDENCE_MGMT%5D.%5BCORRESPONDENCE_REQUEST%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1%2C%20RESUMABLE%3DON)%3B%3CBR%20%2F%3EFAILED%20%3A%20574RESUMABLE%20INDEX%20statement%20cannot%20be%20used%20inside%20a%20user%20transaction.%3CBR%20%2F%3EUPDATE%20STATISTICS%20%5BUA3_CORRESPONDENCE_MGMT%5D.%5BCORRESPONDENCE_RESPONSE%5D%20(%5BCORRESPONDENCE_RESPONSE_PK%5D)%20WITH%20FULLSCAN%3B%3CBR%20%2F%3EUPDATE%20STATISTICS%20%5BUA3_CORRESPONDENCE_MGMT%5D.%5BHPP_TENANT%5D%20(%5BHPP_TENANT_PK%5D)%20WITH%20FULLSCAN%3B%3CBR%20%2F%3EMsg%2050000%2C%20Level%2016%2C%20State%201%2C%20Procedure%20AzureSQLMaintenance%2C%20Line%20527%20%5BBatch%20Start%20Line%20570%5D%3CBR%20%2F%3EScript%20has%20errors%20-%20please%20review%20the%20log.%3C%2FP%3E%3CP%3ECompletion%20time%3A%202021-07-02T12%3A51%3A01.8000450-05%3A00%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2511559%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2511559%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20executing%20admin%20user%20.%26nbsp%3B%20when%20i%20try%20exec%20dbo.AzureSQLMaintenance%20'all'%20seems%20to%20work%20but%20takes%20long%20time%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2511561%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2511561%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20is%20error%20when%20used%20with%20%40ResumableIndexRebuild%3D1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMsg%2050000%2C%20Level%2016%2C%20State%201%2C%20Procedure%20dbo.AzureSQLMaintenance%2C%20Line%20527%20%5BBatch%20Start%20Line%20572%5D%3CBR%20%2F%3EScript%20has%20errors%20-%20please%20review%20the%20log.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2511709%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2511709%22%20slang%3D%22en-US%22%3E%3CP%3Eprevious%20code%20still%20works%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2591808%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2591808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055213%22%20target%3D%22_blank%22%3E%40Jeff_Moden%3C%2FA%3E%26nbsp%3B%20-%20Appreciate%20your%20detailed%20feedback.%20I%20tend%20to%20agree%20with%20the%20points%20you%20mentioned.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20script%20never%20meant%20to%20be%20the%20best%20solution%20to%20every%20scenario%2C%20however%20it%20still%20fit%20to%20most%20of%20the%20scenarios.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20will%20take%20your%20inputs%20and%20will%20consider%20the%20best%20way%20to%20incorporate%20it%20with%20the%20maintenance%20procedure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAgain%20-%20thank%20you%20very%20much%20for%20your%20valuable%20feedback.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2592746%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2592746%22%20slang%3D%22en-US%22%3E%3CP%3EStep%20one%20would%20be%20to%20simply%20stop%20using%20REORGANIZE.%26nbsp%3B%20Step%20two%20would%20be%20to%20stop%20using%20the%20numbers%205%20and%2030%25%20for%20logical%20fragmentation.%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20a%20look%20at%20the%20EightKB%20presentation%20I'm%20putting%20on%20tomorrow%20at%2014%3A30UTC%20and%20make%20adaptations%20to%20your%20code.%26nbsp%3B%20Let%20me%20know%20how%20it%20works%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2661613%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2661613%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this%20code!%20%3B%3C%2FP%3E%3CP%3EA%20question%20%3A%20many%20options%20exists%20to%20run%20this%20script.%20Is%20automation%20account%20still%20the%20best%20suitable%20way%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fthebernardlim.com%2Fazure-scheduling-sql-db-options%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EAzure%20Job%20Scheduling%20Options%20for%20Azure%20SQL%20DB%20-%20Bernard%20Lim%20%7C%20Cloud%20%7C%20Data%20%7C%20Software%20(thebernardlim.com)%3C%2FA%3E%3C%2FP%3E%3CP%3EWe%20have%20a%20situation%20at%20a%20customer%20that%20requires%20multiple%20Azure%20SQL%20DB%20to%20be%20maintained.%3C%2FP%3E%3CP%3EWondering%20what%20your%20view%20is%20%3B%20elastic%20jobs%20sounds%20promising%20too%2C%20but%20still%20preview...%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795197%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795197%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F38446%22%20target%3D%22_blank%22%3E%40Tim%20Braes%3C%2FA%3E%26nbsp%3B%2C%20I'm%20very%20happy%20to%20see%20this%20code%20is%20helping%20you.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eas%20per%20executing%20the%20maintenance%20task%2C%20I%20guess%20that%20Elastic%20jobs%20(considering%20the%20fact%20that%20you%20can%20run%20the%20maintenance%20after%20if%20any%20issue%20happen)%20it%20does%20make%20sense%20to%20use%20preview%20feature.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyes%2C%20it%20is%20not%20recommended%20for%20production%20workload%20as%20this%20is%20still%20considered%20as%20beta%20product%2C%20however%20this%20is%20more%20suitable%20for%20your%20needs%20and%20you%20can%20monitor%20and%20act%20if%20needed%20so%20I%20think%20it%20does%20make%20sense%20to%20use%20Elastic%20jobs%20although%20it%20is%20still%20in%20preview.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Emany%20customers%20already%20had%20some%20jobs%20systems%2C%20of%20automation%20tool%20they%20use%20for%20their%20own%20implementation%20-%20this%20maintenance%20can%20be%20incorporated%20there%20as%20well%20if%20it%20is%20existed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBottom%20line%2C%20my%20vote%20goes%20to%20Elastic%20Jobs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3EYochanan.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2797169%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2797169%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%20i%20been%20advised%20by%20azure%20support%20to%20run%20this%20but%20I%20seem%20to%20be%20getting%20an%20issue%20where%20it%20wants%20to%20rebuild%20an%20index%20again%20and%20again%20on%20the%20same%20table%2C%20I%20think%20it%20is%20because%20of%20the%20partitions%20so%20it's%20detecting%20them%20but%20not%20specifying%20partition%20number%20is%20there%20any%20fixes%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ee.g.%20when%20run%20I%20get%26nbsp%3B%3C%2FP%3E%3CP%3EtxtCMD%20ExtraInfo%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.37%25%20with%20184%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.96%25%20with%20675%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.87%25%20with%20177%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2099.20%25%20with%20624%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.82%25%20with%20762%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.32%25%20with%20179%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2098.88%25%20with%20179%20pages%3CBR%20%2F%3EALTER%20INDEX%20%5BCI_Q_Net_xx%5D%20ON%20%5Bdbo%5D.%5BQ_Net_xx%5D%20REBUILD%20WITH(ONLINE%3DON%2CMAXDOP%3D1)%3B%20Current%20fragmentation%3A%2099.02%25%20with%20916%20pages%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806826%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806826%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1170584%22%20target%3D%22_blank%22%3E%40ECAretonet%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20your%20feedback.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehandling%20partitioned%20tables%20is%20one%20of%20the%20items%20I'm%20currently%20have%20in%20my%20backlog.%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethere%20is%20an%20issue%20about%20it%20in%20my%20git%20hub%20page%20-%20based%20on%20your%20request%20I%20will%20try%20to%20do%20my%20best%20to%20prioritize%20that.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20stay%20tuned%20with%20my%20github%20page.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYochanan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2972090%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2972090%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F246476%22%20target%3D%22_blank%22%3E%40Yochanan_MSFT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20testing%20your%20script.%3C%2FP%3E%3CP%3EI%20have%20a%20question%20regarding%20the%20lines%26nbsp%3B%20below%3A%3C%2FP%3E%3CP%3E%26lt%3B%26lt%3B--------------------------------%3C%2FP%3E%3CP%3Eif%20%40debug!%3D'none'%3CBR%20%2F%3Ebegin%3CBR%20%2F%3Edrop%20table%20if%20exists%20%3CSTRONG%3EidxBefore%3C%2FSTRONG%3E%3CBR%20%2F%3Edrop%20table%20if%20exists%20%3CSTRONG%3EstatsBefore%3C%2FSTRONG%3E%3CBR%20%2F%3Edrop%20table%20if%20exists%20%3CSTRONG%3EcmdQueue%3C%2FSTRONG%3E%3CBR%20%2F%3Eif%20object_id('tempdb..%23idxBefore')%20is%20not%20null%20select%20*%20into%20%3CSTRONG%3EidxBefore%3C%2FSTRONG%3E%20from%20%23idxBefore%3CBR%20%2F%3Eif%20object_id('tempdb..%23statsBefore')%20is%20not%20null%20select%20*%20into%20%3CSTRONG%3EstatsBefore%3C%2FSTRONG%3E%20from%20%23statsBefore%3CBR%20%2F%3Eif%20object_id('tempdb..%3CSTRONG%3Edbo%3C%2FSTRONG%3E.AzureSQLMaintenanceCMDQueue')%20is%20not%20null%20select%20*%20into%20%3CSTRONG%3EcmdQueue%3C%2FSTRONG%3E%20from%20%3CSTRONG%3Edbo%3C%2FSTRONG%3E.AzureSQLMaintenanceCMDQueue%3CBR%20%2F%3Eend%3C%2FP%3E%3CP%3E--------------------------------%26gt%3B%26gt%3B%3C%2FP%3E%3CP%3EI%20see%20your%20script%20create%203%20new%20tables%20(%3CSTRONG%3EidxBefore%3C%2FSTRONG%3E%26nbsp%3B%2C%3CSTRONG%3EstatsBefore%3C%2FSTRONG%3E%26nbsp%3B%20%26amp%3B%26nbsp%3B%3CSTRONG%3EcmdQueue%3C%2FSTRONG%3E%20).%3C%2FP%3E%3CP%3Edo%20they%20phisically%20remain%20even%20after%20the%20execution%20of%20the%20SP%3A%26nbsp%3B%5Bdbo%5D.%5BAzureSQLMaintenance%5D%20%3F%3C%2FP%3E%3CP%3Ebtw%3A%20for%20table%20creation%20of%20AzureSQLMaintenanceCMDQueue%2C%20I%20noticed%20that%20schema%20has%20to%20be%20taken%20into%20consideration%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3Edbo%3C%2FSTRONG%3E.AzureSQLMaintenanceCMDQueue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20required%20to%20create%20these%203%20tables%20%3F%26nbsp%3B%20if%20so%2C%20i%20will%20need%20to%20ad%20dbo%20schema%20to%20each%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2974442%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20maintain%20Azure%20SQL%20Indexes%20and%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2974442%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1220653%22%20target%3D%22_blank%22%3E%40Dino3000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethis%20code%20path%20is%20only%20used%20when%20you%20set%20Debug%20to%201%3C%2FP%3E%0A%3CP%3Ethis%20is%20intended%20only%20for%20troubleshooting%20and%20development%20purposes%3C%2FP%3E%0A%3CP%3Ethose%20table%20will%20not%20be%20created%20on%20regular%20usage%3C%2FP%3E%0A%3CP%3EHTH%2C%20please%20reach%20out%20for%20any%20other%20questions%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Nov 09 2020 02:36 AM
Updated by: