Таймаут оптимизатора измеряется не временем, а количеством задач оптимизации, которое предварительно отводит себе сервер для оптимизации запроса. Оно зависит не от загруженности процессора, а от "сложности" запроса - количества и свойств логических групп оптимизации (типа "я угадаю эту мелодию с пяти нот...", т.е. "я построю план этого запроса за столько-то шагов...").

На мой взгляд, гораздо более вероятная причина разных планов, это пресловутое прослушивание параметров, сохранение и последующее переиспользование плана для тех значений, где он уже неэффективен. Причина довольно распространенная и главное проверить просто.

В следующий раз, когда начнуться тормоза, зафиксируйте это на 5 минут, попросите никого ничего не трогать. Запустите профайлер и отловите вызов долгой процедуры, копируйте себе в SSMS текст вызова, где будут указаны все параметры, кроме того, скопируйте все настройки соединения (они тоже будут в профайлере, если выбирать шаблон по-умолчанию).
Типа такого должно быть.

Включите в студии Include Actual Execution Plan и выполните. Посмотрите на время, если будет долго, то вы поймали ситацию плюс у вас есть нэффективный действительный план. Сохраните его (на всякий случай) и можете анализировать (обратите внимание на "Parameter Compiled Value" и "Parameter Runtime Value"). Можно сразу в профайлере включить соотв.событие, чтобы увидеть план.
Теперь можете рекомпилировать процедуру, и выполнить запрос еще раз и снова посмотреть план. Сравнить, с тем, что был при прошлом, медленном выполнении.

Хорошие кандидаты на такие случаи - универсальные процедуры, в которых набор возвращаемых данных может сильно меняться в зависимости от значений параметров.

aleks2,

Не понял, к чему это вы? Я разве говорил что он не изменяется на протяжении всего процесса оптимизации? Таймаут задает некоторый бюджет задач оптимизации, который оптимизатор расходует. Могу расписать подробнее, если интересно (хотя я вроде описывал у себя в блоге это уже, ну да ладно, "эх раз, еще раз").
Основные понятия
Memo — структура в памяти сервера, которая используется для хранения и анализа получаемых в результате преобразований деревьев операторов.
Group — группа эквивалентности, часть структуры Memo, в которой хранятся эквивалентные выражения (операторы), например — Group 1: (A join B) , (B join A).
Optimization Task — дословно, задача оптимизации, это операция предпринимаемая оптимизатором в процессе поиска плана. Это может быть например, применение правила преобразования к узлу дерева операторов.
Timeout — определенное количество задач оптимизации (Optimization Task), которое отводит себе оптимизатор перед тем, как начинает оптимизировать запрос, т.е. некий бюджет на количество преобразований. По мере выполнения преобразований оптимизатор смотрит на этот счетчик, и как только потратил всё отведенное количество — прекращает оптимизацию и выдает тот план, который у него есть на данный момент.
Процесс оптимизации
Оптимизация состоит из стадий:
- тривиальный план
- search 0
- search 1
- search 2
Каждая стадия характеризуется набором доступных операций оптимизации, входными условиями и т.д. От стадии к стадии набор приемов расширяется. Для отслеживания вариантов планов, на каждой стадии, оптимизатор заполняет структуру Memo в которой хранит группы эквивалентности.
Конечное Memo предыдущей стадии используется как начальное для следующей. И от стадии к стадии число групп растет, сложность растет - таймаут тоже растет . Более того Мемо ведет своеобразный треккинг "лучшего" плана и отметает неэффективные ветки, от этого в частности зависит и величина таймаута (это можно проверить трейсфлагом). Но тем не менее, если только таймаут не отключен (хотя его нельзя полностью отключить, можно только сильно увеличить) - он задает некоторый лимит операций, за который оптимизатор не может выйти на определенной стадии.

Если интересно, можно взять дебаггер, поставить брэйкпоинты на следующие методы:


После этого выполнить какой-нибудь запрос с таймаутом, (я взял пример из своего блога) и посмотреть, какие методы в какой последовательности вызываются.

Результат:


Т.е. было пройдено две стадии полной оптимизации search0 (также известная как Transaction Processing) и search1 (известная как Quick Plan), начата стадия search2 известная как просто Full, т.е. полная оптимизация, но прежде чем на ней был найден достаточно хороший план - получен получен таймаут, когда число преобразований достигло 614400.
Это отразилось в вызовах следующим образом (мои коменты зеленым):


Также в процессе оптимизации есть много всяких эвристик, как в правилах преобразования, так и в выборе самих стадий и внутри стадий. Но - процесс оптимизации - это все равно программа. Если система не изменяется (не добавляются нове индексы, строки, статистика и т.д.) - то процесс получения плана идет одинаково от раза к разу. Нет такого, что "он начал перебор с конца" и по этому не нашел хороший план, а потом луна вошла в водолея, он начал перебор сначала и нашел хороший план. Это я утрирую конечно.

Что касается вообще невозможности найти хороший план из-за таймаута - то такое может быть конечно, но это вряд ли выглядит как описал ТС. И даже если возникает таймаут - то первопричина не в самой концепции таймаута как таковой, а, в исходных условиях, в которые был поставлен оптимизатор, например, то же прослушивание параметров.

В любом случае ведь проще сначала проверить распространенные причины, прежде чем лезть в дебри, имхо.

SomewhereSomehow,
Запрос только не тот запостил, таблиц нужно больше 5, чтоб до стадии 2 (Full) дошел процесс.
Правильный запрос релевантный примеру.

aleks21. Я к тому, что оптимизатор все ж, банально, перебирает разное число планов при перекомпиляциях.
2. И единственно правильный способ стабилизировать план - не давать оптимизатору никакого выбора.
3. А читать многабукав из твоих, возможно правильных, текстов - неразумно.
Много букв это да, смысл в их написании тут - был простой, показать, что банальный перебор идет не от желания левой пятки оптимизатора, а по определенным алгоритмам, и таймаут зависит не от нагрузки.
Что касается стабильности - разное можно под этим понимать, можно стабильность (предсказуемость) выбора плана в зависимости от ситуации, имхо, в большинстве случаев это предпочтительнее, но может быть и некий усредненный план, пусть и не очень оптимальный для каждого конкретного случая, optimize for unknown не зря же придумали. Это смотря что надо ТС.