Azure Reindex Let it delete and add

[Ernst]

What dos JIWA and others suggest. Moving a JIWA7 customer to Azure.
Azure has these setting.(See below) Should we switch them on. OR keep the existing JIWA index and run the recommended Alter Index Rebuild command

Automatic tuning option Description Single database and pooled database support Instance database support
CREATE INDEX Identifies indexes that might improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved. When recommending a new index, the system considers space available in the database. If index addition is estimated to increase space utilization to over 90% toward maximum data size, index recommendation isn't generated. Once the system identifies a period of low utilization and starts to create an index, it will not pause or cancel this operation even if resource utilization unexpectedly increases. If index creation fails, it will be retried during a future period of low utilization. Index recommendations aren't provided for tables where the clustered index or heap is larger than 10 GB. Yes No
DROP INDEX Drops unused (over the last 90 days) and duplicate indexes. Unique indexes, including indexes supporting primary key and unique constraints, are never dropped. This option can be automatically disabled when queries with index hints are present in the workload, or when the workload performs partition switching. On Premium and Business Critical service tiers, this option will never drop unused indexes, but will drop duplicate indexes, if any. Yes No
FORCE LAST GOOD PLAN (automatic plan correction)

[Mike.Sheen]

Our guidance is to leave things at the default, and any suggested index changes be implemented in a controlled fashion.

Why I mean by controlled fashion is:

• Keep a record of the change
• Monitor the Query Store regularly to identify any performance regressions and revert the change

Whenever you move a database, you should always rebuild indexes. So, after lifting a database to Azure SQL, follow our guidance on this.

[Ernst]

Thx Mike, So I guess that means we keep the indexes JIWA came with, And dont let Azure go and add/ delete indexes as it wants to.

[Mike.Sheen]

Thx Mike, So I guess that means we keep the indexes JIWA came with, And dont let Azure go and add/ delete indexes as it wants to.

Initially, yes.

The saying "Premature optimisation is the root of all evil" comes to mind. If you don't have a performance issue, don't go changing anything unless you have the discipline to measure before and after the change, and are able to revert the change.

It's always a good idea to inspect the Query Store or review any recommended index changes suggested by the portal somewhat regularly - suggested changes should be implemented experimentally, with a record of what changed so you can go back, because sometimes suggested indexes make things worse - perhaps not initially but perhaps days, weeks or months later.

[Ernst]

Great thanks for the feedback Zen Master