SQL Server parallelism issue

Question

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

Answer 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

2. 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