» »

Создание физической модели базы данных: проектирование производительности. Создание физической модели базы данных: проектирование производительности 7 какие преимущества дает горизонтальное секционирование таблиц
04.11.2021

В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании Microsoft, приведу выдержку:

«Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.

  • Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.
  • Операции обслуживания можно выполнять быстрее с одной или несколькими секциями. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.
  • Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.

В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. SQL Server может одновременно обращаться только к одному диску, что может снизить производительность. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы ».

К недостаткам же можно отнести сложность в администрировании и поддержке работы секционированных таблиц.

Мы не будем останавливаться на вопросах реализации секционирования, так как данный вопрос очень хорошо описан на сайте компании Microsoft .

Вместо этого мы постараемся показать способ оптимизации работы секционированных таблиц, а точнее покажем оптимальный (на наш взгляд) способ обновления данных за любой промежуток времени.

Большим плюсом секционированной таблицы является физическое разграничение данных секций. Это свойство позволяет нам менять секции местами между собой или же с любой другой таблицей.
При обычном обновлении данных с использованием скользящего окна (к примеру, за месяц), нам потребуется пройти следующие этапы:

1. Найти нужные строки в большой таблице;
2. Удалить найденные строки из таблицы и индекса;
3. Вставить новые строки в таблицу, обновить индекс.

При хранении в таблице миллиардов строк эти операции займут довольно продолжительное время, мы же можем ограничиться практически одним действием: просто заменить нужную секцию на подготовленную заранее таблицу (или секцию). При этом нам не потребуется удалять или вставлять строки, а так же нужно обновлять индекс на всей большой таблице.

Перейдем от слов к делу и покажем, как же это реализовать.

1. Для начала настраиваем секционированную таблицу так, как написано в статье, указанной выше.
2. Создаем таблицы, необходимые для обмена.

Для обновления данных нам потребуется мини-копия целевой таблицы. Мини-копией она является потому, что в ней будут храниться данные, которые должны добавиться в целевую таблицу, т.е. данные всего за 1 месяц. Так же потребуется третья пустая таблица для реализации обмена данных. Зачем она нужна – объясню позже.

К мини-копии и таблице для обмена ставятся жесткие условия:

  • До использования оператора SWITCH должны существовать обе таблицы. Перед выполнением операции переключения в базе данных должны существовать и таблица, откуда перемещается секция (исходная таблица), и таблица, получающая секцию (целевая таблица).
  • Секция-получатель должна существовать и должна быть пустой. Если таблица добавляется как секция в уже существующую секционированную таблицу или секция перемещается из одной секционированной таблицы в другую, то секция-получатель должна существовать и быть пустой.
  • Несекционированная таблица-получатель должна существовать и должна быть пустой. Если секция предназначена для формирования единой несекционированной таблицы, то необходимо, чтобы таблица, получающая новую секцию, существовала и являлась пустой несекционированной таблицей.
  • Секции должны быть из одного и того же столбца. Если секция переключена из одной секционированной таблицы в другую, то обе таблицы должны быть секционированы по одному и тому же столбцу.
  • Исходная и целевая таблицы должны находиться в одной и той же файловой группе. Исходная и целевая таблицы в инструкции ALTER TABLE...SWITCH должны храниться в одной и той же файловой группе, так же как и их столбцы с большими значениями. Любые соответствующие индексы, секции индексов или индексированные представления секций также должны храниться в той же файловой группе. Однако она может отличаться от файловой группы для соответствующих таблиц или других соответствующих индексов.

Объясню ограничения на нашем примере:

1. Мини-копия таблицы должна быть секционирована по тому же столбцу, что и целевая. Если мини-копия является не секционированной таблицей, то она должна хранится в той же файловой группе, что и заменяемая секция.

2. Таблица для обмена должна быть пустой и так же должна быть секционированна по тому же столбцу или же должна храниться в той же файловой группе.

3. Реализуем обмен.

Сейчас мы имеем следующее:
Таблица с данными за все времена (далее Table_A)
Таблица с данными за 1 месяц (далее Table_B)
Пустая таблица (далее Table_C)

Первым делом нам нужно узнать в какой секции у нас хранятся данные.
Узнать это можно запросом:

SELECT
count(*) as
, $PARTITION.(dt) as
, rank() over (order by $PARTITION.(dt))
FROM dbo. (nolock)
group by $PARTITION.(dt)

В этом запросе мы получаем секции, в которых есть строки с информацией. Количество можно не подсчитывать – мы это делали для проверки необходимости обмена данных. Rank же используем, чтобы можно было идти в цикле и обновлять несколько секций в одной процедуре.

Как только узнали в каких секциях у нас хранятся данные – их можно менять местами. Допустим, что данные хранятся в секции 1.

Тогда нужно выполнить следующие операции:
Поменять секции из целевой таблицы с таблицей для обмена.
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице не осталось данных в нужной нам секции, т.е. секция пуста
Поменять местами секции из целевой таблицы и мини-копии
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице появились данные за месяц, а в мини-копии теперь пустота
Очистить или удалить таблицу для обмена.

Если у вас на таблице имеется кластерный индекс, то так же не проблема. Его необходимо создать на всех 3 таблицах с секционированием по одному и тому же столбцу. При смене секций индекс автоматически обновится, не перестраиваясь.

Можно создать секционированную таблицу или индекс в SQL Server 2016 с помощью SQL Server Management Studio или Transact-SQL. Данные в секционированной таблице и индексах горизонтально разделены на блоки, которые могут быть распределены между несколькими файловыми группами в базе данных. Секционирование может улучшить управляемость и масштабируемость больших таблиц и индексов.

Или индекса обычно включает четыре этапа:

    Создание файловой группы или файловых групп и соответствующих файлов, которые будут содержать секции в соответствии со схемой секционирования.

    Создание функции секционирования, которая сопоставляет строки таблицы или индекса с секциями, основываясь на значениях элементов заданного столбца.

    Создание схемы секционирования, которая сопоставляет секции секционированной таблицы или индекса с новыми файловыми группами.

    Создание или изменение таблицы или индекса и указание схемы секционирования в качестве местоположения хранения.

В этом разделе

    Перед началом работы выполните следующие действия.

    Ограничения

    Безопасность

    Создание секционированной таблицы или индекса с использованием следующих средств:

    Среда SQL Server Management Studio

Ограничения

    Область действия функции и схемы секционирования ограничена базой данных, в которой она была создана. Функции секционирования располагаются в отдельном от других функций пространстве имен внутри базы данных.

    Если у каких-либо строк в функции секционирования имеются столбцы секционирования со значениями NULL, эти строки размещаются в крайней левой секции. Однако если значение NULL указано в качестве граничного значения и указан параметр RIGHT, крайняя левая секция остается пустой, а значения NULL располагаются во второй секции.

Безопасность

Разрешения

Для создания секционированной таблицы требуется разрешение CREATE TABLE в базе данных и разрешение ALTER для схемы, в которой создается таблица. Для создания секционированного индекса требуется разрешение ALTER на таблицу или представление, в которых создается индекс. Создание секционированной таблицы или индекса требует любого из следующих дополнительных разрешений:

    Разрешение ALTER ANY DATASPACE. Это разрешение назначено по умолчанию членам предопределенной роли сервера sysadmin и предопределенных ролей базы данных db_owner и db_ddladmin .

    Разрешение CONTROL или ALTER для базы данных, в которой создаются функция и схема секционирования.

    Разрешение CONTROL SERVER или ALTER ANY DATABASE для сервера базы данных, в которой создаются функция и схема секционирования.

Выполните пошаговые инструкции в этой процедуре для создания одной или нескольких файловых групп, соответствующих файлов и таблицы. В следующем примере при создании секционированной таблицы будут приведены ссылки на эти объекты.

Создание новых файловых групп для секционированной таблицы

