July 9, 2014 at 9:29 am
Has anyone seen activity like this? If so, where does it come from?
dd hh:mm:ss.mss:00 00:18:32.210
session_id:79
login_name:me\me
wait_info:(6ms)IO_COMPLETION
CPU:646,180
tempdb_allocations:2,088
tempdb_current:0
blocking_session_id:NULL
reads:171,237,095
writes1,439,934
physical_reads:637,080
used_memory:2
status:rollback
Thanks
July 9, 2014 at 11:53 am
Not sure where you're pulling that information from, but it looks like a session is rolling back a transaction and is waiting on the disk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2014 at 12:10 pm
It's been happening the last couple days. I'll not be able to expand the table or programmability nodes in object explorer, getting an error.
The output that I posted is from sp_whoisactive. It's rolling back because I killed it. The only text in the XML query text field is 'SET STATISTICS XML OFF'. As soon as the rollback completes (which can take up to an hour), I'm able to expand nodes again.
Thanks
July 10, 2014 at 4:36 am
It sounds like you're trying to capture some enormous execution plan or an execution plan on a cursor or something like that. SET STATISTICS XML OFF is just turning off the capture of execution plans. It's not going to be a blocking processing. It has to be the statement (or statements) before that in the batch that are causing the problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 10, 2014 at 8:37 am
I think you nailed it. Both times when it happened I was pulling the actual execution plan for a dead pig of a stored procedure.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply