Statistics update on Azure SQL DB

Question

New to Azure SQL DB. I was wondering what is the best practices for scheduling jobs like update statistics in Azure SQL DB. Googling got a couple of blogs that uses Azure automation.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/01/15/automating-azure-sql-db-index-and-statistic-maintenance-using-azure-automation/

Is that the best way to do this? Just looking for some suggestions on the pros and cons of this approach. I will be testing this soon.

Answer 1

We have been using the approach in your posted link since first reading about it in Sept 2017. Here is a link with comments from MS SQL Architect Conor Cunningham about the need to re-index in Azure SQL Database Link to Conor Cunningham comments.

Answer 2

The article is really good providing scripts and a way of automating maintenance but index and statistics maintenance is an I/O intensive workload. You may consider to scale to premium tiers prior to run index and statistics maintenance tasks, and when the workload finishes scale down to previous tier. You can automate that using T-SQL. That should reduce maintenance time.

Additionally, compacting large objects data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) can save disk space but it can increase the time it takes to maintain indexes. If you have a restricted time window for reindexing you can set LOB_COMPACTION to OFF.