DBCC Freeproccache значительно ускорил время выполнения, почему?
Использование 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 так сильно ускоряет хранимую процедуру?
1 ответ
Краткий ответ: параметр сниффинг.
Длинный ответ: когда вы впервые запускаете хранимую процедуру, оптимизатор запросов просматривает переданные ей параметры и метаданные об запрашиваемых объектах. Если эти метаданные значительно изменяются с течением времени (например, изменяется гистограмма статистики), или если начальные параметры не представляют типичный вызов, план, который кэширует оптимизатор, может стать неоптимальным. Делая freeproccache, вы избавляетесь от "плохого" плана и заставляете оптимизатор запросов выполнить еще один запуск.
Если вы можете выяснить, какая хранимая процедура является дочерним процессом проблемы, вы можете добавить "с перекомпиляцией" в определение процедуры, и она не будет кэшировать план для этой процедуры. Если вы можете найти оператор в процедуре, которая вызывает проблему, вы можете добавить к нему "option (перекомпилировать)", и только этот оператор будет перекомпилирован при запуске процедуры.