Post reply

Sleeping SPID

  • July 8, 2013 at 6:03 am

    #395100

    Hello everyone,

    I have stuck in a situation where I am seeing more than 15K sleeping SPID, I know this is because of a flaw in application which is connecting with SQL (SQL server 2008).

    my question is "do these sleeping SPID's hold a connection and consume Server resources"

    Thanks

  • July 8, 2013 at 6:21 am

    #1630394

    Have a look at the SQL_CONNECTION memory clerk to see if it's taking up too much memory

    Also look at

    select * from sys.dm_exec_connections to see how much reads etc have been performed by the spids

  • July 8, 2013 at 6:23 am

    #1630396

    Yes, they are a SQL Server connection and they hold a small amount of memory (2MB each I think) for the thread stack

    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

  • July 8, 2013 at 6:43 am

    #1630410

    Total SPID's 26K

    number of read from 6 to 991147

    what can I conclude from it ?

  • July 8, 2013 at 6:57 am

    #1630417

    pujain (7/8/2013)

    what can I conclude from it ?

    Virtually nothing.

    All that tells you is the number of reads (pages read from memory) that the connection did from the time it connected to now.

    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

  • July 8, 2013 at 7:06 am

    #1630420

    Thanks GilaMonster for your reply,

    let me ask you in a different way for my better understanding,

    I ran a query and it extract suppose 100K records , the query finished but I didn't closed it and it is still showing me in sleeping mode.

    will it take some memory and resource, if yes how much (2MB) as you said earlier or the amount of space it took while it ran while pulling the data?

  • July 8, 2013 at 7:20 am

    #1630430

    Yes, it will take some memory, just for its threadstack which is 2MB (I think) on x64.

    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

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

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