Wide vs. Narrow Plans

Deadlock Troubleshooting, Part 3

Bart Duncan's SQL Weblog — Tue, 01 May 2007 22:08:22 GMT

Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock

re: Wide vs. Narrow Plans

Michael Teper — Thu, 26 Jul 2007 01:47:27 GMT

Bart, is there a way of forcing narrow vs. wide plan?

Thanks!

re: Wide vs. Narrow Plans

bartd — Thu, 26 Jul 2007 02:10:48 GMT

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.

Optimized Non-clustered Index Maintenance

Craig Freedman's WebLog — Wed, 15 Aug 2007 19:51:16 GMT

Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the list

MSDN Blog Postings » Optimized Non-clustered Index Maintenance

MSDN Blog Postings » Optimized Non-clustered Index Maintenance — Wed, 15 Aug 2007 22:37:18 GMT

PingBack from http://msdnrss.thecoderblogs.com/2007/08/15/optimized-non-clustered-index-maintenance/