結論
在既有的資料表上，新增加資料行，使用資料類型：rowversion，這會當下耗用磁碟I/O資源。
解決方向：
(1) 請安排於 系統離峰時間 建置。
(2) 搭配 高速儲存設備(例如：Flash)來縮短所需的建置時間!
-- P01_查詢目前資料庫內，每一個資料表的使用空間資訊
-- P02_新增 資料行 rowversion，觀察耗費的時間
-- P03_觀察 資料表
-- P04_已經新增資料行rowversion_SSMS
範例程式碼
-- 01_查詢目前資料庫內，每一個資料表的使用空間資訊 USE TSIO GO SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)', a2.create_date N'建立日期', a2.modify_date N'修改日期' FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY a1.data DESC GO -- 02_新增 資料行 rowversion，觀察耗費的時間 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS TIME ON GO ALTER TABLE TS_PK_2GB ADD vercol rowversion; GO -- 03_觀察 資料表 SELECT TOP 100 * FROM TS_PK_2GB GO
參考資料
認識 資料類型：rowversion，在資料庫內自動產生唯一的二進位數字(1)
http://sharedderrick.blogspot.tw/2016/03/rowversion1.html