Создание секционированной таблицы

    Щелкните правой кнопкой мыши таблицу для секционирования, выберите Хранение и щелкните Создать секцию…

    В Мастере создания секций на странице Приветствия мастера создания секций щелкните Далее .

    На странице Выбор столбца секционирования в сетке выберите столбец, по которому необходимо секционировать таблицу. В сетке Доступные столбцы секционирования отображаются только столбцы с типами данных, по которым можно секционировать данные. Если выбрать в качестве столбца секционирования вычисляемый столбец, его необходимо назначить постоянным.

    Выбор столбца секционирования и диапазона значений определяется прежде всего степенью, до которой данные должны быть логически сгруппированы. Например, можно разделить данные на логические группы по месяцам или кварталам года. Планируемые запросы к данным определяют, адекватно ли такое логическое группирование для управления секциями таблицы. В качестве столбцов секционирования могут использоваться данные любого типа, кроме text , ntext , image , xml , timestamp , varchar(max) , nvarchar(max) , varbinary(max) , псевдонимов типов данных, а также определяемых пользователем типов данных CLR.

    Совместное размещение этой таблицы с выбранной секционированной таблицей
    Позволяет выбрать секционированную таблицу, которая содержит связанные данные, для соединения с данной таблицей по столбцу секционирования. Таблицы с секциями, объединенные по столбцам секционирования, обычно более эффективны в запросах.

    Выровнять хранение неуникальных и уникальных индексов с индексированным столбцом секционирования
    Выравнивает все индексы таблицы, секционированные с помощью одной и той же схемы. При выравнивании таблицы и ее индексов секции можно более эффективно перемещать в секционированные таблицы и из них, так как данные секционируются по одному и тому же алгоритму.

    После выбора столбца секционирования и других столбцов щелкните Далее .

    На странице Выбор функции секционирования в разделе Выберите функцию секционирования щелкните или . При выборе Создать функцию секционирования введите имя функции. Если выбран вариант Существующая функция секционирования , то выберите в списке имя функции, которая будет использоваться для секционирования. Обратите внимание, что при отсутствии в базе данных других функций секционирования параметр Существующая функция секционирования будет недоступен.

    Далее .

    На странице Выбор схемы секционирования в разделе Выберите схему секционирования щелкните или . При выборе Создать схему секционирования введите имя схемы. Если выбран вариант Существующая схема секционирования , то выберите в списке имя схемы, которая будет использоваться. При отсутствии других схем секционирования в базе данных параметр Существующая схема секционирования будет недоступен.

    После завершения работы с этой страницей нажмите кнопку Далее .

    На странице Сопоставление секций в разделе Диапазон выберите Левая граница или Правая граница , чтобы выбрать для включения во все создаваемые файловые группы наибольшее или наименьшее ограничивающее значение. В дополнение к числу файловых групп, указанному в качестве граничных значений при создании секций, необходимо всегда вводить еще одну дополнительную файловую группу.

    В сетке Выбор файловых групп и указание граничных значений в поле Файловая группа выберите файловую группу, в которую будут секционироваться данные. В разделе Граница введите граничное значение для каждой файловой группы. Если граничное значение не указано, функция секционирования сопоставляет всю таблицу или индекс с одной секцией, используя имя функции секционирования.

    На этой странице доступны следующие дополнительные параметры:

    Установить границы…
    Открытие диалогового окна Установка граничных значений , в котором можно выбрать граничные значения и диапазоны дат для секций. Этот параметр доступен, только если выбран столбец секционирования, содержащий данные одного из следующих типов: date , datetime , smalldatetime , datetime2 или datetimeoffset .

    Оценка хранения
    Оценка количества строк, необходимого и доступного пространства для хранения каждой файловой группы, указанной для секций. Эти значения доступны в сетке только для чтения.

    В диалоговом окне Задание граничных значений можно задать следующие дополнительные параметры:

    Дата начала
    Выбор даты начала для значений диапазона секций.

    Дата окончания
    Выбор даты окончания для значений диапазона секций. При выборе Левая граница на странице Сопоставление секций эта дата будет последним значением для каждой из файловых групп и секций. При выборе Правая граница на странице Сопоставление секций эта дата будет первым значением в предпоследней файловой группе.

    Диапазон даты
    Выбор степени детализации дат или шага значения диапазона для каждой секции.

    После завершения работы с этой страницей нажмите кнопку Далее .

    На странице Выбор выходного параметра укажите способ заполнения секционированной таблицы. Выберите Создать скрипт для создания скрипта SQL на основе данных на предыдущих страницах мастера. Выберите Запустить немедленно , чтобы создать новую секционированную таблицу после завершения работ со всеми оставшимися страницами мастера. Выберите Расписание , чтобы создать новую секционированную таблицу в заранее заданное время в будущем.

    При выборе Создать скрипт в Параметры скрипта будут доступны следующие параметры:

    Вывести скрипт в файл
    Создание скрипта как SQL-файла. Введите имя и местоположение файла в поле Имя файла или щелкните Обзор , чтобы открыть диалоговое окно Расположение файла скрипта . В разделе Сохранить как выберите Текст в Юникоде или Текст ANSI .

    Вывести скрипт в буфер обмена
    Сохранение скрипта в буфере обмена.

    Вывести скрипт в новое окно запроса
    Скрипт создается в новом окне редактора запросов. Это параметр выбирается по умолчанию.

    При выборе Расписание щелкните Изменить расписание .

    1. В диалоговом окне Создание расписания задания в поле Имя введите имя расписания задания.

      В списке Тип расписания выберите тип расписания:

      • Запускать автоматически при запуске агента SQL Server

        Запускать при бездействии процессоров

        Повторяющееся . Выберите этот параметр, если новая секционированная таблица регулярно обновляется с учетом новых данных.

        Однократно . Это параметр выбирается по умолчанию.

      2. Установите или снимите флажок Включен , чтобы включить или отключить расписание.

      При выборе Повторяющееся :

      1. В разделе Частота в списке Выполняется укажите частоту выполнения:

        • При выборе Ежедневно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в днях.

          При выборе Еженедельно в поле Выполняется каждые укажите частоту повторного выполнения расписания задания в неделях. Выберите день или дни недели, в которые выполняется расписание задания.

          При выборе Ежемесячно щелкните День или Определенный .

          • При выборе День введите дату месяца, в которую должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось 15 числа каждого второго месяца, выберите День и введите в первом поле «15» и «2» - во втором поле. Обратите внимание, что число, введенное во втором поле, не должно превышать «99».

            При выборе Определенный выберите определенный день недели в месяце, в котором должно выполняться расписание задания, и укажите частоту повторного выполнения расписания задания в месяцах. Например, если требуется, чтобы расписание задания выполнялось в последний день недели каждого второго месяца, выберите День , выберите последний в первом списке и рабочий день во втором списке, а затем введите «2» во втором поле. Еще можно выбрать первый , второй , третий или четвертый , а также конкретные дни недели (например, воскресенье или среду) в первых двух списках. Обратите внимание, что число, введенное в последнем поле, не должно превышать «99».

        2. В поле Сколько раз в день укажите частоту повторного выполнения расписания задания в день запуска расписания задания:

        • При выборе Выполнять раз в укажите определенное время дня для запуска расписания задания в поле Выполнять раз в . Укажите время дня: час, минуту и секунду.

          При выборе Выполняется каждые укажите частоту выполнения задания в выбранный день в поле Частота . Например, если требуется, чтобы расписание задания выполнялось каждые 2 часа в день запуска расписания задания, выберите Выполняется кажд. , введите "2" в первом поле, а затем выберите в списке часы . В этом списке также можно выбрать минуты и секунды . Обратите внимание, что число, введенное в первом поле, не должно превышать «100».

          В поле Начинать в введите время для начала запуска расписания задания. В поле Заканчивать в введите время для завершения повторного выполнения расписания задания. Укажите время дня: час, минуту и секунду.

        В разделе Длительность , в области Дата начала введите дату начала запуска расписания задания. Выберите Дата окончания или Без даты окончания , чтобы указать дату завершения выполнения расписания задания. При выборе Дата окончания введите дату завершения запуска расписания задания.

      При выборе значения Однократно в Однократное выполнение в поле Дата введите дату запуска расписания задания. В поле Время введите время запуска расписания задания. Укажите время дня: час, минуту и секунду.

      В разделе Сводка в Описание проверьте правильность всех параметров расписания задания.

      Нажмите кнопку ОК .

    После завершения работы с этой страницей нажмите кнопку Далее .

    На странице Просмотр сводки в разделе Просмотр выбранных параметров разверните все доступные параметры, чтобы убедиться в правильности всех настроек секции. Если все настройки правильные, нажмите кнопку Готово .

    Страница Выполнение мастера создания секций используется для отслеживания сведений о состоянии действий мастера создания секций. В зависимости от действий, выбранных в мастере, страница выполнения может содержать одно или несколько действий. В верхнем поле показано общее состояние мастера и число полученных им сообщений о состоянии, предупреждений и сообщений об ошибках.

    На странице Выполнение мастера создания секций доступны следующие параметры:

    Сведения
    Сведения о событии, состоянии и любых сообщениях, которые возвращены в результате действий мастера.

    Действие
    Задает тип и имя каждого действия.

    Состояние
    Указывает, вернуло ли действие мастера в целом значение Успешно или Ошибка .

    Сообщение
    Любые сообщения об ошибках или предупреждения от процесса.

    Отчет
    Создание отчета, содержащего результаты мастера создания секций. Доступные параметры: Просмотреть отчет , Сохранить отчет в файл , и Отправить отчет по электронной почте .

    Просмотр отчета
    Открытие диалогового окна Просмотр отчета , которое содержит текстовый отчет о работе мастера создания секций.

    Копировать отчет в буфер обмена
    Копирование результатов отчета о работе мастера в буфер обмена.

    Отправить отчет электронной почтой
    Копирование результатов отчета о состоянии мастера в сообщение электронной почты.

    Завершив выбор параметров, нажмите кнопку Закрыть .

