Application timeout (SQL Server always on)

Question

My env is SQL Server Always on.

The application teams told me about sometimes the Application error with alarm timeout(more than 60s).

They cannot reproduce the issue because sometimes Application is running fine sometimes it error alarm timeout.

I tried to check event log and the profiler on the SQL Server but didn't find any warning.

When I tried to query directly in SQL Server, everything is fine not timeout, so I'm not sure this issue is really with SQL Server or the network.

I didn't have the monitoring tool to investigate this issue.

Could anyone suggest the solution to investigate this issue or suggest the tools that can monitor and tracking this issue?

Thank you

Answer 1

So, my understanding is that you have an application that connects to a SQL Server database configured with AlwaysOn Availability Groups, which is a common scenario. Intermittent timeout issues are sometimes hard to track. From my experience, here is a list with a few things you can try:

• Identify the query that is timing out (you already did that - so that's fine)
• Run the query yourself in SQL Server (you already did that and it works - so that's fine)
• Look for the exact moment when the query timed out. Are there any other jobs running at that time on the server that could potentially issue locks on the same tables as those used by the query ? If so, perform a test and see how can you or the application team optimize the query. Consider things like creating indexes, using temporary tables, or using ISOLATION LEVELS for concurrency management.
• Look for a pattern. What is different when it times out? Maybe the primary node changed and there is a problem with the replica. Does the query uses a Linked Server that's under heavy load or is not available at that time?
• Try to run the query under the same user as the application. Maybe you run it under your own user with admin rights and it works fine. But what if you run it under the application user? And, what if, depending on the data, there is an IF statement somewhere, and when entering that IF statement, the stored procedure performs a join with a Linked Server and the application user is not mapped to access that Linked server? This happened to me more than a couple of times and it could explain the intermittent behavior. Many other things can go different.
• Does the query itself reaches the Sql Server instance from the application server every time? Maybe there is a network issue, like you said. Perform a ping or a telnet. Monitor the connection between the DB server and the Application Server. Look for metrics regarding discontinuities in the network service in the company's network availability dashboards.