Azure SQL : Performance recommendation

Question

Anyone know who to speed up the Performance recommendation to come out ?? I have an sql having cost of 5. and have missing index ( shown from actual execution plan). I run this query for around 10000 times , and the missing index recommendation still blank .. after few hours .

Any one has a demo for this Performance Recommendation , so that I can see something there more quickly ..

Answer 1

Performance recommendations use data from Query Store and management views, and combines that data with an internal model to determine the benefit of the index.

For index creation there is, presently, a rolling window of seven (7) days across which the data is tracked, and at a minimum the model needs nine (9) hours of data to recommend an index, along with 12 hours of data in Query Store that will be used as a baseline. If it’s determined that an index will provide significant benefit, then the database engine will recommend the index. The database needs to be heavily used for at least 9 hours, and synthetic workloads may not produce the expected recommendations. Making a realt-time demo of the business intelligence behind the automatic tuning feature and its recommendations has been impossible for me in the past because it may not work with synthetic workloads.

For suggestion related to dropping indexes you may need to wait up to 90 days. if an index has no seeks or scans for 90 days, but does have a maintenance cost (meaning there are inserts, updates, or deletes) then it will be suggested to be dropped. Duplicated indexes will be dropped.

Please make sure query store is working correctly. Make use of this official article to enable query store and query it to know wheter is working or not. Please run the following query to make sure Query Store is working:

SELECT * FROM sys.query_store_query_text

It should return a good number of rows.