[Solved] batch insert causing blocking on other unrelated tables - LCK_M_IS
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

batch insert causing blocking on other unrelated tables - LCK_M_IS

Database type:

I've got a big batch insert going on every 30 secs I'm currently seeing lots of blocking going on when i run sp_whoisactive In the last 2 weeks we upgraded to sql server ent 2017 from 2016 (latest updates applied) I'm not sure if this is related or if the database is just busier now.

From my batch i see this reported in whoisactive

Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #AboutToInsert)

This staging table is not used by anything else, and the blocking is occurring on completely unrelated tables

here is the full batch sql

ALTER PROCEDURE [dbo].[sp_BatchInsertInCommonTrackingTable] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    

BEGIN TRY
    
    BEGIN TRANSACTION
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        --Create a batch table to insert records
        Create Table #AboutToInsert ([ID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,[ifkStagingTrackingID] [bigint] NOT NULL,[vpkDeviceID] [bigint] NULL,
            [vLongitude] [varchar](20) NULL,[vLatitude] [varchar](20) NULL,[vHeading] [int] NULL,[vReportID] [int] NULL,[vOdometer] [decimal](12, 3) NULL,
            [vVehicleSpeed] [int] NULL,[vTextMessage] [nvarchar](250) NULL,[ifkDriverID] [int] NULL,[bIsIgnitionOn] [bit] NULL,
            [bIsEngineOn] [bit] NULL,[dGPSDateTime] [datetime] NULL,[cInput1] [char](1) NULL,[cInput2] [char](1) NULL,[cInput3] [char](1) NULL,[cInput4] [char](1) NULL,
            [cInput5] [char](1) NULL,[cInput6] [char](1) NULL,[vTempSensor1] [varchar](6) NULL,[vTempSensor2] [varchar](6) NULL,[nAltitude] [varchar](7) NULL,[iHdop] [int] NULL,
            [iBatteryBackup] [int] NULL,[vAnalog1] [varchar](10) NULL,[vAnalog2] [varchar](10) NULL,[cOutput1] [char](1) NULL,[cOutput2] [char](1) NULL,[vRoadSpeed] [varchar](4) NULL,
            [bIsGsmLocation] [bit] NOT NULL Default (0),[vAnalog1Raw] [nvarchar](6) NULL,[vAnalog2Raw] [nvarchar](6) NULL,[Cell_MMC] [varchar](20) NULL,[Cell_MNC] [varchar](20) NULL,
            [Cell_Lac] [varchar](20) NULL,[Cell_ID] [varchar](20) NULL,[Fuel1_Litres] [smallint] NULL,[Fuel1_Raw] [float] NULL,[SensorData] [varchar](max) NULL,[additionalEventInfo] [varchar](255) NULL,
            [ObdData] [varchar](max) NULL,[TripType] [smallint] NULL,[Attributes] [varchar](max) NULL,[EngineHours] [bigint] NULL,[DbDate] [datetime2](7) NULL,[RawData] [varchar](4000) NULL,
            [isLastRecord] [bit] NOT NULL default (0)
        ) 

        --Create a Latest Records table
        Create Table #LatestRecordsToInsert ([ID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,[ifkStagingTrackingID] [bigint] NOT NULL,[vpkDeviceID] [bigint] NULL,
            [vLongitude] [varchar](20) NULL,[vLatitude] [varchar](20) NULL,[vHeading] [int] NULL,[vReportID] [int] NULL,[vOdometer] [decimal](12, 3) NULL,
            [vVehicleSpeed] [int] NULL,[vTextMessage] [nvarchar](250) NULL,[ifkDriverID] [int] NULL,[bIsIgnitionOn] [bit] NULL,
            [bIsEngineOn] [bit] NULL,[dGPSDateTime] [datetime] NULL,[cInput1] [char](1) NULL,[cInput2] [char](1) NULL,[cInput3] [char](1) NULL,[cInput4] [char](1) NULL,
            [cInput5] [char](1) NULL,[cInput6] [char](1) NULL,[vTempSensor1] [varchar](6) NULL,[vTempSensor2] [varchar](6) NULL,[nAltitude] [varchar](7) NULL,[iHdop] [int] NULL,
            [iBatteryBackup] [int] NULL,[vAnalog1] [varchar](10) NULL,[vAnalog2] [varchar](10) NULL,[cOutput1] [char](1) NULL,[cOutput2] [char](1) NULL,[vRoadSpeed] [varchar](4) NULL,
            [bIsGsmLocation] [bit] NOT NULL Default (0),[vAnalog1Raw] [nvarchar](6) NULL,[vAnalog2Raw] [nvarchar](6) NULL,[Cell_MMC] [varchar](20) NULL,[Cell_MNC] [varchar](20) NULL,
            [Cell_Lac] [varchar](20) NULL,[Cell_ID] [varchar](20) NULL,[Fuel1_Litres] [smallint] NULL,[Fuel1_Raw] [float] NULL,[SensorData] [varchar](max) NULL,[additionalEventInfo] [varchar](255) NULL,
            [ObdData] [varchar](max) NULL,[TripType] [smallint] NULL,[Attributes] [varchar](max) NULL,[EngineHours] [bigint] NULL,[DbDate] [datetime2](7) NULL,[RawData] [varchar](4000) NULL,
            [isLastRecord] [bit] NOT NULL default (0)
        ) 

        --Create ##LatestRecordsDirty table to keep track of the insert Latest Records
        Create Table #LatestRecordsDirty(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int) 
        
        --Create #LatestRecordscleaned table to remove non latest
        Create Table #LatestRecordsCleaned(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int)       

        Create Table #LatestRecordsJustInserted(Id int identity(1,1), ifkCommonTrackingID Bigint, IMEI Bigint, GpsDateTime DateTime, DriverID int)  

        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        --Get latest records from staging table, and put in #LatestRecordsToInsert ( these might contain duplicates)
        --Insert into #LatestRecordsToInsert
        print 'start [tblStagingTrackingData] to  #LatestRecordsToInsert'       
        Insert into #LatestRecordsToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])

        --Values
        Select TOP 100000 [ipkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]

        From [dbo].[tblStagingTrackingData] where [isLastRecord] = 1 order by dGPSDateTime asc      

        print 'end [tblStagingTrackingData] to  #LatestRecordsToInsert'     
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        
    
        print 'Fill Dirty'
        INSERT INTO #LatestRecordsDirty(ifkCommonTrackingID, IMEI, GpsDateTime, DriverID)
        select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID FROM #LatestRecordsToInsert 

        

        print 'Fill Cleaned'
        --  clean #LatestRecordsToInsert
        --Filter Latest Records to remove multiple records for a single device
        Insert into #LatestRecordsCleaned(ifkCommonTrackingID, IMEI, GpsDateTime, DriverID)
        Select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID From(
            Select ifkStagingTrackingID, vpkDeviceID, dGPSDateTime, ifkDriverID, 
            row_number() over(partition by vpkDeviceID order by dGPSDateTime Desc) as Row From #LatestRecordsToInsert           
        
        ) as A Where Row = 1
        --select * from #LatestRecordsCleaned
            



        -------------------------------------------------------------------------------------------------
        --remove those from #latestRecords that are duplicate imei and put into bulk table for later
        print 'delete from Dirty'
        Delete from #LatestRecordsDirty Where ifkCommonTrackingID in(Select ifkCommonTrackingID from #LatestRecordsCleaned);



        print 'populate #AboutToInsert with dirty latest'       
        --Insert into #AboutToInsert
        Insert into #AboutToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])

        --Values
        Select  [ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]

        From #LatestRecordsToInsert 
        where [ifkStagingTrackingID]  in(Select ifkCommonTrackingID from  #LatestRecordsDirty);
        print 'end populate #AboutToInsert with dirty latest'

        -------------------------------------------------------------------------------------------------









        --Insert into Common Table from #LatestRecordsToInsert table and OUTput the Id's etc for updating latest record on devices table  ( Not Distict IMEIs)
        print 'start #LatestRecordsToInsert to  tblCommonTrackingData - output into #LatestRecords'     
        Insert into tblCommonTrackingData([vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData])

        --Insert the inserted records to the #LatestRecords table
        OUTPUT inserted.ipkCommanTrackingID, inserted.vpkDeviceID, inserted.dGPSDateTime, inserted.ifkDriverID
        INTO #LatestRecordsJustInserted

        --Values
        Select [vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData]

        From #LatestRecordsToInsert 
        where [ifkStagingTrackingID]  in(Select ifkCommonTrackingID from  #LatestRecordsCleaned);       

       print 'end #LatestRecordsToInsert to  tblCommonTrackingData - output into #LatestRecords'        
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                


        print 'start update wlt_tblDevices from  #LatestRecordsCleaned'     
        --Update the Device Table with the latest records
        Update wlt_tblDevices 
        SET LastRecord=B.ifkCommonTrackingID, LastGpsDateTime =B.GpsDateTime
        FROM wlt_tblDevices A
        Inner join #LatestRecordsJustInserted B on B.IMEI= A.imeinumber;            
        print 'end update wlt_tblDevices from  #LatestRecordsCleaned'       

        print 'start update wlt_tblAssets_Driver from  #LatestRecordsCleaned'       
        --Update Drivers Table with the latest record
        Update wlt_tblAssets_Driver 
        SET LastRecord=B.ifkCommonTrackingID
        FROM wlt_tblAssets_Driver A
        Inner join #LatestRecordsJustInserted B on B.DriverID= A.ipkDriverID
        where B.DriverID is not null;       
        print 'end update wlt_tblAssets_Driver from  #LatestRecordsCleaned'         
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        
        print 'start  [tblStagingTrackingData]  to  #AboutToInsert'     
        --Insert into #AboutToInsert
        Insert into #AboutToInsert([ifkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord])

        --Values
        Select TOP 100000 [ipkStagingTrackingID],[vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData],[isLastRecord]

        From [dbo].[tblStagingTrackingData] 
        where [isLastRecord] = 0 order by dGPSDateTime asc;
        print 'end  [tblStagingTrackingData]  to  #AboutToInsert'       


        print 'start  #AboutToInsert  to  tblCommonTrackingData'        
        --Insert into Common Table from #AboutToInsert table
        Insert into tblCommonTrackingData([vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData])

        --Values
        Select [vpkDeviceID],[vLongitude],[vLatitude],[vHeading],[vReportID],[vOdometer],[vVehicleSpeed],[vTextMessage],[ifkDriverID],[bIsIgnitionOn],
            [bIsEngineOn],[dGPSDateTime],[cInput1],[cInput2],[cInput3],[cInput4],[cInput5],[cInput6],[vTempSensor1],[vTempSensor2],[nAltitude],[iHdop],[iBatteryBackup],[vAnalog1],
            [vAnalog2],[cOutput1],[cOutput2],[vRoadSpeed],[bIsGsmLocation],[vAnalog1Raw],[vAnalog2Raw],[Cell_MMC],[Cell_MNC],[Cell_Lac],[Cell_ID],[Fuel1_Litres],[Fuel1_Raw],[SensorData],[additionalEventInfo],
            [ObdData],[TripType],[Attributes],[EngineHours],[DbDate],[RawData]

        From #AboutToInsert;                
        print 'end  #AboutToInsert  to  tblCommonTrackingData'      
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        --Delete all the records insert to the common tracking table from the StagingTrackingTable
        print 'start  delete [tblStagingTrackingData]'  
    
        Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #AboutToInsert);
        Delete from [dbo].[tblStagingTrackingData] Where ipkStagingTrackingID in(Select ifkStagingTrackingID from #LatestRecordsToInsert);  
        print 'end  delete [tblStagingTrackingData]'        

        --Drop the temporal tables
        Drop Table #AboutToInsert
        Drop Table #LatestRecordsToInsert
        Drop Table #LatestRecordsDirty
        Drop Table #LatestRecordsCleaned
        Drop Table #LatestRecordsJustInserted
        -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        COMMIT

END TRY
BEGIN CATCH
    print 'Rolling back'


    SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    ROLLBACK    
    

END CATCH

END

sp_whoisactive is complaining about the deleting from [tblStagingTrackingData], but that is one of the last operations, and just prior to this, this statement is what I expect there to be locking on:

Insert into tblCommonTrackingData

It appears the locking occurs after the insert to this table,

Do I need to be doing some sort of transaction to stop this blocking? Or is there anything that might keep the lock on this table until the whole batch finishes?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX tblstagingtracking_idx_ipkstagingtrackingid ON dbo.tblStagingTrackingData (ipkStagingTrackingID);
The optimized query:
SELECT
        1 
    FROM
        [dbo].[tblStagingTrackingData] 
    WHERE
        [dbo].[tblStagingTrackingData].ipkStagingTrackingID IN (
            SELECT
                #AboutToInsert.ifkStagingTrackingID 
            FROM
                #AboutToInsert
        )

Related Articles



* original question posted on StackOverflow here.