Excessive memory grants from indexed foreign key cascade delete
I believe you're encountering the same bug in the new Cardinality Estimator that I did. I wrote about the problem here, with a more-detailed look here.

In short, the new Cardinality Estimator has a bug when estimating rows to be deleted on a cascading delete. If the driving value on the parent table is far enough outside its histogram, the CE will assume that every single row of the child table gets deleted.

Lucky you though, my server took the high estimates and decided to start scanning instead :(

Also luckily for you, you're dealing with a proc instead of ORM madness, so there are viable solutions. One would be to add DBCC TRACEOFF( 4199) to your procedure (potentially causing permissions issues). That doesn't future-proof you though, since in 2016+ 4199 status doesn't matter for the CE bug. Another option might be to add OPTION(OPTIMIZE FOR (@1 = &lt;some middle value&gt;)).

Since you're not getting a scanning plan, you could also just strongarm the memory grant with MAX_GRANT_PERCENT.