Indexes are Crucial on Azure SQL Database!

January 26, 2014 — 2 Comments

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.

2 responses to Indexes are Crucial on Azure SQL Database!

  1. 
    Ashish Narnoli April 16, 2015 at 1:28 AM

    Truly amazing and it helped me a lot!!!

    Like

Trackbacks and Pingbacks:

  1. Reading Notes 2014-02-03 | Matricis - February 3, 2014

    […] Indexes are Crucial on Windows Azure SQL Database! – This post explains very clearly how we can (and should) plan our Azure database to get better performance. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s