High no of user connections and HIGh CPu Usage ??

Question

High no of user connections and HIGh CPu Usage ??

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
June 29, 2012 at 3:32 am

#263091

Hi ,
i run a perfmon and we observed a high no of user connections nearly 1500 ,cpu goes to 100 % .
Its not allowing another connections from Application side.
From application side we are seeing no managed connections .
i checked sp_configure , user connections config value is 0 [unlimited].
can you please advise on this ???
Thanks
Lavanya

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ant-Green SSC Guru Points: 113498 More actions · Answer 1

Diagnose why your CPU is at 100%, get a copy of the Accidental DBA guide link here[/url] which should help you diagnose the pressure.

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

Why the user connections are at 1500, is it not closed properly and is ther any dormant sessions frequently.

Regards
Durai Nagarajan

Ant-Green SSC Guru Points: 113498 More actions · Answer 3

I dont know why your connections are at 1500.

Could be a connection string is not closing connections down properly, could be 1500 users online at same time, could be a process which spawns multiple connections, you will need to debug your app to see what is going on.

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 4

Thanks for the update .

Normally we are seeing 400-450 connections in user connections counter in perfmon.

But suddenly connections increased to 1500 and cpu 100 % and site not loading .

Ant-Green SSC Guru Points: 113498 More actions · Answer 5

Debug why your seeing so many extra connections, debug your 100% CPU usage using the guide, some good things to check on that.

But without some custom monitoring solution which is running 24/7 it will be difficult to give you root cause.

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 6

at that time we are seeing the below error from the application side

ERROR [nucleusNamespace.atg.dynamo.servlet.dafpipeline.DynamoServlet]

java.lang.RuntimeException: CONTAINER:atg.repository.RepositoryException; SOURCE:org.jboss.util.NestedSQLException: No ManagedConnections available within configured bl

ocking timeout ( 5000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 5000 [ms] ))

I have a question ::: DB allows unlimited connections [32767] but why it goes 100 % once it reached to 1500 connections??

Ant-Green SSC Guru Points: 113498 More actions · Answer 7

depends what the connections are doing, thats why you need to diagnose your CPU pressure, it could be that there is a bad query somewhere which is executed when a connection logs in

get the guide I detailed and read through it, also debug why something caused the excessive spike in connections

i've just done a google for that error message and got a load of results

durai nagarajan SSCoach Points: 15778 More actions · Answer 8

we got the error once when a dormant session blocks numeruous connections at that time.

before i diagonise i was in urgency to kill it.

once i killed it it took around 30 secs to come back to normal CPU time, we have .net applications we faced the same type of error.

Hope you have to get in to SP_who2 and see whats happening when CPU reaches 100%.

Dont do the mistake that i did diagonise and find a permanent solution , luckily i havent got in to the issue again.

Regards
Durai Nagarajan

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 9

Thanks Nagarajan.

So this issue is related to APP or DB??

durai nagarajan SSCoach Points: 15778 More actions · Answer 10

Lavanyasri (6/29/2012)
Thanks Nagarajan.
So this issue is related to APP or DB??

Poor code so DB.

Regards
Durai Nagarajan

SpringTownDBA SSCertifiable Points: 6917 More actions · Answer 11

You are having performance issues on the SQL server. This is the root cause of your problem.

As a side effect, your application is opening additional connections to service requests because there are no unused connections in the connection pool. 1500 is probably the default limit on the size of the connection pool in your application. It should be configurable, but increasing it will not solve your problem, so don't increase it and expect your problems will go away. (actually, lowering it may marginally help overall throughput, but I don't recommend changing it).

You need to figure out why you are having performance issues.

What is your MAXDOP setting?

What is your plan cache hit ratio?

Do you have "Optimize for Ad-Hoc workloads turned on?"

How many compiles/sec?

How many re-compiles/sec?

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 12

Lavanyasri (6/29/2012)
Thanks Nagarajan.
So this issue is related to APP or DB??

I would say it can be either or both. The possible reasons I can think of are...

- Increase in number of applications OR application users

- Database performance. Due to DB growth (or code change) transactions are taking longer time. This leads to more number of concurrent connections from application

- Application side code change, if added more stuff in single transaction (connection remains open for longer time than before)