Почему dbcc freeproccache помогает?

Бывает что MS SQL на нагруженных проектах загружает процессор под сотку. А выполнение dbcc freeproccache неожиданно решает эту проблему на пару часов. А потом снова CPU 100%. Разберемся?

Так исторически сложилось, что SQL Server имеет кэш. То есть, прежде чем выполнить запрос или процедуру, SQL Server парсит их, компилит по-всякому, а потом только выполняет. В общем идея нормально работает до тех пор, пока выполняются запросы типа

Тогда все хорошо. Создается план выполнения, добавляется в кеш, потом в него подставляется переменная @var и все работает быстро и хорошо. Но как только дело доходит до запросов типа

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

Это само по себе не особо плохо, но когда таких планов три миллиона, это забивает память и затрудняет поиск нужных планов выполнения. Поможет определить сколько таких планов в кеше запрос:

И если такой фигни больше 50% от всего кеша, с этим надо что-то делать. Есть пара вариков решения данной проблемы.

Parameterization forced

То есть для своей базы надо выполнить запрос

И SQL Server будет по жести параметризировать все запросы — то есть даж если вы не особо парились по поводу использования переменных в запросах, теперь сервер сам их будет втыкать везде куда можно. Так как запросы кешируются, то чем меньше уникальных запросов — тем лучше. Простым языком

намного лучше, чем

И если включить PARAMETERIZATION FORCED то Sql server начнет сам автоматически эти самые параметры везде втыкать. безусловно это разгрузит кеш планов, но может получиться, что сам процесс автоматической параметризации будет занимать еще больше процессорного времени. В общем надо тестить. Кстати отключить этот режим можно командой

Optimize for ad hoc workloads по-русски

И к 2008 версии ребята из Microsoft осознали проблему — и сделали у Sql Server в настройках пункт Optimize for ad hoc workloads или Оптимизировать для нерегламентированных рабочих нагрузок если по-нашему.

И если это включить, то Sql Server будет добавлять план в кеш только со второго использования. Это может реально решить проблему, но скорее всего ее просто отсрочит.

Лучшим вариантом было бы переписать логику приложения с использованием нормальных запросов, планы которых просто и удобно кешировать.

По мотивам: sqlmag.com/sql-server/fine-tuning-plan-reuse

← Previous post

Next post →

1 Comment

  1. ShoGUN

    Можно улучшить, при заполнении #work_to_do не надо делать лишнюю работу, лучше сделать доп. условие:
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 128

    Источник: MS советует перестраивать только большие индексы(>128 страниц), т.к. маленькие индексы хранятся вместе, вперемешку с индексами других объектов(при условии, что такие объекты есть, конечно), и перестраивай их, не перестраивай — фрагментация не уменьшится. Можете сами поэкспериментировать.

    Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are sometimes stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

    Отсюда: https://docs.microsoft.com/ru-ru/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

Добавить комментарий