select into #temptable hung state ???

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  October 25, 2012 at 12:54 pm

  #393465

  I am stuck at a very very weird situation. I have a sproc which occasionally hangs at below query:

  select * into #temptable

  from my statictable

  where id= 10000

  I) there is no blocking

  ii) drive on tempdb has enough space.

  iii) current spid is using less than 200 MB of tempdb.

  iv) if i take the same query and manually run it completes in less than 1 min

  v) when i do sp_who2 on that spid i see very very high CPU usage but no change in IO.

  Any thoughts?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  October 25, 2012 at 1:05 pm

  #1552889

  Duplicate thread. Please post replies at active copy: http://www.sqlservercentral.com/Forums/Topic1377161-149-1.aspx?Update=1

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon