분할된 테이블 및 인덱스Partitioned Tables and Indexes

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 2016SQL Server 2016 SP1 전에는 분할된 테이블 및 인덱스를 일부 SQL ServerSQL Server 버전에서만 사용할 수 있습니다.Prior to SQL Server 2016SQL Server 2016 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 2012SQL Server 2012이전 버전에서는 파티션 수가 기본적으로 1,000개로 제한되었습니다. x86 기반 시스템에서는 파티션 수가 1,000개를 초과하는 테이블 또는 인덱스를 만들 수 있지만 해당 테이블 또는 인덱스는 지원되지 않습니다.In versions earlier than SQL Server 2012SQL Server 2012, 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 가 I/O 작업을 위해 데이터를 정렬할 때 먼저 파티션을 기준으로 데이터가 정렬됩니다.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. 예를 들어 매출 주문 데이터가 포함된 테이블이 있다고 가정할 경우 매출 날짜와 같은 datetime 열을 기준으로 테이블을 12개(월별) 파티션으로 분할해야 할 수 있습니다.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

많은 수의 파티션을 사용할 경우 16GB 이상의 RAM을 사용하는 것이 좋습니다.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. 메모리를 많이 사용하는 프로세스를 실행하는 16GB RAM이 장착된 시스템에서 많은 수의 파티션에서 실행되는 작업을 수행하면 메모리가 부족해질 수 있습니다.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. 따라서 메모리(16GB 이상)가 많을수록 성능 및 메모리 문제가 발생할 가능성이 더 낮아집니다.Therefore, the more memory you have over 16 GB, the less likely you are to encounter performance and memory issues.

메모리 제한 사항은 분할된 인덱스를 작성하는 SQL Server의 성능 또는 기능에 영향을 줄 수 있습니다.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

메모리 제한 사항은 분할된 인덱스를 작성하는 SQL Server의 성능 또는 기능에 영향을 줄 수 있습니다.Memory limitations can affect the performance or ability of SQL Server to build a partitioned index. 특히 정렬되지 않은 인덱스를 사용하는 경우에 큰 영향을 줍니다.This is especially the case with nonaligned indexes. 파티션 수가 1,000개를 초과하는 테이블에서 정렬되지 않은 인덱스를 만들거나 다시 작성할 수 있지만 해당 인덱스는 지원되지 않습니다.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. 그런 다음 각 파티션에 있는 각각의 파일 그룹에 정렬 테이블을 만들거나 SORT_IN_TEMPDB 인덱스 옵션이 지정된 경우 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페이지이며 페이지당 8KB의 용량이 필요합니다.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개의 프로세서에서 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. 분할된 인덱스가 정렬되어 있는 경우에는 4개의 프로세서에서 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.

DBCC 명령DBCC 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.

예를 들어 테이블에 1억 개의 행과 A, BC열이 있다고 가정합니다.For example, assume a table has 100 million rows and columns A, B, and C. 시나리오 1에서는 테이블의 A열이 1,000개의 파티션으로 분할됩니다.In scenario 1, the table is divided into 1000 partitions on column A. 시나리오 2에서는 테이블의 A열이 10,000개의 파티션으로 분할됩니다.In scenario 2, the table is divided into 10,000 partitions on column A. A 열에서 필터링하는 WHERE 절이 있는 테이블에 대한 쿼리는 파티션 제거를 수행하고 파티션 하나를 검사합니다.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. B 열에서 필터링하는 WHERE 절이 있는 쿼리는 모든 파티션을 검사합니다.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 2012SQL Server 2012부터 분할된 인덱스를 만들거나 다시 작성할 때 테이블의 모든 행을 검사하여 통계를 작성하지 않습니다.Beginning with SQL Server 2012SQL Server 2012, 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. 테이블의 모든 행을 검사하여 분할된 인덱스에 대한 통계를 얻으려면 FULLSCAN 절에서 CREATE STATISTICS 또는 UPDATE STATISTICS를 사용합니다.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.