question

sakuraime avatar image
0 Votes"
sakuraime asked sakuraime commented

Azure SQL : Performance recommendation

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 ..

100242-image.png


azure-sql-database
image.png (56.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @sakuraime To view and apply performance recommendations, you need the correct Azure role-based access control (Azure RBAC) permissions in Azure. Reader, SQL DB Contributor permissions are required to view recommendations, and Owner, SQL DB Contributor permissions are required to execute any actions; create or drop indexes and cancel index creation.
Please note: Azure SQL Database needs to monitor activities at least for a day in order to identify some recommendations. More info can be found here.
Please let us know if you have further query or need more assistance.
Regards,
Oury


0 Votes 0 ·

I wait for so many days already , the missing index recommendation still not come out .

I would like to know if there are any example , so that I can quickly trigger one , and show on the Performance recommendation blade?

0 Votes 0 ·

Hi @Sakuraime Can you please confirm your permissions in Azure .
Thanks
Oury

0 Votes 0 ·

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered sakuraime commented

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.






· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Sakuraime In addition to what Morillo has mentioned above, Azure SQL Database gives you full control over how recommendations are enabled using any of the following three options:
Apply individual recommendations one at a time.
Enable the Automatic tuning to automatically apply recommendations.
To implement a recommendation manually, run the recommended T-SQL script against your database.
This documentation explains this in more details. Please also make sure you have Reader, SQL DB Contributor permissions to view recommendations.

Regards,
Thanks


0 Votes 0 ·

Hi @Sakuraime Do you have further query regarding this .
Please let us know
Regards,
Oury

0 Votes 0 ·

so it's not just a missing index recommendation from the dm_db_missing_index_details or dm_db_missing_index_groups??

and finally , any example and workload which can simulate that . ?

0 Votes 0 ·

I was not able to find any synthetic workload to originate performance recommendations. I have requested myself that type of workload for demo purposes, and I received above answer. The artificial intelligence model shows recommendations under real life workloads.

0 Votes 0 ·

fI have tried , need around 9000 times to select a table with missting index .
then the recommendation will come out 1 day later(fast?) (for create index )

0 Votes 0 ·