Sql Server responds quickly or produces Timeout

Question

I use Sql Server 2022 with the latest Update and with several instances.

From SSMS I perform any type of query on different tables and, sometimes it responds immediately, and other times for the same query, takes a long time or it times out. I only use the computer and I am not doing anything else. The memory is 12Gb at 90%.

I have 5 instances running. I have found that if I disable one, the memory is at 85% and it responds fine. If I enable instance 5, regardless of which one it is, it responds fine sometimes and sometimes not.

What should I do?

Answer 1

Hi @zequion ,

Thank you for your reaching out and welcome to Microsoft Q&A!

Here are a few reasons why query is running slow sometimes even though page reads/writes are the same.

1. The table may be locked which are accessed in the query. You may check that with sp_who2 or sp_whoisactive.
2. Autogrowth for your database may have kicked in during that time. Change autogrowth to a higher value.
3. Index maintenance or statistics update might have kicked in for any tables used in query.

In addition, you could refer to this official documentation to troubleshoot your issue!

I hope this helps! If you have any more issues or need further assistance, feel free to share with us!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications