Reduce Query Resource Use - SQL Server Query Performance Tuning - page 384

WHILE @Count <= 10000
BEGIN
INSERT INTO dbo.Test1
(C1)
VALUES (@Count % 256);
SET @Count = @Count + 1;
END
Since every execution of the INSERT statement is atomic in itself, SQL Server will write to the transaction log for
every execution of the INSERT statement.
An easy way to reduce the number of log disk writes is to include the action queries within an explicit transaction.
DECLARE @Count INT = 1;
DBCC SQLPERF(LOGSPACE);
BEGIN TRANSACTION
WHILE @Count <= 10000
BEGIN
INSERT INTO dbo.Test1
(C1)
VALUES (@Count % 256) ;
SET @Count = @Count + 1 ;
END
COMMIT
DBCC SQLPERF(LOGSPACE);
The defined transaction scope (between the BEGIN TRANSACTION and COMMIT pair of commands) expands the
scope of atomicity to the multiple INSERT statements included within the transaction. This decreases the number
of log disk writes and improves the performance of the database functionality. To test this theory, run the following
T-SQL command before and after each of the WHILE loops.
DBCC SQLPERF(LOGSPACE);
This will show you the percentage of log space used. On running the first set of inserts on my database, the log
went from 2.6 percent used to 29 percent. When running the second set of inserts, the log grew about 6 percent.
The best way is to work with sets of data rather than individual rows. A WHILE loop can be an inherently costly
operation, like a cursor (more details on cursors in Chapter 22). So, running a query that avoids the WHILE loop and
instead works from a set-based approach is even better.
DECLARE @Count INT = 1;
BEGIN TRANSACTION
WHILE @Count <= 10000
BEGIN
INSERT INTO dbo.Test1
(C1)
VALUES (@Count % 256);
SET @Count = @Count + 1;
END
COMMIT