태그 보관물: clustered-index

clustered-index

클러스터형 인덱스가있는 테이블에 효율적인 삽입 COL_C FROM STAGING_TABLE 내 질문은-클러스터 된 인덱스 열의

TRACKING_NUMBER 열에 클러스터 된 인덱스가있는 테이블에 행을 삽입하는 SQL 문이 있습니다.

EG :

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, COL_C
FROM STAGING_TABLE

내 질문은-클러스터 된 인덱스 열의 SELECT 문에서 ORDER BY 절을 사용하는 데 도움이됩니까, 아니면 ORDER BY 절에 필요한 추가 정렬에 의해 달성 된 이득이 무시됩니까?



답변

다른 답변에서 이미 지적했듯이 SQL Server는 행이 클러스터 된 인덱스 순서로 정렬되도록 명시 적으로 또는 확실하지 않을 수 있습니다. insert .

이는 계획의 클러스터형 인덱스 연산자에 계획이 있는지 여부에 따라 다릅니다. DMLRequestSort 속성 세트 따라 달라집니다 (삽입 된 예상 행 수에 따라 달라짐).

당신은 SQL 서버 사용자가 명시 적으로 추가 혜택을 누릴 수있는 어떤 이유에서이 과소 평가 것으로 확인되면 ORDER BY받는 사람 SELECT으로부터 단편화를 페이지 분할을 최소화하기 위해 쿼리 및 다음의 INSERT동작

예:

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;  

T엄청나게 조각난 쇼

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

그러나 T2조각화는 최소화됩니다

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

반대로 데이터가 이미 미리 정렬되어 있고 불필요한 정렬을 피하려는 경우 SQL Server가 행 수를 과소 평가하도록 할 수 있습니다. 주목할만한 예는 newsequentialid클러스터 된 인덱스 키 를 사용하여 테이블에 많은 수의 행을 삽입하는 경우 입니다. Denali 이전의 SQL Server 버전에서는 SQL Server가 불필요하고 비용이 많이 드는 정렬 작업을 추가합니다 . 이것은 피할 수 있습니다

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

그런 다음 SQL Server는 크기가 Bar계획에 정렬이 추가되는 임계 값보다 작은 크기에 관계없이 100 개의 행이 삽입 될 것으로 추정합니다 . 그러나 아래 주석에서 지적했듯이 이것은 삽입이 불행히도 최소 로깅을 이용할 수 없음을 의미합니다.


답변

옵티마이 저는 삽입 전에 데이터를 정렬하는 것이 더 효율적이라고 결정하면 삽입 연산자의 업스트림 어딘가에서 그렇게합니다. 쿼리의 일부로 정렬을 도입하면 옵티마이 저는 데이터가 이미 정렬되어 있음을 인식하고 다시 수행하지 않아야합니다. 선택한 실행 계획은 준비 테이블에서 삽입 된 행 수에 따라 실행마다 다를 수 있습니다.

명시 적 정렬을 사용하거나 사용하지 않고 프로세스의 실행 계획을 캡처 할 수 있으면 질문에 첨부하여 의견을 보내십시오.

편집 : 2011-10-28 17:00

@Gonsalu의 답변 은 정렬 작업이 항상 발생한다는 것을 나타내는 것으로 보입니다. 데모 스크립트가 필요합니다!

대본이 상당히 커지면서 요즘으로 이동했습니다. . 실험을 쉽게하기 위해 스크립트는 SQLCMD 모드를 사용합니다. 테스트는 2K5SP3, 듀얼 코어, 8GB에서 실행됩니다.

인서트 테스트에는 세 가지 시나리오가 포함됩니다.

  1. 대상과 동일한 순서로 준비 데이터 클러스터 된 인덱스
  2. 준비 데이터 클러스터 된 인덱스를 역순으로.
  3. 임의의 INT를 포함하는 col2에 의해 클러스터 된 스테이징 데이터.

먼저 25 행을 삽입하여 실행합니다.

세 가지 실행 계획은 모두 동일하며 계획의 어느 곳에서도 정렬이 발생하지 않으며 클러스터형 인덱스 스캔은 “순서 = 거짓”입니다.

두 번째 실행, 26 행 삽입