Мастер создания секций создаст функцию и схему секционирования, а затем применит секционирование к указанной таблице. Чтобы проверить секционирование таблицы, в обозревателе объектов щелкните правой кнопкой мыши таблицу и выберите Свойства . Перейдите на страницу Хранилище . На странице отображается информация, в том числе имя функции секционирования, схема и число секций.

Создание секционированной таблицы

    В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

    На стандартной панели выберите пункт Создать запрос .

    Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить . В следующем примере показано создание файловых групп, функции и схемы секционирования. Новая таблица создается при указании схемы секционирования в качестве места хранения.

    USE AdventureWorks2012; GO -- Adds four new filegroups to the AdventureWorks2012 database ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Adds one file for each filegroup. ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test1dat1, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test1fg; ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test2dat2, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test2fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test3dat3, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test3fg; GO ALTER DATABASE AdventureWorks2012 ADD FILE (NAME = test4dat4, FILENAME = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf" , SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test4fg; GO -- Creates a partition function called myRangePF1 that will partition a table into four partitions CREATE PARTITION FUNCTION myRangePF1 (int ) AS RANGE LEFT FOR VALUES (1 , 100 , 1000 ) ; GO -- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY , col2 char (10 )) ON myRangePS1 (col1) ; GO

Определение секционирования таблицы

    Следующий запрос возвращает одну или несколько строк, если таблица PartitionTable секционирована. Если таблица не секционирована, не возвращается ни одна строка.

Определение граничных значений для секционированной таблицы

    Следующий запрос возвращает граничные значения для каждой секции в таблице PartitionTable .

    SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_id = s.data_space_id JOIN sys .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values AS r ON f .function_id = r.function_id and r.boundary_id = p .partition_number WHERE t .name = "PartitionTable" AND i .type <= 1 ORDER BY p .partition_number;

Определение столбца секционирования секционированной таблицы

    Следующий запрос возвращает имя столбца секционирования таблицы. PartitionTable .

    SELECT t . AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .indexes AS i ON t . = i . AND i . <= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column JOIN sys .columns AS c ON t . = c . AND ic.column_id = c .column_id WHERE t .name = "PartitionTable" ; GO

Дополнительные сведения см. в разделе.

В современных условиях очень странно бывает иногда слышать "нам нужно свернуть БД 1С - её объём превышает 50 ГБ". Если бы такое собирались сделать администраторы систем SAP R3 или Oracle e Business Suite или даже MS Dynamics Ax их бы наверное уволили. Тем не менее, для 1С это является "стандартной практикой".

Для файловых версий история тянется ещё с версии 1С 7.7 с ограничением в 2ГБ на размер базы. Сейчас ограничение 2ГБ уже только на размер таблицы, размер файла уже может получиться очень и очень не маленьким. Правда если база у вас выросла до такого размера, то наверное туда активно вносились данные - может нужно задуматься о клиент-сервере?

Собственно целью данной статьи является "отговорить" от выполнения свертки БД пользователей клиент-серверного варианта 1С, за счет использования несколько более "продвинутых" технологий.

Итоговая цифра получается 30-40 т. минимум против 20-25 в случае покупки жесткого диска, и получения 500 ГБ дополнительного места

Поэтому появляются продукты вроде
Хорошие наверное продукты, и цели свои выполняют. Вот только меняется структура таблиц от версии к версии платформы. 1С нам об этом не раз говорили. Появился разделитель данных в 14-ом релизе и всё... скорее всего эта обработка для 14 релиза уже не подойдёт. Да и страшно как-то, не говоря уже о нарушении лицензионного соглашения.

И даже после этого найдутся пользователи которым "вдруг неожиданно понадобились" стертые данные, которые "как раз хотели поправить" каку-то циферку, которая "не влияет на последовательнсти" в документе закрытого периода. А хуже если выяснится что кто-то эти документы смотрел постоянно для каких-то только ему ведомых целей. Конечно это всё лишь ошибки в методике работы, но тем не менее недовольство пользователей будет.


- Открываем Management Studio в списке баз выбираем нужную, открываем её свойства.
- Переходим на вкладку "Файловые группы" как показано на рисунке, и добавляем ещё одну файловую группу (на примере она названа SECONDARY)

- Переходим на вкладку "Файлы" и добавляем новый файл, для которого выбираем созданную файловую группу. Этот файл МОЖНО РАСПОЛОЖИТЬ НА ДРУГОМ ДИСКЕ


- Теперь используя обработку к примеру: определяем какие таблицы мы можем смело "пожертвовать" на более медленный (ну или наоборот всё на медленный, остальные - на более быстрый) носитель. Правило 80/20 здесь действует. 80% операций проводятся с 20% данными, так что думайте какие таблички вам нужны оперативно, а какие не очень. "Хранилище дополнительной информации", документы ввода начальных остатков, документы которые уже не используете сразу определяйте как те которые можно перенести в "медленную" файловую группу.

Выбираем таблицу которую нужно перенести в другую файловую группу - выбираем меню изменения таблицы (проект) и в свойствах меняем файловую группу:

индексы таблицы при этом тоже переносятся в данную файловую группу.
Достаточно удобный механизм распределения таблиц по дисковым массивом разной скорости. Лицензионному соглашению это не противоречит, т.к. в решении мы не используем доступ к данным и к информационной базе средствами отличными от платформы 1С. Мы лишь организуем хранение этих данных удобным образом.


DBCC TRACEON (1807)

Пишем данную команду в Management Studio, выполняем и можем успешно создавать базы по сети. Само собой при этом экземпляр SQL Server-а должен быть запущен от имени доменной учетной записи, и у этой записи должны быть права на нужную сетевую папку.
Но прошу быть очень внимательными при использовании данной команды в случае если у вас пропадёт сеть при работе с БД вся БД на время её отсутствия будет не доступной. Microsoft не зря закрыли эту возможность для массового использования. Вообще эта возможность предполагается для создания баз на NAS хранилищах, что и настоятельно рекомендую. Подойдёт так же стабильный и надежный файловый сервер, имеющий прямое подключение к серверу на котором запущен MS SQL СУБД.
Подробнее про другие флаги трассировки можно прочитать в статье http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Т.е. часть файловую группу можно вообще хранить в сети, а уж там дисковое пространство расширяется без проблем.

Разделить таблицы на разные файловые группы конечно хорошо... но вы скажете что есть тут парочка таблиц... которые тянутся с 2005 года... и уже занимают с десяток гигабайт.. вот бы в них все данные да отдельный диск положить, а текущие оставить.
Вы не поверите, но и это тоже возможно, хотя и не очень просто:

Создаём функуцию секционирования по дате:

create partition function YearSection(datetime)
as range right for values ("20110101");

Всё что до 2011 года будет попадать в одну секцию, всё что после - в другую.

Создаём схему секционирования

create partition scheme YearScheme
as partition YearSection to (SECONDARY, PRIMARY);

Этим говорим, что все данные до 11 года будут попадать в файловую группу "Secondary" а после - в "Primary"

Теперь осталось таблицу перестроить с разделением на секции. Для этого проще всего воспользоваться уже management studio, потому как процесс не простой. Вам нужно перестроить кластерный индекс для таблицы (который по сути и является самой таблицей), выбрав для индекса созданную схему секционирования:

На рисунке вы видите что выбор не доступен - всё правильно, секционирование таблиц возможно только в версии Enterprise MS SQL Server . Кластерный индекс отличить легко - картинка с круглыми скобками. Для РН и всех объектов 1С он создаётся. Для РН кластерный индекс по периоду есть всегда. Для документов и справочников хорошо бы конечно создать другой, который включает реквизит по которому будет секционирование... но это уже будет являться нарушением лицензионного соглашения.

Но для этого нужно не сворачивать базу, а проделать следующее:
а) Объяснить всем как пользоваться отборами, как они сохраняются, как пользоваться интервалами журнала, как они сохраняются
б) Пометить на удаление ненужные данные если они не несут никакой смысловой нагрузки (контрагентов и номенклатуру, с которыми больш не работаете) - этим вы принесёте пользователям больше пользы чем сверткой. В случае наличия ресурсов настроить автоматическую пометку на удаление неиспользуемых объектов и сделать отбор по умолчанию в программном коде для того чтобы не отображались по умолчанию не нужные пользователям объекты - помеченные на удаление
в) Настроить другие полезные "отборы по умолчанию" - например чтобы каждый менеджер по умолчанию видел только свои документы. А если хочет посмотреть документы "товарища" - нужно отключать отбор.

По всем реквизитам, которые участвуют в отборе не забывайте ставить признак "Индексировать с доп. упорядочиванием" - тогда на производительности системы такие "удобства" не скажутся.

В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с SQL Server 2005) и секционированными представлениями (которые были доступных ещё в SQL Server 2000, и по-прежнему доступны в SQL Server 2005 и последующих версиях). Особенности планов запросов к секционированным представлениям я продемонстрирую в другой статье.

Просмотр таблицы

Давайте создадим простую секционированную таблицу:

create partition function pf(int) as range for values (0, 10, 100)

create partition scheme ps as partition pf all to ()

create table t (a int, b int) on ps(a)

Этот сценарий создает таблицу с четырьмя секциями. SQL Server присвоил значения идентификаторам каждой из четырех секций так, как это показано в таблице:

PtnId Values
1 t.a <= 0
2 0 < t.a <= 10
3 10 < t.a <= 100
4 100 < t.a

Теперь давайте рассмотрим план такого запроса, который бы вынудил оптимизатор использовать просмотр всей таблицы (Table Scan):


……|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]))

В представленном выше плане, SQL Server явно указывает все идентификаторы секции в операторе «Constant Scan», который реализует просмотр таблицы и поставляет данные оператору соединения вложенных циклов. Тут следует напомнить, что оператор соединения вложенных циклов выполняет проход по внутренней таблице (в данном случае это полный просмотр таблицы) один раз для каждого значения из внешней таблицы (в нашем случае это «Constant Scan»). Таким образом, мы выполняем просмотр таблицы четыре раза; один раз для каждого идентификатора секции.

