Indexes are Crucial on Azure SQL Database!

Azure is really good to developer, it handles lots of configurations, maintenance and disaster recovery work for us. Unfortunately, we as developers seem to forget that it doesn’t do everything for us.

This should come as no surprise for DBAs, having a sound initial index strategy is crucial. On-premise, developers can get away with it for a long time. SQL Server hosted on in-house hardware is usually quite forgiving. It’s literally a workhorse that keeps on serving requests. It can take quite some time before you reach the critical load necessary to notice performance issues.

We also have to keep in mind that SQL Server is usually hosted (physically) close to our application servers. Furthermore, we also assume that the connection is never going to falter, that latency will be constant and almost non-existent. Normally, SQL Server is also hosted on high end servers built for punishment. Consequently, it compensates for much of our inefficient code.

Azure SQL Database is a different beast. Due to its nature and design, it enforces best practices and punishes those who don’t follow them. In other words, it doesn’t forgive!

One of the issues that seem to come up time and time again, is the lack of a basic index strategy. Many Azure SQL Databases fail under load because they lack indexes. They also fail because our queries are far too demanding for the shared nature of the service. Implementing a sound index strategy can help it cope with bad queries. It can also buy you some time so that you can evaluate ways to optimize your Azure SQL Database.

Creating indexes has two really amazing benefits. The first, is that queries will benefit from them without the need to alter existing Stored Procedures. The second, is that you can deploy indexes to your production database without altering code or requiring a full application deployment. Consequently, in most cases, creating indexes to help the overall performance of your Azure solution, will be the lowest hanging fruit. In other words, it will be the cheapest and most effective optimization that you can make.

By now, the question that you may be asking yourself is probably "how can I prevent these kinds of performance issues from happening?” The answer is actually not that complicated. But is requires a little extra attention to details during the initial development effort of your Azure solution.

The following steps help me prepare a database for an initial deployment to Azure SQL Database.

• Create Clustered Indexes on the tables’ Natural Keys. Natural Keys, are the fields that best identify the row’s data. For example, the Primary Key used for a table might be a Unique Identifier or a Big Int, but I might access data through a combination of columns. For a car these columns could be Year, Make, Model and VIN. When you create the Clustered Index, order the columns by their selectivity and use this same order when you query for the information.
• Create Non-Clustered Indexes on your Primary Keys
• Create Non-Clustered Indexes for all Foreign Keys
• Create Non-Clustered Indexes for columns that are used in WHERE, ORDER BY, MERGE, JOIN and other clauses that require matching data.

Trying to predict the need for other indexes might end up costing you unnecessary amounts of money. Let’s reflect on that statement for a second. Indexes can eat up a lot of space in your database. In some cases, it can account for more than 50% of the database size. Therefore, you really need to think about your indexes and reduce overlap to a minimum. One way to approach this, is to create narrow indexes. This is especially true for Clustered Indexes, as their keys will be copied to all your Non-Clustered Indexes and will be used as their Row Keys.

The best way to optimize a database for Azure SQL Database, is to have a good initial index strategy and to observe how it’s actually being used in production. As I mention in my post about steps towards optimized Azure SQL Databases, it’s important to regularly poll the database for missing indexes and for its health.

Once in production, I strongly recommend monitoring the index fragmentation and to rebuild them regularly. This will keep the overall performance closer to being predictable.

This bring up another important fact about Azure SQL Database. The service is multi-tenant, therefore its performance will vary. If you desire more predictability, I strongly suggest that you take a look at the Azure SQL Database Premium offer. This flavor of Azure SQL Database is hosted on dedicated hardware in order to provide you with more power.

Transitioning to the Premium flavor on Azure SQL Database will get strong reactions from your accountants, so be sure to do it for the right reasons. Moving to this flavor will not fix performance issues related to a bad indexing strategy. It will only push your performance issues to later.

To wrap this up, I’d like to put a bit of emphasis on what you should take away from this post.

• Take time to create a good initial index strategy for you database.
• Scale up using the Azure Premium SQL Database offer only when it’s really necessary.
• Continuously monitor the database’s performance and act on your findings before your customers notice performance degradation.
• Azure will take care of most of the administration around Azure SQL Database, but it’s your responsibility to take care of your schema and your data.