Method to update LOB data with minimal log

Use the .WRITE (expression, @Offset,@Length) clause to perform a partial or full update of varchar(max)nvarchar(max), and varbinary(max) data types.

For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple.

UPDATE <table_or_view_name>
SET column_name.WRITE (expression, @offset, @length)
FROM <table_source>
WHERE <search_condition>

expression is the value that is copied to column_nameexpression must evaluate to or be able to be implicitly cast to the column_name type. If expression is set to NULL, @Length is ignored, and the value in column_nameis truncated at the specified @Offset.

@Offset is the starting point in the value of column_name at which expression is written. @Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the length of the column_name value, the Database Engine returns an error. If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value. If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.

@Length is the length of the section in the column, starting from @Offset, that is replaced by expression@Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

The regular update statement results in overwriting the entire string using full logging and it’s very inefficient when dealing with large value updates.

Note: The update using WRITE method will fail if the value is null.

More information about performance in my tip on MSSQLTips



10 Configurations Best Practices

downloadThe first checklist before start using any application using SQL Server should be the default configurations.

What configurations I used to change?

  1. Max Server Memory: SQL Server will grab all memory available and that will cause concurrency with the OS
  2. MAXDOP: for OLTP also yes, the good thing is to use it
  3. Cost Threshold for Parallelism: the default value is too low, and the number will depend on the environment, I used to monitor and change as needed. I use to start with 40
  4. Model File Sizes: the model database will provide information when need to create more databases. (initial size and autogrowth are way too small and need to avoid file system fragmentation)
  5. Tempdb Files: look how many CPUs in the server and if the number is less than 8, so would be the number of CPUs, if it is more, so, start with 8 and the files should be the same size and the same autogrowth.
  6. Database Mail: used to send alerts, very important to get notified when something bad happens.
  7. Backup Compression: the benefit will be much more than the CPU cost.
  8. Remote Dedicated Administrator Connection: provides dedicated CPU, memory, and scheduler and by default only works via RPD or physically on server
  9. Maintenance: start as soon as possible with checkdb, index maintenance, statistics, backups. In this case, I like Ola Hallengren SQL Server solution.
  10. The last but not least, Security: this is the most important in the list, user accounts or even DBAs as sysadmin, look my tip in MSSQLTips. In addition, disable sa, disable unnecessary logins.