Бывает что 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