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])
)

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

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

The table has no triggers on it.

该表没有触发器。

The operation takes:

该操作需要：

• Duration: 11821 ms
• 持续时间：11821毫秒
• CPU: 297
• CPU：297
• Reads: 14340
• 阅读：14340
• Writes: 1707
• 写道：1707

The table contains 11 indexes:

该表包含11个索引：

• 1 clustered index (SessionGUID)
• 1个聚簇索引（SessionGUID）
• 1 unique (primary key) index
• 1个唯一（主键）索引
• 9 other non-unique, non-clustered indexes
• 9个其他非唯一的非聚集索引

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

我怎么能弄清楚为什么这个删除操作执行14,340读取，需要11秒？

• the Avg. Disk Read Queue Length reaches 0.8
• 平均磁盘读取队列长度达到0.8
• the Avg. Disk sec/Read never exceeds 4ms
• 平均磁盘秒/读取不会超过4毫秒
• the Avg. Disk Write Queue Length reaches 0.04
• 平均磁盘写入队列长度达到0.04
• the Avg. Disk sec/Write never exceeds 4ms
• 平均磁盘秒/写永远不会超过4毫秒

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

其他读物有哪些？执行计划没有说明它正在阅读什么。

---

Update:

更新：

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)

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

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

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

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

Update Two: Fragmentation

更新二：碎片

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

i defragmented TransactionEntries17

我对TransactionEntries17进行了碎片整理

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

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.

因为INDEXDEFRAG是一个“在线操作”（即它只持有IS Intent共享锁）。我打算对其他人进行手动碎片整理，直到业务操作调用，说系统已经死了 - 他们转而在纸上做所有事情。

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

怎么说你; 50％的碎片，只有12％的扫描密度，导致可怕的索引扫描性能？

3 个解决方案