Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22

    Unanswered: Getting random LCK_M_SCH_M on convert and bulk insert task

    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


    Click image for larger version. 

Name:	Snap3.jpg 
Views:	5 
Size:	94.0 KB 
ID:	16166

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Easy way to test would be to "join" the items in the sequence container together so that they run in serial rather than parallel.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by gvee View Post
    Easy way to test would be to "join" the items in the sequence container together so that they run in serial rather than parallel.
    Yes it would be but it would expand the time too much..

    Before going there do you think its nice to separate ALL the truncates to run in serial?

    I mean to take them outside in their own loopo, truncate one by one and then make the convert->export....

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is this wait type actually blocking the ETL job from running, or is it that while the ETL job is running, it has this lock? Since you are able to truncate the tables, I am going to assume there are no indexed views that reference these tables. If the tables are truly independent, the individual loads should not interfere with one another at all. Is the container somehow only taking out one thread at a time?

  5. #5
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by MCrowley View Post
    Is this wait type actually blocking the ETL job from running, or is it that while the ETL job is running, it has this lock? Since you are able to truncate the tables, I am going to assume there are no indexed views that reference these tables. If the tables are truly independent, the individual loads should not interfere with one another at all. Is the container somehow only taking out one thread at a time?
    This is actually the first step of our ETL process where we load the initial DB which has only tables and some union views (one view per one table from all 4 countries).

    So since truncate is locking the schema , i suspect that the parallel run of the truncates may cause problems randomly...

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The truncates alone would not cause this. You may have some reports queuing up in front of the truncates, and you have to wait for those to finish.

  7. #7
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by MCrowley View Post
    The truncates alone would not cause this. You may have some reports queuing up in front of the truncates, and you have to wait for those to finish.
    MMM no i have no queries in front as far as i know....So you say that it is not possible for blockage when 4 truncates are happening at the same time?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Four truncates on four separate unrelated tables should not interfere with each other in any way. I strongly suspect there is another process involved in this, but it is hard to tell without seeing the processes themselves. Keep in mind that a truncate table statement will take out locks and is subject to blocking like any other statement.

  9. #9
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by MCrowley View Post
    Four truncates on four separate unrelated tables should not interfere with each other in any way. I strongly suspect there is another process involved in this, but it is hard to tell without seeing the processes themselves. Keep in mind that a truncate table statement will take out locks and is subject to blocking like any other statement.
    if there is a process that is locking the schema and is unrelated to this, should i put a powershell script or something check during the day the top waits?

    I mean to run dform Glen berry's dmv scripts the following:

    Code:
    -- Isolate top waits for server instance since last restart or statistics clear  (Query 23) (Top Waits)
    WITH [Waits] 
    AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
              (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
               signal_wait_time_ms / 1000.0 AS [SignalS],
               waiting_tasks_count AS [WaitCount],
               100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
               ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
        FROM sys.dm_os_wait_stats WITH (NOLOCK)
        WHERE [wait_type] NOT IN (
            N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
    		N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
            N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
            N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
    		N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
            N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
            N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 
    		N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
            N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
    		N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
    		N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
            N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
            N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
    		N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
    		N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
    		N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
            N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
        AND waiting_tasks_count > 0)
    SELECT
        MAX (W1.wait_type) AS [WaitType],
        CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
        CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
        CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
        MAX (W1.WaitCount) AS [Wait Count],
        CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
        CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
        CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
        CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec]
    FROM Waits AS W1
    INNER JOIN Waits AS W2
    ON W2.RowNum <= W1.RowNum
    GROUP BY W1.RowNum
    HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
    OPTION (RECOMPILE);
    and check for big values in avg fields....

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Wait_Stats are kept from the start of the instance, unless you manually reset them. Use this instead:
    Code:
    select r.start_time, r.session_id, s.login_name, s.host_name, s.program_name, u.MBUsed as "TempDB Used MB", r.command, r.blocking_session_id, r.wait_type, r.wait_resource, r.wait_time, r.last_wait_type, t.text
    --, p.query_plan
    from sys.dm_exec_requests r join
    	sys.dm_exec_sessions s on r.session_id = s.session_id join
    	(select session_id, sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)/128 as MBUsed, db_name(database_id) as DBName from sys.dm_db_task_space_usage group by database_id, session_id) u on r.session_id = u.session_id cross apply
    	sys.dm_exec_sql_text (r.sql_handle) t 
    --	cross apply sys.dm_exec_query_plan (r.plan_handle) p 
    where s.is_user_process = 1
      and r.session_id <> @@spid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •