Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
Is the process blocked? How many CPUs on the box? What is the server's MAXDOP setting? –  Jon Seigel Sep 20 '12 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? –  Aaron Bertrand Sep 20 '12 at 13:01
2  
Does the T-SQL complete if you run it outside of SSIS? –  Eric Higgins Sep 20 '12 at 15:51

1 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
  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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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