Следует также отметить, что соединение вложенных циклов показывает явно что внешняя таблица является значениями столбца , где хранятся ID секций. Хотя это не сразу видно в текстовом представлении плана исполнения (к сожалению, мы иногда не замечаем эту информацию), просмотр таблицы использует столбец с идентификаторами секций, которые выбираются для выполнения просмотра и определяют какую секцию сканировать. Эта информация всегда доступна в графическом плане исполнения (нужно заглянуть в свойства оператора просмотра таблицы), а также в XML представление плана исполнения запроса:

Статическая фильтрация секций

Рассмотрим следующий запрос:

select * from t where a < 100

|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Constant Scan(VALUES:(((1)),((2)),((3))))
<(100)) PARTITION ID:())

Предикат «а <100» явно исключает все строки для секции со значением идентификатора равным 4. В данном случае, нет смысла в просмотре соответствующей секции, поскольку ни одна из строк этой секции не удовлетворяет условию предиката. Оптимизатор учитывает этот факт и исключает эту секцию из плана исполнения запроса. В операторе «Constant Scan» указаны только три секции. У нас принято называть это статической фильтрацией секций (static partition elimination), поскольку мы знаем, что во время компиляции список просматриваемых секций остаётся статичным.

Если в результате статичной фильтрации будут исключены все разделы, кроме одного, нам вообще не понадобятся операторы «Constant Scan» и «Nested Loops Join»:

select * from t where a < 0

|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<(0)) PARTITION ID:((1)))

Обратите внимание, что указание «PARTITION ID:((1))», которое задаёт идентификатор подлежащей просмотру секции, теперь является частью оператора просмотра таблицы (Table Scan).

Динамическая фильтрация секций

