January 5, 2016 at 12:00 pm
I have a simple ssis dataflow with one input (from a staging db) and two outputs (one to a target db and another to a different table in the staging db like this:
input: staging table in staging db
output 1: target table to insert new rows in target db
output 2: new staging table to hold updated rows (complement of output 1) in staging db.
Shortly after the package starts up, it gets hung up on waits. In sp_whoisactive I see:
session 1 (staging db connection): PREEMPTIVE_OS_WAITFORSINGLEOBJEC
session 2 (target db connection): LCK_M_X
I'm trying to understand what I should look at to resolve this. There is not a lot of doc on PREEMPTIVE_OS_WAITFORSINGLEOBJEC . How can I find out exactly what it is waiting on?
Gerald Britton, Pluralsight courses
January 5, 2016 at 1:30 pm
Found the problem:
The staging table was being read using a view that referenced the target table. SSIS was not reading all the staging records in one go (which would drain the view and release the SELECT lock) so the INSERT operation, trying to get an exclusive lock, was waiting for that but SSIS was waiting for the INSERT to complete (in a small batch as it turns out). That's the preemptive wait.
I changed the view to a proc and read the results to a temp table, then read from the temp table instead of the view.
Problem solved!
Gerald Britton, Pluralsight courses
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply