2

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.

4

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.