SQL Server database becoems non responsive on queries

Question

Hi

SQL Server 2019 on a Windows Server 2019 vps.

We have this issue with queries in dotnet apps or even in SSIS queries that at times the queries on a database can get stuck for a very long time. At other times the queries on same database respond OK. We have run profiler on the database for extended time but have not received any 'Blocked process report'. We have scripted and re-created db in case database was corrupt but still no luck.

How can we track down the issue that is holding back the database from time to time.

Thanks

Regards

Answer 1

We have run profiler on the database for extended time but have not received any 'Blocked process report'.

To get this event, you need to configure this. For instance

EXEC sp_configure 'blocked process threshold (s)', 15
RECONFIGURE

This setting cause the trace event to be fired if a process has been blocked for more than 15 seconds. If you decide to pick another value, pick a multiple of 5, because SQL Server checks for this condition every five seconds.

But the issue is necessarily not blocking, it could also be that the query wobbles between different query plans. I recommend the article that Cathy pointed you to - because I wrote it. :-)

Answer 2

Hi @-- -- ,

Did you mean that the query run in dotnet apps or using SSIS task execute query, the query will run for a long time. But the same query works well when it run in SSMS. If I misunderstood, please let me know.

Suggest you reading the blog Slow in the Application, Fast in SSMS？

If you mean that some query run for a long time at some time, but it works well at other times. Suggest you analyzing the execution plan of the slow query. Refer to the blog Execution Plans in SQL Server.