Auto update statistics asynchronously 1с для чего
Эта статья написана для администраторов, обслуживающих сервера СУБД MS SQL Server, которые используются вместе с 1С:Предприятием. Статья скорее практическая, чем разъяснительная, но я постарался хотя бы кратко обосновать те или иные решения, хотя большая часть информации дана несколько упрощенно и поверхностно.
Индексы и статистики в MS SQL Server — основа эффективного выполнения запросов. Без них сервер не сможет выполнять запросы за разумное время.
Статистика — небольшая таблица, до 200 строк, в которой хранится обобщенная информация о том, какие значения и как часто встречаются в таблице. На основании статистики сервер принимает решение, какой индекс использовать при выполнении запроса.
Индекс — особым образом структурированные данные (хранящиеся в базе данных), которые позволяют быстро найти нужные записи. Устроен он примерно так, как оглавление в книге или предметный указатель. Большинство баз данных 1С по объёму более чем наполовину состоят из индексов. Для каждого индекса обязательно хранится его статистика.
За подробностями внутреннего устройства, как обычно отсылаю в BOL:
Для обслуживания есть специальные "кирпичики" в планах обслуживания (maitenance plan), которые так и называются:
- Update Statistics Task
- Rebuild Index Task
- Reorganize Index Task
Казалось бы всё просто: накидал кирпичиков, соединил стрелочкой и поехали. Такое решение возможно, но оно очень неэффективно:
- Индексы перестраиваются/реорганизуются только все сразу в данной базе. То есть даже если таблица никогда не меняется, её индексы будут перестраиваться. Это очень расточительно, а при полной модели восстановления еще и приводит к огромному росту журналов транзакций.
- Статистики тоже перестраиваются вне зависимости от актуальности, причем даже если они были только что обновлены при перестроении индексов.
- Нет никаких гарантий, что операция обслуживания завершится за то время, которое вы ей выделили.
Решение очень простое: пишется свой скрипт обслуживания, который убирает эти ограничения. Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее). В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом". Этот скрипт и приведён ниже. Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).
- Как и в большинстве подобных скриптов, анализируется динамическое представление sys.dm_db_index_physical_stats, по которому выясняется степень фрагментации и заполненности страниц индекса.
- Можно задать для обработки лишь часть баз данных, можно, наоборот, исключить некоторые БД из обслуживания.
- Контролируется время выполнения скрипта.
- Очень грубо, но оценивается размер записи в журналы транзакций.
- Есть возможность исключить из обработки совсем небольшие таблицы.
- У скрипта есть режим "эмуляции" работы, чтобы оценить то, как он будет работать.
- Сначала обрабатываются самые большие таблицы (так как обычно их обслуживание важнее).
- Скрипт работает на SQL Server 2008 и более поздних (на 2005 тоже должен работать, но мне уже негде проверить)
- Результат вывода в режиме эмуляции сам является корректным TSQL скриптом.
- Ну и конечно, при регулярном выполнении этот скрипт на порядок легче, чем стандартные операции плана обслуживания.
С чем нужно быть осторожным при запуске скрипта:
- Нежелательно пересечение работы скрипта с интенсивной работой пользователей или с полным резервным копированием.
- Чтение из sys.dm_db_index_physical_stats в режиме DETAILED достаточно интенсивно читает с дисков.
- Скрипт предназначен для баз 1С или подобных. Не стоит экспериментировать с ним на совсем специфичных базах данных с нестандартными индексами.
- Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.
- Статистики пересчитываются без полного сканирования. Это заметно быстрее. Если вам нужно полное сканирование каких-то таблиц, то пишите отдельный скрипт.
Рекомендации по запуску:
- Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!
- При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов. Иначе при необходимости восстановления придётся донакатывать достаточно тяжёлый кусок журналов транзакций после восстановления основного образа. Простую модель восстановления на промышленно используемых БД я считаю либо редким исключением, либо частым недоразумением.
- Первый запуск лучше выполнить вручную в SSMS чтобы оценить время работы.
Остальное можно прочитать в коде и в комментариях.
PS: Движок сайта некорректно отобажает текст со знаками больше-меньше, поэтому скрипт приложен файлом, а в статье оставлено только начало скрипта.
При этом предполагается "вмешательство" в стандартные структуры и процедуры БД от 1С.
Вот к чему я пришел:
У всех этих методов есть существенный недостаток:
- если вмешиваться в штатные механизмы от 1С: тогда сложно поддерживать восстановление своих "хотелок" после реструктуризации БД - .
Предлагаю собственно иной взгляд и подход: посмотрим на родные средства сервера SQL+Win и попробуем оптимизировать скорость только "там", не изменяя "коробочные" технологии от 1С.
* В БОЛЬШИНСТВЕ СЛУЧАЕВ СТАНДАРТНЫЕ МЕХАНИЗМЫ 1С "ИЗ КОРОБКИ" ОПТИМАЛЬНЫ (в 90%)
* НЕ ОПТИМАЛЬНЫ (как правило) НАШИ ДОПОЛНИТЕЛЬНЫЕ "навески" НА ТИПОВЫЕ РЕШЕНИЯ
- либо потому что мы чего-то не знаем
- либо потому что этот "узкоспециализированный костыль" по другому не работает
В результате мы начинаем оптимизировать "всё" и "вся" , жадно вычитывая решения из интернет.
Я же предлагаю (исходя из соображения стоимости сопровождения) по-иному взглянуть на проблему:
1) свои ошибки (внутри 1С) исправлять (однозначно)
2) бросить затею "оптимизировать 1С" - вместо этого посмотрим на САМ сервер WIN+SQL
В моем случае (на одной площадке, сервере) имеем 8шт 1С7.7 баз + 3 штуки 1С8, одна из которых УПП
Все разного размера и интенсивности.
Как угодить всем?
железо (минимум, для моего объема)
По настройкам SQL+Win:
2) настройки собственно самого SQL (у меня такие - методом проб и экспериментов, для этого железа):
если запрос долше 1сек
заставляем SQL принудительно использовать
все ядра, что имеются
по умолчанию (когда = 0) использует 512
количество блокировок вплоть до этого значения
не будем считать проблемой
у нас сеть 1Гбит, интенсивность работы высокая,
увеличиваем размер пакета чтоб не ходили вхолостую
оставляем стандартно, но
не забываем про количество пакетов
( у меня макс доходит до 5000 batch/sec )
смотрите чтобы не сьели всю память
чекпоинты БД делаем не чаще 1раз/час (для скорости),
хотя это условность (см.MSDN)
3) выносим tempdb - на RAM-диск в 2Гб (я пользуюсь imDisk Toolkit-ом, ни разу не подводил, GPL) |
4) для всех баз устанавливаем такие параметры (много раз писалось, приведу еще раз для общей картины): |
обсуждалось много раз
или вообще NONE: надежность вряд ли пострадает, скорость выше
* если более 1 (физического) массива дисков - разностим MDF/LDF (иначе не обращаем внимания)
* путь к файлам MDF/LDF - должен быть без длинных путей (т.е. переностим все MDF/LDF файлы в корень диска/ков)
* но не на диске С:\ (естественно)
* помним про autogrow и прочее. (думаем)
5) В СЛУЧАЕ МОНОПОЛЬНОГО ПЕРЕПОВЕДЕНИЯ БАЗЫ (1С77) :
делаем "финт ушами":
- средставим тогоже imDisk создаем RAM диск размером 20Гб (ну или сколько там БД + 10%)
- переносим туда БД (backup/restore в новое место)
- выставляем: autocreate stat / autoupdate stat = ON
- запускаем _1sp_DbReindex + sp_updatestats
- проводим базу
- выставляем: autocreate stat / autoupdate stat = OFF
- опять backup и restore на диск, на старое место.
6) если RAM совсем много: переносим "туда" нашу БД "на всегда" :
при этом:
- каждые 10 минут FULL BACKUP
- при старте SQL - агентом вызываем скрипт для restore
- при стопе SQL - агентом вызываем скрипт для full backup
Все остальные способы (индексы и прочее)
- именно в случае вмешательсва в стандартные структуры и процедуры от 1С-ки
- дают выигрыш в производительности максимум 10-15%
- при этом затрат на обслуживание (времени) уходит просто уйма.
А с учетом того что память и диски сегодня стоят .
Проще наростить мощность сервера и вынести (когда нужно) базу в память.
Я пошел экстенсивным путем.
Всё это - к обсуждению
Специальные предложения
В большинстве случаев (1с77/1с8х) всё рекомендации, методики и алгоритмы сводятся к двум вещам:
- оптимизация индексов и статистики
- управление блокировками
Основная и единственная рекомендация - это грамотные, оптимальные запросы! Оптимизация индексов, это что, добавить новые, реструктуризировать старые? Блокировки - сейчас все на управляемых блокировках, автоматических режим - это редкость. А в целом, все проблемы от самих же программистов, а точнее, их безграмотности. (ИМХО)
(1) DoctorRoza, не все проблемы от программистов. Если сам скуль настроен криво, то никакие хорошие запросы не помогут. Если работает типовое решение, то вероятность его положить плохими запросами есть, но она не так сильно может испортить жизнь, как кривой скуль.
Спасибо, очень помогло: exec sp_configure 'max degree of parallelism',64. (заставляем SQL принудительно использовать все ядра, что имеются). Была проблема недозагруженности процессоров. Респект!
По моему опыту, для существенного ускорения 1Сv7.7, особенно в случаях массового перепроведения документов, не обязательно на RAM-диск отсылать всю базу. Достаточно туда перепрописать temp-каталоги!
"путь к файлам MDF/LDF - должен быть без длинных путей"
А зачем? Путь используется один раз - при открытии файла. Дальше все обращения через дескриптор. Физическое место записи файла тоже от пути не зависит.
Кстати, не стоит переоценивать мастерство типовых программистов. Я тут пару дней назад хотел даже статью написать, как ускорил УПП на 40%, слегка изменив типовой код, да что-то глюкануло и она не сохранилась, зараза такая.
For servers that use more than eight processors, use the following configuration:
MAXDOP=8
For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
Учитывая, что указанные опции строго рекомендуется ВКЛЮЧИТЬ, а PAGE_VERIFY даже после сброса сервером в NONE после апгрейда базы рекомендуют выставить в CHECKSUM, то данные рекомендации похожи на способ поиметь проблем на ровном месте.
Безусловно, если вы специалист, то вы с данными проблемами справитесь, а вот те кто повторит за вами вряд ли поймет, что именно у него происходит.
UPD. А да, для базы sharepoint и biztalk серверов указанные опции по дефолту выключены и включать их не нужно. Сомневаюсь, что характер работы 1С с SQL сервером похож на работу указанных приложений.
(8) Автор, видимо, предполагает ручное обновление статистики, что для больших баз не просто лучший выход, а единственный, так как триггериться автообновление статистики будет реже (меньше процент изменений) и в итоге планы запросов будут строиться по статистике данных давно забытых периодов.
Для больших баз все решается индивидуально. Вот только базы с параметрами "создаем RAM диск размером 20Гб (ну или сколько там БД + 10%)" вряд ли можно отнести к большим базам, хотя бы 100-200Gb надо для начала.
exec sp_configure 'recovery interval (min)',60
чекпоинты БД делаем не чаще 1раз/час (для скорости), хотя это условность (см.MSDN)
То MDSN https://msdn.microsoft.com/ru-ru/library/ms191154.aspx прямо говорит, что "Этот параметр является дополнительным и его следует изменять только опытным администраторам баз данных или сертифицированным техническим специалистам SQL Server."
В комментариях увидел 6 критических замечаний. Постараюсь ответить.
1) Вы утверждаете (ссылаясь на MS)
у них же (MS) здесь https://msdn.microsoft.com/en-us/library/ms188611.aspx
сказано, что "Setting the max degree of parallelism option to 0
allows SQL Server to use all available processors upt to a maximum of 64 processors
in a parallel plan execution." иными словами
"дает ВОЗМОЖНОСТЬ использовать все процессоры вплоть до значения 64"
Я же ЗАСТАВЛЯЮ его использовать ВСЕГДА значение 64
(это не значит, что у меня 64 ядра, это значит что реально будет использовать ВСЕ что есть физически)
2) по поводу статистики
В моем случае
* очень высокая интенсивность работы (кол-во запросов доходит до 5000шт/сек)
* tempdb - находится в памяти
* БД (иногда) тоже там же (а значит IOPS в 100 выше чем при работе с дисками)
Поэтому
* статистика - не нужна (оптимизация планов только зря расходует ресурсы CPU на вычисления для планов)
Опять же
* на сервере установлена ECC-память (аппаратный CHECKSUM)
* базы (как правило) в пямяти (опять же экономим ресурсы CPU на вычислениях)
Я не знаю, какой "характер" работы у указанных Вами приложений, я знаю 2 типа работы приложений с БД
* OTLP (очень большая интерсивность маленьких, часто повторяющихся запросов, например FOREX)
* OLAP (объемные запросы на выборку данных, для построения "глобальных" аналитических отчетов)
В моем (конкретном) случая я бы отнес "характер работы"
(при массированном проведении документов) к типу OTLP
где критическим местом является: (1) дисковая подсистема (2) процессор
(11) yukon, Чуть ниже постом, Вы говорите:
Данная статья не рассматривает работу SQL с большими базами данных.
В данной статье идет разговор "о производительности" (т.е. о скорости работы)
в высоконагруженных системах.
В данном случае (как в пословице) : "Размер не имеет значения".
Я считаю (когда показатель "Количество запросов в секунду" доходит иногда до 5000)
- у меня HighLoad System
Опять же - я делаю не "просто изменить параметр", а ровно 2 вещи:
* recovery interval (min) = 60
= хочу отключить АВТОМАТИЧЕСКИЙ вызов CHECKPOINT "мыслями" сервера SQL
* и выполняю каждые 10 минут FULL BACKUP(который уже имеет CHECKPOINT)
= своей головой (у меня БД в RAM)
Так что здесь - проблем не вижу.
Надеюсь, "осветил" все критические моменты.
Коллеги, задавайте вопросы, с удовольствием отвечу.
Поэтому
* статистика - не нужна (оптимизация планов только зря расходует ресурсы CPU на вычисления для планов)
Знаете, очень напомнило серию Футурамы, как Зойдберг кино снимал: "Самым длительным и дорогим этапом в кинопроизводстве является монтаж и прочий постпродакшн, поэтому мы решили обойтись без него".
Почитайте на MSDN, в каких случаях апдейтится статистика, вычисляются планы и для чего это вообще нужно.
1) предполагается что будут использоваться родные 1С-кие кластерные индексы "из коробки"
и в (собственно) запросах никаких хинтов писать не будем.
Естественно: не забываем про порядок полей в запросах (как в индексах). и т.д.
2) как я уже говорил
* "родные индексы" (как правило) - изначально оптимальны в большинстве случаев
* или Вы хотите еще что-то ускорить ? может insert/update при помощи статистики ? 8))
3) Еще раз:
* физически скорость носителя RAM "без статистики" выше чем HDD "со статистикой"
* конкретно в случае с RAM: нагружать процессор расчетами статистики - считаю абсолютно лишним (+память сьедает)
* индексы никто не отменял (по крайней мере я)
* настаиваю - в структуры и процедуры от 1С (на строне SQL) не нужно "лазить"
4) прочитайте на том же MSDN как влияет статистика на производительность
* как в сторону ускорения - о чем говорят все (в том числе и Вы, и я)
* а также в сторону ухудшения - о чем тоже написано, но никто об этом не говорит (Вот я хочу сказать, но не дают)
Не зря ведь в самом видимом места свойств базы есть флажек "ON/OFF" ?
Не нужно носом тыкать других, читать умеют все (ну или почти).
Да, и простите за мой "футуристический" язык.
Хотелось бы больше услышать "практиков" ( а не умеющих читать теоретиков ).
Коллеги, больше конструктивизма.
дык не вопрос, нагружайте Nested Loops'ами.
Я еще вечером до вас доберусь и пройдусь по вашим "рекомендациям".
сказано, что "Setting the max degree of parallelism option to 0
allows SQL Server to use all available processors upt to a maximum of 64 processors
in a parallel plan execution." иными словами
"дает ВОЗМОЖНОСТЬ использовать все процессоры вплоть до значения 64"
Я же ЗАСТАВЛЯЮ его использовать ВСЕГДА значение 64
(это не значит, что у меня 64 ядра, это значит что реально будет использовать ВСЕ что есть физически)
Тут дело в том, что в 1С стандартно довольно сложные запросы, которые почти не параллелятся, либо делают это очень криво и в итоге теряют в скорости. Я неоднократно слышал о том, что надо принудительно выставлять max degree of parallelism в 1 и именно это ускоряет работу. Поэтому лично мне принудительное выставление maxdop в 64 для 1с кажется нонсенсом, ведь 90% запросов всё равно будут выполнять однопоточно, ещё 9% просто замедлят свою производительность изза кривого распараллеливания и 1% может быть ускорится :) но может у вас на это другие взгляды
Просто я не админ, я программист. У меня конечно стоит sql на своем компе и я частенько играюсь с планами запросов и разбираюсь в них. И в принципе крайне редко я видел многопоточные планы запросов.
Быть может на крупных hightload базах это выглядит иначе)
1с77 - да, криво, однопоточно.
Не зря ведь все (или почти все) "прикрутили" 1С++ и ПрямыеЗапросы к 77,
и получили побольше вольностей в отношении SQL на стороне клиента. Запросы пишем САМИ, не 1С.
При проведении документов: "ВременныеРегистры" считаю оптимизировать бессмысленно: единственное что может ускорить проведение: правильная расстановка измерений (чтобы select отрабатывал быстрее) и отказ на регистрах от лишних галочек (чтобы не тормозил insert/delete/update).
На этом оптимизация проведения документов заканчивается
(ну кроме - как всегда - собственно "бизнес" логики, и запутанных циклов/ветвлений/. )
Дальше: начинаем лезть во внутренности 1С77, кишки ей накручивать, оптимизировать SQL сервер.
1С8х
А разве "язык запросов" в снеговике не "просто парсится" самой 1С-кой в обычный SQL и отдается ODBC?
Или Вы про те запросы, которые отвечают за стандартные 1С-вские диалоги ?
Ну так их много и они очень коротки, там параллелится собственно нечему.
У меня = только те, что больше 1й секунды = exec sp_configure 'cost threshold for parallelism',1
Остальные автоматом будут последовательными.
РЕБЯТА. ОПЯТЬ ВЫ ПРО "ТОЛЩИНУ БАЗЫ" .
Параллелизм не зависит от толщины базы - он зависит от времени выполнения запроса.
А в случае сервера 1С (который типа кластер): разве там не используется параллелизм?
Они явно указывают MAXDOP=1 для всех и всегда .
Не верится что-то . Неужели настолько всё запущено.
уж 2015 год, 2014 SQL, 2012 Win. а 1С всё в ХХ веке?
Я действительно - не знаю, может Вы и правы.
Внутренности 1С8 не исследовал, но тогда. Блин, что же делать.
Нужно переходить на OpenERP ! или SAP .
Шутка.
(0) мы на курсах http://www.gilev.ru/kurs/ рассказываем прежде всего о том, что нет единого универсального рецепта для всех
создаваемое замедление состоит из набора разного количества причин и степени вклада в общее замедление
можно конечно делать "с бубном", но анализ причин на перспективу даст больше пользы
и железо, и настройки среды, и код - все должно быть сбалансировано
для маленьких баз можно больше результата достичь улучшением железа
для больших баз больше кодом.
Конечно же, это не универсальный рецепт на все случаи жизни.
Это ПОДХОД для решения конкретной задачи:
Условия:
* размер БД позволяет "засунуть" ее в RAM
* высокая интенсивность (нагрузка) на SQL - большое количество МЕЛКИХ запросов (у меня иногда до 5000/сек)
* крутится несколько "разношерстных" баз на одном SQL - оптимизировать каждую (средствами 1С) затратно (time=mony)
* в данных условиях узким местом являются: (а) дисковая система (б) процессор
Решение:
1. снять нагрузку на процессор: убираем ВСЁ что заставляет его пересчитывать и оптимизировать
2. отказываемся от тяжеловесных механизмов (таких как CHECKPOINT-ы)
3. помещаем БД в память
Требования:
1. памяти должно быть достаточно
2. память обязательно должна быть ECC .
3. обязательно "шедулим" бекапы БД из памяти на диск (чаще, чем это предполагает делать SQL авто-CHECKPOINT-ом)
Замечание1:
* у меня замечено, что даже при запуске "бухами" всяких там "концемесячных"
регламентов в 1С8 УПП = нагрузка на сервер сохраняется равномерная
* поэтому (конечно же, прежде всего) такой "подход подходит" (каламбурчик) к 1С77 (особенно при перепроведении БД)
* а уж если их (77) несколко запустить на перепроведение одновременно , тогда держитесь.
* да собственно и пользователи (если оставить БД в памяти на всегда) будут ОЧЕНЬ благодарны за скорость 8))
Замечание2:
* конечно же эти "мои" 1с77 давно используют "костыли": такие как 1С++, "ПрямыеЗапросы" и т.д.
* "ПрямыеЗапросы" (однозначно) использовать в 2х случаях: для форм (параметризованные) и для отчетов (select-ы всякие)
* а вот если говорить про модуль проведения: не думаю, что они будут эффективней "временных регистров" от 1С.
(кстати, известный всем, кто в теме 1с++ = ЁПРСТ = тоже так думает = это я о проведении и прямых запросах. )
ЭТА СТАТЬЯ НЕ РЕЦЕПТ - ЭТО ПОДХОД . И ДЛЯ 1С8 = ОН ТОЖЕ РАБОТАЕТ .
А рецепты - действительно - каждый должен варить сам: по своему вкусу. и своей ситуации 8))
P.S. Думаю этот подход позволит прожить 1С77 еще 20 лет, к тем которые уже она прожила. 8))
Настройки Microsoft SQL Server для работы с 1С:Предприятием
В данной статье приводится описание действий по настройке Microsoft SQL Server. Можно использовать как check-list для контроля.
Общие
Проверить, что установлен последний Service Pack и последний cumulative update.
Выровнять сектора дисков по границе 1024Кб и отформатировать с размером блока 64Кб (если нет иных рекомендаций от производителя).
Операционная система
Включить возможность «Database instant file initialization» для пользователя, от которого запущена служба Microsoft SQL Server
Проверить работу «Database instant file initialization».
Создать новую базу с размером файла данных 5 Гб, журнал транзакций - 1 Мб. Если база создалась моментально, то все работает корректно. Созданную базу - удалить.
Установить разрешение на «Lock pages in memory» (блокировку страниц в памяти) для пользователя, от которого запущена служба Microsoft SQL Server
Если сервер 1С:Предприятия установлен вместе с Microsoft SQL Server, то данную настройку производить не нужно.
Схема управления питанием – «Высокая производительность»
Проверить отсутствие сжатия файлов данных и файлов журналов.
Добавить файлы данных и журнала транзакций в исключения системы автоматического резервного копирования
Системы автоматического резервного копирования (например Symantec Backup Exec) не должны копировать файлы базы данных и журнала транзакций.
Настройки сервера (Server Properties)
Использование памяти.
Если сервер 1С:Предприятия установлен вместе с Microsoft SQL Server, то верхний порог памяти необходимо уменьшить на величину, достаточную для работы сервера 1С.
Задать расположение файлов базы данных по умолчанию.
Файлы данных и файлы журналов транзакций желательно размещать на разных дисковых массивах. При этом, требование к скорости дисковой подсистемы файла журнала транзакций, выше чем у файла данных. Cогласно рекомендации от Microsoft время отклика «диска» с файлами базы данных должно составлять 10-20 миллисекунд, а «диска» с файлами журнала транзакций 1-5 мс.
Установить параметр «Max degree of parallelism» = 1
Включить аутентификацию SQL Server.
Создать логины для каждой рабочей базы
Назначить логинам роли: public, dbcreator
Включить возможность административного подключения
Параметры базы данных
model
Новая база данных создается из копии базы model. Все настройки, указанные в model, будут в новой базе данных.
Начальный размер файла данных - от 1Гб до 10Гб.
Начальный размер журнала транзакций - от 1Гб до 2Гб.
Прирост файлов – 512Мб.
Установить модель восстановления, в зависимости от политики резервного копирования. Установить параметр «Auto update statistics asynchronously» = True
tempdb
Начальный размер файла данных:
- если tempdb расположена на отдельном массиве (диске), то начальный размер файла данных (Initial size) установить равным (50% всего объема / Кол-во файлов).
- если tempdb расположен вместе с рабочими базами данных, то начальный размер установить от 1Гб до 10Гб.
Прирост файлов – 512Мб
Рабочая база
Параметры рабочей базы аналогичны параметрам базы model, за исключением начального размера файлов (Initial Size).
Начальный размер файла данных стоит указывать равным ожидаемому размеру базы за длительный период эксплуатации.
Размер файла журнала транзакций следует указывать таким, чтобы исключить его расширение (auto grow). Т.е. указанного размера файла журнала должно хватать на весь период работы между операциями «BACKUP LOG».
Флаги трассировки
Настройка сетевых протоколов
Если сервер 1С:Предприятия расположен вместе с Microsoft SQL Server - включить протокол Shared Memory.
Протокол "Named pipes" необходимо отключить.
Обслуживание баз
Создать database mail account
Настроить операторов для оповещения об ошибках
В «планах обслуживания» при ошибках отправлять оповещение оператору
Настроить резервное копирование
Проверить восстановление базы
После того как был сделан первый автоматический бэкап, необходимо, на резервном сервере, восстановить базу данных и проверить ее работоспособность.
В предыдущем посте была рассмотрена автоматизация процесса дефрагментации индексов. Теперь пришла очередь статистики.
Собственно для чего она нужна?
При выполнении любого запроса, оптимизатор запросов, в рамках имеющейся у него информации, пытается построить оптимальный план выполнения — который будет отображать из себя последовательность операций, за счет выполнения которых можно получить требуемый результат, описанный в запросе.
В процессе выбора той или иной операции, оптимизатор запросов к числу наиболее важных входных данных относит статистику, описывающую распределение значений данных для столбцов внутри таблицы или индекса.
Такая оценка количества элементов позволяет оптимизатору запросов создавать более эффективные планы выполнения. В то же время, если статистика будет содержать устаревшие данные, могут быть выбраны менее эффективные операции, которые приведут к созданию медленных планов выполнения. Например, когда для небольшой выборки на устаревшей статистике выбирается более затратный оператор Index Scan, вместо оператора Index Seek.
Как Вы видите, чтобы быть максимально полезной для оптимизатора запросов, статистика должна быть точной и свежей. Время от времени SQL Server периодически сам обновляет статистику — данное поведение регулируется опциями AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS.
Кроме того, при пересоздании индексов, статистика по ним обновляется автоматически с включенным флагом FULLSCAN, гарантирующим наиболее точное распределение данных. При реорганизации индексов же — статистика не обновляется.
Когда данные в таблицах изменяются очень часто, целесообразно выполнять избирательное обновление статистики вручную, с помощью операции UPDATE STATISTICS.
Также ручное обновление, очень важно, когда для статистики задан флаг NORECOMPUTE, означающий, что автоматическое обновление статистики в дальнейшем не требуется. Просмотреть это свойство, как впрочем и на все остальные, можно в свойствах статистики:
Применяя возможности динамического SQL, напишем скрипт по автоматическому обновлению устаревшей статистики:
При выполнении будут генерироваться следующие стейтменты:
Критерий устаревания статистики в каждой конкретной ситуации может быть свой. В данном примере — 1 день.
В некоторых случаях слишком частное обновление статистики для больших таблиц может заметно снижать производительность базы данных, поэтому данный скрипт можно модифицировать. Например, для больших таблиц обновлять статистику реже:
В следующей части мы рассмотрим автоматизацию резервного копирования баз данных.
Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
SQL Server Typical Maintenance Plans: Automatic Statistics Update
За годы работы с SQL Server я обнаружила, что есть несколько тем, которые часто игнорируются. Их что боятся, думают, что они сложные или что они не такие важные. Также есть мнение, что эти знания не нужны, так как SQL Server "все делает за меня". Я слышала это об индексах. Я слышала это о статистике.
Итак, давайте поговорим, почему статистика важна и почему знание о том, что она важна, поможет вам существенно повлиять на производительность ваших запросов.
Есть несколько случаев, когда статистика обрабатывается автоматически:
- SQL Server автоматически создает статистику для индексов
- SQL Server автоматически создает статистику для столбцов, когда ему требуется больше информации для оптимизации запроса
- ВАЖНО! Это происходит только тогда, когда включен параметр базы данных auto_create_statistics. Этот параметр включен по умолчанию во всех версиях и редакциях SQL Server. Однако иногда встречаются рекомендации его выключить. Я категорически против этого.
- ВАЖНО! Это происходит только тогда, когда включен параметр базы данных auto_update_statistics. Этот параметр включен по умолчанию во всех версиях и редакциях SQL Server. Иногда также встречаются рекомендации его выключить. Обычно я против этого. Узнать больше об автоматическом обновлении вы можете в статье (англ.) Updating SQL Server Statistics Part I – Automatic Updates, а про ручное обновление статистики (но с помощью более избирательного подхода) в статье Updating SQL Server Statistics Part II – Scheduled Updates.
Однако, важно знать, что, хотя статистика обрабатывается автоматически, этот процесс не всегда работает так хорошо как вы ожидаете. Для действительно эффективной оптимизации SQL Server’у нужны определенные шаблоны и наборы данных. Для понимания этого, я хочу немного поговорить о доступе к данным и о процессе оптимизации.
Доступ к данным
Обычно, когда вы отправляете запрос к SQL Server для получения данных, вы пишете код на Transact-SQL в виде простого SELECT или, возможно, в виде хранимой процедуры (да, есть и другие варианты). Однако главное в том, что вы говорите какой набор данных вы хотите получить, а не описываете то, как эти данные должны быть извлечены. Как же SQL Server “доберется“ до данных?
Обработка данных
В получении и обработке данных SQL Server’у помогает статистика. Статистика предоставляет информацию о том, какой объем данных нужно будет обработать при выполнении запроса. Если нужно будет обработать небольшой объем данных, то обработка может быть проще (возможно, другим способом), чем если бы запрос обрабатывал миллионы строк.
В частности, SQL Server использует оптимизатор запросов на основе стоимости (cost based). Существуют и другие варианты оптимизации, но сегодня, чаще всего, используются оптимизаторы, основанные на стоимости. Почему? Оптимизаторы, основанные на стоимости, используют информацию о запрашиваемых данных, чтобы сформировать более эффективные, оптимальные и целенаправленные планы, с учетом информации об этих данных. Как правило, этот процесс работает хорошо. Хотя с планами, которые сохраняются для последующих выполнений (кэшированные планы), могут быть проблемы. Тем не менее, в других способах оптимизации есть еще более серьезные недостатки.
ВАЖНО! Я здесь не говорю о кеше планов… Я говорю о начальном процессе оптимизации и о том, как SQL Server определяет, какой объем данных ему надо будет получить. Последующее выполнение кэшированного плана может привести к дополнительным проблемам с ним (известными как parameter sniffing). Об этом много написано в других статьях.
Чтобы объяснить оптимизацию на основе стоимости, позвольте мне рассказать о других видах оптимизации. Это поможет понять преимущества стоимостной оптимизации.
Оптимизация на уровне синтаксиса
SQL Server может обработать запрос, используя только его текст и не тратить время на поиск наилучшего порядка обработки таблиц. Оптимизатор может просто соединить (join) ваши таблицы в том порядке, в котором вы их указали во FROM. Хотя для того чтобы начать выполнять запрос не требуется никаких затрат, но выполнение самого запроса может быть далеко не оптимальным. В общем случае, соединение больших таблиц с маленькими менее оптимально, чем соединение маленьких таблиц с большими. Давайте посмотрим на эти два примера:
Сравните стоимости планов. Второй запрос значительно дороже.
Стоимость одинаковых запросов с FORCE ORDER с разным порядком соединения.Да, это чрезмерное упрощение способов оптимизации соединения, но дело в том, что вы вряд ли укажете самостоятельно оптимальный порядок таблиц во FROM. Хорошая новость в том, что если у вас есть проблемы с производительностью, то вы можете применить такие оптимизации, как соединение таблиц в указанном порядке (см. пример выше). Есть много других хинтов (hint), которые вы можете использовать:
-
(принудительное использование уровня параллелизма [MAXDOP], оптимизация запроса для быстрого получения первых строк, а не всего набора данных с помощью FAST n и т.д.) (принудительное использование индекса [INDEX], поиска в индексе [FORCESEEK] и т.д.) (принудительное использование типа соединения LOOP / MERGE / HASH)
Надо сказать, что использование хинтов всегда должно быть последним способом оптимизации. Следует попробовать другие способы оптимизации, прежде чем использовать хинты. Применяя их, вы не позволяете SQL Server оптимизировать ваши запросы при добавлении или изменении индексов, при добавлении или изменении статистики и при обновлении SQL Server. Конечно, если только вы не задокументировали используемые хинты и не протестировали их, чтобы убедиться, что они останутся полезными для вашего запроса после выполнения заданий по обслуживанию БД, модификации данных и обновлений SQL Server. Честно говоря, я не вижу, чтобы это делалось так часто, как хотелось бы. Большинство хинтов добавляются так, как будто они всегда будут работать отлично, и остаются в запросе до тех пор, пока не возникнут серьезные проблемы.
Всегда важно посмотреть, на другие способы улучшения производительности, прежде чем применять хинты. Да, легко поставить хинт сейчас, но время, которое вы потратите на поиск других решений, может окупиться сполна в долгосрочной перспективе.
Для проблем с конкретным запросом с его конкретными значениями стоит посмотреть:
- Является ли статистика точной/актуальной? Исправит ли обновление статистики проблему?
- Статистика основана на частичной выборке? Исправит ли FULLSCAN проблему?
- Можете ли вы переписать запрос и улучшить план?
- Есть ли плохие условия поиска? Столбцы всегда должны находиться с одной стороны выражения:
- Так хорошо: MonthlySalary > expression / 12
- Так плохо: MonthlySalary * 12 > expression
FROM table2 AS t1
JOIN table2 AS t2 ON t1.colX = t2.colX
WHERE t1.colX = 12 AND t2.colX = 12- Иногда простой переход от соединения к подзапросу или от подзапроса к соединению исправляет проблему (нет, одно не всегда лучше другого, но иногда переписывание может помочь оптимизатору).
- Иногда использование производных (derived) таблиц (подзапросов в FROM (. ) AS J1) может помочь оптимизатору более оптимально соединить таблицы.
- Есть ли у вас условия OR? Можете ли вы переписать их через UNION или UNION ALL для получения такого же результата (это самое главное) с лучшим планом выполнения? Будьте осторожны, семантически это разные запросы. Вам нужно хорошо понимать различие между всеми ими.
- OR удаляет дубликаты строк (на основе ID строки)
- UNION удаляет дубликаты на основе столбцов, указанных в SELECT
- UNION ALL объединяет множества (что может быть намного быстрее, чем удаление дубликатов), но это может быть (а может и не быть) проблемой:
- иногда дубликатов нет (вы должны знать свои данные)
- иногда допустимо вернуть дубликаты (вы должны знать ваших пользователей / аудиторию / приложение)
Это совсем небольшой список, но он может помочь вам повысить производительность без использования хинтов. Это значит, что при последующих изменениях в данных, индексах, статистике, в версии SQL Server, оптимизатор сможет учитывать эти изменения!
Но все-таки здорово, что хинты есть, и если мы действительно нуждаемся в них, то можем ими воспользоваться. Бывают ситуации, когда оптимизатор может не быть в состоянии придумать эффективный план. Для этих случаев можно воспользоваться хинтами. Так что да, мне нравится, что они есть. Я просто не хочу, чтобы вы использовали их, пока не определите, истинную причину проблемы.
Так что, да, вы можете добиться оптимизации на уровне синтаксиса… если вам это нужно.
Оптимизация с использованием правил (эвристика)
Я упоминала, что оптимизация на основе стоимости требует статистики. Но что, если у вас нет статистики?
На это также можно посмотреть с другой стороны — почему SQL Server не может просто использовать "набор правил" для быстрой оптимизации запросов без необходимости просматривать/анализировать информацию о ваших данных? Разве это не было бы быстрее? SQL Server может делать так, но часто это бывает не самое лучшее решение. Для демонстрации этого надо запретить SQL Server при обработке запроса использовать статистику. Я могу показать пример, когда это действительно работает хорошо, и гораздо больше примеров, когда работает плохо.
Эвристика — это правила. Простые, статичные, фиксированные правила. Тот факт, что они простые, является их преимуществом. Не нужно смотреть на данные. Делается простая и быстрая оценка запроса на основе предикатов. Например, “меньше” и “больше” имеют внутреннее правило “30%”. Проще говоря, когда вы запускаете запрос с предикатом “больше” или “меньше” и нет информации о данных (статистики), SQL Server будет использовать правило, которое говорит, что условию будут соответствовать 30% данных. Оптимизатор будет использовать это в своих оценках и придумает план, соответствующий этому правилу.
Чтобы это "заработало", нужно сначала отключить auto_create_statistics и проверить существующие индексы и статистику:Посмотрите sp_helpindex и sp_helpstats . В базе данных WideWorldImporters в таблице Customers на столбце DeliveryPostalCode по умолчанию нет индексов и статистики. Если вы добавили что-то самостоятельно (или SQL Server создал автоматически), то следует их удалить перед выполнением следующих примеров.
Для первого запроса мы поставим ZipCode , равный 90248 и используем предикат “меньше”. Посмотрим как SQL Server оценит количество строк без использования статистики и возможности ее автоматического создания.
Столбцы без статистики будут использовать эвристику.
Если не найти «идеальное» значение, то большую часть времени эти правила будут неправильными!
Для первого запроса оценка работает хорошо (30% от 663 = 198,9), так как фактическое количество строк для запроса составляет 197. Один важный момент, на который стоит обратить внимание — это предупреждение рядом с таблицей Customers и около самого левого оператора SELECT. Оно говорит нам о том, что здесь что-то не так. Хотя, оценка количества строк «правильная».
Для второго запроса мы возьмем значение ZipCode равное 90003. Запрос точно такой же, за исключением значения ZipCode. Как сейчас SQL Server оценит количество строк?
Столбцы без статистики используют эвристику (простые правила). Часто они сильно ошибаются!
Для второго запроса оценка также равна 198,9, а фактически строк только 1. Почему? Потому что без статистики эвристика для “меньше” (и “больше”) составляет 30%. Тридцать процентов от 663 — это 198,9. Конкретное значение меняется при модификации данных, но процент остается постоянным 30%.Если этот запрос будет более сложным (с соединениями и/или дополнительными предикатами), то наличие некорректной информации — это уже проблема для последующих шагов оптимизации. Да, время от времени вам может везти с эвристиками, но это маловероятно. Более того эвристика для BETWEEN и “равно” отличается от значений для “меньше” и “больше” (равной равна 30%). На самом деле, некоторые из них даже меняются в зависимости от используемой вами модели оценки кардинальности (например, для “равно”). А меня, вообще, это должно беспокоить? На самом деле, нет! В действительности я никогда не хочу их использовать.
Итак, SQL Server может использовать оптимизацию на основе правил… но только тогда, когда у него нет лучшей информации.
Я не хочу эвристику! Я хочу статистику!
Статистика — это одно из немногих мест в SQL Server, которой не может быть мало. Нет, я не говорю о том, чтобы создавать статистику для каждого столбца таблицы, но есть некоторые случаи, когда можно предварительно создать статистику. Но это тема для отдельной статьи.
Итак, почему статистика так важна для стоимостной оптимизации?Оптимизация на основе стоимости
Что же на самом деле делает оптимизация на основе стоимости? Если кратко, то SQL Server быстро получает приблизительную оценку того, сколько данных будет обработано. Затем, используя эту информацию, он оценивает стоимость различных алгоритмов, которые могут быть использованы для доступа к данным. После этого, основываясь на “стоимости” этих алгоритмов, SQL Server выбирает тот, который, по его расчетам, является наименее дороги. Затем он его компилирует и выполняет.
Это звучит здорово, но есть много факторов, когда это может работать не так хорошо, как хотелось бы. Самое главное, что базовая информация, используемая для выполнения оценки (статистика), может быть некорректной:
- Статистика может быть устаревшей
- Статистика может быть не точной из-за ограниченной выборки
- Статистика может быть не точной из-за размера таблицы и ограничений того, что храниться в ней.
Некоторые спросят меня — может ли SQL Server иметь более подробную статистику (более подробные гистограммы и т.п.)? Да, может. Но тогда процесс чтения / доступа к этой, все большей и большей, статистике будет становиться все дороже (и занимать больше времени, больше кеша и т. д.). Что, в свою очередь, сделает процесс оптимизации более дорогим. Это сложная проблема. Везде есть плюсы и минусы, компромиссы. На самом деле, все не так просто, как “подробные гистограммы”.
Наконец, в процессе оптимизации нельзя проанализировать все возможные комбинации планов. Это сделало бы сам процесс оптимизации настолько дорогим, что это было бы непозволительно!
Лучший способ думать о процессе оптимизации — это как найти “хороший план быстро”. Иначе процесс оптимизации стал бы настолько сложным и затянулся бы настолько, что не достиг бы своей цели!
Итак, почему статистика очень важна:
- Она используется на всем протяжении процесса стоимостной оптимизации (а вы хотите оптимизацию на основе стоимости)
- Она должна присутствовать, иначе вы будете вынуждены использовать эвристику
- как правило, я настоятельно рекомендую включить параметр auto_create_statistics, если вы его выключили
СТАТИСТИКА — КЛЮЧ К ЛУЧШЕЙ ОПТИМИЗАЦИИ И, СЛЕДОВАТЕЛЬНО, ЛУЧШЕЙ ПРОИЗВОДИТЕЛЬНОСТИ (но это все еще пока далеко от идеала!)
Я надеюсь, что эта статья мотивирует вас изучить больше о статистике. Статистика в SQL Server на самом деле проще, чем вы думаете и, очевидно, она очень, очень важна!
Довольно старая, но все еще полезная статья — Statistics Used by the Query Optimizer in Microsoft SQL Server 2008Читайте также:
- Есть ли плохие условия поиска? Столбцы всегда должны находиться с одной стороны выражения: