/* In the demo below we'll compare three different approaches to updating a lot of rows:

		Monolithic - updating all the rows using a single statement
		Cursor -	updating one row at a time
		Batched -	updating in batches (e.g., 10K rows at a time)

The monolithic option runs a long time, and should you need to cancel it, 
the rollback will take at least as long as the query's run time.  It's 
also going to hold large-scope locks for long durations blocking other processes.
Another risk is that since the tran log records recording the changes can't be
truncated until the transaction commits (once *all* the rows have been updated) making it more
likely that you'll fill up the tran log. Check out Accelerated Database Recovery that can help with
this issue.

Using a cursor to update rows one at a time allows you to stop processing on a 
dime and won't fill up the log because it can be truncated after each one-row
transaction. The downside is that this triples the logging load compared to 

It also makes for lots of small, inefficient writes to the tran log file on 
disk since log buffers must be flushed to disk (persisted) whenever a COMMIT is issued.  Fewer 
flushes of fuller buffers (60 KB capacity) is much more efficient. 
Option 2 is also the slowest option.

Option 3 will typically be our best performer. Batched updates get the work done faster with less
blocking, quicker rollbacks (if necessary), and a relatively light logging load since multiple 
updates are bundled into each transaction:

The smaller transactions allow completed work to persist, even if there's a later 
rollback and allow better reuse of the log.
	
In the demo below we'll try out each approach comparing their durations, use
of log space, and writes to disk (number and size).

---------------------------------------------------------------------------

For added amusement, fire up PerfMon with the counters below for just the
AdventureWorks database.  You'll find them in the Databases object.

Log Bytes Flushed/sec 
Log File(s) Used Size (KB) 
Log Flushes/sec
Percent Log Used 

The output shows the impact of the update-one-row-at-a time approach.  
	
There are commented-out WAIFOR statements that you can use to space out the 
metrics from each test on the PerfMon graph.

---------------------------------------------------------------------------

It's probably easiest to run the script as one batch, but it's set up so you
can run each test separately, if you'd rather.

And - there's a little bonus material at the end comparing the performance
and logging loads of delete and truncate.
*/

SET NOCOUNT ON;

USE AdventureWorks2025;	/* any version will work */
GO

-- To prevent SQL Server from truncating the log using automatic checkpoints,
-- we'll work in Full Recovery Mode.
ALTER DATABASE AdventureWorks2025 SET RECOVERY FULL;
GO 

-- The database isn't really in full recovery until after an initial backup is made
BACKUP DATABASE AdventureWorks2025 TO DISK = 'NUL';
GO

-- Create a demo table and insert enough rows to make updates (sort of) costly
CREATE TABLE Sales.SalesDetail (
	DemoDetailId INT IDENTITY PRIMARY KEY,
	SalesOrderID INT NOT NULL,
	SalesOrderDetailID INT NOT NULL,
	CarrierTrackingNumber NVARCHAR(25) NULL,
	OrderQty SMALLINT NOT NULL,
	ProductID INT NOT NULL,
	SpecialOfferID INT NOT NULL,
	UnitPrice MONEY NOT NULL,
	UnitPriceDiscount MONEY NOT NULL,
	LineTotal NUMERIC(38, 6) NOT NULL,
	rowguid UNIQUEIDENTIFIER NOT NULL,
	ModifiedDate DATETIME NOT NULL );
GO

INSERT INTO Sales.SalesDetail ( SalesOrderID, SalesOrderDetailID,
	CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice,
	UnitPriceDiscount, LineTotal, rowguid, ModifiedDate )
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
    ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal,
    rowguid, ModifiedDate
FROM Sales.SalesOrderDetail;
GO 

-- SELECT COUNT(*) FROM Sales.SalesDetail;		/* 121317 rows */

-- Optional pause to space out the test results on the PerfMon graph, if using
-- WAITFOR DELAY '00:00:05';

/*****************************************************************************/

/*
	First let's update the entire table with a single statement
*/

-- Backup the log to allow truncation of committed transaction records
BACKUP LOG AdventureWorks2025 TO DISK = 'NUL'; 

/* Run the block of code below through the next GO */

DECLARE @start_time DATETIME2,
		@log_space_used_begin BIGINT,
		@log_space_used BIGINT,
		@bytes_written BIGINT, 
		@writes INT;

-- Grab starting metrics
SELECT @log_space_used_begin = log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

SELECT @start_time = SYSDATETIME (), @writes = num_of_writes,
    @bytes_written = num_of_bytes_written
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );

-- Update the entire table in one transaction
UPDATE Sales.SalesDetail SET OrderQty += 1;

-- Calculate resouce usage
SELECT @log_space_used = ( log_space_in_bytes_since_last_backup - @log_space_used_begin )
FROM sys.dm_db_log_space_usage;

SELECT 'Monolithic update' AS operation, @log_space_used/1024 AS log_space_used_kb,
    ( num_of_writes - @writes ) AS log_writes,
    ( num_of_bytes_written - @bytes_written )/1024 AS log_kb_written, 
	(( num_of_bytes_written - @bytes_written )/( num_of_writes - @writes ))/1024 AS avg_write_kb,
    DATEDIFF ( MILLISECOND, @start_time, SYSDATETIME ()) AS dur_ms
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );
GO

/*
Since our demo table is pretty small (containing only 121 K pretty narrow rows), 
the update ran quickly.  When I tested with a larger number of rows, the 
cursor portion of the demo ran forever, so we'll just use our imaginations 
and know that against a production-sized table this would run for a long time,
holding an exclusive table lock for the duration, and gradually filling up
the tran log.  And if you decided to cancel the update after it had been running
for an hour or two, you'll have a long -- WAIT.  The roll back will take at least
as long as the transaction ran before you cancelled it.
*/

-- WAITFOR DELAY '00:00:05';

/*****************************************************************************/

/*
Now we'll go to the other extreme and update each row individually using a 
a cursor.  This approach is known as RBAR (pronounced "ree' bar") - Row by 
Agonizing Row.  It's the worst way to get anything done in SQL Server.

Note that using a WHILE loop to achieve the same thing (row-by-row updates)
is equally bad.

Be patient - this takes up to 2 min my server.
*/

-- Backup the log to allow truncation of committed transaction records
BACKUP LOG AdventureWorks2025 TO DISK = 'NUL';

/* Run the block of code below through the next GO */

DECLARE @start_time DATETIME2,
		@log_space_used_begin BIGINT,
		@log_space_used BIGINT,
		@bytes_written BIGINT, 
		@writes INT;

-- Grab starting metrics
SELECT @log_space_used_begin = log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

SELECT @start_time = SYSDATETIME (), @writes = num_of_writes,
    @bytes_written = num_of_bytes_written
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );

-- Update each row individually using a cursor
DECLARE RowsToUpdate 
CURSOR FOR 
SELECT DemoDetailId FROM Sales.SalesDetail;
DECLARE @Id INT;
OPEN RowsToUpdate;
FETCH RowsToUpdate INTO @Id;
WHILE ( @@fetch_status = 0 ) 
BEGIN
	UPDATE Sales.SalesDetail SET OrderQty += 1  WHERE DemoDetailId = @Id;
	FETCH RowsToUpdate INTO @Id;
END;
CLOSE RowsToUpdate;
DEALLOCATE RowsToUpdate;

-- Calculate resouce usage
SELECT @log_space_used = ( log_space_in_bytes_since_last_backup - @log_space_used_begin )
FROM sys.dm_db_log_space_usage;

SELECT 'Cursor update' AS operation, @log_space_used/1024 AS log_space_used_kb,
    ( num_of_writes - @writes ) AS log_writes,
    ( num_of_bytes_written - @bytes_written )/1024 AS log_kb_written,
	(( num_of_bytes_written - @bytes_written )/( num_of_writes - @writes ))/1024 AS avg_write_kb,
    DATEDIFF ( MILLISECOND, @start_time, SYSDATETIME ()) AS dur_ms
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );
GO

/*
This approach keeps locking very granular and brief (good for concurrency), 
but generates a huge logging load and lots and lots of small writes to the 
tran log file on disk - one for each individual commit.  [Changes must be 
persisted to disk before a transaction can commit.]  This heavy logging load
makes this the slowest approach of the three we're testing.
*/

-- WAITFOR DELAY '00:00:05';

/*
Splitting the difference and taking a divide and conquer approach will typically 
be the most efficient means of inserting, updating or deleting a lot of rows.

You'll need to do some testing up front to determine the optimal (fastest)
batch size, then you'll use a looping construct (e.g., WHILE) to update the
table in batches until all the work is completed.

This approach is faster, easier on the tran log and results in less blocking
as fewer resources are locked for shorter durations.  Additionally, should 
you ever need to stop processing, it happens very quickly as there's very
little work to rollback - and you don't lose the work accomplished in previous
iterations.  All good!

I'll use a batch size of 10K below.  It's a good place to start testing in
your production systems (although it actually isn't the fastest option for
my itty-bitty data set).  Try repeating the test with a wide range of values.
*/

-- Backup the log to allow truncation of committed transaction records
BACKUP LOG AdventureWorks2025 TO DISK = 'NUL';

/* Run the block of code below through the next GO */

DECLARE @start_time DATETIME2,
		@log_space_used_begin BIGINT,
		@log_space_used BIGINT,
		@bytes_written BIGINT, 
		@writes INT;

-- Grab starting metrics
SELECT @log_space_used_begin = log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

SELECT @start_time = SYSDATETIME (), @writes = num_of_writes,
    @bytes_written = num_of_bytes_written
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );

