Запрос такой:

ВЫБРАТЬ
    ТЗ.ссылка
ПОМЕСТИТЬ ВТЗ
ИЗ
    &ТаблицаТоваров КАК ТЗ

ИНДЕКСИРОВАТЬ ПО
    ТЗ.ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Идентификаторы.Ссылка,
    Идентификаторы.Владелец
ПОМЕСТИТЬ Товары
ИЗ
    ВТЗ КАК ВТЗ
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Идентификаторы КАК Идентификаторы
        ПО ВТЗ.ссылка = Идентификаторы.Ссылка

ИНДЕКСИРОВАТЬ ПО
    Идентификаторы.Ссылка,
    Идентификаторы.Владелец
;

Судя по профайлеру, до 3000 записей в ТаблицеТоваров - используется кластерный индекс по ссылке справочника (Clustered Index Seek) 30мс. Больше  3000 записей - Index Scan по другим индексам >150мс.
Если принудительно отключить все индексы, кроме кластерного, то используется кластерный, те же 30мс.
Справочник идентификаторы > 1млн записей.
Как забороть, чтобы использовался кластерный индекс, не отключая все остальные индексы?

(24) А сколько всего записей в таблице? Варианты, либо статистика почему-то не актуальна, либо поиск по индексу для записей более 3000 выходит дороже чем просто скан (по расчетам движка SQL)

так, ради эксперимента, что будет

ВЫБРАТЬ
    Идентификаторы.Ссылка,
    Идентификаторы.Владелец
ПОМЕСТИТЬ Товары
ИЗ Справочник.Идентификаторы КАК Идентификаторы
ГДЕ Идентификаторы.Ссылка в (выбрать ВТЗ.ссылка из ВТЗ)

ИНДЕКСИРОВАТЬ ПО
    Идентификаторы.Ссылка,
    Идентификаторы.Владелец

План запроса в студию? Выполняются ли регламентные процедуры на СУБД?

(2) За такое надо руки отрывать....

(4) чего это?
нормальное условие в запросе. другое дело, что обычно план запроса абсолютно идентичен запросу с соединением

(4) более того, сильно давно на 8.0 эксперименты показали, что для SQL это совершенно равноценно, а для файловой базы условие работает быстрее.
Сейчас правда не знаю, как.

(5)(6) Оптимизатор СУБД часто не может составить оптимальный план выполнения таких запросов. Проблема в определении алгоритма соединения, который зависит от количества записей в выборке. При использовании временных таблиц размер выбираемых таблиц известен заранее, поэтому СУБД легче составить оптимальный план выполнения. Но при этом появляются накладные расходы на создание временных таблиц.

(7) + А Составления плана запроса является одной из самых тяжелых процедур получения выборки.

Хрень какая-то

По ссылке справочника будет либо

clustered index seek
либо
clustered index scan

в зависимости от того, входит ли Владелец в кластерный индекс (а мне - лень проверять)

(10) Глупость пишешь))) как выбираемое поле влияет на используемые индексы? )))

А оптимизатор может выбрать вообще произвольный план, если за ним не ухаживать )

А, нет, забудьте, что я писал.
Тут не это.
100500 Владелец входит в кластерный индекс, селективность у таблиц - одинакова, должен быть indexScan по мелкой таблице и clustered index seek по большой.

(11) Еще как может

(13) Думаю, автор статистику обновляет.

Автору - что мешает отключить индексирование первой таблицы? Ты ее потом используешь?

(13) Причем оптимизатор выбирает не по реальному размеру таблицы, а по статистике , то есть у него приведено примерная величина данной таблице. И если таблица не большая или нужно выбрать значительные объемы данной таблице, то будет скан.

(15) Как бы спорное предложение.... - гадание на гуще )))

Я бы все же посмотрел трасеровку, а именно план запроса.

(16) Тут надо понять, что значит фраза

"Больше  3000 записей "

насколько она близка к миллиону.

(17) Я думаю - автор ее уже смотрел.

(18) Именно. Я бы посмотрел план запроса Show plan xml statistic и тыкнул бы предполагаемое значение выборки и сравнить его с реальным.

(19) Значит он слепой )))

(0)ты показываешь одинесный запрос а профайлером смотришь результаты скульных
они могут отличаться.

Включение\отключение индексирования первой таблицы, не влияет на план второго запроса.
Фрагментация индексов таблицы 0,01%, статистика обновляется каждую ночь.
В первой таблице может быть до 30000 записей, но с 4000 уже применяется индекс скан.
Предполагаемое число  записей действительно не посмотрел, там стоит >1млн записей, а фактическое - 4008.
В свойствах справочника видно, что статистика обновлялась этой ночью.
Собственно план: https://dl.dropboxusercontent.com/u/64336556/План%20запроса.jpg

(23) Какая степень параллелизма? Как часто обновляется кеш?

автор, сделай запрос в скуле принудительно заставив его использовать кластерный индекс и посмотри. (скорее всего) дело в том, что при использовании кластерного индекса ему надо лазить за владельцем дополнительно, а при использовании индекса с владельцем все данные можно взять из него и оптимизатор решил, что при большом количестве записей количество страниц которое надо прочитать будет меньше при использовании некластерного.

(25) Что то не сходится.... кластерный индекс это фактически упорядоченная таблица и лазить за данными дополнительно не куда не надо... тогда как в индексах на уровне листьев row id и как раз за Владельце лезть и надо.

(26) + Так же выбираемое поле не влияет на используемые индексы....

(26) http://msdn.microsoft.com/ru-ru/library/ms190457.aspx
в общем, строки-то в таблице отсортированы, но читает-то он индекс (чтобы все данные таблицы не читать). Соответственно, если ты руками добавишь в кластерный индекс владельца - то ИМХО будет использовать его, а так - есть индекс, в котором все данные, хоть и не кластерный - он юзает его. Потому что не надо читать всю таблицу (типа индекс сик лучше чем тэйбл скан :)).

а вообще мозги оптимизатора - темный лес.

Макс параллелизм - 7, всего ядер 8. Пробовал в копии базы, не кешированной - результаты те же.
При отключении всех системных индексов, кроме кластеризованного - используется кластеризованный. Выполняется при любом размере первой таблицы ~30мс, т.е. в 5 раз быстрее, чем индекс скан по левому индексу. Но оптимизатор настойчиво выбирает индекс скан.

Вот план запроса для 3к записей в первой таблице:
https://dl.dropboxusercontent.com/u/64336556/План%20запроса%203к.jpg
Предполагаемое количество записей = 1.
Получается какой-то косяк статистики, но как его исправить - непонятно.
Пробовал принудительно пересчитать статистику для этого справочника - не помогло. После пересчета статистики кэш чистил.

(30)переписать запрос?