Соединяю две таблицы left join ом.
В обоих одинаковые поля одинаковых типов.
По всем полям что в on создан примари индекс в обеих таблицах.
Одно из соединяемых полей типа varchar(12) безбожно тормозит. Убираешь его, join отрабатывает за долю секунды,добавлешь его в on тормозит на 3 минуты. Уже голову сломал, где можно посмотреть причину тормоза?

в план запроса уже смотрел?

(0)
нет понятия "примари индекс".

что такое "Соединяю две таблицы left join ом" ?

какой collation у полей  varchar(12) ?

+ (2)
какой collation у tempDB

(1) В плане написано что во втором случае 90% времени занимает clustered index seek.
Действительно я не заметил что по этому полю есть кластерны индекс? И что теперь? Убирать этот индекс чтобы ускорить запрос? Обычно наоборот делают

select count(*)
from
buf_table b
left join
cur_table c
   on (c.id = b.id)
   and (c.period = b.period)
   and (c.dim=b.dim)

в обеих таблицах
по id кластерный индекс.
по (id,period,dim) уникальный некластерный индекс
Запрос выполняется три минуты

План выполнения этого запроса
select 0% <- Compute scalar 0% <- Stream Agregate 0% <=nested loops (inner join) 4%<- Clustered Index scan(таблица b) 7%
                                       <-Clustered Index seek(таб c) 90%



ВТорой запрос выполняется за долю сек

select count(*)
from
buf_table b
left join
cur_table c
   on (c.id = 'MyId')
   and (c.period = b.period)
   and (c.dim=b.dim)

План выполнения этого запроса

select 0% <- Compute scalar 0% <- Stream Agregate 0% <=nested loops (inner join) 0% <- Clustered Index seek (таблица c)50%
                                       <-Index seek (таблица b) 50%

перестроить индексы пробовал?

(6) да

есть мнение, что записей в запросе "2" раз в 100500 меньше, чем в "1". ну и почему-то в запросе "1" используется индекс не по тому полю.  попробуй статистику обновить

Интересно как оптимизатор не выкинул это соединение так как результат должен быть одинаковым и без левого соединения

COUNT со звездочкой включает и NULL

(9) не факт, а вдруг в b дубли

Вообщем шаманя и колдуя, помогло удаление уникального индекса (id,period,dim) у таблицы b

может проще было хинт использовать?

(12) А не... ни фига не помогло

у тебя по кластерному ищен а должен по другому

(15) Я уже понял, но как мне машине то это объяснить. Перестановка таблиц местами, так же ничего не дает

select count(*)
from
buf_table b
left hash join
cur_table c
  on (c.id = b.id)
  and (c.period = b.period)
  and (c.dim=b.dim)

select count(*)
from
buf_table b
left merge join
cur_table c
  on (c.id = b.id)
  and (c.period = b.period)
  and (c.dim=b.dim)

select count(*)
from
buf_table b
left nested loops join
cur_table c
  on (c.id = b.id)
  and (c.period = b.period)
  and (c.dim=b.dim)

забацай полный текст плана запроса, он тебе выдаст циклы, в котором у тебя 90% времени съедается

либо

select count(*)
from
buf_table b
left join
cur_table c (with index(ix_blablabla))
  on (c.id = b.id)
  and (c.period = b.period)
  and (c.dim=b.dim)

а вообще если b.id повторяются, то смысла в кластерном индексе нет вообще никакого. потому как уникальность сервак добиваем своими силами

select count(*)
from
buf_table b (with index(ix_blablabla))
left join
cur_table c
 on (c.id = b.id)
 and (c.period = b.period)
 and (c.dim=b.dim)
индекс на другой таблице =)

(11) Ну вроде как уникальный индекс в 0
в обеих таблицах
по id кластерный индекс.
по (id,period,dim) уникальный некластерный индекс