Is it mandatory to create index's for azure sql database object's and also how to handle performance issue on azure databases.

Hi Anandhswarupp

Greetings!

In addition to the response provided by @Olaf Helper, I would like to add the following information.

We would like to inform you that, creating indexes in Azure SQL Database is not mandatory, but it is highly recommended for performance optimization. Indexes help speed up query execution by allowing the database engine to locate data more efficiently. Azure SQL Database also provides an Automatic Index Advisor that can suggest and apply index optimizations based on workload patterns. Please click on this link for additional information. https://www.sqlshack.com/automatic-index-advisor-in-azure-sql-database/

If a primary key is not defined, the table will not have a clustered index by default, which can impact query performance. It is advisable to define a primary key or create appropriate indexes manually.

How to Handle Performance Issues in Azure SQL Databases

To improve performance in Azure SQL Database, consider the following strategies:

• Query Optimization – Identify and optimize slow queries using Query Performance Insights.
• Index Management – Regularly review and maintain indexes to prevent fragmentation.
• Resource Monitoring – Use Azure Monitor and Dynamic Management Views (DMVs) to track CPU, memory, and I/O usage.
• Automatic Tuning – Enable automatic tuning to allow Azure SQL Database to create or drop indexes dynamically.
• Scaling Resources – Adjust compute and storage resources based on workload demands.
• Database Advisor – Utilize Azure SQL Database Advisor for performance recommendations.
• Query Store – Enable Query Store to capture execution details and identify regressions.
• Query Refactoring – Rewrite inefficient queries, avoid unnecessary functions on indexed columns, and use JOINs instead of subqueries.

I would request you to refer the below mentioned links for more information.

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview?view=azuresql

https://www.cloudopsnow.in/azure-sql-database-query-tuning-and-system-performance-tuning-interview-questions/

https://reintech.io/blog/query-performance-tuning-azure-sql-databases

https://techcommunity.microsoft.com/blog/azuredbsupport/learnings-from-the-trenches---azure-sql-database-best-practice-performance-recom/1828214

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. If you still have questions, please let us know what is needed in the comments so the question can be answered.

Thank you for helping to improve Microsoft Q&A!

Your question is a bit vague.

It's not mandatory to create indexes, but having primary key = main index is best practice and indexes can solve performance issues.