Freigeben über

How to maintain Azure SQL Indexes and Statistics

  • Artikel

There are a lot of work that Azure SQL saves from you, and most of the users we meet seems 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 update for at least 20% + 500 rows in the table. if the condition is met, the update will occur when the optimizer need 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 need to be done by the DBA, 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 (V12)

Now, all you have to do is download the T-SQL script, execute it on your Azure SQL DB and execute the maintenance procedure.

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.

 

Procedure Code:

AzureSQLMaintenance

 

 

Scheduling and automation:

one of the easiest way to implement automation for this maintenance is to use Azure Functions that is now on GA

Here is example about how to do that: functions scenario database table cleanup

 

! updated: 2016-10-27 - fixed performance issue while updating statistics.

! updated: 2016-11-14 - fixed issue with double rebuild of indexes. (thank you JPelttari for the comment)

! updated: 2016-11-29 - adding information about scheduling and automation.

Comments

  • Anonymous
    November 14, 2016
    If there is multiple fragmented indexes in table, this script rebuilds indexes on that table multiple times as it rebuilds them with ALL identifier
    • Anonymous
      November 14, 2016
      Thank you JPelttari for the comment. you absolutely right. It's now fixed.
  • Anonymous
    November 19, 2016
    Very helpful script, thank you. I somehow thought that index fragmentation is no big issue on Azure SQL, maybe because maintenance functionality is absent. But running this script actually made a big difference for a clustered index of a table where 80% of the records were deleted before.
    • Anonymous
      November 19, 2016
      Thank you for the feedback :)
  • Anonymous
    December 22, 2016
    Thank you, it is really helpfull, works also on an on premise server (at least SQL 2012)I suggest you add an option to check constraint to retrust foreign keys and constraints, it will be usefull after bcp upload of data
    • Anonymous
      December 22, 2016
      Thank you for the feedback. about constraints and foreign keys this is a different subject that is not handled by regular maintenance task as you first need to disable them before you run the bcp load, and this probably will be more selective on the database.if you meant to something else - please reply on this comment.
  • Anonymous
    February 22, 2017
    like
  • Anonymous
    March 11, 2017
    Thank you, Yochanan!You saved my day. Suddenly SQL performance degraded to expreme. Index maintenance helped alot!