MS SQL секционирование таблиц

В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании 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)

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

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

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

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

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

SQL Server. Введение в секционирование таблиц

По материалам статьи Крейга Фридмана: Introduction to Partitioned Tables

Александр Гладченко

В этой статье я собираюсь продемонстрировать особенности планов исполнения запросов при обращении к секционированным таблицам. Обратите внимание, что существует большая разница между секционированными таблицами (которые стали доступны только с 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):

select * from t

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

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

<TableScan Ordered=”0″ ForcedIndex=”0″ NoExpandHint=”0″>

<Object Database=”” Schema=”” Table=”” />

<ColumnReference Column=”PtnIds1004″ />

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

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

select * from t where a < 100

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

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

select * from t where a < 0

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

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

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

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

declare @i int

select @i = 0

select * from t where a < @i

Это параметризованный запрос. Так как до исполнения значение параметра мы не знаем (то, что я использую константу в качестве параметра в том же батче, не меняет положение вещей), то на этапе компиляции невозможно определить значение идентификатора секции для оператора «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». Пример:

declare @i int

select @i = 0

select * from t where a = @i

|–Table Scan(OBJECT:(), WHERE:(.=) PARTITION ID:(RangePartitionNew(,(0),(0),(10),(100))))

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

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

declare @i int

select @i = 0

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

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

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

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

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

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

Статья в блоге SQL Server Development Customer Advisory Team содержит несколько интересных примеров фильтрации секций.

*** *** *** *** *** *** *** *** *** *** *** ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *