what query(s) will benefit from azure sql server performance recommendations

Question

what query(s) will benefit from azure sql server performance recommendations

Graham Dudley 0

So I have a batch of CREATE INDEX performance recommendations on an Azure SQL Server database and now I am thinking what troubled query(s) will these indexes address? I don't need to be tuning workloads that are batch and/or overnight jobs, but of course would like to tune application queries to keep clients happy.

Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-12T19:57:14.09+00:00

Hi @Graham Dudley

As per our understanding that you're looking to understand which specific queries might benefit from the performance recommendations tied to your CREATE INDEX suggestions.

When you create indexes based on the performance recommendations provided by Azure SQL Database, the primary goal is to optimize queries that frequently access the columns involved in those indexes. Here are a few steps you can take to identify those queries:

Query Performance Insights: Utilize the Query Performance Insight tool to pinpoint which queries are consuming the most resources. This will give you an idea of which queries could benefit from the performance improvements your new indexes will provide.

Identify High-Impact Queries: Start by looking at the queries that are frequently executed by your application and have a significant impact on performance. These are typically the queries that your clients interact with regularly and are critical to their experience.

Query Store: The Query Store feature can help you analyze query performance over time. It collects detailed execution statistics and will enable you to identify any changes in performance after applying your indexing recommendations.

Monitor Wait Statistics: Look into the wait statistics for your database. Queries that show high wait times might indicate that they could improve significantly with the right indexing strategies.

Analyze Execution Plans: Use tools like Query Store or SQL Server Profiler to analyze the performance of your queries. Look for queries with high execution times, high CPU usage, or those that cause significant I/O operations. These metrics will help you pinpoint the queries that are causing performance bottlenecks. Examine execution plans for your most resource-intensive queries. If you see scans rather than seeks on your relevant columns, this could be a strong indication that indexing would be beneficial.

Evaluate Application Queries: Keep an eye on the application queries that users typically run. Focus on optimizing those as they directly impact user experience.

Review Execution Plans: Examine the execution plans of the identified queries. Execution plans can provide insights into how SQL Server is processing the queries and where the performance issues lie. Look for operations like table scans or index scans that could be optimized with the recommended indexes.

Match Index Recommendations: Compare the CREATE INDEX recommendations with the execution plans of the troubled queries. The recommended indexes are typically suggested to optimize specific query patterns. By matching these recommendations with the execution plans, you can determine which queries will benefit from the new indexes.

Prioritize Application Queries: Since you want to focus on tuning application queries rather than batch or overnight jobs, prioritize the queries that are part of your application's regular operations. These are the queries that will have the most immediate impact on client satisfaction.

By following these steps, you can ensure that the CREATE INDEX recommendations are applied to the queries that will provide the most benefit to your application's performance and keep your clients happy. If you need further assistance with specific queries or recommendations, feel free to ask!

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".
Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-13T17:06:42.08+00:00

Hi @Graham Dudley

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-14T16:35:43.8166667+00:00

Hi @Graham Dudley

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Graham Dudley 0 Reputation points

2025-05-14T19:41:36.2366667+00:00

Thanks for the reply.

RE: Query Performance Insights:

Yes, so I use that to pinpoint a poor performing query, which CREATE INDEX performance recommendations provided will help it perform better?

RE: Identify High-Impact Queries:

Yes, so I look at the frequently executed queries, again which CREATE INDEX performance recommendations provided will help them perform better? Which recommendation is for which query(s)?

RE: Evaluate Application Queries:

Again, which performance recommendations will benefit these application queries?

RE: Monitor Wait Statistics:

Again, which performance recommendations will benefit these high wait time queries?

RE: Review Execution Plans: and Match Index Recommendations:

So I identify a number of queries utilizing the methods above, now I have to review all the execution plans to see if any of the performance recommendations will help? Why would not the performance recommendations identify them rather then me having to try to match up execution plans with performance recommendations?

RE: Prioritize Application Queries:

This is where matching the queries to performance recommendations, which I now have to do manually. Say I am trying to solve quickly a nasty query from last week, which performance recommendations will tame it? Should I not be able to see what, if any, performance recommendations exists for it?
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2025-05-14T20:19:42.3233333+00:00

As you may have guessed, Manasa Akula gave you a standard summary of how to do performance tuning, possibly after some conversation with Chat GPT or similar (which would be in violation of the AI Usage Policy for Microsoft Q&A).

