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])
)
The table is clustered by SessionGUID , so the 240 rows are physically together.
The table has no triggers on it.
The operation takes:
The table contains 11 indexes:
SessionGUID ) How can i figure out why this delete operation is performing 14,340 reads, and takes 11 seconds?
Avg. Disk Read Queue Length Avg. Disk Read Queue Length reaches 0.8 Avg. Disk sec/Read Avg. Disk sec/Read never exceeds 4ms Avg. Disk Write Queue Length Avg. Disk Write Queue Length reaches 0.04 Avg. Disk sec/Write Avg. Disk sec/Write never exceeds 4ms 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.
Counter intuitive that it can be so difficult for 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
DBCC INDEXDEFRAG (0, 'TransactionEntries', 'TransactionEntries17')
since INDEXDEFRAG is an "online operation" (ie 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.
What say you; 50% fragmentation, and only 12% scan density, cause horrible index scan performance?
As @JoeStefanelli points out in comments, it's the extra non-clustered indexes.
You are deleting 240 rows from the table.
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.
I think the indexing might be the likeliest culprit but I wanted to throw out another possibility. You mentioned no triggers, but are there any tables that have a foreign key relationship to this table? They would have to be checked to make sure no records are in them and if you have cascade delete turned on, those records would have to be deleted as well.
Having banged my head on many-a-SQL performance issue, my standard operating procedure for something like this is to: