Azure SQL Database - Index And Statistic Management

- 6 minutes read - 1268 words

DBA Tasks in Azure DB

It was a big surprise to me when I learned that Azure SQL Database doesn’t update statistics or indexes automatically. The option to automatically update statistics exists (e.g., ALTER DATABASE [WideWorldImporters] SET AUTO_UPDATE_STATISTICS ON), and is on by default, but it does not give the same control as updating them manually with the ‘UPDATE STATISTICS’ command. When Azure DB was first pitched to us, on an internal project, one of the big selling points was “no maintenance required”. This wasn’t a statement from Microsoft or even from the documentation. It was clearly a marketing talking-point that leaked into the technical discussions. It’s partially true. Some things are automatic, but there are a few things you still need to maintain or at least monitor yourself. The big shocker, as you already know from the first sentence above, is that indexes aren’t automatically rebuilt for you and statistics aren’t maintained beyond the database configuration item.

Just like your on-prem SQL Server databases.

The problem with this isn’t that they need to be maintained, the problem is that it’s not straight forward to automate this maintenance. When I learned this, I confirmed it with our Microsoft contact and got some input on methods to perform this maintenance. I have that advice below, along with some of my own thoughts.

It’s possible this will be addressed more fully in a future iteration of Azure DB. But you need to perform this maintenance yourself for now.

Examples

Teams moving to Azure DB from an on-prem instance likely already have scripts to maintain indexes and statistics. The basic statements are simple, and look something like the following.

Note: The examples are single tables. Your production scripts will update multiple tables in your database, likely all of them.

These examples also include a BEGIN TRY - END TRY block to catch errors. It isn’t necessary, but I use it in my scripts so I can see which tables fail to update and why. They also allow the remainder of the script to continue running in the event of an error, such as a permission issue.

Index Rebuild

The following is a very simple example of rebuilding all the indexes on the table Sales.Invoices. You will probably use additional settings in your environment, so check the documentation.

BEGIN TRY
	ALTER INDEX ALL ON [Sales].[Invoices] REBUILD
	WITH (
		SORT_IN_TEMPDB = ON
		)
	PRINT 'Sales.Invoices'
END TRY
BEGIN CATCH
	PRINT 'Sales.Invoices'
END CATCH

Statistics Update

This is a simple example of updating all statistics on the same table shown above, Sales.Invoices. As with the index example, check the documentation and use the options that make sense for your database. The FULLSCAN option is more thorough and accurate, but it takes longer. The SAMPLE and RESAMPLE options are faster but may not reflect large changes in the distribution of the data.

BEGIN TRY
	UPDATE STATISTICS [Sales].[Invoices] WITH FULLSCAN, ALL;
	PRINT 'Statistics updated on: Sales.Invoices'
END TRY
BEGIN CATCH
	PRINT 'Error updating statistics: Sales.Invoices'
END CATCH

Options for Maintenance in Azure DB

  1. Manual Updates
    You can always just address issues when you have a problem. I’m being somewhat facetious with this option, but it’s what I see most teams doing. It’s not the best option, but it works until it doesn’t. Once the statistics become skewed to a degree that it impacts query plans, it impacts performance. Teams often do emergency maintenance at this point and then ask how they can prevent it in the future.
    I see two primary reasons for the manual update strategy in Azure DB.
    • Teams assume this is done automatically by Azure DB. That was my assumption and the assumption of everyone on the various teams I worked with. Now you know too.
    • Small teams may not even be aware of that indexes and statistics need to be maintained. Developers new to SQL may not know anything about this. It is something that was previously done by their DBA team or it was managed via the maintenance plan or they are primarily front-end developers and want the database to just work. The newest versions of SQL Server are great, but they don’t fix everything. And moving to Azure doesn’t fix everything either.
  2. External Scheduling Tool
    Most larger enterprises have an internal scheduling tool to automate jobs. There are a number of commercial and free tools that do this and some places create their own tool. If you already use an enterprise scheduling tool that can run SQL scripts against Azure DB, it’s a good choice. There is no reason to bring another tool into the enterprise, especially if it is established and a team is monitoring the status of jobs. It would be overkill to do this just for Azure DB, but that’s not the nature of enterprise tools - they are meant to be used for as many projects as possible for consistency. If you don’t already have one, don’t start here. If a scheduling tool already exists in your enterprise, you might be forced to use it anyway.
  3. ETL
    Existing ETL tools are an obvious choice for scheduling maintenance jobs. It’s not the normal method a DBA team would use, but it might fit well with a development team. This choice works for teams without an external scheduling tool, especially if the ETL tool is already in use. ADF is the obvious choice, but any ETL tool that can access Azure DB would work. Just be sure it includes scheduling.
  4. Elastic Jobs
    Elastic jobs are similar to SQL Agent jobs. They require more configuration, but it’s a DBA-centric tool. They are billed in the same way as Azure DB, include scheduling, and can target multiple servers. If you work as a DBA, this would be my choice if I was trying to get everything into Azure.
  5. Others
    • An on-prem SQL Server running SQL Agent can be used. You may need to deal with firewall rules, but that’s no different than configuring them for an enterprise scheduling tool. This isn’t a likely option, since you are moving things to Azure, but it would work. If you do this, you are likely adding tech debt, but it could cover the scheduling gap until another, permanent, solution is configured.
    • Creating your own scheduler is possible. I wouldn’t recommend this option, but as with on-prem servers, there is nothing that prohibits this from working. Some developers like to build everything themselves. I’m strongly in the camp of using existing tools that are maintained, tested, and updated regularly instead of rolling your own.
    • Azure Functions fall into the category of creating your own, but I think it deserves a call-out since I have seen this in use. It’s relatively easy to implement and lightweight. It’s a better option than a full application and it puts everything into Azure.

Summary

Azure DB requires some intervention to run properly. Indexes and statistics need to be maintained, just as you would with an on-prem server. Don’t assume that Azure is doing all the magic for you.
The choice of a scheduling tool is important and will impact your functionality and team productivity. If a scheduling tool already exists in the enterprise, it’s a good solution. If you need to add a new scheduler, consider Azure Elastic Jobs. It is the natural successor to SQL Agent and falls squarely into the realm of “DBA stuff”. If you don’t want each development team managing their databases to this level, use a scheduling tool.

The choice depends on your needs and team configuration.

References

Alter / Rebuild Index
Update Statistics
Elastic Jobs
SQL Server Agent
Azure Functions

comments powered by Disqus