Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Let Sleeping SPIDS lie

    A developer came over and asked me a couple of things

    1. Can C# not cleanup SPIDs and just open new ones lieing around

    2. Do a large number of spids, even if they are sleeping, cause performance issues?

    3. What's the Max # of spids sql server can handle?

    BOL ain't too helpful right now
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    An application (such as a service, but not a web page) can easily open a spid, and do nothing with it for great periods of time.

    A large number of SPIDs (>10000 say) will cause memory starvation, as each SPID requires about 80K of memory for its own housekeeping.

    SQL Server can theoretically handle 32,000 SPIDs. Can't say as I have tried to push it that far myself.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    An application (such as a service, but not a web page) can easily open a spid, and do nothing with it for great periods of time.

    A large number of SPIDs (>10000 say) will cause memory starvation, as each SPID requires about 80K of memory for its own housekeeping.

    SQL Server can theoretically handle 32,000 SPIDs. Can't say as I have tried to push it that far myself.

    Nah, it's a couple hundred

    They are complaining about performace...I suggested she look into blocking
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Tell them to make sure they close all their connection objects anyway.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK

    We use websphere and connection pooling so I never have to worry about it

    Wonder why they don't follow best practices in the same area
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A few hundred connections for a connection pooling app seems a bit high to me. I have not worked with WebLogic extensively, though.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well they're using .NET

    Does that manage connections to the box and is it configurable?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

  9. #9
    Join Date
    Feb 2008
    Posts
    3
    I am working on SQL server 2000. I am very new it as this is my first job.
    I have one SQL server where i have lot sleeping SPIDs more than 100 SPIDs. When i have click its properties some are blank while other having some commands. So can one tell me how to identify and clear unwanted SPIDs

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Easy... Don't.

    Unless a spid is actively causing pain (locking, blocking, burning the CPU, etc) I never bother them. The limit for spids used to be 32,700 or so. That limit has long since been banished, so there is no effective limit.

    Unless you are the compuslive type that will go into a beehive and remove the non-productive bees with a tweezers, then I'd leave the spids alone.

    -PatP

  11. #11
    Join Date
    Feb 2008
    Posts
    3
    Thanks.
    But these sleeping SPIDs would eat up all the physical Memory available.
    Production Server has 2 GB Physical Memory and usage is more than 80 %.
    So what do in this case.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    At 80 Kb per connection, 100 SPIDs will take up approximately 8 MB of memory. I think you are safe for the moment ;-)

  13. #13
    Join Date
    Feb 2008
    Posts
    3
    I am sorry i gave less SPID count. The Actual Count is 1261 in the peak hours.

  14. #14
    Join Date
    Sep 2005
    Posts
    161
    Here is something that you can modify to suit your needs. Just use the lastbatch column to select the spid's you want to kill.

    Code:
    CREATE TABLE #temp (
                spid int,
                status varchar(256),
                loginname varchar(256),
                hostname varchar(256),
                blkby varchar(256),
                dbname varchar(256),
                command varchar(256),
    			cputime int,
    			diskio int,
    			lastbatch varchar(256),
    			programName varchar(256),
    			spid2 int,
    			requestid int) 
    
    INSERT INTO #temp
    EXECUTE sp_who2 
    
    select * from #temp
    
    DECLARE kill_spids CURSOR FOR
    --Add a WHERE clause here to indicate which processes to kill.
    SELECT spid
    FROM #temp
    
    
    DECLARE @spid SMALLINT
    
    OPEN kill_spids
    
    FETCH NEXT FROM kill_spids INTO @spid
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN 
    
                DECLARE @dynamicsql NVARCHAR(4000)
                SET @dynamicsql = 'KILL '+CAST(@spid AS CHAR)
                PRINT @dynamicsql
                --When you are sure you know what you're doing, un-comment this line
                --EXECUTE sp_executesql @dynamicsql
                FETCH NEXT FROM kill_spids INTO @spid
    
    END
    
    CLOSE kill_spids
    DEALLOCATE kill_spids
    DROP TABLE #temp

  15. #15
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Not sure I like the idea of shotgun-killing spids like that. Process control should be discrete and manually handled. There's a lot more to look at than "Last Batch". You have to exclude system processes, watch out for SQLAgent jobs, etc... It's a bit too risky for my tastes. (Your risk tolerance may vary, but it's not my data.)

    Kill manually, if at all. :-)

    -D.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •