I need some help with a 'Timeout Expired' error I am facing with SQL Server. This issue arises when running certain queries; especially during peak usage times, and it's significantly impacting my application's performance.
I am using SQL Server 2019 with a web application built on ASP .NET Core. The problematic query is a complex SELECT statement that joins multiple large tables with various conditions; and the error tends to occur frequently in the afternoons when user activity is at its highest.
I have reviewed and optimized the query execution plans and added indexes to the involved tables to improve performance. Monitored CPU and memory usage during these peak times using SQL Server Profiler and Performance Monitor, and I have checked for blocking and long-running transactions through SQL Server Management Studio (SSMS) Activity Monitor. I verified the command timeout settings in my application, which are currently set to 30 seconds ; and ensured that network latency is within acceptable limits by testing the network connection and reviewing logs.
I need advice on what are the common causes of 'Timeout Expired' errors in SQL Server ?
Are there specific tools that you would recommend for diagnosing and resolving this issue; such as SQL Server Extended Events or Dynamic Management Views (DMVs)? checked https:// github. com /dotnet/ machinelearning- modelbuilder/issues/2759 guide but still need help .
Any tips would be greatly appreciated. Should I consider hardware upgrades or changes in server configuration?
Most of these type of problem come down to tuning the query or getting the results in a different manner. eg Can you do background processing to pre-aggregate most of the data etc
Sometimes more hardware can help alleviate the problem. eg If CPU usage is regularly over 90% then more CPUs can help, if the cache is under pressure then more memory can help etc.
Have you looked at the wait statistics while the query is running? Or just the overall top waits on your system?
Paul Randal has a top waits query here, and it filters out benign waits:
Glenn Berry has a similar one, in addition to all the other great DMV queries he has:
Have you also tried running the query in SSMS directly on the SQL Server? This would eliminate network waits as a problem, and isolate actual SQL Server performance issues. Typically, the "it's slow in my app but fast in SSMS on the server" problem is an ASYNC_NETWORK_IO wait:
I don't think that's the underlying cause though, since you're getting a timeout error, which I presume is a CommandTimeout. Async waits are typically slow client processing, but not stuck.
MS has a link here on an extended event that they use to detect command timeouts:
Ignore the credential and master key part, just take the CREATE EVENT SESSION part, and remove the URL from filename paramter, just use ' DemoPersistedTimeout.xel'
