Partitioned Tables and Indexes

Статья
06/13/2017

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

Важно!

SQL Server 2014 поддерживает до 15 000 секций по умолчанию. В версиях, предшествующих SQL Server 2012 г., количество секций по умолчанию было ограничено 1000. В системах на базе архитектуры x86 создание таблицы или индекса с более чем 1000 секций возможно, но не поддерживается.

Преимущества секционирования

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

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

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

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

Компоненты и основные понятия

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

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

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

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

Выровненный индекс
Индекс, созданный на основе той же схемы секционирования, что и соответствующая таблица. Когда таблица и ее индексы выровнены, SQL Server может при обслуживании структуры секционирования как таблицы, так и индексов быстро и эффективно переключать секции. Для выравнивания с базовой таблицей индексу необязательно использовать функцию секционирования с тем же именем. Однако функции секционирования индекса и базовой таблицы не должны существенно различаться, то есть: 1) аргументы функции секционирования должны иметь один и тот же тип данных, 2) функции должны определять одинаковое количество секций и 3) функции должны определять для секций одинаковые граничные значения.

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

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

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

Рекомендации по производительности

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

Использование памяти и рекомендации

При большом количестве используемых секций рекомендуется использовать ОЗУ не менее 16 ГБ. Если у системы недостаточно памяти, возможен сбой инструкций языка обработки данных (DML), инструкций языка описания данных (DDL) и других операций из-за нехватки памяти. В системах с ОЗУ 16 ГБ и большим количеством процессов, интенсивно использующих память, возможны сбои операций, работающих на большом количестве секций, из-за нехватки памяти. Поэтому чем больше у вас памяти сверх 16 МБ, тем меньше вероятность проблем с производительностью и памятью.

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

Операции с секционированными индексами

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

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

При сортировке, выполняемой при построении секционированных индексов, SQL Server сначала создает для каждой секции по одной таблице сортировки. Затем он создает таблицы сортировки в соответствующей файловой группе каждой секции или в tempdb, если задан параметр SORT_IN_TEMPDB. Для всех таблиц сортировки требуется минимальный объем оперативной памяти. При построении секционированного индекса, выровненного со своей базовой таблицей, таблицы сортировки создаются по одной за раз, экономно расходуя оперативную память. Однако при построении невыровненного секционированного индекса таблицы сортировки создаются одновременно. В результате необходим достаточный объем оперативной памяти, чтобы параллельно их обрабатывать. Чем больше число секций, тем больше требуется оперативной памяти. Для каждой из секций размер таблицы сортировки составляет не менее 40 страниц, по 8 килобайт каждая. Например, для невыровненного секционированного индекса, разбитого на 100 секций, потребуется объем оперативной памяти для одновременной сортировки 4 000 страниц (40*100). Если такой объем памяти доступен, операция создания будет выполнена успешно, но может пострадать производительность. Если же такой объем памяти недоступен, операция построения завершится ошибкой. Для выровненного секционированного индекса, разбитого на 100 секций, для сортировки потребуется всего 40 страниц, поскольку сортировки осуществляются не одновременно.

Как для выровненных, так и для невыровненных индексов может потребоваться больше оперативной памяти, если SQL Server применяет степени параллелизма для операции создания на многопроцессорном компьютере. Чем больше степень параллелизма, тем больше требуется оперативной памяти. Например, если для SQL Server определена степень параллелизма 4, то невыровненному секционированному индексу, содержащему 100 секций, потребуется такой объем памяти, чтобы четыре процессора могли одновременно отсортировать по 4 000 страниц, то есть 16 000 страниц. Если секционированный индекс выровнен, требования оперативной памяти снижаются до 40 страниц для каждого из четырех процессоров, то есть 160 страниц (4*40). С помощью параметра индекса MAXDOP можно вручную снизить степень параллелизма.

Команды DBCC

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

Запросы

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

Предположим, таблица имеет 100 миллионов строк и столбцов A, Bи C. В примере 1 таблица делится на 1000 секций по столбцу A. В примере 2 таблица делится на 10,000 секций по столбцу A. Запрос к таблице, включающий предложение WHERE с фильтром по столбцу A , выполнит функцию устранения секций и просканирует одну секцию. Тот же самый запрос может быть выполнен быстрее в примере 2, так как в секции меньше строк для сканирования. Запрос, включающий предложение WHERE с фильтром по столбцу B, будет сканировать все секции. В примере 1 этот запрос может быть выполнен быстрее, чем в примере 2, так как в этом случае меньше секций для сканирования.

Запросы, в которых используются такие операторы, как TOP или MAX/MIN, в столбцах, отличных от столбца секционирования, могут столкнуться со снижением производительности при секционировании, поскольку вычисляться должны все секции.

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

Начиная с SQL Server 2012 года статистика не создается путем сканирования всех строк в таблице при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. После обновления базы данных с секционированными индексами можно заметить разницу в гистограммах для этих индексов. Это изменение в поведении может не влиять на время выполнения запросов. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.


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

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