Использование MSSQL 2005
Сегодня меня вызвали посмотреть на хранимую процедуру, которая начала медленно выполняться при использовании из нашей программы. Примерно через пару секунд, где он использовал, он выполняется мгновенно. Я запустил на нем SQL Server Profiler, и он использовал более 1000 ЦП и более 400 000 операций чтения. Я скопировал строку Exec из SQL Server Profiler в SQL Management Studio, чтобы посмотреть на план выполнения. Хранимая процедура была выполнена мгновенно и вернула правильные результаты. План выполнения выглядел правильно, без явных ошибок. Я несколько раз пытался запустить его из нашей программы, чтобы проверить, не кэшируется ли он, и я просто наблюдал замедление первого запуска, когда был определен план выполнения, но это было согласовано на 2-3 секунды за запуск.
Затем я запустил DBCC FreeProcCache, чтобы посмотреть, не замедлит ли это выполнение моих запусков из SQL Management Studio. Запустил хранимую процедуру из Management Studio, и она по-прежнему выполнялась мгновенно. Затем я снова запустил его из программы с запущенным профилировщиком, и он тоже запустился мгновенно. Профилировщик показал, что ЦП упал до 0, а число чтений упало до 40. Похоже, что теперь он остается стабильно быстрым.
Почему запуск DBCC FreeProcCache так резко ускоряет хранимую процедуру?
Краткий ответ: анализ параметров.
Длинный ответ: когда вы впервые запускаете хранимую процедуру, оптимизатор запросов просматривает переданные ему параметры и метаданные об запрашиваемых объектах. Если эти метаданные значительно изменяются со временем (например, изменяется гистограмма статистики) или если начальные параметры не являются репрезентативными для типичного вызова, план, который кэширует оптимизатор, может стать неоптимальным. Выполняя freeproccache, вы избавляетесь от «плохого» плана и заставляете оптимизатор запросов выполнить его еще раз.
Если вы можете узнать, какая хранимая процедура является проблемным потомком, вы можете добавить «с перекомпиляцией» к определению процедуры, и она не будет кэшировать план для этой процедуры. Если вы можете найти в процедуре оператор, вызывающий проблему, вы можете добавить к нему «опцию (перекомпилировать)», и только этот оператор будет перекомпилирован при запуске процедуры.