-- Update rows in batches 
DECLARE @min_id BIGINT = 1, @rows_updated INT = 1, @batch_size INT = 10000;
WHILE ( @rows_updated > 0 ) 
BEGIN
    BEGIN TRAN;
		UPDATE Sales.SalesDetail
		SET OrderQty += 1
		WHERE DemoDetailId >= @min_id AND DemoDetailId < ( @min_id + @batch_size );
		SET @rows_updated = @@ROWCOUNT;
		SET @min_id += @batch_size;
    COMMIT
END;

-- Calculate resouce usage
SELECT @log_space_used = ( log_space_in_bytes_since_last_backup - @log_space_used_begin )
FROM sys.dm_db_log_space_usage;

SELECT 'Batched (' + CAST(@batch_size AS VARCHAR(10)) + ' rows)' AS operation, @log_space_used/1024 AS log_space_used_kb,
    ( num_of_writes - @writes ) AS log_writes,
    ( num_of_bytes_written - @bytes_written )/1024 AS log_kb_written,
	(( num_of_bytes_written - @bytes_written )/( num_of_writes - @writes ))/1024 AS avg_write_kb,
    DATEDIFF ( MILLISECOND, @start_time, SYSDATETIME ()) AS dur_ms
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );
GO

/*
Notice the variation in execution times and log record generation for each
batch size.  When working with production tables you'll find an optimal batch
size above or below which total run times are slower.  I think my data's a 
little wonky because my table is on the small side, but you can see the trend.

Interestingly, 10K rows often turns out to be the optimal batch size.
*/

-- WAITFOR DELAY '00:00:05';

/*****************************************************************************/
/*****************************************************************************/

/*
Lastly, some bonus material to demonstrate the value of using truncation 
instead of delete where you can.

Truncation doesn't delete individual rows but instead just deallocates the
pages allocated to the table so there's less information to log.

First we'll delete half the table's rows then truncate the remainder.
*/

-- Backup the log to allow truncation of committed transaction records
BACKUP LOG AdventureWorks2025 TO DISK = 'NUL';

/* Run the block of code below through the next GO */

DECLARE @start_time DATETIME2,
		@log_space_used_begin BIGINT,
		@log_space_used BIGINT,
		@bytes_written BIGINT, 
		@writes INT;

-- Grab starting metrics
SELECT @log_space_used_begin = log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

SELECT @start_time = SYSDATETIME (), @writes = num_of_writes,
    @bytes_written = num_of_bytes_written
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );

-- Delete about a third of the table's rows
DELETE TOP ( 40000 ) FROM Sales.SalesDetail;

-- Calculate resouce usage
SELECT @log_space_used = ( log_space_in_bytes_since_last_backup - @log_space_used_begin )
FROM sys.dm_db_log_space_usage;

SELECT 'Delete (40K rows)' AS operation, @log_space_used/1024 AS log_space_used_kb,
    ( num_of_writes - @writes ) AS log_writes,
    ( num_of_bytes_written - @bytes_written )/1024 AS log_kb_written,
	(( num_of_bytes_written - @bytes_written )/( num_of_writes - @writes ))/1024 AS avg_write_kb,
    DATEDIFF ( MILLISECOND, @start_time, SYSDATETIME ()) AS dur_ms
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );
GO

-- WAITFOR DELAY '00:00:05';

/*****************************************************************************/

-- Backup the log to allow truncation of committed transaction records
BACKUP LOG AdventureWorks2025 TO DISK = 'NUL';

/* Run the block of code below through the next GO */

DECLARE @start_time DATETIME2,
		@log_space_used_begin BIGINT,
		@log_space_used BIGINT,
		@bytes_written BIGINT, 
		@writes INT;

-- Grab starting metrics
SELECT @log_space_used_begin = log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

SELECT @start_time = SYSDATETIME (), @writes = num_of_writes,
    @bytes_written = num_of_bytes_written
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );

-- Truncate the remaining rows 
TRUNCATE TABLE Sales.SalesDetail;

-- Calculate resouce usage
SELECT @log_space_used = ( log_space_in_bytes_since_last_backup - @log_space_used_begin )
FROM sys.dm_db_log_space_usage;

SELECT 'Truncate (81K rows)' AS operation, @log_space_used/1024 AS log_space_used_kb,
    ( num_of_writes - @writes ) AS log_writes,
    ( num_of_bytes_written - @bytes_written )/1024 AS log_kb_written,
	(( num_of_bytes_written - @bytes_written )/( num_of_writes - @writes ))/1024 AS avg_write_kb,
    DATEDIFF ( MILLISECOND, @start_time, SYSDATETIME ()) AS dur_ms
FROM sys.dm_io_virtual_file_stats ( DB_ID (), 2 );
GO

/*****************************************************************************/

-- Clean up
IF @@TRANCOUNT > 0 
	ROLLBACK;
GO 

DROP TABLE Sales.SalesDetail;
GO

ALTER DATABASE AdventureWorks2025 SET RECOVERY SIMPLE;
GO