Recently we have migrated from On-prem hosting to Azure hosting, and migrated our Sql Server to Azure SQL Managed Instance.
We have a huge table having close to 1 billion records and it has a non-cluster index. When we rebuild index or update statistics with FullScan the query uses that particular index for 2-3 days and then again stop using the index. This is very weird because I have never come across such issue while working with on-prem SQL database. The difference is the configuration of memory. Earlier we were using 128GB memory and with Azure we are using 20GB memory. Even using 54GB memory on Azure resulting in same issue.
Does anyone had faced similar issue and how you have resolved it?