Optimized Non-clustered Index Maintenance in Per-Index Plans

Recursive CTEs

Craig Freedman's SQL Server Blog — Thu, 25 Oct 2007 22:06:49 GMT

One of the most important uses of CTEs is to write recursive queries. In fact, CTEs provide the only

Aggregation WITH CUBE

Craig Freedman's WebLog — Thu, 27 Sep 2007 21:39:17 GMT

In my last post, I wrote about how aggregation WITH ROLLUP works. In this post, I will discuss how aggregation

re: Optimized Non-clustered Index Maintenance in Per-Index Plans

Craig Freedman — Thu, 30 Aug 2007 17:40:35 GMT

Also, starting with SQL Server 2008 CTP5, the USE PLAN query hint will work with INSERT, UPDATE, and DELETE statements. However, because USE PLAN requires a valid XML plan, it is most useful only when you can generate the plan you want through some other means.

re: Optimized Non-clustered Index Maintenance in Per-Index Plans

Craig Freedman — Wed, 29 Aug 2007 20:52:47 GMT

There is no easy way to force narrow or wide plans. For testing purposes (on a development system only), you can fake the stats as I've demonstrated above. Alternatively, Stefano from the optimizer team suggests forcing a wide plan by including a non-persisted computed column in the index. Note that in some cases, the optimizer cannot choose a narrow plan. (This is why we can force a wide plan by including a non-persisted computed column in an index.)

re: Optimized Non-clustered Index Maintenance in Per-Index Plans

AlexLopez — Fri, 24 Aug 2007 22:24:18 GMT

Hi Craig,

Nice post.

I was wondering if it is possible to force the query optimizer to use a narrow or a wide plan. I thought the USE PLAN query hint could be the solution but then I realized that it cannot be specified with INSERT, UPDATE, or DELETE statements. Is there a way to do it?

Thanks,