October 25, 2012 at 11:25 am
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
Any thoughts?
October 25, 2012 at 11:31 am
Periodic hardware over-use?
Really can't tell from what little you've posted. Not sure what you'd have to post to help. Sporadic, no-repro issues like that almost always require being able to look at the server-state while it's hanging fire.
- 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
October 25, 2012 at 12:52 pm
GSquared (10/25/2012)
Periodic hardware over-use?Really can't tell from what little you've posted. Not sure what you'd have to post to help. Sporadic, no-repro issues like that almost always require being able to look at the server-state while it's hanging fire.
When i do sp_who2 on that spid..i see very very high CPU but IO usage doesnt change at all ,stays at 50.
October 25, 2012 at 1:04 pm
Not just CPU from that SPID. Overall CPU use for the server. Can "hang" a query just waiting for CPU resources. Check what your wait-types are on the query while it's hanging.
- 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
October 25, 2012 at 1:09 pm
GSquared (10/25/2012)
Not just CPU from that SPID. Overall CPU use for the server. Can "hang" a query just waiting for CPU resources. Check what your wait-types are on the query while it's hanging.
select * from sys.dm_os_waiting_tasks
where session_id=99 returns no records
October 25, 2012 at 1:11 pm
Try this one: sys.dm_exec_requests
- 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
October 25, 2012 at 1:17 pm
GSquared (10/25/2012)
Try this one: sys.dm_exec_requests
wait_type = NULL
wait_time =0
last_wait_type=SOS_SCHEDULER_YIELD
wait_resource= [blank]
status=running
October 25, 2012 at 1:48 pm
What wait type is the query incurring?
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
October 25, 2012 at 1:57 pm
GilaMonster (10/25/2012)
What wait type is the query incurring?
shows as NULL
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply