July 8, 2013 at 6:03 am
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
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
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
July 8, 2013 at 6:43 am
Total SPID's 26K
number of read from 6 to 991147
what can I conclude from it ?
July 8, 2013 at 6:57 am
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
July 8, 2013 at 7:06 am
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
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply