Partitioned Tables and IndexesPartitioned Tables and Indexes

ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ: даSQL ServerдаБаза данных SQL Azure нетХранилище данных SQL Azure нетParallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server поддерживает секционирование таблиц и индексов. supports table and index partitioning. Данные секционированных таблиц и индексов подразделяются на блоки, которые могут быть распределены по нескольким файловым группам в базе данных.The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. Данные секционируются горизонтально, поэтому группы строк сопоставляются с отдельными секциями.The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. Все секции одного индекса или таблицы должны находиться в одной и той же базе данных.All partitions of a single index or table must reside in the same database. Таблица или индекс рассматриваются как единая логическая сущность при выполнении над данными запросов или обновлений.The table or index is treated as a single logical entity when queries or updates are performed on the data. До версии SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 секционированные таблицы и индексы были доступны не в каждом выпуске SQL ServerSQL Server.Prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, partitioned tables and indexes were not available in every edition of SQL ServerSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL ServerSQL Server, см. в статье Возможности, поддерживаемые различными выпусками SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

Важно!

SQL Server 2017SQL Server 2017 поддерживает по умолчанию до 15 000 секций. supports up to 15,000 partitions by default. В версиях, предшествующих SQL Server 2012 (11.x)SQL Server 2012 (11.x), количество секций ограничивалось 1000 по умолчанию. В системах x86 создание таблицы или индекса с числом секций более 1000 возможно, но не поддерживается.In versions earlier than SQL Server 2012 (11.x)SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default.On x86-based systems, creating a table or index with more than 1000 partitions is possible, but is not supported.

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

Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.Partitioning large tables or indexes can have the following manageability and performance benefits.

  • Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных.You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.

  • Операции обслуживания можно выполнять быстрее с одной или несколькими секциями.You can perform maintenance operations on one or more partitions more quickly. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей.The operations are more efficient because they target only these data subsets, instead of the whole table. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.For example, you can choose to compress data in one or more partitions or rebuild one or more partitions of an index.

  • Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования.You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

    В процессе сортировки данных для операций ввода-вывода в SQL ServerSQL Server сначала проводится сортировка данных по секциям.When SQL ServerSQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL ServerSQL Server может одновременно обращаться только к одному диску, что может снизить производительность. accesses one drive at a time, and this might reduce performance. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID.To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. Таким образом, несмотря на сортировку данных по секциям, SQL ServerSQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.In this way, although SQL ServerSQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

    Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы.In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. Это может уменьшить количество конфликтов блокировок для таблицы.This can reduce lock contention on the table.

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

Следующие термины относятся к секционированию таблиц и индексов.The following terms are applicable to table and index partitioning.

Функция секционированияPartition function
Объект базы данных, который определяет распределение строк таблицы или индекса по секциям на основе значений определенных столбцов, называемых столбцами секционирования.A database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column, called a partitioning column. То есть функция секционирования определяет количество разделов в таблице и как будут определены границы разделов.That is, the partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined. Например, таблицу, содержащую данные заказов на продажу, может потребоваться разделить на 12 месячных секций по значениям столбца datetime , например по дате продаж.For example, given a table that contains sales order data, you may want to partition the table into twelve (monthly) partitions based on a datetime column such as a sales date.

Схема секционированияPartition scheme
Объект базы данных, который сопоставляет секции функции секционирования набору файловых групп.A database object that maps the partitions of a partition function to a set of filegroups. Главная причина, по которой секции разделяются по разным файловым группам, заключается в необходимости независимого резервного копирования этих секций,The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. поскольку оно всегда выполняется отдельно для каждой из файловых групп.This is because you can perform backups on individual filegroups.

