0

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?

Ranjit Singh
  • 3,715
  • 1
  • 21
  • 35

1 Answers1

0

Sounds like you're experiencing memory pressure which leads to the query getting flushed from cache. Then, on recompile, the statistics are out of date so the plan that gets recreated isn't reflective of the actual data, resulting in poor performance. Pretty standard stuff.

Update the statistics more frequently since it seems they are the issue, aging faster than you would like, combined with less memory resulting in more recompiles.

You can check all this by getting the execution plan when the query is running well and when it's running poorly. Look at the statistics used in each plan (they're visible in the properties of the first operator on the left). See if it's because of out of dates statistics. You can also monitor for the volume of recompiles using a raw count from sys.dm_os_performance_counters. If you care about watching individual recompiles you can use Extended Events and the sp:recompile event.

Grant Fritchey
  • 2,645
  • 19
  • 21