Trouble with SQL Timeouts

Question

Trouble with SQL Timeouts

mqbk

SSCommitted

Points: 1513
More actions
December 10, 2014 at 2:48 pm

#293388

I am having sporadic SQL timeout errors from on of my companies applications. The first time we have seen this was when our connections went over the default 100 connections in the connection string. The developers increased from 100 - 200. Then we began seeing it again when we the user connections would exceed 200. Now were are at 300 and it is the same thing. At this point I do not believe continuously raising the connection pool limit will solve the issue. I need to know how to determine where the hang up is. Whether it is in SQL or coming from the application server. Thanks for your help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

You need to check the condition of the server when you're getting the timeouts. Are there lots of connections, or only a few. Of those connections, are any blocked or blocking or using excessive resources. It sounds like the app might not be releasing connections appropriately, but it's hard to know from here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

mqbk (12/10/2014)
I am having sporadic SQL timeout errors from on of my companies applications. The first time we have seen this was when our connections went over the default 100 connections in the connection string. The developers increased from 100 - 200. Then we began seeing it again when we the user connections would exceed 200. Now were are at 300 and it is the same thing. At this point I do not believe continuously raising the connection pool limit will solve the issue. I need to know how to determine where the hang up is. Whether it is in SQL or coming from the application server. Thanks for your help

If you're saying that increasing the number of allowed connections temporarily helps but requires you to increase it again later, you could have a "connection leak" and you need to find that and fix it or get used to bouncing the server to "fix" it. The latter would be the loathsome thing to do,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mqbk SSCommitted Points: 1513 More actions · Answer 3

I have a PowerShell job that pulls the performance counters every 5 minutes. I have noticed that during the times of the timeouts that we have an excessive number of connections. During the incident yesterday, I had nearly 400 user connections. The developers went through the code and found that in some places in the code the connection timeout was set to 3000. Not sure if that is why we appear to have so many connections at one time. Is there some way I can check from the SQL end to see if I have connection leaks?

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

No, I'm pretty sure that's a code issue. They're not cleanly disconnecting.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mqbk SSCommitted Points: 1513 More actions · Answer 5

Grant Fritchey (12/11/2014)
No, I'm pretty sure that's a code issue. They're not cleanly disconnecting.

I believe that to be the issue as well, but I have to be able to prove beyond a doubt that SQL isn't the cause. I wanted to make sure that my understanding of the SQL process was correct before I start mentioning application optimization.