Столбец секционированияPartitioning column
Столбец таблицы или индекса, используемый функцией секционирования для секционирования таблицы или индекса.The column of a table or index that a partition function uses to partition the table or index. Вычисляемые столбцы, участвующие в функции секционирования, должны быть явно помечены как PERSISTED.Computed columns that participate in a partition function must be explicitly marked PERSISTED. Все типы данных, допустимые для использования в качестве индексных столбцов, могут использоваться как столбцы секционирования, за исключением timestamp.All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. Типы данных ntext, text, image, xml, varchar(max), nvarchar(max) или varbinary(max) указать нельзя.The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. Также нельзя указать определяемый пользователем тип данных среды Microsoft .NET Framework CLR и столбцы типа данных псевдонима.Also, Microsoft .NET Framework common language runtime (CLR) user-defined type and alias data type columns cannot be specified.

Выровненный индексAligned index
Индекс, созданный на основе той же схемы секционирования, что и соответствующая таблица.An index that is built on the same partition scheme as its corresponding table. Если таблица и ее индексы выровнены, SQL Server может быстро переключаться с секции на секцию, сохраняя при этом структуру секций как таблицы, так и ее индексов.When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes. Для выравнивания с базовой таблицей индексу необязательно использовать функцию секционирования с тем же именем.An index does not have to participate in the same named partition function to be aligned with its base table. Однако функции секционирования индекса и базовой таблицы не должны существенно различаться, то есть: 1) аргументы функции секционирования должны иметь один и тот же тип данных, 2) функции должны определять одинаковое количество секций и 3) функции должны определять для секций одинаковые граничные значения.However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions.

Невыровненный индексNonaligned index
Индекс, секционированный независимо от соответствующей таблицы.An index partitioned independently from its corresponding table. Т. е. индекс имеет другую схему секционирования или находится не в той файловой группе, где находится базовая таблица.That is, the index has a different partition scheme or is placed on a separate filegroup from the base table. Создание невыровненного секционированного индекса может быть полезно в следующих случаях:Designing an nonaligned partitioned index can be useful in the following cases:

  • Базовая таблица не секционирована.The base table has not been partitioned.

  • Ключ индекса является уникальным и не содержит столбца секционирования таблицы.The index key is unique and it does not contain the partitioning column of the table.

  • Требуется участие базовой таблицы в выровненных соединениях с таблицами, использующими другие столбцы соединения.You want the base table to participate in collocated joins with more tables using different join columns.

    Устранение секций. Процесс, в ходе которого оптимизатор запросов обращается только к определенным секциям в соответствии с критериями фильтра запроса.Partition elimination The process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query.

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

Более высокое новое максимальное количество секций (15 000) влияет на память, операции с секционированными индексами, команды DBCC и запросы.The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries. В этом разделе показано, как влияет на производительность создание более 1 000 секций и как обойти проблемы.This section describes the performance implications of increasing the number of partitions above 1,000 and provides workarounds as needed. Увеличение максимального количества секций до 15 000 позволяет дольше хранить данные.With the limit on the maximum number of partitions being increased to 15,000, you can store data for a longer time. Однако рекомендуется хранить данные ровно столько времени, сколько требуется, и поддерживать баланс между производительностью и количеством секций.However, you should retain data only for as long as it is needed and maintain a balance between performance and number of partitions.

Рекомендации относительно процессорных ядер и числа секцийProcessor Cores and Number of Partitions Guidelines

Чтобы добиться максимальной производительности с помощью параллельных операций, рекомендуется, чтобы число секций и процессорных ядер совпадало. но не превышало 64 (это максимальное число параллельных процессоров, которые SQL Server может использовать).To maximize performance with parallel operations, we recommend that you use the same number of partitions as processor cores, up to a maximum of 64 (which is the maximum number of parallel processors that SQL Server can utilize).

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

При большом количестве используемых секций рекомендуется использовать ОЗУ не менее 16 ГБ.We recommend that you use at least 16 GB of RAM if a large number of partitions are in use. Если у системы недостаточно памяти, возможен сбой инструкций языка обработки данных (DML), инструкций языка описания данных (DDL) и других операций из-за нехватки памяти.If the system does not have enough memory, Data Manipulation Language (DML) statements, Data Definition Language (DDL) statements and other operations can fail due to insufficient memory. В системах с ОЗУ 16 ГБ и большим количеством процессов, интенсивно использующих память, возможны сбои операций, работающих на большом количестве секций, из-за нехватки памяти.Systems with 16 GB of RAM that run many memory-intensive processes may run out of memory on operations that run on a large number of partitions. Поэтому чем больше у вас памяти сверх 16 МБ, тем меньше вероятность проблем с производительностью и памятью.Therefore, the more memory you have over 16 GB, the less likely you are to encounter performance and memory issues.

Ограничения оперативной памяти могут повлиять на производительность или возможность создания секционированного индекса.Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. Такое случается, например, когда индекс не выровнен со своей базовой таблицей или со своим кластеризованным индексом, если такой существует в таблице.This is especially the case when the index is not aligned with its base table or is not aligned with its clustered index, if the table already has a clustered index applied to it.

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

Ограничения оперативной памяти могут повлиять на производительность или возможность создания секционированного индекса.Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. Это особенно относится к невыровненным индексам.This is especially the case with nonaligned indexes. Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не поддерживаются.Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.Doing so may cause degraded performance or excessive memory consumption during these operations.

Создание и перестройка выровненных индексов может занимать больше времени по мере увеличения количества секций.Creating and rebuilding aligned indexes could take longer to execute as the number of partitions increases. Не рекомендуется выполнять одновременно несколько команд создания и перестройки индекса, так как возможны проблемы с производительностью и памятью.We recommend that you do not run multiple create and rebuild index commands at the same time as you may run into performance and memory issues.

Когда компонент SQL Server выполняет сортировку для создания секционированных индексов, сначала он создает по одной таблице сортировки для каждой секции.When SQL Server performs sorting to build partitioned indexes, it first builds one sort table for each partition. Затем либо в соответствующей файловой группе каждой секции, либо в tempdb, если задан параметр индекса SORT_IN_TEMPDB, производится построение таблиц сортировки.It then builds the sort tables either in the respective filegroup of each partition or in tempdb, if the SORT_IN_TEMPDB index option is specified. Для всех таблиц сортировки требуется минимальный объем оперативной памяти.Each sort table requires a minimum amount of memory to build. При построении секционированного индекса, выровненного со своей базовой таблицей, таблицы сортировки создаются по одной за раз, экономно расходуя оперативную память.When you are building a partitioned index that is aligned with its base table, sort tables are built one at a time, using less memory. Однако при построении невыровненного секционированного индекса таблицы сортировки создаются одновременно.However, when you are building a nonaligned partitioned index, the sort tables are built at the same time. В результате необходим достаточный объем оперативной памяти, чтобы параллельно их обрабатывать.As a result, there must be sufficient memory to handle these concurrent sorts. Чем больше число секций, тем больше требуется оперативной памяти.The larger the number of partitions, the more memory required. Для каждой из секций размер таблицы сортировки составляет не менее 40 страниц, по 8 килобайт каждая.The minimum size for each sort table, for each partition, is 40 pages, with 8 kilobytes per page. Например, для невыровненного секционированного индекса, разбитого на 100 секций, потребуется объем оперативной памяти для одновременной сортировки 4 000 страниц (40*100).For example, a nonaligned partitioned index with 100 partitions requires sufficient memory to serially sort 4,000 (40 * 100) pages at the same time. Если такой объем памяти доступен, операция создания будет выполнена успешно, но может пострадать производительность.If this memory is available, the build operation will succeed, but performance may suffer. Если же такой объем памяти недоступен, операция построения завершится ошибкой.If this memory is not available, the build operation will fail. Для выровненного секционированного индекса, разбитого на 100 секций, для сортировки потребуется всего 40 страниц, поскольку сортировки осуществляются не одновременно.Alternatively, an aligned partitioned index with 100 partitions requires only sufficient memory to sort 40 pages, because the sorts are not performed at the same time.

