users in sleeping mode

Question

users in sleeping mode

forsqlserver

SSCoach

Points: 18902
More actions
June 1, 2009 at 1:35 am

#204954

There are lots of users in sleeping mode in sql server are they unused or what does it mean ...are they connected....Please any one help.......
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Means they are not currently running any queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

VastSQL

SSC-Dedicated

Points: 36097

June 1, 2009 at 5:49 am

#1002664

They are connect but no executing anything

forsqlserver SSCoach Points: 18902 More actions · Answer 3

If they are in sleeping mode yet can they be a reason of slow performance?

Is there any specific number that this much user could connect to sql server?

Can we restrict that after 50 or 60 users connection will not be created?

Please give the three answers seperately...Help

Thanks

GURSETHI SSCommitted Points: 1912 More actions · Answer 4

Hello Ajay,

Here are answers to your questions.

Q: If they are in sleeping mode yet can they be a reason of

slow performance?

A: Sleeping means waiting for a event to occur. At any given

point in time there can be multiple connections in sleeping

mode as they will be waiting for IO, CPU, MEMORY etc.

The thing is we need to identify which resource is

which is waited most.

Q: Is there any specific number that this much user could

connect to sql server?

A: There is no specific number.

Q: Can we restrict that after 50 or 60 users connection will not

be created?

A: We can restict concurrent connection count on SQL by a

SP_CONFIGURE paramater named USER CONNECTIONS.

or

Right click on Instance Name in SSMS under object explorer

and select Properties.

Select Connections option under "Select a Page"

from Server Properties screen.

Right hand side provide values (default 0 unlimited)

under Maximum number of concurrent connectins options.

Regards

GURSETHI

forsqlserver SSCoach Points: 18902 More actions · Answer 5

You have done a great job sethi...

One more thing is confusing me, I have a db server and web server.If anyone log in into website using user id password will it also make a connection in the db server?

If no then what users are login into db servers...?

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 6

Dear,

I am also not able to save the result of a query into a CSV file.After saving it doesn't give a readable format.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

GURSETHI (6/1/2009)
Q: If they are in sleeping mode yet can they be a reason of
slow performance?
A: Sleeping means waiting for a event to occur. At any given
point in time there can be multiple connections in sleeping
mode as they will be waiting for IO, CPU, MEMORY etc.
The thing is we need to identify which resource is
which is waited most.

A sleeping connection is one that is not running a query at the moment. If a connection is running a query and is waiting for a resource (lock, memory, IO) it will be SUSPENDED. If it is waiting for time on the CPU it will be RUNNABLE. If it is currently executing it's status will be RUNNING

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dbuendiab SSC Eights! Points: 993 More actions · Answer 8

The web server uses some SQL credentials to connect to the SQL Server. So, if you have such a hardwired user for your SQL connection, that one will be repeatedly setting the connections. Usually you'll get a connection, perform the query and leave the SQL Server.

forsqlserver SSCoach Points: 18902 More actions · Answer 9

forsqlserver

SSCoach

Points: 18902

June 2, 2009 at 10:32 pm

#1003757

Thanx

Thanks

P Jones SSChampion Points: 12361 More actions · Answer 10

Connection pooling and timeouts also come into play here - the connection can thus remain after a user has finished and - especially if you are using the same sql login for any connection from your web app - connections can be re-used thus saving time and resources.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

It isn't uncommon to have sleeping spids sitting on your servers. If you use something like MOSS 2007, it has hundreds of sleeping spids at any given point in time because the application and services all use connection pooling to save the cost and time required to open a connection. When the code is done with a connection, it resets it and returns it to the pool for reuse by the application. There is nothing wrong with this unless your connection count continuously climbs into the thousands of connections which would point to a connection leak in your application where you aren't closing connection to allow them to reset and return to the pool, or you don't have pooling enabled.