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?

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  February 10, 2015 at 8:16 am

  #1776639

  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

  February 10, 2015 at 9:07 am

  #1776659

  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