Постоянная производительность запросов с течением времени
Мы выполняем интенсивную нагрузку приложения (тысячи операций в секунду) на базу данных SQL Server с довольно большим объемом данных. Некоторые таблицы содержат миллиарды строк, некоторые из них содержат множество вставок и обновлений.
Производительность БД в целом нормальная, но периодически возникают проблемы с производительностью запросов; довольно простые запросы, которые раньше работали нормально, могут внезапно занять в 10–100 раз больше времени.
Это, по-видимому, связано со статистикой таблицы / индекса и оптимизатором запросов - в большинстве случаев обновление статистики решает проблему, а в других случаях обновление статистики ухудшает ситуацию (повторный запуск обновления статистики обычно решает проблема в конце концов).
Похоже, что происходит то, что оптимизатор решает использовать объективно неправильные индексы для некоторых запросов; внезапно, после нескольких дней и недель использования правильного.
Мой вопрос: почему это происходит и что мы можем с этим поделать?
Эта база данных работает в течение многих лет с практически одинаковой нагрузкой, практически одинаковыми запросами и тем же количеством обновлений. Для 99,995% запросов не должно быть причин когда-либо выбирать различные стратегии индексации с течением времени, независимо от ввода (и - действительно - это явно полностью снизит производительность запроса).
Как указано выше, автоматическое обновление статистики по расписанию часто вызывает ужасные проблемы - если статистическая выборка искажается (что, по-видимому, случается по крайней мере в 5% случаев), мы попадаем в мир боли.
Есть ли способ сообщить SQL Server (в определенных таблицах), что гистограмма статистики и плотность не будут меняться со временем, поэтому, пожалуйста, просто продолжайте использовать тот же план запроса для запросов, связанных с этой таблицей? Если нет, то как мы можем гарантировать предсказуемый результат обновления статистики с течением времени (избегая проблемы искаженной статистики, описанной выше)?
Нет хранимых процедур. У нас есть контроль над SQL, поэтому он потенциально может быть изменен, но это МНОГО кода, поэтому было бы неудачно, если бы нам пришлось изменять каждый отдельный запрос (например, добавляя дополнительное предложение).
Последующий вопрос: анализ параметров имеет значение только для хранимых процедур, верно?
Ответы
Я предлагаю вам сначала определить, вредит ли вам статистика или его параметры.
Независимо от вышесказанного, я предлагаю вам прочитать статью Эрланда на эту тему.
Что с этим делать, сказать сложно. Мы не знаем, статистика это или нюхание.
Но возможно добавление OPTIMIZE FORможет быть "решением". Это дешевле, чем RECOMPILEпотому, что вам не нужно получать удар по плану при каждом выполнении. И это дает вам предсказуемость. Это, конечно, предполагает, что у вас нет случая, когда статистика различается настолько, что один и тот же ввод параметров дает разные планы из-за причин статистики.
Попытайтесь определить один запрос. Посмотрите, есть ли у вас один или несколько планов для запроса. Протестируйте с помощью OPTIMIZE FORи / или RECOMPILE. Единственный «глобальный» вариант в масштабе базы данных, который у вас есть, - это отключить отслеживание параметров для базы данных. Это означает, что оптимизатор оптимизирует, поскольку не имеет понятия о значении. Обо всем этом и многом другом в статье Эрланда.
Анализ параметров применим не только к хранимым процедурам. Это также применимо к параметризованному SQL (обычно выполняемому с использованием sp_executesql), который, вероятно, гораздо более распространен в настоящее время, чем хранимые процедуры.
Ответ сформирован из комментариев
Вы можете получить неправильные планы запросов из-за неправильной статистики, которую вы получили после обновления статистики. Но вы также можете получить неправильные планы запросов из-за обнюхивания параметров, когда после обновления статистики первые параметры, полученные вашим запросом, были не такими, как обычно. Из вашего вопроса невозможно понять, какая именно из проблем присутствует. Попробуйте перекомпилировать план запроса, когда запрос становится хуже, вместо обновления статистики для разделения двух разных проблем. - Денис Рубашкин
Есть много факторов, которые могут вызвать «перестройку» плана выполнения. Таким образом, это объяснило бы, почему какое-то время он работает нормально, а потом вдруг начинает работать медленно. Когда вы обновляете статистику, все планы выполнения, которые имеют какое-либо отношение к этому объекту, становятся недействительными, и это приведет к созданию нового плана при следующем выполнении. В зависимости от используемых для него значений, он может исправить или нет проблему (большинство значений исправляет ее, а некоторые - нет, что объясняет, почему иногда это работает, а иногда нет).
Другой способ «исправить» план выполнения - это использовать Query Store (я думаю, что он начался с SQL Server 2016) и «исправить» план для использования. У него могут быть некоторые недостатки, если данные сильно изменяются (поскольку SQL Server не сможет составить лучший план), но он может исправить такую проблему (у меня есть запрос, работающий с планом выполнения исправлений уже 2 года, и я не с тех пор не возникает проблемы с анализом параметров). - Доминик Буше