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?
Best Answer
You haven't committed the transaction yet, and all exclusive locks (and if in Repeatable Read or Serializable all shared locks) are held until the end of the transaction.