I've got some batch job processes that are all built on the same pattern - load new data into a temp table, figure out what operations need to be done based on the data in the temp table, and then run some dynamic sql to shuffle the data in the temp tables into the final tables.
These operations can ultimately be operating on, say, 100 million rows.
The original code, made a dynamic INSERT statement and then ran sp_executeSql in a WHILE loop in batches of 100,000
I thought thousands of invocations of sp_executeSql might be more expensive than executing 1 statement with the WHILE loop built in, so I tried this instead. To my great surprise, it took 8 times longer to execute than running EXEC sp_executeSql many thousands of times.
Anyone have an idea why doing a WHILE in a dynamic sql statement would perform so badly?
SET @sql = '
DECLARE @minRowID int = 0, @maxRowID int = @batchSize, @totalInsertedCounts bigint = 0;
WHILE (1=1)
BEGIN
INSERT INTO ' + @destinationTable + ' ('+ @columnList +')
SELECT ' + @columnsForSelect + '
FROM ' + @fromTable + ' AS D (NOLOCK)
INNER JOIN ' + @workTable + ' AS W (NOLOCK) ON D.[RowID] = W.[RowID_Source]
WHERE W.RowID > @minRowID AND W.RowID <= @maxRowID
OPTION (MAXDOP 1)
SET @counts = @@ROWCOUNT
CHECKPOINT;
IF @counts = 0 BREAK
SET @totalInsertedCounts += @counts
IF (@totalInsertedCounts % 500000 = 0)
BEGIN
DECLARE @percent float = ROUND(@totalInsertedCounts * 100 / CAST(@totalCounts as float), 3);
DECLARE @percentStr varchar(20) = CAST (@percent as varchar);
RAISERROR(''Insert completed percent: %s'', 10,1, @percentStr) WITH NOWAIT;
END
SET @minRowID += @batchSize
SET @maxRowID += @batchSize
END
SET @counts = @totalInsertedCounts;
'
EDIT:
Based on the comment, I got the plans for the while-in and single statement (with many sp_executeSql execution).
https://www.brentozar.com/pastetheplan/?id=rkDUlLKb5 - while loop runs 1 dynamic statement
https://www.brentozar.com/pastetheplan/?id=S1sjxIFW5 - while loop in dynamic statement
BREAK;
after one run of theWHILE
. I suspect that you may have a spill on the bigHash Join
due to bad estimation, which is causing the slowdown, that will only show up on the actual plan. If so, one solution is to addOPTION (MIN_GRAN_PERCENT = 10)
or some good percentage of your server memory.sqlplan
files and open in SSMS you will see that. The plan cache does not hold that information anyway, the only ways to get is by usingSET STATISTICS XML ON
, or clickingActual Execution Plan
in SSMS, or an XEvent session, or a Trace or through Query Store. Via SSMS is the easiest, a trace is probably the next easiest if you cannot use SSMS.