Random Execute SQL Task(s) keeps hanging when I try to run it in parallel

Question

Random Execute SQL Task(s) keeps hanging when I try to run it in parallel

water490

SSC Eights!

Points: 972
More actions
November 4, 2024 at 8:47 pm

#4477415

Hi everyone
I am trying to implement parallelism in my SSIS package. There are about 20 SP in total that have various dependencies on them. I organized them into 3 SSIS Sequence Containers called Step 1, Step 2, and Step 3. SPs in Step 2 depend on SPs in Step 1 and SPs in Step 3 depend on SPs in Step 2. Each SP is run by Execute SQL Task. Each container is connected by a precedence constraint (ie success).
I have two version of SSIS setup. First one is where each SP is executed in linear order (ie no parallelism). The second one has parallelism implemented. First version works fine. Second version is having issues. I created the various Execute SQL Task in parallel by ensuring they have no precedence constraint attached to it. For some reason, some of the Execute SQL Task will not finish. I wait for over 30 minutes at times and still nothing. The SPs behind the Execute SQL Task take close to 20 seconds or less to finish so I don't think it has anything to do with the SP logic. I think it has something to do with the way I implemented parallelism. The issue appears to be random. Each time I run the SSIS package I get different Execute SQL Task having an issue. What could be the issue? Please give me ideas on what I should investigate further. This issue has me really stumped b/c its always a different Execute SQL Task that is not finishing.
Thank you
November 17, 2024 at 2:35 am

This was removed by the editor as SPAM
December 6, 2024 at 9:06 am

This was removed by the editor as SPAM
December 6, 2024 at 9:07 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3669 More actions · Answer 1

Have you looked at what's going on in SQL server when the procedures get stuck? If you use something like sp_whoisactive it's easy to see if you have spids that are blocking each other in a closed loop. e.g. spid 1 is blocked by spid 2 which is blocked by spid 3 which is blocked by spid 1. If this were the case I would expect to see more than one stuck procedure, but I would start by looking. If there is no blocking, you can also look at the wait type to see what's holding it up. Sometimes we get bad execution plans, but that tends to cause occasional delays, not constant waits.

If you can't work out what is causing the issue, try running fewer tasks in parallel. Within the container we sometimes create five serial sequences of tasks so that only 5 will run at once (5 is arbitrary, it might be 2, it might be 10). I would also check that the stuck task is indeed random. If it's always once of 3 or 4, maybe run those in series if you can't work out why.

water490 SSC Eights! Points: 972 More actions · Answer 2

Ed B wrote:

Have you looked at what's going on in SQL server when the procedures get stuck? If you use something like sp_whoisactive it's easy to see if you have spids that are blocking each other in a closed loop. e.g. spid 1 is blocked by spid 2 which is blocked by spid 3 which is blocked by spid 1. If this were the case I would expect to see more than one stuck procedure, but I would start by looking. If there is no blocking, you can also look at the wait type to see what's holding it up. Sometimes we get bad execution plans, but that tends to cause occasional delays, not constant waits.
If you can't work out what is causing the issue, try running fewer tasks in parallel. Within the container we sometimes create five serial sequences of tasks so that only 5 will run at once (5 is arbitrary, it might be 2, it might be 10). I would also check that the stuck task is indeed random. If it's always once of 3 or 4, maybe run those in series if you can't work out why.

Thanks for the reply. I ran the SP you mentioned. I am getting an error. Do you know why I am getting error?

Command:

exec sp_whoisactive

Error:

Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'sp_whoisactive'.

Ed B Hall of Fame Points: 3669 More actions · Answer 3

Sorry, I should have mentioned that sp_whoisactive is not a system stored procedure. It's something you can download and install, created by Adam Mechanic. It has not been updated for several years, but I still use it frequently.

https://whoisactive.com/downloads/

It has many parameters, but can be run without providing any. I usually use @get_plans = 1 and if the server is busy I filter based on login name.

exec sp_whoisactive @get_plans = 1
-- , @get_locks  =1
-- , @filter = '%myname%', @filter_type = 'login'