Auto update statistics asynchronously 1с для чего
При этом предполагается "вмешательство" в стандартные структуры и процедуры БД от 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) 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.
autocreate stat off
autoupdate stat off
page verify torn page detection
Учитывая, что указанные опции строго рекомендуется ВКЛЮЧИТЬ, а 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, Чуть ниже постом, Вы говорите:
Вот только базы с параметрами . <skip>. вряд ли можно отнести к большим базамДанная статья не рассматривает работу 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 0allows 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))
Регламентные операции на уровне СУБД для MS SQL Server
Инструкция по выполнению регламентных операций на уровне СУБД.
Информация применима к клиент-серверному варианту "1С:Предприятия 8" при использовании СУБД MS SQL Server.
Общие сведения
Одной из часто встречающихся причин неоптимальной работы системы является неправильное или несвоевременное выполнение регламентных операций на уровне СУБД. Особенно важно выполнять эти регламентные процедуры в крупных информационных системах, которые работают под значительной нагрузкой и обслуживают одновременно большое количество пользователей. Специфика таких систем в том, что обычных действий, выполняемых СУБД автоматически (на основании настроек) оказывает недостаточно для эффективной работы.
Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.
Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур. В настоящей статье для каждой регламентной процедуры дан пример ее настройки при помощи Maintenance Plan для MS SQL Server 2005.
Для MS SQL Server рекомендуется выполнять следующие регламентные операции:
Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.
Обновление статистик
MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.
В этом случае возможно проявление проблем с производительностью запросов. При этом в планах запросов наблюдаются характерные признаки неоптимальной работы (неоптимальные операции).
Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.
Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий SQL запрос:
Обновление статистик не приводит к блокировке таблиц, и не будет мешать работе других пользователей. Статистика может обновляться настолько часто, насколько это необходимо. Следует учитывать, что нагрузка на сервер СУБД во время обновления статистик возрастет, что может негативно сказаться на общей производительности системы.
Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день .
Приведенный выше запрос обновляет статистики для всех таблиц базы данных. В реально работающей системе разные таблицы требуют различной частоты обновления статистик. Путем анализа планов запроса можно установить, какие таблицы больше других нуждаются в частом обновлении статистик, и настроить две (или более) различных регламентных процедуры: для часто обновляемых таблиц и для всех остальных таблиц. Такой подход позволит существенно снизить время обновления статистик и влияние процесса обновления статистики на работу системы в целом.
Настройка автоматического обновления статистик (MS SQL 2005)
Запустите MS SQL Server Management Studio и подключитесь к серверу СУБД. Откройте папку Management и создайте новый план обслуживания:
Создайте субплан (Add Subplan) и назовите его «Обновление статистик». Добавьте в него задачу Update Statistics Task из панели задач:
Настройте расписание обновления статистик. Рекомендуется обновлять статистики не реже одного раза в день. При необходимости частота обновления статистик может быть увеличена.
Настройте параметры задачи. Для этого следует два раза кликнуть на задачу в правом нижнем углу окна. В появившейся форме укажите имя базу данных (или несколько баз данных) для которых будет выполняться обновление статистик. Кроме этого вы можете указать для каких таблиц обновлять статистики (если точно неизвестно, какие таблицы требуется указать, то устанавливайте значение All).
Обновление статистик необходимо проводить с включенной опцией Full Scan.
Сохраните созданный план. При наступлении указанного в расписании срока обновление статистик будет запущено автоматически.
Очистка процедурного КЭШа
Оптимизатор MS SQL Server кэширует планы запросов для их повторного выполнения. Это делается для того, чтобы экономить время, затрачиваемое на компиляцию запроса в том случае, если такой же запрос уже выполнялся и его план известен.
Возможна ситуация, при которой MS SQL Server, ориентируясь на устаревшую статистическую информацию, построит неоптимальный план запроса. Этот план будет сохранен в процедурном КЭШе и использован при повторном вызове такого же запроса. Если Вы обновили статистику, но не очистили процедурный кэш, то SQL Server может выбрать старый (неоптимальный) план запроса из КЭШа вместо того, чтобы построить новый (более оптимальный) план.
Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.
Для очистки процедурного КЭШа MS SQL Server необходимо выполнить следующий SQL запрос:
Этот запрос следует выполнять непосредственно после обновления статистики. Соответственно, частота его выполнения должна совпадать с частотой обновления статистики.
Настройка очистки процедурного КЭШа (MS SQL 2005)
Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.
В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:
Дефрагментация индексов
При интенсивной работе с таблицами базы данных возникает эффект фрагментации индексов, который может привести к снижению эффективности работы запросов.
Рекомендуется регулярное выполнение дефрагментации индексов. Для дефрагментации всех индексов всех таблиц базы данных необходимо использовать следующий SQL запрос (предварительно подставив имя базы):
sp_msforeachtable N'DBCC INDEXDEFRAG (<имя базы данных>, ''?'')'
Дефрагментация индексов не блокирует таблицы, и не будет мешать работе других пользователей, однако создает дополнительную нагрузку на SQL Server. Оптимальная частота выполнения данной регламентной процедуры должна подбираться в соответствии с нагрузкой на систему и эффектом, получаемым от дефрагментации. Рекомендуется выполнять дефрагментацию индексов не реже одного раза в неделю.
Возможно выполнение дефрагментации для одной или нескольких таблиц, а не для всех таблиц базы данных.
Настройка дефрагментации индексов (MS SQL 2005)
В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов».Добавьте в него задачу Reorganize Index Task:
Задайте расписание выполнения для задачи дефрагментации индексов. Рекомендуется выполнять задачу не реже одного раза в неделю, а при высокой изменчивости данных в базе еще чаще – до одного раза в день.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.
Реиндексация таблиц базы данных
Реиндексация таблиц включает полное перестроение индексов таблиц базы данных, что приводит к существенной оптимизации их работы. Рекомендуется выполнять регулярную переиндексацию таблиц базы данных. Для реиндексации всех таблиц базы данных необходимо выполнить следующий SQL запрос:
sp_msforeachtable N'DBCC DBREINDEX (''?'')'
Реиндексация таблиц блокирует их на все время своей работы, что может существенно сказаться на работе пользователей. В связи с этим реиндексацию рекомендуется выполнять во время минимальной загрузки системы.
После выполнения реиндексации нет необходимости делать дефрагментацию индексов.
Настройка реиндексации таблиц (MS SQL 2005)
В ранее созданном плане обслуживания создайте новый субплан с именем «Реиндексация». Добавьте в него задачу Rebuild Index Task:
Задайте расписание выполнения для задачи реиндексирования таблиц. Рекомендуется выполнять задачу во время минимальной нагрузки на систему, не реже одного раза в неделю.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.
Контроль выполнения регламентных процедур на уровне СУБД
Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.
Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:
Откроется окно с протоколом выполнения всех заданных регламентных процедур.
Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.
MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.
В этом случае возможно проявление проблем с производительностью запросов. При этом в планах запросов наблюдаются характерные признаки неоптимальной работы (неоптимальные операции).
Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.
Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий SQL запрос:
Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день.
Приведенный выше запрос обновляет статистики для всех таблиц базы данных. В реально работающей системе разные таблицы требуют различной частоты обновления статистик.
Поэтому в общем случае, путем анализа планов запроса можно установить, какие таблицы больше других нуждаются в частом обновлении статистик, и настроить две (или более) различных регламентных процедуры: для часто обновляемых таблиц и для всех остальных таблиц. Такой подход позволит существенно снизить время обновления статистик и влияние процесса обновления статистики на работу системы в целом.
В Вашем случае, имеет смысл настроить хотя бы ежедневное обновление статистик.
Можно сделать, например, так: В MS SQL:
1) Создайте новый план обслуживания
2) Создайте субплан (Add Subplan) и назовите его «Обновление статистик».
3) Добавьте в него задачу Update Statistics Task из панели задач:
4) Настройте расписание обновления статистик (рекомендация не реже 1 раза в день).
5) Настройте саму задачу:
5.1) Базу данных для который выполняется обновление статистики
5.3) Укажите опцию Full scan
(Примечание. Такой режим будет эквивалентен скрипту
где dname имя вашей базы)
Также после обновлении статистики имеет смысл очистить процедурный кэш (иначе запросы будут выполнятся по планам из кэш, которые могли быть сформированы на основании устаревшей статистики.
Поэтому в субплан:
6) Добавьте задачу Execute T-SQL Statement Task.
7) Соедините задачу Update Statistics Task стрелочкой с новой задачей
8) В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»
Важно знать:
Если включен параметр AUTO_UPDATE_STATISTICS (автоматическое обновление статистики), оптимизатор запросов определяет, устарела ли статистика, и при необходимости обновляет ее, если она используется в запросе. Статистика становится устаревшей, после того как операции вставки, обновления, удаления или слияния изменяют распределение данных в таблице или индексированном представлении. Оптимизатор запросов определяет, устарела ли статистика, подсчитывая операции изменения данных с момента последнего обновления статистики и сравнивая число изменений с пороговым значением. Пороговое значение основано на количестве строк в таблице или индексированном представлении.
Если используется версия до SQL Server 2014 (12.x), SQL Server применяет пороговое значение в зависимости от процента измененных строк. Это значение не зависит от числа строк в таблице. Пороговое значение:
Если на момент оценки статистических данных кратность в таблице не превышала 500, обновление выполняется для каждых 500 модификаций.
Если на момент оценки статистических данных кратность в таблице превышала 500, обновление выполняется для каждых 500 + 20 % модификаций
Начиная с версии SQL Server 2016 (13.x) и при уровне совместимости базы данных 130 SQL Server используется пороговое значение для динамического обновления статистических данных по убыванию. Значение изменяется в зависимости от числа строк в таблице. Оно вычисляется как квадратный корень из произведения текущего значения кратности в таблице и 1000. Например, если таблица содержит 2 миллиона строк, значение вычисляется как квадратный корень из (1000 * 2000000) = 44721,359. Благодаря этому изменению статистика для больших таблиц будет обновляться чаще. Но если уровень совместимости для базы данных ниже 130, применяется пороговое значение SQL Server 2014 (12.x).
Оптимизатор запросов проверяет наличие устаревшей статистики перед компиляцией запроса и до выполнения кэшированного плана запроса.
Параметр AUTO_UPDATE_STATISTICS_ASYNC (асинхронное обновление статистики) определяет, какой режим обновления статистики использует оптимизатор запросов: синхронный или асинхронный. По умолчанию параметр асинхронного обновления статистики отключен, и оптимизатор запросов обновляет статистику синхронно.
При синхронном обновлении статистики запросы всегда компилируются и выполняются с актуальной статистикой. Если статистика оказывается устаревшей, оптимизатор запросов ожидает появления обновленной статистики, прежде чем начать компиляцию и выполнение запроса. При асинхронном обновлении статистики запросы компилируются с существующей статистикой, даже если она устарела. Если на момент компиляции запроса статистика оказывается устаревшей, оптимизатор запросов может выбрать неоптимальный план запроса. Запросы, которые компилируются после выполнения асинхронного обновления, будут усовершенствованы благодаря использованию обновленной статистики.
Асинхронная статистика рекомендуется для достижения более прогнозируемого времени ответа на запросы в следующих сценариях.
Приложение часто выполняет один и тот же запрос, схожие запросы или схожие кэшированные планы запроса. Асинхронное обновление статистики может обеспечить более прогнозируемое время ответа на запрос по сравнению с синхронным, так как оптимизатор запросов может выполнять входящие запросы, не ожидая появления актуальной статистики. Это устраняет задержку в некоторых запросах, но не влияет на другие запросы.
Были случаи, когда в приложении истекало время ожидания клиентских запросов в результате ожидания обновленной статистики. В некоторых случаях ожидание синхронной статистики может вызвать аварийное завершение приложений, в которых задано малое время ожидания.
1. Оставить один логический процессор, или оставить один физический процессор, старый сервер был один физический и два логических, с гипертрейдингом
2. Требуется ли после этого перезапуск сервера? Если требуется, то смогу проверить только поздно вечером.
3. Стоит ли менять настройки связаные с паралелизмом?
Или оставить как есть?Для проверки "вреда" параллелизма, попробуйте, рассматриваемый выше запрос, протестировать в Managment Studio в оригинальном виде и с OPTION (MAXDOP 1)
+1
если запрос выполнится существенно быстрее
для установки Max Degree of paralelizm, 1
перезагрузка не нужна
Вот! В этом и соль. Для OLTP системы как раз актуально как можно быстрее выполнить эти короткие запросы (в основном модифицирующие), которые в случаи "стандартного" RC могут висеть на S локах "тяжелых" запросов, типа озвученного в этом топике.
Используются все четыре процессора, проценты показываются не для одного процессора, а как сумма всех процессоров принимается равной 100%, поэтому каждый процессор нагружен примерно на 25%, ну иногда третий на 50, тогда остальные меньше 25%
Когда запросов нет, то нагрузка менее 2%.
Сеть показывает стабильную нагрузку в районе 2 кб/сек (мирроринг).
Из множества запросов видел использование одного процессора, только для одного запроса из отчетов по зарплате.
Сейчас deadlock исчез, время исполнения запроса практически не уменьшилось, так что индекс по DealDate не помог.
Непонято. Каждое отдельно взятое ядро может быть нагружено в диапазоне от 0 до 100%, что собсно и видно в таск менеджере.
Опция MaxDOP регулирует IntraQuery Parallelizm.
Мне это мало что сказало :-(
Следует ли понимать, что регулирует выполнение паралельных запросов или как?
Вечером попробую привязать сервер сначала к двум логическим процессорам, а потом к одному.
Возможно я зря грешу на падение из-за переноса, это может быть связано и с изменением запроса конфигуратором, он как раз в этот период занимался этим, но он пока не доступен.
MAXDOP запрешает распаралеливание одного конкретного запроса. IntraQuery Parallelizm означает паралелльное выполнение отдельных участков плана выполнения. Даже поставив MAXDOP = 1 вы не заставите Database Engine не использовать в параллель все доступные ему процессоры\ядра для выполнения своих "внутренних" операций.
1. У Вас в плане нет параллелизма.
2. Отключать IntraQuery Parallelizm стоит только в случаи некорректного выполнения запроса.
MAXDOP запрешает распаралеливание одного конкретного запроса. IntraQuery Parallelizm означает паралелльное выполнение отдельных участков плана выполнения. Даже поставив MAXDOP = 1 вы не заставите Database Engine не использовать в параллель все доступные ему процессоры\ядра для выполнения своих "внутренних" операций.
1. У Вас в плане нет параллелизма.
2. Отключать IntraQuery Parallelizm стоит только в случаи некорректного выполнения запроса.
если же делаю обновление статистики с помощью процедуры
Exec sp_updatestats
то обновление идет всего 5 минут.
Подскажите в чем разница? Почему процедурой получается намного быстрее? Как делать обновление статистики правильно?
Предполагаю , что увеличение времени идет из-за FULLSCAN, но насколько оно необходимо?
если же делаю обновление статистики с помощью процедуры
Exec sp_updatestats
то обновление идет всего 5 минут.
Подскажите в чем разница? Почему процедурой получается намного быстрее? Как делать обновление статистики правильно?
Предполагаю , что увеличение времени идет из-за FULLSCAN, но насколько оно необходимо?
AUTO_UPDATE_STATISTICS Option
When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.
The query optimizer checks for out-of-date statistics before compiling a query and before executing a cached query plan. Before compiling a query, the query optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. Before executing a cached query plan, the Database Engine verifies that the query plan references up-to-date statistics.
В теории - да. На практике, критерии автообновления статистики сервером совершенно неприемлемы, если таблица уже достаточно большая, скажем 10М записей, и регулярно обновляется. Говорят, в 2012 это поправили, но насколько полноценно - хз.
Читайте также: