0

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?

Improve this question
1
  • Too general of information to be able to answer. To find clues immediately in the moment of slowness, you should run sp_WhoIsActive and look for things with long runtimes, high CPU, or long chains of blocking sessions. The wait_info column is helpful too. You may also find the First Responder Kit helpful with debugging too, but that's a little more advanced.
    – J.D.
    Sep 22 at 14:15

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.