Sql-Server
為什麼 DELETE 會對性能產生揮之不去的影響？
最後是一個測試腳本，用於比較@table 變數和#temp 表之間的性能。我想我已經正確設置了——性能計時是在 DELETE/TRUNCATE 命令*之外進行的。*我得到的結果如下（以毫秒為單位）。
@Table Variable #Temp (delete) #Temp (truncate) --------------- -------------- ---------------- 5723 5180 5506 15636 14746 7800 14506 14300 5583 14030 15460 5386 16706 16186 5360只是為了確保我是理智的，這表明 CURRENT_TIMESTAMP (aka ) 是在語句時採用的，而不是在批處理時採用的，因此 TRUNCATE/DELETE 與語句
GetDate()之間應該沒有互動。SET @StartTime = CURRENT_TIMESTAMPselect current_timestamp waitfor delay '00:00:04' select current_timestamp ----------------------- 2012-10-21 11:29:20.290 ----------------------- 2012-10-21 11:29:24.290當使用DELETE清表時，第一次執行和後續執行之間的跳轉是相當一致的。我對DELETE的理解缺少什麼？我已經重複了很多次，交換了順序，將 tempdb 調整為不需要增長等。
CREATE TABLE #values ( id int identity primary key, -- will be clustered name varchar(100) null, number int null, type char(3) not null, low int null, high int null, status smallint not null ); GO SET NOCOUNT ON; DECLARE @values TABLE ( id int identity primary key clustered, name varchar(100) null, number int null, type char(3) not null, low int null, high int null, status smallint not null ); DECLARE @ExecutionTime TABLE( Duration bigINT ) DECLARE @StartTime DATETIME, @i INT = 1; WHILE (@i <= 5) BEGIN DELETE @values; DBCC freeproccache With NO_InfoMSGS; DBCC DROPCLEANBUFFERS With NO_InfoMSGS; SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() /****************** measured process ***********************/ INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000; /**************** end measured process *********************/ INSERT @ExecutionTime SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP) SET @i += 1 END -- WHILE SELECT DurationInMilliseconds = Duration FROM @ExecutionTime GO -- Temporary table DECLARE @ExecutionTime TABLE( Duration bigINT ) DECLARE @StartTime DATETIME, @i INT = 1; WHILE (@i <= 5) BEGIN delete #values; -- TRUNCATE TABLE #values; DBCC freeproccache With NO_InfoMSGS; DBCC DROPCLEANBUFFERS With NO_InfoMSGS; SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate() /****************** measured process ***********************/ INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000; /**************** end measured process *********************/ INSERT @ExecutionTime SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP) SET @i += 1 END -- WHILE SELECT DurationInMilliseconds = Duration FROM @ExecutionTime GO DROP TABLE #values SET NOCOUNT OFF;
這種差異似乎只適用於對像是 B+樹的情況。當刪除
primary key表變數時，它是一個堆，我得到了以下結果2560 2120 2080 2130 2140但是對於 PK，我在測試中也發現了類似的模式，典型的結果如下。
+--------+--------+---------+-------------------+ | @table | #table | ##table | [permanent_table] | +--------+--------+---------+-------------------+ | 2670 | 2683 | 9603 | 9703 | | 6823 | 6840 | 9723 | 9790 | | 6813 | 6816 | 9626 | 9703 | | 6883 | 6816 | 9600 | 9716 | | 6840 | 6856 | 9610 | 9673 | +--------+--------+---------+-------------------+我的理論是，在對本地臨時 B+ 樹進行批量插入時，有一些優化可用，這些優化僅適用於尚未分配任何頁面的情況。
我基於以下觀察。
- 在執行各種版本的測試程式碼時，我只看到了這種模式
@table_variables和#temp表格。tempdb也不是表中的永久##表。- 為了獲得較慢的性能，不必事先從表中添加和刪除大量行。只需添加一行並將其保留在那裡就足夠了。
TRUNCATE從表中釋放所有頁。DELETE不會導致表中的最後一頁被釋放。- 使用 VS 2012 分析器表明，在更快的情況下 SQL Server 使用不同的程式碼路徑。36% 的時間花在了較慢的情況下
sqlmin.dll!RowsetBulk::InsertRow，而 61% 的時間花在sqlmin.dll!RowsetNewSS::InsertRow了較慢的情況下。跑步
SELECT * FROM sys.dm_db_index_physical_stats(2,OBJECT_ID('tempdb..#values'),1,NULL, 'DETAILED')刪除返回後
+-------------+------------+--------------+--------------------+ | index_level | page_count | record_count | ghost_record_count | +-------------+------------+--------------+--------------------+ | 0 | 1 | 0 | 1 | | 1 | 1 | 1 | 0 | | 2 | 1 | 1 | 0 | +-------------+------------+--------------+--------------------+我發現通過啟用跟踪標誌 610可以在一定程度上減少時間差異。
這具有顯著減少後續插入的日誌記錄數量的效果（從 350 MB 下降到 103 MB，因為它不再記錄單個插入的行值），但這對第 2 次和後續情況
@table的計時只有很小的改進#table差距仍然存在。跟踪標誌顯著提高了插入其他兩種表類型的一般性能。+--------+--------+---------+-------------------+ | @table | #table | ##table | [permanent_table] | +--------+--------+---------+-------------------+ | 2663 | 2670 | 5403 | 5426 | | 5390 | 5396 | 5410 | 5403 | | 5373 | 5390 | 5410 | 5403 | | 5393 | 5410 | 5406 | 5433 | | 5386 | 5396 | 5390 | 5420 | +--------+--------+---------+-------------------+通過查看事務日誌，我注意到針對空本地臨時表的初始插入似乎記錄得更少（96 MB）。
值得注意的是，與較慢情況下的 over 相比，這些較快的插入只有
657事務（LOP_BEGIN_XACT/對）。特別是操作似乎大大減少。較慢的情況對於表中的每個頁面都有一個事務日誌條目（大約），而在快速情況下只有這樣的條目。LOP_COMMIT_XACT``10,000``LOP_FORMAT_PAGE``10,270``4這三種情況下使用的日誌如下（我已經刪除了更新系統基表的日誌記錄以減少文本量，但它們仍然包含在總數中）
記錄第一次插入
@table_var(96.5 MB)+-----------------------+----------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-----------------------+----------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_XACT | LCX_NULL | NULL | 83876 | 658 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 34164 | 657 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 120 | 3 | | LOP_FORMAT_PAGE | LCX_HEAP | dbo.#531856C7 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | dbo.#531856C7 | 84 | 1 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_HOBT_DDL | LCX_NULL | NULL | 216 | 6 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 320 | 5 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100240000 | 2506000 | | LOP_INSERT_ROWS | LCX_HEAP | dbo.#531856C7 | 72 | 1 | | LOP_MODIFY_ROW | LCX_IAM | dbo.#531856C7 | 88 | 1 | | LOP_MODIFY_ROW | LCX_PFS | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 158592 | 1848 | | LOP_MODIFY_ROW | LCX_PFS | dbo.#531856C7 | 80 | 1 | | LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 216016 | 2455 | | LOP_SET_BITS | LCX_GAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84360 | 1406 | | LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 147120 | 2452 | | LOP_SET_BITS | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 84360 | 1406 | | LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 147120 | 2452 | | Total | NULL | NULL | 101209792 | 2519475 | +-----------------------+----------+----------------------------------------------+---------------+---------+記錄後續插入 TF 610 關閉 (350 MB)
+-----------------------+--------------------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-----------------------+--------------------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_CKPT | LCX_NULL | NULL | 96 | 1 | | LOP_BEGIN_XACT | LCX_NULL | NULL | 1520696 | 12521 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 651040 | 12520 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 40 | 1 | | LOP_DELETE_SPLIT | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 2160 | 36 | | LOP_END_CKPT | LCX_NULL | NULL | 136 | 1 | | LOP_FORMAT_PAGE | LCX_HEAP | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 859236 | 10229 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 3108 | 37 | | LOP_HOBT_DDL | LCX_NULL | NULL | 648 | 18 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 657088 | 10267 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100239960 | 2505999 | | LOP_INSERT_ROWS | LCX_CLUSTERED | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 258628000 | 2506000 | | LOP_INSERT_ROWS | LCX_HEAP | dbo.#531856C7 | 72 | 1 | | LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 1042776 | 10302 | | LOP_MODIFY_HEADER | LCX_HEAP | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 859236 | 10229 | | LOP_MODIFY_HEADER | LCX_INDEX_INTERIOR | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 3192 | 38 | | LOP_MODIFY_ROW | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 704 | 8 | | LOP_MODIFY_ROW | LCX_PFS | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 934264 | 11550 | | LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 783984 | 8909 | | LOP_SET_BITS | LCX_GAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 76980 | 1283 | | LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 534480 | 8908 | | LOP_SET_BITS | LCX_IAM | dbo.#4F47C5E3.PK__#4F47C5E__3213E83F51300E55 | 76980 | 1283 | | LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 534480 | 8908 | | LOP_SHRINK_NOOP | LCX_NULL | NULL | 32 | 1 | | LOP_XACT_CKPT | LCX_NULL | NULL | 92 | 1 | | Total | NULL | NULL | 367438748 | 5119297 | +-----------------------+--------------------+----------------------------------------------+---------------+---------+記錄後續插入 TF 610 (103 MB)
+-------------------------+-------------------------+----------------------------------------------+---------------+---------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | +-------------------------+-------------------------+----------------------------------------------+---------------+---------+ | LOP_BEGIN_CKPT | LCX_NULL | NULL | 192 | 2 | | LOP_BEGIN_XACT | LCX_NULL | NULL | 1339796 | 11099 | | LOP_BULK_EXT_ALLOCATION | LCX_NULL | NULL | 20616 | 162 | | LOP_COMMIT_XACT | LCX_NULL | NULL | 577096 | 11098 | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | NULL | 40 | 1 | | LOP_DELETE_SPLIT | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 2160 | 36 | | LOP_END_CKPT | LCX_NULL | NULL | 272 | 2 | | LOP_FORMAT_PAGE | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 863520 | 10280 | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 84 | 1 | | LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 3108 | 37 | | LOP_HOBT_DELTA | LCX_NULL | NULL | 666496 | 10414 | | LOP_IDENT_NEWVAL | LCX_NULL | NULL | 100239960 | 2505999 | | LOP_INSERT_ROWS | LCX_CLUSTERED | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 23544 | 218 | | LOP_INSERT_ROWS | LCX_HEAP | dbo.#719CDDE7 | 72 | 1 | | LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 1042776 | 10302 | | LOP_MODIFY_HEADER | LCX_BULK_OPERATION_PAGE | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 780216 | 10266 | | LOP_MODIFY_HEADER | LCX_HEAP | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 1718472 | 20458 | | LOP_MODIFY_HEADER | LCX_INDEX_INTERIOR | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 3192 | 38 | | LOP_MODIFY_ROW | LCX_IAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 704 | 8 | | LOP_MODIFY_ROW | LCX_PFS | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 114832 | 1307 | | LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 231696 | 2633 | | LOP_RANGE_INSERT | LCX_NULL | NULL | 48 | 1 | | LOP_SET_BITS | LCX_GAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 77100 | 1285 | | LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 157920 | 2632 | | LOP_SET_BITS | LCX_IAM | dbo.#6DCC4D03.PK__#6DCC4D0__3213E83F6FB49575 | 77100 | 1285 | | LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 157920 | 2632 | | LOP_XACT_CKPT | LCX_NULL | NULL | 92 | 1 | | Total | NULL | NULL | 108102960 | 2602218 | +-------------------------+-------------------------+----------------------------------------------+---------------+---------+