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.
Microsoft
