I have a production Sql Server 2019.150.4316.3 instance (2 Core licence) which holds two mid active databaes (~200 concurrent users) along with low active DBs, installed on a VM (64 GB Ram).
Occasionally we encounter timeouts for all queries only on one of the databases (3.7 GB data size, Compatability Level 140).This happenes in a weekly basis sometimes outside peak hours. During the performance degrade, I open ssms and make simple selects on any table on that database and it would take minutes or may hangs forever regardless the size of the table, but queries runs normally on the other databases. I run
DBCC opentran() and it reports no active open transaction. If I bring the database offline/online the queries runs back normally.
What would make such performance degradation specific only to one database?