5

We recently migrated our database from SQL Server 2014 to SQL Server 2022 Enterprise Edition. Our compatibility level is set for 160. After migration we observed few stored procedures started timing out frequently. The main symptom we identified is frequent statistics updates happening on certain tables leading to timeouts. We disable Auto Update of statistics on few tables and the problem went away. I need your expert opinion on following:

  1. Why we are facing frequent statistics updates in SQL Server 2022 and why this is not observed on all tables. Our database is huge with many big tables.
  2. Disabling Auto Update of statistics and doing it manually is the right approach?
  3. Should we change Auto Update of statistics to Async? Will it cause any issues in high transactional system?

Majority of tables are partitioned.

We have disabled Auto Update of statistics on few tables.

Michael Green
  • 25,255
  • 13
  • 54
  • 100

1 Answers1

3
  1. Check your compatibility level. Statistics maintenance and the update frequency were changed in SQL Server 2016 and behaves differently. You can read about it in the documentation here. Depending on your compatibility level, you may need traceflag 2371, also in the documentation linked. If you have updated your compatibility level beyond 130, you usually see reduced statistics updates. However, like anything, your mileage may vary.
  2. Sometimes, yes.
  3. Testing is your friend. Lots of people switched to async stats updates and saw a benefit. Lots of others switched and saw pain. Still others switched and couldn't tell the difference. You'll have to test in your environment. No one can tell you for sure what the outcome will be.
Grant Fritchey
  • 4,615
  • 20
  • 19