이 SQL Server 제약 조건에서 PAD_INDEX의 목적은 무엇입니까?
테이블 중 하나에 다음과 같은 제약 조건이 적용되지만 PAD_INDEX가 무엇을 의미하는지 모르겠습니다.
누가 나를 깨우쳐 줄 수 있습니까?
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[EmployeeId] ASC
) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
^--------------^
this part here
SQL Server의 인덱스가 B-트리입니다.
FILL FACTOR는 맨 아래 레이어에 적용됩니다.
노드입니다.PAD_INDEX ON은 "모든 레이어에 채우기 요소 적용"을 의미합니다.
아래 그림의 중간 수준입니다(루트와 데이터 사이).
즉, PAD_INDEX는 FILLFACTOR가 설정된 경우에만 유용합니다. FILLFACTOR는 데이터 페이지의 여유 공간을 결정합니다(대략).
기본적으로 인덱스에 많은 랜덤 변경이 정기적으로 예상되는 경우 PAD_INDEX = ON을 설정합니다.
이렇게 하면 색인 페이지 분할을 방지할 수 있습니다.
인덱스에 포함된 랜덤 레코드의 30%+가 정기적으로 삭제될 것으로 예상될 때 설정합니다.
MSDN에서:
PAD_INDEX = {ON | OFF}
인덱스 패딩을 지정합니다.기본값은 OFF입니다.
ON: 채우기 비율로 지정된 여유 공간의 백분율이 인덱스의 중간 수준 페이지에 적용됩니다.
OFF 또는 채우기 비율이 지정되지 않았습니다.중간 수준 페이지는 거의 최대 용량까지 채워지며, 중간 페이지의 키 집합을 고려할 때 인덱스가 가질 수 있는 최대 크기의 적어도 하나의 행에 충분한 공간을 남깁니다.
PAD_INDEX 옵션은 FILLFACTOR가 지정한 백분율을 사용하므로 FILLFACTOR가 지정된 경우에만 유용합니다.FILLFACTOR에 지정된 백분율이 한 행을 허용할 만큼 크지 않으면 데이터베이스 엔진은 내부적으로 최소를 허용하도록 백분율을 재정의합니다.중간 인덱스 페이지의 행 수는 채우기 비율 값이 얼마나 낮은지에 관계없이 두 개 이상이어야 합니다.
이전 버전과 호환되는 구문에서 WITH PAD_INDEX는 WITH PAD_INDEX = ON과 동일합니다.
이것은 사실 매우 복잡한 주제입니다.PAD_INDEX를 설정하면 큰 테이블의 읽기 성능과 메모리 압력에 큰 영향을 미칠 수 있습니다.테이블이 클수록 효과가 큽니다.일반적으로 드물지 않은 범주에 속하지 않는 한 이 범주를 제외하는 것이 좋습니다.그리고 나서, 이 충고를 주의 깊게 따르세요.아래 예제에서 보듯이 PAD_INDEX가 ON일 때 FILLFACTOR를 조정하면 신중하게 균형을 맞춰야 하는 지수 효과를 얻을 수 있습니다.
- PAD_INDEX는 항상 읽기에 악영향을 미칩니다!FILL FORTER가 낮을수록 효과가 커지므로 FILL FORTER를 켤 때 값에 주의를 기울여야 합니다.큰 테이블에서 여러분은 근본적으로 잎 분할을 줄이는 관점에서 FILL Factor에 대한 생각을 멈추고 중간 팽창 대 중간 분할에 대한 영향에 대해 생각하기 시작합니다.
- PAD_INDEX는 행 수가 100,000개 미만인 인덱스에는 거의 효과가 없으며 NEVER는 ID 또는 삽입 시간 유형 열을 포함하는 인덱스에는 항상 긍정적인 영향을 미칩니다.
- 위에서 PAD_INDEX를 설정할 경우 음의 효과와 양의 효과 사이에서 신중하게 균형을 맞춰야 합니다.
경험의 법칙: PAD_INDEX는 다음에서 거의 유용하지 않습니다.
- 비클러스터형 인덱스 - 상당히 넓은 경우를 제외하고는 사용할 수 없습니다.
- 매우 좁은 테이블의 클러스터된 인덱스입니다.
- 행 수가 100K 미만인 테이블의 경우 - 삽입물이 고도로 군집화되어 있더라도 문제가 있을 수 있습니다.
작동 방식을 이해해야 합니다.인덱스에 삽입할 때 행은 적절한 범위의 키를 포함하는 리프 블록에 맞아야 합니다.클러스터된 인덱스는 일반적으로 비클러스터된 인덱스보다 행이 훨씬 넓기 때문에 리프 블록의 행 수가 적습니다.FillFactor는 리프에 새 행을 위한 공간을 생성하지만, 매우 넓은 행이나 균일하게 분산되지 않고 함께 군집화된 대량의 삽입물의 경우 분할을 방지하기 위해 충분한 슬랙(1-pct fill)을 생성하는 것이 비현실적이거나 불가능한 경우가 많습니다.
분할이 발생하면 새 블록을 가리키도록 새 중간 행이 생성되고 해당 행이 해당 블록에 적합해야 합니다.중간 블록이 가득 찬 경우 먼저 분할해야 합니다.만약 당신이 특별히 운이 나쁘다면 분열은 뿌리까지 계속될 수 있습니다.루트가 분할되면 새 인덱스 수준이 생성됩니다.
PAD_INDEX의 요점은 중간 수준 블록에 최소 사용 가능한 공간을 확보하는 것입니다.
재구성 후 하위 레벨에 공간이 거의 없거나 아예 없을 수 있습니다.따라서 많은 리프 스플릿이 있고 PAD_INDEX가 켜져 있지 않으면 모든 곳에서 중간자의 대규모 스플릿이 발생할 수 있습니다!
대부분의 분할은 FILLFACTOR를 사용하여 관리할 수 있습니다.더 큰 분할 문제는 사용 가능한 공간이 충분하지 않다는 것을 사실상 보장하는 삽입 패턴에서 발생하며 PAD_INDEX를 설정하면 더 깊은 수준의 공간을 제공하므로 분할이 발생할 때 다중 수준의 분할이 많이 발생할 가능성이 줄어듭니다.
예제 사례
저는 10만 줄의 고객 테이블을 가지고 있습니다.어느 날이든 제 고객의 약 5%가 활동할 것입니다.고객별 활동을 시간별로 기록한 표가 있습니다.고객은 평균적으로 20개의 작업을 수행하고 설명에는 평균 1K가 소요됩니다.따라서 100MB의 데이터를 수집하고 이미 1년이 있다고 가정해 보겠습니다. 즉 36GB입니다.
테이블에는 키 열에 대한 customer_number 및 insert_time(순서대로)이 있는 1Kb 행의 삽입이 있습니다.일반 고객은 예상되는 20개의 행을 삽입하면서 8K 리프 블록을 여러 번 분할할 것이 분명합니다. 각 행은 분할 및 분할될 때까지 동일한 블록에서 이전 행 바로 뒤에 삽입되기 때문입니다(비클러스터된 인덱스만 있는 힙으로 간주됩니다...).적절한 리프를 가리키는 중간 블록에 적어도 4개의 행을 위한 공간이 충분하지 않은 경우(실제로는 8개이지만...) 중간 블록이 분할되어야 합니다.이 예제의 키가 22바이트를 차지하는 경우 중간 블록은 367개의 항목을 수용할 수 있습니다.즉, 중간 블록에 6%의 여유 공간이 필요하거나 4개의 항목을 저장하기 위해 94%의 여유 공간이 필요합니다.
블록 하나에 8개의 행만 저장할 수 있기 때문에 1% FILLFACTOR도 리프 블록 분할을 막지 못합니다.FILLFACTOR를 80%로 설정하면 리프가 분할되기 전에 한 행만 추가할 수 있지만 PAD_INDEX가 설정된 경우 중간 블록당 800바이트 이상의 사용 가능한 공간이 주입됩니다.88개만 있으면 모든 중간 블록에 대해 최대 800바이트의 빈 바이트가 됩니다.
이것은 정말 중요합니다!:표에 이미 36M개의 행이 있는 경우 80%를 사용하면 중간 블록당 294개의 행이 생성됩니다. 즉, 122K개의 블록을 사용하면 94%가 블록당 345개의 행을 처리할 수 있으므로 104K개의 중간 블록만 생성할 때 98MB를 중간 블록 구조에 추가했습니다.104K 블록 각각에 88바이트를 추가하면 9.2바이트만 추가됩니다.98MB가 아닌 MB입니다.
이제 제 고객의 5%만이 어떤 일도 했다는 것을 생각해 보십시오.일부는 20개 이상의 작업을 수행하고 일부는 더 적은 수의 블록이 분할되었으며, 실제로는 275KB만 하루의 인덱스 행(100k/8*22)을 유지해야 하므로 가장 좋은 경우는 8.9입니다.나의 9.2 MBMB는 죽은 공기였습니다.분할 방지가 중요하다면 9mb의 가치가 있지만 98mb는 더 고민하고 있습니다.
따라서 PAD_INDEX를 설정하면 리프 분할 제어를 완전히 포기하고 중간 분할 제어로 전환해야 합니다.
첫 번째 중급 단계를 제외하고는 아무 것도 걱정하지 마세요!모든 군집화(이 경우 customer_number의 군집화)에 의해 유도된 나비 효과가 있으며, 이는 사용자가 만든 모든 계산을 창 밖으로 던집니다.삽입물이 완벽하게 균일하지 않은 경우 분할로 팽창의 균형을 맞추기 위한 올바른 숫자를 찾는 데 있어 오차 한계는 일반적으로 하위 수준 블록 공간의 효과보다 훨씬 큽니다.
@bielawski PAD_INDEX=ON 및 FILLFACTOR가 1에서 99 사이인 경우만 설명합니다.PAD_INDEX=ON과 FILLFACTOR=0 또는 100을 설정하면 이전 행보다 항상 새로운 순서의 행을 삽입할 수 있습니다.
CREATE CLUSTERED INDEX [IX_z_arch_export_dzienny_pre] ON [dbo].[z_arch_export_daily_pre]
(
[Date] ASC,
[Object Code] ASC,
[From date] ASC,
[Person_role] ASC,
[Departure] ASC,
[Room code] ASC,
[period_7_14] ASC
)WITH (PAD_INDEX = ON, FILLFACTOR=100)
insert into z_arch_export_daily_pre
select * from export_daily_pre
order by [Date] ASC,[Object Code] ASC,[From date] ASC,[Person_role] ASC,[Departure] ASC,[Room code] ASC,[period_7_14] ASC
모든 새 행이 인덱스의 "끝"에 삽입될 것임을 100% 확신하며, 이 옵션(PAD_INDEX = ON, FILLFACTOR = 100)을 사용해야만 삽입 후 조각화 인덱스의 0.01%를 얻을 수 있습니다.이 설정에서 이러한 가정을 사용하는 것이 위험합니까?
언급URL : https://stackoverflow.com/questions/6857007/what-is-the-purpose-of-pad-index-in-this-sql-server-constraint
'programing' 카테고리의 다른 글
| Git를 사용하여 변경 로그를 관리하는 좋은 방법은 무엇입니까? (0) | 2023.07.15 |
|---|---|
| Angular 2-Types 스크립트에서 선택적 클래스 매개 변수를 설정하는 방법은 무엇입니까? (0) | 2023.07.15 |
| SQL 서버에 null 값을 삽입하는 방법 (0) | 2023.07.15 |
| .yml 파일에서 속성 자리 표시자를 사용하는 방법 (0) | 2023.07.10 |
| 이미 체크인된 디렉토리의 내용을 무시하시겠습니까? (0) | 2023.07.10 |