I don't know the exact details of the Azure recommendations, but at least in the past, Microsoft's recommendation tools has relied to quite some extent on the sys.dm_missing_indexes DMVs. These DMVs are updated by the optimzer when it works with queries and starts thinking "I wish there was an index for...". But it does not track the queries. It is possible figure this out by connecting the DMVs to the plan cache, I know that some people have written queries for this, but I don't have any queries to offer myself.

So this could be part of the answer that you don't get the queries that would benefit from the recommendations. Another reason could be that there are umpteen of them. You may not have umpteen queries overall in your database, but other people have.

So the answer is that if you want to know which queries that benefit from the indexes, you will need to dig quite a bit. And when you do that you will make so much findings that you are able to identify useful indexes on your own. And since you know your system better than Azure does, you also know which queries you should focus on.

Alberto makes a good point when he says that you should ignore these recommendations. These recommendations tend to be overlapping with existing indexes, not the least if you have applied a round of recommendations already.

If you decide to apply these indexes anyway, it is worth checking out the Query Store reports in SSMS for the database, and particularly the node Regressed Queries. Because, while an index is intended to improve performance, it can sometimes backfire and make queries to slower. (Because the optimizer thought the new index was a good idea when it was not.)
Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-14T20:27:59.8+00:00
Hi @Graham Dudley

Thank you for your detailed follow-up questions. I understand that you are looking for a more direct way to match specific CREATE INDEX performance recommendations to the queries they will benefit. Here are some clarifications and steps to help you achieve this:

Query Performance Insights

To pinpoint which CREATE INDEX recommendations will help a specific poor-performing query, you can.

Identify the Query: Use Query Performance Insights to identify the query that is consuming the most resources.

Check Recommendations: Look at the performance recommendations provided by Azure SQL Database. Each recommendation typically includes details about the columns and tables involved.

Match Columns and Tables: Compare the columns and tables in the recommendation with those used in the identified query. If they match, the recommendation is likely to benefit that query.

Identify High-Impact Queries

For frequently executed queries:

List High-Impact Queries: Identify the high-impact queries from your application.

Review Recommendations: Check the CREATE INDEX recommendations and see which ones involve the same columns and tables as your high-impact queries.

Apply Recommendations: Implement the recommendations that match to improve the performance of these queries.

Evaluate Application Queries

To determine which performance recommendations will benefit your application queries:

Analyze Query Patterns: Look at the patterns in your application queries.

Compare with Recommendations: Match these patterns with the columns and tables mentioned in the CREATE INDEX recommendations.

Implement Relevant Recommendations: Apply the recommendations that align with your application query patterns.

Monitor Wait Statistics

For high wait time queries:

Identify High Wait Time Queries: Use wait statistics to identify queries with high wait times.

Match with Recommendations: Compare these queries with the CREATE INDEX recommendations to see if the columns and tables match.

Apply Recommendations: Implement the recommendations that match to reduce wait times.

Review Execution Plans and Match Index Recommendations

To streamline the process of matching execution plans with performance recommendations:

Use Query Store: Query Store can help you track the performance of queries over time and see the impact of applied recommendations.

Automated Tools: Consider using automated tools or scripts that can help match execution plans with performance recommendations, reducing the manual effort required.

Prioritize Application Queries

For quickly solving a specific query from last week:

Identify the Query: Pinpoint the query that caused issues.

Check Recommendations: Look at the CREATE INDEX recommendations and see if any of them involve the same columns and tables as the problematic query.

Apply Relevant Recommendations: Implement the recommendations that match to improve the performance of the query.

By following these steps, you can more effectively match CREATE INDEX recommendations to the queries that will benefit from them.
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".
Graham Dudley 0 Reputation points

2025-05-15T15:48:45.5666667+00:00

Thanks Erland, I guess my hopes for a vendor with internal product knowledge to create a useful recommendation have been dashed. Thanks for the additional suggestions I will research to build tools to identify the query to the missing index relationship. And yes there are umpteen queries which is why I want to target the problem children.

I was suspicious about the performance recommendations and you and Alberto have confirmed that I should be. So I won't even consider Auto tuning (Auto guessing?, Auto hoping?)
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2025-05-15T20:50:11.5633333+00:00

Those kind of questions do not always have a straightforward answer. To be honest, I have no direct experience of the automatic indexes in Azure SQL Database, but I've seen previous iterations with their Database Engine Tuning Advisor and not been impressed. Then again, people have often used that tool in the wrong way.

Nevertheless, I'm inclined to think that you get a better result if you hire a good consultant for performance tuning, or you become a good performance tuner yourself. But is not exactly cheap - the consultant's hourly rate is likely to be above the normal for a plain developer. And learning yourself - it takes time and effort.

