Updating a table which contains an Image datatype column

Question

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF.

I have a requirement to update one of the other columns in the table, however the query is taking a ludicrously long time to complete - that's if it does complete at all.

The table is the following format:

ID (int), HexData (image), FileName(nvar), Filetype(nvar), folder(nvar), user(int)

The query used is simply -

update [database].[dbo].[myTable]
set user = 5
where id = 2

This is a test table on a test database, not the live system. There are only 5 rows, no indexes (including clustered), and no other tables in the database.

Answer 1

The table in question is a heap. This storage structure does not release pages made empty by delete operations, unless the delete occurs while an exclusive table lock is taken (and not always then).

From DELETE (Transact-SQL) in the documentation:

To delete rows in a heap and deallocate pages, use one of the following methods.
Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated.

The upshot is that your table likely contains a large number of empty pages allocated for rows the heap held in the past. You can check this using:

SELECT
 DDIPS.index_id,
 DDIPS.partition_number,
 DDIPS.index_type_desc,
 DDIPS.alloc_unit_type_desc,
 DDIPS.index_depth,
 DDIPS.index_level,
 DDIPS.page_count,
 DDIPS.record_count,
 DDIPS.ghost_record_count,
 DDIPS.version_ghost_record_count,
 DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
 (
 DB_ID(),
 OBJECT_ID(N'dbo.Heap', 'U'), -- Your table name here
 0,
 NULL,
 'DETAILED'
 ) AS DDIPS;

Without any indexes, the only way for the SQL Server to process the update query is to employ a full table scan. This will touch every data page allocated to the table, including the empty ones. If there are many pages that must be brought into memory from permanent storage, this can take a long time.

The solution is to rebuild the heap table using ALTER TABLE <table_name> REBUILD, or create a clustered index.

The image data type has been deprecated in favour of varbinary(max) for a very long time now. If you have any freedom to change this, you probably should. Even so, it is also possible that the existence of this LOB column is causing the slowness through a quite separate mechanism I describe in detail here:

• Deletes that Split Pages and Forwarded Ghosts

From that article:

Summary

If you ever wonder why your deletes are so slow, it is worth checking to see if you are suffering from page splitting due to an enabled trigger and a table definition that allows for LOB allocations or ROW_OVERFLOW.

Any table with a LOB column (including the max data types) qualifies, as does one with even a surprisingly small number of variable-length columns, as shown in the examples in this post.

This is a great reason to avoid using 'max' or old-style LOB data types unnecessarily, and to be careful about the maximum length of 'normal' variable-length data types too.

Remember, it is the potential maximum row size that is important, not the actual row size.