Как для выровненных, так и для невыровненных индексов может потребоваться больше оперативной памяти, если SQL Server применяет степени параллелизма для операции создания на многопроцессорном компьютере.For both aligned and nonaligned indexes, the memory requirement can be greater if SQL Server is applying degrees of parallelism to the build operation on a multiprocessor computer. Чем больше степень параллелизма, тем больше требуется оперативной памяти.This is because the greater the degrees of parallelism, the greater the memory requirement. Например, если SQL Server задает степень параллелизма 4, то невыровненному секционированному индексу со 100 секциями потребуется такой объем памяти, чтобы четыре процессора могли одновременно отсортировать по 4 000 страниц, то есть 16 000 страниц.For example, if SQL Server sets degrees of parallelism to 4, a nonaligned partitioned index with 100 partitions requires sufficient memory for four processors to sort 4,000 pages at the same time, or 16,000 pages. Если секционированный индекс выровнен, требования оперативной памяти снижаются до 40 страниц для каждого из четырех процессоров, то есть 160 страниц (4*40).If the partitioned index is aligned, the memory requirement is reduced to four processors sorting 40 pages, or 160 (4 * 40) pages. С помощью параметра индекса MAXDOP можно вручную снизить степень параллелизма.You can use the MAXDOP index option to manually reduce the degrees of parallelism.

Команды DBCCDBCC Commands

При большем количестве секций выполнение команд DBCC может занимать больше времени по мере увеличения количества секций.With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.

ЗапросыQueries

Запросы, использующие функцию устранения секций, могут иметь сопоставимую или более высокую производительность с большим числом секций.Queries that use partition elimination could have comparable or improved performance with larger number of partitions. Запросы, не использующие функцию устранения секций, могут занимать больше времени по мере увеличения количества секций.Queries that do not use partition elimination could take longer to execute as the number of partitions increases.

Предположим, таблица имеет 100 миллионов строк и столбцов A, Bи C.For example, assume a table has 100 million rows and columns A, B, and C. В примере 1 таблица делится на 1000 секций по столбцу A.In scenario 1, the table is divided into 1000 partitions on column A. В примере 2 таблица делится на 10,000 секций по столбцу A.In scenario 2, the table is divided into 10,000 partitions on column A. Запрос к таблице, включающий предложение WHERE с фильтром по столбцу A , выполнит функцию устранения секций и просканирует одну секцию.A query on the table that has a WHERE clause filtering on column A will perform partition elimination and scan one partition. Тот же самый запрос может быть выполнен быстрее в примере 2, так как в секции меньше строк для сканирования.That same query may run faster in scenario 2 as there are fewer rows to scan in a partition. Запрос, включающий предложение WHERE с фильтром по столбцу B, будет сканировать все секции.A query that has a WHERE clause filtering on column B will scan all partitions. В примере 1 этот запрос может быть выполнен быстрее, чем в примере 2, так как в этом случае меньше секций для сканирования.The query may run faster in scenario 1 than in scenario 2 as there are fewer partitions to scan.

Запросы, в которых используются такие операторы, как TOP или MAX/MIN, в столбцах, отличных от столбца секционирования, могут столкнуться со снижением производительности при секционировании, поскольку вычисляться должны все секции.Queries that use operators such as TOP or MAX/MIN on columns other than the partitioning column may experience reduced performance with partitioning because all partitions must be evaluated.

Изменения в поведении при статистических вычислениях во время операций с секционированным индексомBehavior Changes in Statistics Computation During Partitioned Index Operations

Начиная с SQL Server 2012 (11.x)SQL Server 2012 (11.x), статистические данные не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса.Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию.Instead, the query optimizer uses the default sampling algorithm to generate statistics. После обновления базы данных с секционированными индексами можно заметить разницу в гистограммах для этих индексов.After upgrading a database with partitioned indexes, you may notice a difference in the histogram data for these indexes. Это изменение в поведении может не влиять на время выполнения запросов.This change in behavior may not affect query performance. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

ЗаданияTasks РазделTopic
Описано, как создать функции секционирования и схемы секционирования и применить их к таблице или индексу.Describes how to create partition functions and partition schemes and then apply these to a table and index. Создание секционированных таблиц и индексовCreate Partitioned Tables and Indexes

Следующие публикации по стратегиям секционированных таблиц и индексов и примеры внедрения могут оказаться полезными.You may find the following white papers on partitioned table and index strategies and implementations useful.