В некоторых случаях SQL Server не может определить, что на момент компиляции состав просматриваемых секций не изменится, но ему видно, что некоторые секции можно исключить.

select * from t where a < @i

|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
…….|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
…….| |–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i]) PARTITION ID:())

Это параметризованный запрос. Так как до исполнения значение параметра мы не знаем (то, что я использую константу в качестве параметра в том же батче, не меняет положение вещей), то на этапе компиляции невозможно определить значение идентификатора секции для оператора «Constant Scan». Возможно придётся просматривать только секцию 1, или это будут секции 1 и 2, и так далее. Поэтому, в этом операторе указаны все четыре идентификатора секций, и мы используем фильтрацию идентификаторов секций на этапе исполнения. Мы называем это «Динамическая фильтрация секций» (Dynamic Partition Elimination).

Фильтр сравнивает каждый идентификатор секции c результатом работы специальной функции «RangePartitionNew». Эта функция вычисляет результаты применения функции секционирования к значению параметра. Аргументами этой функции (слева направо) являются:

  • значение (в данном случае параметр @i), который мы хотим отобразить на ID секции;
  • булевой флаг, указывающий, отображает ли функция секционирования граничные значения слева (0) или справа (1);
  • граничные значения секций (в данном случае это 0, 10, и 100).

В этом примере, поскольку @i имеет значение 0, результатом «RangePartitionNew» является 1. Таким образом, мы просматриваем только секцию с идентификатором 1. Заметим, что в отличие от примера со статической фильтрацией секций, хотя мы сканируем только один раздел, мы по-прежнему имеем «Constant Scan» и «Nested Loops Join». Нам потому нужны эти операторы, что до этапа исполнения мы не знаем секции, которые будут просмотрены.

В некоторых случаях оптимизатор уже на этапе компиляции может определить, что мы будем сканировать только одну секцию, даже если он не может определить, какую именно. Например, если в запросе используется предикат эквивалентности по ключу секционирования, тогда мы знаем, что только одна секция может удовлетворять такому условию. Поэтому, несмотря на то, что у нас должна была быть динамическая фильтрация секций, у нас отпадает необходимость в операторах «Constant Scan» и «Nested Loops Join». Пример:

select * from t where a = @i

|–Table Scan(OBJECT:([t]), WHERE:([t].[a]=[@i]) PARTITION ID:(RangePartitionNew([@i],(0),(0),(10),(100))))

Сочетание статической и динамической фильтрации секций

SQL Server может совмещать статическую и динамическую фильтрацию секций в одном плане запроса:

select * from t where a > 0 and a < @i

