Post reply

Any issues with sleeping spids

  • July 9, 2019 at 9:44 am

    #3661051

    Hi Experts,

    I want to know if there any issues with the "SLEEPING" SPIDs. In one of our QA environment, I am seeing a bunch of sleeping spids doing nothing.  Do they cause any performance issue?? Will any memory will be utilized by these spids and affects the incoming requests. Looks like they are doing the testing and leaving the applications opened even after they finished executing their queries.

    What kind of action can be taken on these SPIDs? I am little worried that these can cause any performance issues for incoming requests. They will just send a mail saying database server is slow. So, I wanted to know more about these sleeping spids and what can I do about them. Thing is that I don't want to KILL those spids directly.

    sleeping spids

     

    Thanks,

    Sam

    Attachments:
    You must be logged in to view attached files.

  • July 9, 2019 at 10:12 am

    #3661057

    SQL server uses memory to maintain a user connection, so, if there are many of them, it will cause memory consumption.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4705d6cf-9b60-4a47-9ca2-800b299dd35c/connection-and-associated-memory-usage?forum=sqldatabaseengine

    Also, there can be a problem of an application which doesn't reuse its connections  and opens more and more new ones.

    On the pic shown there are connections made yesterday , however there can be many more.

    If you count existing connections and group by date you will see how many of them are old ones.

    example:

    select 
    convert(date,connect_time) [date]
    ,datepart(hh,connect_time) [hour]
    ,count(1) [connections]

    from sys.dm_exec_connections
    group by convert(date,connect_time),datepart(hh,connect_time)
    order by 1,2

     

     

  • July 9, 2019 at 11:31 am

    #3661078

    Looks like an issue with how the application is configured and/or coded. Go to Bingle and look up "sql server connection pool" and ensure that your app is doing this.

    "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

  • July 9, 2019 at 3:33 pm

    #3661226

    Thank you for providing the information.

Viewing 4 posts - 1 through 3 (of 3 total)

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