11秒删除SQL Server中的240行 [英] 11 seconds to delete 240 rows in SQL Server

问题描述

我正在运行一个删除语句:

i am running a delete statement:

DELETE FROM TransactionEntries
WHERE SessionGUID = @SessionGUID

删除的实际执行计划为:

The actual execution plan of the delete is:

Execution Tree
--------------
Clustered Index Delete(
   OBJECT:([GrobManagementSystemLive].[dbo].[TransactionEntries].IX_TransactionEntries_SessionGUIDTransactionGUID]), 
   WHERE:([TransactionEntries].[SessionGUID]=[@SessionGUID])
)

该表由 SessionGUID 聚类，因此 240 行物理上在一起.

The table is clustered by SessionGUID, so the 240 rows are physically together.

该表上没有触发器.

操作需要:

• 持续时间:11821 毫秒
• CPU:297
• 读取:14340
• 写入:1707

该表包含 11 个索引:

The table contains 11 indexes:

• 1 个聚集索引 (SessionGUID)
• 1 个唯一(主键)索引
• 其他 9 个非唯一、非聚集索引

我怎样才能弄清楚为什么这个 delete 操作正在执行 14,340 次读取，并且需要 11 秒?

How can i figure out why this delete operation is performing 14,340 reads, and takes 11 seconds?

• 平均.磁盘读取队列长度达到0.8
• 平均.磁盘秒/读永远不会超过4ms
• 平均.磁盘写入队列长度达到0.04
• 平均.磁盘 sec/Write 永远不会超过 4ms

• the Avg. Disk Read Queue Length reaches 0.8
• the Avg. Disk sec/Read never exceeds 4ms
• the Avg. Disk Write Queue Length reaches 0.04
• the Avg. Disk sec/Write never exceeds 4ms

什么其他读取?执行计划没有说明正在读取什么.

What are the other reads for? The execution plan gives no indication of what it's reading.

更新:

EXECUTE sp_spaceused TransactionEntries

TransactionEntries  
  Rows      6,696,199
  Data:     1,626,496 KB (249 bytes per row)
  Indexes:  7,303,848 KB (1117 bytes per row)
  Unused:      91,648 KB    
            ============
  Reserved: 9,021,992 KB (1380 bytes per row)

每行 1,380 字节，240 行，即 340 kB 被删除.

With 1,380 bytes per row, and 240 rows, that's 340 kB to be deleted.

反直觉，340 kB 可能如此困难.

Counter intuitive that it can be so difficult for 340 kB.

更新二:碎片化

Name                           Scan Density  Logical Fragmentation
=============================  ============  =====================
IX_TransactionEntries_Tran...  12.834        48.392
IX_TransactionEntries_Curr...  15.419        41.239
IX_TransactionEntries_Tran...  12.875        48.372
TransactionEntries17           98.081         0.0049325
TransactionEntries5            12.960        48.180
PK_TransactionEntries          12.869        48.376
TransactionEntries18           12.886        48.480
IX_TranasctionEntries_CDR...   12.799        49.157
IX_TransactionEntries_CDR...   12.969        48.103
IX_TransactionEntries_Tra...   13.181        47.127

我整理了TransactionEntries17

DBCC INDEXDEFRAG (0, 'TransactionEntries', 'TransactionEntries17')

因为 INDEXDEFRAG 是一个在线操作"(即它只持有 IS 意图共享锁).我打算然后手动对其他人进行碎片整理，直到业务运营打电话说系统已经死了 - 他们转而在纸上做所有事情.

since INDEXDEFRAG is an "online operation" (i.e. it only holds IS Intent Shared locks). i was going to then manually defragment the others until the business operations called, saying that the system is dead - and they switched to doing everything on paper.

你说什么；50% 的碎片，只有 12% 的扫描密度，导致可怕的索引扫描性能?

What say you; 50% fragmentation, and only 12% scan density, cause horrible index scan performance?

推荐答案

正如@JoeStefanelli 在评论中指出的那样，它是额外的非聚集索引.

As @JoeStefanelli points out in comments, it's the extra non-clustered indexes.

您要从表中删除 240 行.

You are deleting 240 rows from the table.

这相当于 2640 个索引行，其中 240 个包含表中的所有字段.

This equates to 2640 index rows, 240 of which include all fields in the table.

根据它们的宽度和包含的字段数量，这可能等同于您看到的所有额外阅读活动.

Depending on how wide they are and how many included fields you have, this could equate to all the extra read activity you are seeing.

非聚集索引行肯定不会在磁盘上组合在一起，这会增加延迟.

The non-clustered index rows will definitely NOT be grouped together on disk, which will increase delays.

这篇关于11秒删除SQL Server中的240行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！