%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%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%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%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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-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
Microsoft
First published on MSDN on Jul 03, 2016
[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.



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.

7 Comments
Occasional Visitor

You will need to use 

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

and

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

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

Occasional Visitor

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

 

Thanks.

Microsoft

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

 

Occasional Visitor

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

Azure_Compute_100.jpg

Microsoft

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. 

Occasional Visitor

Hi @Yochanan_MSFT ,
 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

Microsoft

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.