users in sleeping mode

  • 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

  • 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
  • They are connect but no executing anything

  • 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

  • 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

  • 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

  • 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

  • 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
  • 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.

  • Thanx

    Thanks

  • 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.

  • 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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

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

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