On the other hand, the Azure index recommendations come with the service at no extra cost, and they may be good enough. How do you know if they are good enough? Well, what do you think of your performance today? What do think after a week with the new indexes? While subjective impressions are not to be ignored, it can be better if you can identify some objective pieces of data, for instance response time of important operations.

1 answer

Your answer

Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-13T17:06:42.08+00:00

Hi @Graham Dudley

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Manasa Akula 680 Reputation points Microsoft External Staff Moderator

2025-05-14T16:35:43.8166667+00:00

Hi @Graham Dudley

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Graham Dudley 0 Reputation points

2025-05-14T19:41:36.2366667+00:00

Thanks for the reply.

RE: Query Performance Insights:

Yes, so I use that to pinpoint a poor performing query, which CREATE INDEX performance recommendations provided will help it perform better?

RE: Identify High-Impact Queries:

Yes, so I look at the frequently executed queries, again which CREATE INDEX performance recommendations provided will help them perform better? Which recommendation is for which query(s)?

RE: Evaluate Application Queries:

Again, which performance recommendations will benefit these application queries?

RE: Monitor Wait Statistics:

Again, which performance recommendations will benefit these high wait time queries?

RE: Review Execution Plans: and Match Index Recommendations:

So I identify a number of queries utilizing the methods above, now I have to review all the execution plans to see if any of the performance recommendations will help? Why would not the performance recommendations identify them rather then me having to try to match up execution plans with performance recommendations?

RE: Prioritize Application Queries:

This is where matching the queries to performance recommendations, which I now have to do manually. Say I am trying to solve quickly a nasty query from last week, which performance recommendations will tame it? Should I not be able to see what, if any, performance recommendations exists for it?
Graham Dudley 0 Reputation points

2025-05-15T15:48:45.5666667+00:00

Thanks Erland, I guess my hopes for a vendor with internal product knowledge to create a useful recommendation have been dashed. Thanks for the additional suggestions I will research to build tools to identify the query to the missing index relationship. And yes there are umpteen queries which is why I want to target the problem children.

I was suspicious about the performance recommendations and you and Alberto have confirmed that I should be. So I won't even consider Auto tuning (Auto guessing?, Auto hoping?)
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2025-05-15T20:50:11.5633333+00:00

Those kind of questions do not always have a straightforward answer. To be honest, I have no direct experience of the automatic indexes in Azure SQL Database, but I've seen previous iterations with their Database Engine Tuning Advisor and not been impressed. Then again, people have often used that tool in the wrong way.

Nevertheless, I'm inclined to think that you get a better result if you hire a good consultant for performance tuning, or you become a good performance tuner yourself. But is not exactly cheap - the consultant's hourly rate is likely to be above the normal for a plain developer. And learning yourself - it takes time and effort.

On the other hand, the Azure index recommendations come with the service at no extra cost, and they may be good enough. How do you know if they are good enough? Well, what do you think of your performance today? What do think after a week with the new indexes? While subjective impressions are not to be ignored, it can be better if you can identify some objective pieces of data, for instance response time of important operations.

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

My suggestion is to avoid recommendations made by Azure and search for query plans in cache that require indexes. Create indexes based on that and the usage count of those queries requesting indexes.

Usually, Azure index recommendations lead to a proliferation of indexes and some of those indexes differentiate in one or two columns only and that proliferation will have an impact on INSERT/UPDATE/DELETE operations. Try to match those index recommendations to the query plans requesting indexes on the cache, and before creating them try to consolidate the new indexes with existing indexes on the database. Create indexes needed for queries used for your applications and not requested by synthetic workloads and adhoc queries.

Graham Dudley 0 Reputation points

2025-05-14T19:51:16.0366667+00:00

Thanks Alberto, was really hoping that Azure's performance recommendations would do the work here: "Hey this query would benefit from this index" which I could then evaluate along with the existing indexes.
Oury Ba-MSFT 20,911 Reputation points Microsoft Employee Moderator

2025-05-23T17:25:13.71+00:00

Thank you @Alberto Morillo and @Erland Sommarskog for your inputs.

Graham Dudley

Use the query performance insight in the Azure portal to check queries with high-CPU and long-duration queries. Cross-reference the columns used in those queries with the columns targeted by your index recommendations. If you have a query that generates a lot of CPU, I would also check the Query Store to look at performance over time.

Use sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats to see which queries are triggering missing index warnings. These views often align with Azure’s recommendations.Regards,

Regards,Oury

Share via

what query(s) will benefit from azure sql server performance recommendations

1 answer

Your answer