High Thread Count - No associated SPIDs

• sdmgill

  SSC Veteran

  Points: 299

  More actions

  October 4, 2012 at 12:09 pm

  #393316

  Hello all,

  I'll start with the server specs first:

  OS: Windows 2008 R2 Enterprise x64

  Mem: 256 GB

  Procs: 64

  SQL: 2005 Ent x64 SP3 (9.00.4266.00)

  Mem: 230 GB allocated to SQL

  MaxDop: 24 (BI server)

  Max Worker Threads: 0

  Procs: Using all procs

  It is a 2 node cluster with an instance of SQL on one node and SSIS/SSAS on the other node. Node specs are identical.

  Every once in a while we see a spike in the number of threads on the system and a decline in server performance. This is not a case where everytime we see a high thread count the performance suffers but more often than not we see a high thread count when we have issues. I have checked the THREADPOOL waits and nothing jumps out as extra-ordinary. I was thinking that a process or some sort of connection pool was grabbing a bunch of connections so I started looking into the problem from that perspective. If I run this code:

  SELECT max_workers_count,

  (SELECT COUNT(*) FROM sys.dm_os_threads) UsedThreads,

  max_workers_count - (SELECT COUNT(*) FROM sys.dm_os_threads) AvailableThreads,

  CAST((SELECT COUNT(*) from sys.dm_os_threads) AS DECIMAL(12,2))/CAST(max_workers_count AS DECIMAL(12,2)) PercentUsed

  FROM sys.dm_os_sys_info

  I get:

  max_workers_countUsedThreadsAvailableThreadsPercentUsed

  14721443290.980298913043478

  I ran this a few minutes ago and we are not receiving any complaints about server performance. And yes we would hear about it if the BI folks felt any disturbance in The Force :-).

  So I run this:

  SELECTos.thread_address,

  os.started_by_sqlservr,

  os.os_thread_id,

  os.status,

  os.creation_time,

  os.kernel_time,

  os.usermode_time,

  sp.spid,

  sp.kpid,

  sp.login_time,

  sp.last_batch

  FROM sys.dm_os_threads os

  LEFT JOIN sys.sysprocesses sp ON sp.kpid = os.os_thread_id

  ORDER BY os.os_thread_id DESC

  Here is an abbreviated return:

  thread_addressstarted_by_sqlservros_thread_idstatuskernel_timeusermode_timespidkpid

  0x000007FFFF5974A811842804614773NULLNULL

  0x000007FFFF4D94A8118424000NULLNULL

  0x000007FFFF6A94A81184160282359490916618416

  0x000007FFFF8E74A81184080160642354NULLNULL

  0x000007FFFF9C14A81183960137280324NULLNULL

  0x000007FFFF52D4A811839205306130NULLNULL

  0x000007FFFF95F4A811838402277111322NULLNULL

  0x000007FFFF5834A8118376046764NULLNULL

  0x000007FFFF5314A8118356029610233NULLNULL

  0x000007FFFFB254A8118340043914807663NULLNULL

  So my question is what would be started by SQL that grabs an OS thread but does not have an associated SPID? I can see a system process here or there but my system has close to 1000 threads without a SPID. Any ideas? Thanks.

  Wow...preview looks ugly so I attached the results for easier reading.