更新包含 Image 數據類型列的表

我有一個包含幾列的表，其中一個是圖像數據類型，用於儲存 BLOB 數據，可以是從 jpg 到大型 PDF 的任何內容。

我需要更新表中的其他列之一，但是查詢需要很長時間才能完成 - 如果它確實完成的話。

表格格式如下：

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

使用的查詢很簡單 -

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

這是測試數據庫上的測試表，而不是實時系統。數據庫中只有 5 行，沒有索引（包括聚群），也沒有其他表。

有問題的表是一個堆。此儲存結構不會釋放由刪除操作變為空的頁面，除非在獲取排他表鎖定時發生刪除（並且並非總是如此）。

來自文件中的DELETE（Transact-SQL）：

要刪除堆中的行並釋放頁面，請使用以下方法之一。在語句中

指定TABLOCK提示。DELETE使用TABLOCK提示會導致刪除操作在表上使用排他鎖，而不是行或頁鎖。這允許頁面被釋放。

結果是您的表可能包含大量為過去持有的行分配的空頁。您可以使用以下方法進行檢查：

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;

在沒有任何索引的情況下，SQL Server 處理更新查詢的唯一方法是使用全表掃描。這將觸及分配給表的每個數據頁，包括空的。如果有許多頁面必須從永久儲存中帶入記憶體，這可能需要很長時間。

解決方案是使用 重建堆表ALTER TABLE <table_name> REBUILD，或創建聚集索引。

很長一段時間以來，圖像數據類型已被棄用，取而代之的是varbinary(max) 。如果您有任何改變這一點的自由，您可能應該這樣做。即便如此，這個 LOB 列的存在也有可能通過我在 SQLblog.com 上詳細描述的一種完全獨立的機制導致緩慢：

• 刪除拆分頁面和轉發的幽靈

從那篇文章：

概括

如果您想知道為什麼刪除速度如此之慢，那麼值得檢查一下您是否由於啟用的觸發器和允許 LOB 分配的表定義或ROW_OVERFLOW.

任何具有 LOB 列（包括最大數據類型）的表都符合條件，即使是具有極少數可變長度列的表也是如此，如本文中的範例所示。

這是避免不必要地使用“max”或舊式 LOB 數據類型的一個重要原因，並且也要注意“正常”可變長度數據類型的最大長度。

請記住，重要的是潛在的最大行大小，而不是實際的行大小。

引用自：https://dba.stackexchange.com/questions/123611