|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
……|–Filter(WHERE:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
……| |–Constant Scan(VALUES:(((2)),((3)),((4))))
……|–Table Scan(OBJECT:([t]), WHERE:([t].[a]<[@i] AND [t].[a]>(0)) PARTITION ID:())

Обратите внимание, что в последнем плане присутствует статическая фильтрация секции ID = 1 с использованием «Constant Scan», и также присутствует динамическая фильтрация для других секций, определяемых предикатами.

$partition

Можно явно вызвать функцию RangePartitionNew, используя $partition:

select *, $partition.pf(a) from t

|–Compute Scalar(DEFINE:(=RangePartitionNew([t].[a],(0),(0),(10),(100))))
……|–Nested Loops(Inner Join, OUTER REFERENCES:() PARTITION ID:())
………..|–Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
………..|–Table Scan(OBJECT:([t]))

Отличительной особенностью такого плана исполнения запроса является появление оператора Compute Scalar.

Дополнительная информация

В СУБД Oracle есть возможность секционировать представления . Основная идея проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования . Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования . Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования .

Секции представления могут быть определены предикатами секционирования , заданными либо при помощи ограничения CHECK , либо с использованием предложения WHERE . Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы "Продажи" (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года - Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.

Пример 20.14 .

С помощью ограничения CHECK . С помощью команды ALTER TABLE можно добавить ограничения на колонку "Дата продажи" (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.

ALTER TABLE Q1_Sales ADD CONSTRAINT C0 CHECK (s_date BETWEEN "jan-1-2002" AND "mar-31-2002"); ALTER TABLE Q2_Sales ADD CONSTRAINT C1 CHECK (s_date BETWEEN "apr 1-2002" AND "jun-30-2002"); ALTER TABLE Q3_Sales ADD CONSTRAINT C2 check (s_date BETWEEN "jul-1-2002" AND "sep-30-2002"); ALTER TABLE Q4_Sales ADD CONSTRAINT C3 check (s_date BETWEEN "oct-1-2002" AND "dec-31-2002"); CREATE VIEW sales_v AS SELECT * FROM Q1_Sales UNION ALL SELECT * FROM Q2_Sales UNION ALL SELECT * FROM Q3_Sales UNION ALL SELECT * FROM Q4_Sales;

Преимуществом такого секционирования представлений является то, что предикат ограничения CHECK не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования , извлекаются из базы данных быстрее.

Пример 20.15 .

Секционирование представлений с помощью предложения WHERE . Создадим представление для тех же таблиц, что и в примере выше.

CREATE VIEW sales_v AS SELECT * FROM Q1_Sales WHERE s_date BETWEEN "jan-1-2002" AND "mar-31-2002" UNION ALL SELECT * FROM Q2_Sales WHERE s_date BETWEEN "apr-1-2002" AND "jun-30-2002" UNION ALL SELECT * FROM Q3_Sales WHERE s_date BETWEEN "jul-1-2002" AND "sep-30-2002" UNION ALL SELECT * FROM Q4_Sales WHERE s_date BETWEEN "oct-1-2002" AND "dec-31-2002";

Метод секционирования представлений с помощью предложения WHERE имеет некоторые недостатки. Во-первых, критерий секционирования проверяется во время выполнения для всех строк во всех секциях, которые охватываются запросом. Во-вторых, пользователи могут ошибочно вставить строку не в ту секцию, т.е. вставить строку, относящуюся к первому кварталу, в третий квартал, что приведет к неправильной выборке данных по этим кварталам.

У этого приема есть и достоинство по сравнению с использованием ограничения CHECK . Можно разместить секцию, соответствующую предикату WHERE , на удаленной базе данных. Фрагмент определения преставления приведен ниже.

Принимая решение о создании , необходимо помнить о следующих факторах.

  • и удаление данных, работать на уровне секции, а не целой базовой таблицы.
  • Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
  • СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений .
  • Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.

Секционирование таблиц в СУБД семейства MS SQL Server

Создание секционированных таблиц

В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.

В MS SQL Server все таблицы и индексы в БД считаются секционированными, даже если они состоят всего лишь из одной секции. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов . Это означает, что логическая и физическая архитектура таблиц и индексов , включающая несколько секций, полностью отражает архитектуру таблиц и индексов , состоящих из одной секции.

Секционирование таблиц и индексов задается жестко на уровне строк (секционирование по столбцам не допускается) и позволяет осуществлять доступ через единую точку входа (имя таблицы или имя индекса ) таким образом, что в коде приложения не требуется знать число секций. Секционирование может осуществляться на базовой таблице, а также на связанных с ней индексах .

Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES . Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).

Теперь создадим схему секционирования . Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:

CREATE PARTITION SCHEME MyPartitionScheme AS MyPartitionFunction TO (MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4)

MyPartitionScheme – это имя схемы секционирования , а имя MyPartitionFunction определяет функцию секционирования . Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки "Дата продажи" (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1 . Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2 . Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3 . Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4 .

Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно "Количество граничных значений" + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT , количество секций всегда будет равно "Количество граничных значений" + 1, вплоть до 1000 секций на таблицу.

Теперь мы можем создать секционированную таблицу фактов "Продажи" (SALES). Создание секционированной таблицы мало чем отличается от создания обычной таблицы, нужно только сослаться на имя схемы секционирования в условии ON , как показано в команде ниже.

CREATE TABLE SALES (Sales_ШВ bigint identity (1, 1) primary not clustered NOT NULL, Cust_ID bigint null, Prod_ID bigint null, Store_ID bigint null, REG_ID char(10) null, Time_of_Event time null, Quantity integer not null, Amount dec(8,2) not null, Date_of_Event date NOT NULL) ON MyPartitionScheme (Date_of_Event)

Определяя имя схемы секционирования , проектировщик указывает, что эта таблица является индексы . Это позволяет проектировщику проектировать структуру индекса на основе разделенных данных, а не на основе всех данных таблицы. Создание секционированных индексов влечет за собой создание отдельных сбалансированных деревьев на секционированных индексах . В результате разделения индексов создаются индексы меньшего размера, и администратору БД или ХД становится проще их обслуживать во время изменения, добавления и удаления данных.

При создании секционированных индексов можно создавать выровненные или невыровненные индексы . Выровненные индексы подразумевают прямую связь с секционированными данными таблицы. В случае с невыровненными индексами выбирается другая схема секционирования .

Из этих двух методов предпочтителен выровненный индекс , который выбирается по умолчанию, если после создания секционированной таблицы индексы создаются без указания другой схемы секционирования . Использование выровненных индексов предоставляет необходимую гибкость для создания дополнительных секций в таблице, а также позволяет переводить принадлежность той или иной секции на другую таблицу. Для решения большинства задач, связанных с секционированием , достаточно применить для индексов схему секционирования таблицы.

Пример. 20.19 .

Создадим секционированный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из предыдущего примера 20.18.

CREATE PARTITION SCHEME Index_primary_Left_Scheme AS PARTITION Index_Left_Partition ALL TO ()

Теперь выполним команду создания индекса , как показано ниже.

CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_partition(Cust_ID) ON Index_primary_Left_Scheme (Cust_ID)

В этом некластеризованном индексе в качестве ключа индекса используется колонка "Идентификатор покупателя" (Cust_ID), которая не является ключом секционирования таблицы "Продажи" (SALES).

Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса .

Похожие статьи:
Категории
Популярное