Eduard

Использовать можно любую, но по факту имеет смысл только Express или Developer. Остальные стоят денег.
SQL Server 2016 SP1 Developer edition is now free

Без них в этом конкретном случае никак. Нельзя создать констрейнт с такой логикой.

select * 
from FruitsTABLE f
where name = 'яблоко'
  and exists (select * 
              from FruitTagTABLE t 
              where t.fruit = f.fruit
                and f.tag  = 'сладкий'
             )
  and exists (select * 
              from FruitTagTABLE t 
              where t.fruit = f.fruit
                and f.tag  = 'круглый'
             )

что-то похожее уже было:
https://toster.ru/answer?answer_id=867572

Скорее всего строка коннекта другая или конфигурация базы другая. Сами данные при backup-restore не меняются или как вы делали копию?

1. замените все left join на inner join. Это не обязательно принесет автоматически производительность, но будет правильнее. Потому что AND cp.path_id IN (3558) делает из всех "left join" "inner join".
2. уберите из "FROM" все соединения oc_product_attribute, и замените условия в where по этим таблицам на exists например:

and exists (select * 
                  from oc_product_attribute a 
                  where a.product_id=p2a.product_id 
                      and a.attribute_id = 12 
                      and a.text LIKE '%ЛПО%'
               )

таким образом вы избавитесь от distinct
если еще нет индекса на attribute_id, создайте его.
3. Вам не надо использовать подзапрос. Это лишнее
4. Оставьте в select только product_id
Это все, что можно сказать только по запросу. Все остальное будет гаданием на кофейной гуще. Впрочем, это тоже все может быть пальцем в небо :)

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

SELECT
    OBJECT_NAME(i.object_id) AS [Table Name]
    , i.name AS [Index Name]
    , i.is_disabled
    , s.last_user_update AS [Last Update Time]
    , s.user_updates AS [Updates]
    , user_scans
    , user_seeks
    , user_lookups
    , last_user_scan
    , last_user_seek
    , last_user_lookup
FROM     sys.dm_db_index_usage_stats AS s
   JOIN     sys.indexes AS i ON       i.object_id = s.object_id AND      i.index_id = s.index_id
   JOIN     sys.objects AS o ON       o.object_id = s.object_id
WHERE    s.database_id = DB_ID()
AND      OBJECTPROPERTY(i.[object_id],         'IsSystemTable'   ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, 'IsAutoStatistics') = 0
AND      INDEXPROPERTY (i.[object_id], i.name, 'IsHypothetical'  ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, 'IsStatistics'    ) = 0
AND      INDEXPROPERTY (i.[object_id], i.name, 'IsFulltextKey'   ) = 0
AND      o.type <> 'IT'
AND i.object_id = object_id('TABLE_NAME')
ORDER BY OBJECT_NAME(i.object_id)

update Table1
set F1 = (SELECT TOP 1 (F2) FROM Table2 T2 WHERE T1.F3 = T2.F4 order by Id desc)
from Table1 t1

или с группировкой, так как первое значение в списке, отсортированном по убыванию это максимум

update Table1
set F1 = (SELECT MAX(F2) FROM Table2 T2 WHERE T1.F3 = T2.F4 GROUP BY F2)
from Table1 t1

при этом надо учесть, что если в Table2 не найдется подходящих данных, то обновится NULL'ом.

стандартное решение это создать таблицу-календарь и присоединить ее с right join.

Запустите profiler.
А вообще приложению не нужна роль sysadmin, а если нужна, то есть сомнения в квалификации разработчика.

используйте set statistics io, time on для понимания что реально тормозит