I can think of two possible explanations for why you experience poor performance with that query.
The first relates to blocking. Have you checked the wait events while the query is running? If it is blocked by another session and waiting for a lock then you don't have a performance problem. Instead, you have a concurrency problem.
The second relates to the table and index structure on the table that you are deleting from. The
PriceDiffs table has 194807000 rows. If you don't have an index that can seek on the
UserID column it could take a long time to find the single row to delete. There's also a scan on the
PriceDifferenceRecords table which I assume is there to enforce a constraint or a foreign key, but that table only has 15843 rows.
You might be wondering why the query takes two minutes to run when it has a relatively low estimated subtree cost of 0.0569578. This is probably due to the row goal introduced to the query by your use of
TOP 1. SQL Server scans the
IX_PriceDiffs_PriceDate index to find a matching row but the filter is implemented as predicate instead of a seek predicate. I assume this is because
UserID is not a key column. Thus, SQL Server scans the index in order until it finds a row where
UserID = 384166. If you are really lucky the first matching row could be in the first page of the index. If you are really unlucky the first matching row could be in the last page of the index, so your
DELETE query would scan the entire table to delete just one row.
It's worth noting that deleting just one row from a table often isn't a good way to test performance when you need to delete lots of rows. For a starting point, consider creating an index on
UserId and deleting in batches.