Blocking during bulk insert between table swaps

Question

Blocking during bulk insert between table swaps

kmcfadden

Default port

Points: 1472
More actions
February 10, 2020 at 10:03 pm

#3724526
I am having some troubles understanding the mechanism behind and mitigation of a blocking issue related to an hourly data refresh. The data is loaded into a staging table using a bulk insert, then the staging and live tables are swapped using sp_rename. The issue occurs when the next bulk insert is running. A select query using a table-valued-function on the live table is being blocked by the bulk insert on the staging table.
Some pseudo-code for the bulk insert and table swap:
```
BULK INSERT StagingTable FROM 'newForecast.csv' WITH (FIRSTROW=2,FIELDTERMINATOR=',',ROWTERMINATOR='0x0a',TABLOCK)

UPDATE STATISTICS LiveTable_Staging

BEGIN TRANSACTION
EXEC sp_rename 'LiveTable', 'TempTable'
EXEC sp_rename 'StagingTable', 'LiveTable'
EXEC sp_rename 'TempTable', 'StagingTable'
COMMIT
```
The blocking happens during subsequent runs of the bulk insert: a table-valued function tries to select data from LiveTable, but is being blocked by the bulk insert on StagingTable.
Some pseudo-code for the function
```
CREATE FUNCTION dbo.ifn_getDataById
( @ID INT
)
SELECT f.ID, d.Forecast1, ..., d.Forecast
FROM fields f INNER JOIN LiveTable d ON f.lat =  d.lat AND f.long = d.long
WHERE f.ID = @ID
```
LiveTable and StagingTable have the same columns/column definitions, and similar indexes (which differ in name only). When I attempt to investigate the blocked query, I notice that the query is blocked from accessing a resource in StagingTable (e.g. if LiveTable has a reference ID of 722101613 and StagingTable has a reference ID of 946102411, the blocked query, SELECT * from dbo.ifn_getDataByID(254), is waiting for object 12:946102411:8)
The bulk insert ends up blocking the resource for a few minutes, causing some timeout issues on the application end.
I'm thinking that the blocked query is trying to reuse an old plan from the query plan cache, from when LiveTable had a reference ID of 946102411 and StagingTable had a reference ID of 722101613, but I'm not sure why that would be the case or how to prevent it.
Any thoughts/advice would be greatly appreciated.
- This topic was modified 5 years, 8 months ago by kmcfadden.

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

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

ScottPletcher SSC Guru Points: 101205 More actions · Answer 1

I'd do an explicit sp_recompile on all the tables affected by the renames, just to be safe. Renaming is great, but it's a short-cut method that's not fully "recognized" by SQL Server in all things.

Maybe something like this?!:

BEGIN TRANSACTION
EXEC sp_rename 'dbo.LiveTable', 'TempTable'
EXEC sp_rename 'dbo.StagingTable', 'LiveTable'
EXEC sp_rename 'dbo.TempTable', 'StagingTable'
COMMIT
EXEC sp_recompile 'dbo.LiveTable' --<<--
EXEC sp_recompile 'dbo.StagingTable' --<<--
EXEC sp_recompile 'dbo.TempTable' --<<--

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Have you considered using SYNONYMs to perform the swaps, rather than sp_rename? Might be worth testing.

kmcfadden Default port Points: 1472 More actions · Answer 3

ScottPletcher wrote:

I'd do an explicit sp_recompile on all the tables affected by the renames, just to be safe. Renaming is great, but it's a short-cut method that's not fully "recognized" by SQL Server in all things.

Thanks for the sp_recompile suggestion. After some research and testing the sp_recompile statements, the blocking issue remained. sp_recompile did sound very promising though.

Phil Parkin wrote:

Have you considered using SYNONYMs to perform the swaps, rather than sp_rename? Might be worth testing.

SYNONYMs also sound like another good approach, and one that completely escaped me at the time. While I haven't yet tested an approach using SYNONYMs, I did come across another method using some ALTER TABLE [table1] SWITCH [PARTITION 1] TO [table2] statements to switch out the partition between LiveTable, TempTable, and StagingTable.

I ended up testing the partition switching method before the SYNONYM method, and so far, the blocking issue has disappeared. The reference IDs of the three tables remain static and SELECT queries are no longer being blocked by the BULK INSERT on the staging table. The pseudo-code appears below:

BEGIN TRANSACTION
ALTER TABLE LiveTable SWITCH TO TempTable
ALTER TABLE StagingTable SWITCH TO LiveTable
ALTER TABLE TempTable SWITCH TO StagingTable
COMMIT

Again, thanks for the suggestions.