When creating an index on a temp table within a single stored procedure, the optimizer actually will consider such an index for use. It appears that the proc is basically being ‘recompiled’ at run time. Is this correct? (i.e. Basically the same ‘cost’ is added to such a proc as one that was created or executed ‘WITH RECOMPILE’.
Answer: What’s happening is that the ‘create index’ statement is indicating a change to the ‘schema’ of the table. The index will be create and immediately following the procedure will be recompile. Processing of the procedure will pick up again just following the ‘create index’, thus the index and it’s statistics are known to the optimizer.
Why “enable sort-merge join and JTC” is combined in one configuration parameter?
Answer: JTC was linked to sort-merge join because there was concern that the extra plans that are costed could lead to longer optimization times. However, you can use trace 334 to enable JTC alone. This can be used at the session level or you can boot with it. I strongly suggest you test it out well before implementing it though.
What is the difference between “update statistics” and “update index statistics?”
Answer: The ‘update statistics table_name [index_name]’ syntax will update/create statistics on the leading column of all indexes on the table or index you specify. You can see which column is the leading column by running sp_helpindex or sp_help on the table. The ‘update index statistics table_name [index_name]’ will update/create statistics on all columns that are included in an index. This will be done for all indexes on the table or for the specified index.