3

I am rather new to SQL Server administrations, I am running an SSIS that never ends...

A) looking at the processes using

SELECT r.*, t.text 
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t 
WHERE r.status IN (N'Suspended', N'Running', N'Runnable', N'Pending')

I see the session is SUSPENDED

B) looking at the actual execution plan using

SELECT plan_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 55

SELECT query_plan 
FROM sys.dm_exec_query_plan (0x06000A00FB4E0A2240615FAE010000000000000000000000)

I see 95% of time spent on parallelism

How can I solve this issue?

Thanks in advance, Assaf

3
  • 2
    Is the process blocked? How many CPUs on the box? What is the server's MAXDOP setting?
    – Jon Seigel
    Sep 20, 2012 at 13:01
  • 3
    Usually there is a root cause (e.g. outdated statistics). Did you look at the wait types for all of the threads in sys.dm_exec_requests? (They won't ALL be CXPACKET, but it's often "blamed" for the wait, when that isn't actually correct.) Can you show the plan somewhere? Sep 20, 2012 at 13:01
  • 2
    Does the T-SQL complete if you run it outside of SSIS? Sep 20, 2012 at 15:51

1 Answer 1

1

Suspended status doesn't tell you much; the wait type tells you the story. I would;

  1. Download and Install sp_whoIsActive execute the below command while the processes are suspended to get the leading blocker, it's wait type along the lock graph. This should show the lock areas. (please upload)
exec sp_WhoIsActive 
  @show_own_spid=1, 
  @show_sleeping_spids=1, 
  @get_task_info =2, 
  @get_locks=1, 
  @get_additional_info=1, 
  @find_block_leaders=1,
  @format_output = 0
  1. Setup a SQL blocking report trace. Here is the link with scripts to set up the blocked report SQL trace. Set the blocked process threshold to 20 secs or higher

Please upload these results for review

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.