Indexing in Azure SQL vs. regular SQL Server

Question

Indexing in Azure SQL vs. regular SQL Server

King Java 790

I am trying to come up with a plan to add indexes on Azure SQL database tables.

Is there any difference in terms of adding indexes for Azure SQL database tables vs. regular SQL Server database tables?

What are some of most commonly way of adding indexes for Azure SQL database tables?

Thanks.

Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-15T23:36:25.7933333+00:00
Hi King Java,

Thank you for your question regarding indexing in Azure SQL versus regular SQL Server. I'd be happy to provide some insights.

Differences in Indexing:

Scalability and Performance: Azure SQL Database is designed to handle cloud-specific workloads, which may involve different performance optimizations compared to on-premises SQL Server. However, the fundamental principles of indexing remain the same.

Automatic Index Management: Azure SQL Database offers automatic tuning features, such as automatic index management, which can automatically create and drop indexes based on query performance. This feature is not available in on-premises SQL Server.

Resource Constraints: Azure SQL Database operates within certain resource constraints (e.g., DTUs or vCores), which might influence your indexing strategy to ensure optimal performance within those limits.

Common Ways to Add Indexes in Azure SQL Database:

Clustered Indexes: These are indexes where the data rows are stored in order based on the index key. They are useful for improving the performance of queries that return a range of values.

Nonclustered Indexes: These indexes have a structure separate from the data rows and include pointers to the data. They are beneficial for queries that search for specific values.

Filtered Indexes: These are nonclustered indexes that include a WHERE clause to index a subset of rows. They are useful for improving query performance on well-defined subsets of data.

Columnstore Indexes: These are designed for large-scale data warehousing and analytics workloads. They store data in a columnar format, which can significantly improve query performance for large datasets.

Here is an example of how to create a nonclustered index in Azure SQL Database:Thank you for your question regarding indexing in Azure SQL versus regular SQL Server. I'd be happy to provide some insights.

Differences in Indexing:

Scalability and Performance: Azure SQL Database is designed to handle cloud-specific workloads, which may involve different performance optimizations compared to on-premises SQL Server. However, the fundamental principles of indexing remain the same.

Automatic Index Management: Azure SQL Database offers automatic tuning features, such as automatic index management, which can automatically create and drop indexes based on query performance. This feature is not available in on-premises SQL Server.

Resource Constraints: Azure SQL Database operates within certain resource constraints (e.g., DTUs or vCores), which might influence your indexing strategy to ensure optimal performance within those limits.

Common Ways to Add Indexes in Azure SQL Database:

Clustered Indexes: These are indexes where the data rows are stored in order based on the index key. They are useful for improving the performance of queries that return a range of values.

Nonclustered Indexes: These indexes have a structure separate from the data rows and include pointers to the data. They are beneficial for queries that search for specific values.

Filtered Indexes: These are nonclustered indexes that include a WHERE clause to index a subset of rows. They are useful for improving query performance on well-defined subsets of data.

Columnstore Indexes: These are designed for large-scale data warehousing and analytics workloads. They store data in a columnar format, which can significantly improve query performance for large datasets.

Using Azure Portal: You can also create and manage indexes through the Azure portal, where you can visualize your database structure and properties, making it easier to manage performance tuning.

Here is an example of how to create a nonclustered index in Azure SQL Database:

CREATE INDEX IX_YourIndexName ON YourTableName (YourColumnName);

I hope this information helps. Please do let us know if you have any further queries.

If the comment is helpful, please click "Upvote it".
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-05-16T16:18:58.98+00:00

Since Manasa Akula's answer may give the impression of being AI-generated, I like as a human to butt in to confirm that what Manasa is saying is correct. There are not any differences as such between creating indexes in Azure SQL Database and on-prem SQL Server. After all, it is the same engine. But as Manasa points out, the automatic indexing could add extra indexes. It is also possible that a heavy CREATE INDEX statement on a database with feeble capabilities could lead to some throttling.

In practice, I would not care about any of these, but add the index I think are needed.

Accepted answer

0 additional answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-05-16T16:18:58.98+00:00

Since Manasa Akula's answer may give the impression of being AI-generated, I like as a human to butt in to confirm that what Manasa is saying is correct. There are not any differences as such between creating indexes in Azure SQL Database and on-prem SQL Server. After all, it is the same engine. But as Manasa points out, the automatic indexing could add extra indexes. It is also possible that a heavy CREATE INDEX statement on a database with feeble capabilities could lead to some throttling.

In practice, I would not care about any of these, but add the index I think are needed.

Answer 1

Oury Ba-MSFT 20,911 Microsoft Employee Moderator

@King Java Thank you for reaching out.

The indexing architecture between Azure SQL Database and SQL Server is pretty much the same as Erland Sommarskog mentioned above. In addition to that,

Automatic plan correction identifies problematic query execution plans, such as a parameter sensitivity or parameter sniffing issues, and fixes query execution plan-related performance problems by forcing the last known good plan before the regression occurred. Applies to: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database, and SQL database in Microsoft Fabric, and Azure SQL Managed Instance
Automatic index management identifies indexes that should be added in your database, and indexes that should be removed. Applies to: Azure SQL Database and SQL database in Microsoft Fabric.

Azure SQL provides automatic tuning (Automatic plan correction) if the below options are turn on from automatic tuning tab. SQL on premises does support automatic tuning**,** which was introduced in SQL Server 2017, but with some limitations compared to Azure SQL Database. Automatic tuning it is a built-in intelligence that you can leverage to pretty much optimize and tune your SQL DB. Read more Automatic tuning.

I you go to the Query performance insight; you can see queries that contribute to CPU.

User's image

Is there any difference in terms of adding indexes for Azure SQL database tables vs. regular SQL Server database tables?

Please do check this guide for more details SQL Server and Azure SQL index architecture and design guide. It applies to Azure SQL Database and SQL Server.

See an example on how to create an Index. CREATE INDEX (Transact-SQL)

User's image Please do let us know if you have any additional questions.

Optimizing Your SQL Database Workloads w/ Automatic Tuning on Azure SQL | Data Exposed: MVP Edition

Azure SQL DB Performance monitoring and troubleshooting

I hope this clarifies your ask.

Regards,

Oury

Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2025-05-20T15:55:02.07+00:00

@King Java Please don't forget to mark as accept answer if the reply was helpful.

Regards,

Oury
King Java 790 Reputation points

2025-05-20T23:37:47.1633333+00:00

@Oury Ba-MSFT How do I go about doing this?

What is best practice?
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2025-05-21T18:01:41.57+00:00
Hi King Java, thank you for accepting the answer.

In your Azure SQL Database instance.

In the left-hand menu, select "Automatic tuning".

You’ll see three main tuning options as you showed above in the screen shot:

FORCE_LAST_GOOD_PLAN

CREATE_INDEX

DROP_INDEX

For each option, you can:

Enable it manually.

Inherit settings from the parent server (recommended).

Disable it.

After selecting your desired configuration, click "Apply" to save the changes.

Best Practices

Manage at the Server Level:

Monitor Regularly: Even with automatic tuning enabled, monitor performance and query plans to ensure the changes are beneficial.

Please take a look at the below doc. It contains more detailed

information about automatic tuning and best practices.

https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable?view=azuresql

Hope these answers helped.

Regards,

Oury
King Java 790 Reputation points

2025-05-21T19:05:02.75+00:00

@Oury Ba-MSFT

Thank you for your continuous help.

So, is this the best option (bottom screenshot)?

Also, it is better to inherit from "Server" or "Azure defaults"?

Also, I am little bit confused because if it is "automatic", what do we have to do manually?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-05-21T21:06:06.87+00:00
I see no issues with enable FORCE_PLAN. It will detect a query that suddenly starts perform worse (which can happen for instance because statistics changes), and automatically force an old plan. This can reduce the damage of such a regression to almost nothing.

I'm more hesitant with the other two. There are two problems:

What if Azure decides to create an index while your workload is at peak. The index creation will take resources and can cause blocking.

Automatic indexing often results in redundant and overlapping indexing. Manual indexing where you have understanding of the database and the business can do a lot better job. But, that should not be denied, you need to have the skills.

The essence of the note that Oury quoted is that you should first configure on server level, and then you should inherit from the server defaults.
King Java 790 Reputation points

2025-05-21T21:34:51.83+00:00

@Erland Sommarskog Thank you for your feedback, but I am still confused.

What are best options?

Inherit from "Server" (not "Azure defaults"), and then what would be your choice for "FORCE PLAN" and rest twos?

Thanks.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-05-21T21:39:37.99+00:00

First to inherit from server, you first need to configure the server. Once you have done that, it is better to inherit from the server.

For the server, I would enable FORCE_PLAN, but not the other two, if it was my server. I can't say what is best for you, but included some discussion in my previous post.
King Java 790 Reputation points

2025-05-21T21:48:05.2566667+00:00

@Erland Sommarskog I am little bit confused what you meant by "enable" FORCE_PLAN. Are you referring to "On" on the "Desired state"?

Is bottom what you are referring to (if it was your server)?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-05-21T21:49:27.31+00:00

ON usually means enabling, yes. :--)
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2025-05-23T19:24:08.6166667+00:00

Thank you Erland Sommarskog for your insights.

Share via

Indexing in Azure SQL vs. regular SQL Server

0 additional answers

Your answer