Getting random LCK_M_SCH_M on convert and bulk insert task

Question

Getting random LCK_M_SCH_M on convert and bulk insert task

elias.stassinos

SSC Enthusiast

Points: 186
More actions
February 9, 2015 at 11:50 pm

#304014

I starting getting random LCK_M_SCH_M locks with huge wait time, which hung my etl proccess.
The ssis package runs like this:
I have 4 containers that run in parallel and do the same thing:
Convert a tab delimited file from unicode->utf8
Truncate the table (within a foreach loop)
Bulk insert the data
Also transactionoption is set to NotSupported.
What could be causing the lock?
All foreach loops do not overlap ragarding tables/files.
Do they contest somehow?

Viewing 3 posts - 1 through 3 (of 3 total)

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

Martin Schoombee SSCoach Points: 19135 More actions · Answer 1

elias.stassinos (2/9/2015)
I starting getting random LCK_M_SCH_M locks with huge wait time, which hung my etl proccess.
The ssis package runs like this:
I have 4 containers that run in parallel and do the same thing:
Convert a tab delimited file from unicode->utf8
Truncate the table (within a foreach loop)
Bulk insert the data
Also transactionoption is set to NotSupported.
What could be causing the lock?
All foreach loops do not overlap ragarding tables/files.
Do they contest somehow?

Have a look at the following article: http://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/

There may be another process causing the schema-m locks.

elias.stassinos SSC Enthusiast Points: 186 More actions · Answer 2

Martin Schoombee (2/10/2015)
elias.stassinos (2/9/2015)
I starting getting random LCK_M_SCH_M locks with huge wait time, which hung my etl proccess.
The ssis package runs like this:
I have 4 containers that run in parallel and do the same thing:
Convert a tab delimited file from unicode->utf8
Truncate the table (within a foreach loop)
Bulk insert the data
Also transactionoption is set to NotSupported.
What could be causing the lock?
All foreach loops do not overlap ragarding tables/files.
Do they contest somehow?
Have a look at the following article: http://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/
There may be another process causing the schema-m locks.

Yeap i saw this in the morning. although i think i have nothing else at that time. This is a DW server and the whole proccess happens in a safe tiem window.....

I will try to NOT run the truncates in parallel to see what is going to happen