Troubleshooting - Timeout from database without a specific procedure

Question

I've got a performance degradation without a specific stored procedure being timeout. there are some errors here it is listed from most to least:

1. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (97% of the errors)
2. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
3. A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 35 - An internal exception was caught)
4. The lock supplied is invalid. Either the lock expired, or the message has already been removed from the queue. For more information please see https://aka.ms/ServiceBusExceptions . Reference:0a6d4e92-5df9-461e-9f02-80a863f6f975, . For troubleshooting information, see https://aka.ms/azsdk/net/servicebus/exceptions/troubleshoot.

My enviorment is : SQL azure PAAS (P11) There are no high DTU for this database (about 40%) at max. and there is few timeout for 1 stored procedure (from the query store)

Most of the time when i have a timeout issue i have a specific problematic stored procedure and this is not the case.

is it a application error (the max pool issue) i've read somewhere that in the connection pool you can use the MAX thread to keep a connection idle.

how can i troubleshoot this?

Answer 1

Have you examined the Resource Health section on Azure Portal for that database? The database may be affected by temporary health issues or transient errors.

In addition, query/stored procedure execution can be delayed when the statistics are outdated, and the query optimizer is waiting for auto-updates of statistics to finish to proceed to generate a plan for queries contributing therefore to query timeouts. Updating statistics and defragment indexes regularly may help to prevent those timeouts. You may be able to see on Query Performance Insight (under the Intelligent performance section) queries like below demonstrating update of statistics by sampling exactly at the same time the timeouts appear.