0

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

5
  • 1
    Please share both query plans via brentozar.com/pastetheplan Mar 9, 2022 at 14:05
  • @Charlieface, the plans were added to the question.
    – Ronaldo
    Mar 15, 2022 at 17:54
  • Can you share the actual execution plans, as opposed to estimate, obviously you will have to BREAK; after one run of the WHILE. I suspect that you may have a spill on the big Hash 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 add OPTION (MIN_GRAN_PERCENT = 10) or some good percentage of your server memory Mar 15, 2022 at 21:44
  • Thanks for the response. The approach I chose (which may have been incorrect) was to empty the work table, run both queries, and then fish the actual plans out of the plan cache. So the attached plans were actual - just not on a full dataset Mar 16, 2022 at 14:57
  • No neither of those have runtime information. If you save them as .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 using SET STATISTICS XML ON, or clicking Actual 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. Mar 16, 2022 at 16:39

1 Answer 1

1

It might be due to the query optimizer generating a new execution plan for each iteration of the loop as opposed to what happens when you execute the sp_executesql many times. Check one of the Remarks from the doc:

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

The WHILE might cause a different behavior resulting in bad performance. You should also check the execution plan from each method as it would improve your guesses when trying to tune a query.

See the following query from Understanding SQL Server query plan cache to check if an execution plan is being reused or not:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC
2
  • Thanks for the response. I tried using Display Estimated Plan on one insert and the whole while loop, and it came up with exactly the same plans - but the difference between Estimated and Actual might be the difference. Mar 9, 2022 at 14:25
  • @user1664043, Even though they're the same plan, one might be reused and the other not, making SQL Server generate the plan over and over again for each iteration of the WHILE (and generating an execution plan costs resources). See Understanding SQL Server query plan cache. Note: this article uses a command to clear the plan cache: do not do that in production.
    – Ronaldo
    Mar 9, 2022 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.