이번에는 계획이 다릅니다.

  • 첫 번째는 클러스터 된 인덱스 스캔을 ordered = false로 표시합니다. 소스 데이터가 적절히 정렬되어 정렬이 수행되지 않았습니다.
  • 두 번째로 클러스터형 인덱스 스캔은 ordered = true로, 역방향으로 스캔합니다. 따라서 정렬 작업이 없지만 데이터를 정렬해야 할 필요성은 옵티마이 저가 인식하고 역순으로 스캔합니다.
  • 세 번째는 정렬 연산자를 보여줍니다.

따라서 옵티마이 저가 필요하다고 생각되는 티핑 포인트가 있습니다. @MartinSmith에서 알 수 있듯이 이것은 삽입 될 예상 행을 기반으로하는 것으로 보입니다. 내 테스트 장비 25에서 정렬이 필요하지 않고 26이 필요합니다 (2K5SP3, 듀얼 코어, 8GB)

SQLCMD 스크립트에는 테이블의 행 크기를 변경하여 (페이지 밀도 변경) 추가 삽입 전에 dbo.MyTable의 행 수를 변경할 수있는 변수가 포함됩니다. 내 테스트에서 티핑 포인트에는 영향을 미치지 않습니다.

독자가 너무 기울어 지면 스크립트실행하고 팁을 설명으로 추가하십시오. 테스트 장비 및 / 또는 버전에 따라 다를 수 있습니다.

편집 : 2011-10-28 20:15

동일한 리그에서 2K8R2로 반복 테스트. 이번에는 티핑 포인트가 251 행입니다. 페이지 밀도와 기존 행 수를 변경해도 아무런 영향을 미치지 않습니다.


답변

명령문 의 ORDER BY절이 SELECT중복됩니다.

삽입 될 행이 정렬되어야하는 경우 어쨌든 정렬 되므로 중복됩니다 .

테스트 사례를 만들어 봅시다.

CREATE TABLE #Test (
    id INTEGER NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id);

CREATE TABLE #Sequence (
    number INTEGER NOT NULL
);

INSERT INTO #Sequence
SELECT number FROM master..spt_values WHERE name IS NULL;

실제 쿼리 계획의 텍스트 표시를 활성화하여 쿼리 프로세서가 수행하는 작업을 확인할 수 있습니다.

SET STATISTICS PROFILE ON;
GO

이제 절 INSERT없이 2K 행을 테이블에 넣습니다 ORDER BY.

INSERT INTO #Test
SELECT number
  FROM #Sequence

이 쿼리의 실제 실행 계획은 다음과 같습니다.

INSERT INTO #Test  SELECT number    FROM #Sequence
  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
       |--Top(ROWCOUNT est 0)
            |--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))

보시다시피 실제 INSERT가 발생하기 전에 Sort 연산자가 있습니다.

이제 테이블을 지우고 절 INSERT을 사용하여 테이블에 2k 행을 봅시다 ORDER BY.

TRUNCATE TABLE #Test;
GO

INSERT INTO #Test
SELECT number
  FROM #Sequence
 ORDER BY number

이 쿼리의 실제 실행 계획은 다음과 같습니다.

INSERT INTO #Test  SELECT number    FROM #Sequence   ORDER BY number
  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
       |--Top(ROWCOUNT est 0)
            |--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))

INSERT명령이없는 명령문 에 사용 된 것과 동일한 실행 계획입니다 .ORDER BY절 .

이제 Mark Smith가 다른 답변 (삽입 할 행 수가 적은 경우)에 표시된 것처럼Sort 작업이 항상 필요한 것은 아니지만 명시적인 경우에도ORDER BYORDER BYSort 작업이 생성 됩니다 쿼리 프로세서에서

INSERT최소 로그를 사용하여 클러스터 된 인덱스가있는 테이블로 명령문을 최적화 할 수 INSERT있지만이 질문의 범위를 벗어납니다.

2011-11-02 업데이트 : 로 마크 스미스가 보여 주었다 , INSERT클러스터 된 색인이있는 테이블에이야 항상 정렬 할 필요가 있습니다 – ORDER BY절은하지만, 또한 경우에 중복됩니다.


답변