Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock
Bart, is there a way of forcing narrow vs. wide plan?
No, unfortunately not. If you have a parameter sniffing issue where a wide plan occasionally gets stuck in cache where you want a narrow plan (or vice versa), you might consider:
- An "OPTION (OPTIMIZE FOR @param=...)" hint. Plug in a parameter there that causes the desired plan. If the query text can't be easily modified, you could apply this hint with a plan guide.
- An "OPTION (RECOMPILE)" so that everyone gets a plan custom tailored to their params.
Both of the above are only available on SQL 2005 and later. On SQL 2000 you could approximate the recompile hint with a WITH RECOMPILE (on stored proc definition) or dynamic SQL.
Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the list
PingBack from http://msdnrss.thecoderblogs.com/2007/08/15/optimized-non-clustered-index-maintenance/