Создание плана запросов SQL занимает 5 минут, сам запрос выполняется в миллисекундах. Что?

У меня довольно сложная (или уродливая в зависимости от того, как вы ее смотрите) хранимую процедуру, запущенную на SQL Server 2008. Она основывает много логики на представлении с таблицей pk и таблицей fk. Таблица fk слева соединена с таблицей pk чуть более 30 раз (таблица fk имеет плохой дизайн - она использует пары значений имени, которые мне нужно сгладить. К сожалению, это сторонняя сторона, и я не могу ее изменить).

Во всяком случае, он работал отлично в течение нескольких недель, пока я не заметил заметок, которые заняли бы 3-5 минут. Оказывается, это время, необходимое для формирования плана запроса. Как только план запроса существует и кэшируется, сама хранимая процедура выполняется очень эффективно. Все будет работать гладко, пока не будет причин для восстановления и кэширования плана запроса.

Кто-нибудь видел это? Почему для составления плана требуется много времени? Есть ли способы ускорить разработку плана?

Ответ 1

Вы можете попробовать использовать Руководство по планированию. Генерация плана будет продолжаться некоторое время, но должна быть значительно короче.

Ответ 2

Рассматривали ли вы переписывание своего 30-join SELECT таким образом?

SELECT [key], NULL AS [a], NULL AS [b]
  INTO #temp
  FROM [pk-table]

UPDATE t SET t.[a] = fk.[a], t.[b] = fk.[b]
  FROM #temp t
  INNER JOIN (
    SELECT f.[key],
      MAX(CASE WHEN f.[name] = 'a' THEN f.[value] ELSE NULL END) AS [a],
      MAX(CASE WHEN f.[name] = 'b' THEN f.[value] ELSE NULL END) AS [b]
    FROM [fk-table] f
    GROUP BY f.[key]
    ) fk ON (fk.[key] = t.[key]

Хотя это, возможно, не ответ на ваш оригинальный вопрос:)

Ответ 3

Что-то заставило план требовать повторной компиляции, такой как обновление статистики или изменение DDL. Список, если здесь: Кэширование и повторное использование плана выполнения

Запрос в текущей форме всегда будет занимать 3-5 минут, чтобы перекомпилировать: этого нельзя избежать.

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

Ответ на план плана Ремуса - один из способов. Я также посмотрел бы на мое статистическое обслуживание и гарантирую, что это будет сделано в одночасье, так что это происходит только один раз в начале дня.