SQL Server: Execution Plans, Key Lookup, Statistics, and Partitioning

English version

This note explains how to read SQL Server execution plans beyond the simple rule of “seek is good, scan is bad.” In practice, tuning depends on how much data SQL Server reads, how many rows it returns, how accurate the cardinality estimate is, and whether the chosen index actually covers the query.

Logical reads and execution plans

SET STATISTICS IO ON shows how many 8KB pages SQL Server reads. The key metric is logical reads.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

If a query reports 7,405 logical reads, the approximate amount of data read is:

7405 pages * 8KB = 59,240KB
about 59MB

Logical reads are useful because two queries can return the same result while reading very different amounts of data.

SQL Server execution plans are usually read from right to left and top to bottom. If most of the estimated cost is on a Clustered Index Scan, SQL Server expects to spend most of the work reading the clustered index. If the plan includes Parallelism, SQL Server decided the query was expensive enough to split work across multiple workers.

Estimated Subtree Cost is a rough optimizer estimate of CPU and I/O work. It is useful for understanding the optimizer’s choice, but it is not the same as real elapsed time. Always compare it with actual rows, logical reads, CPU time, elapsed time, and expensive operators such as Sort, Hash Match, and Parallelism.

ORDER BY and covering indexes

ORDER BY can be expensive. Without an index that supports the requested order, SQL Server may need to read rows first and then sort them. Sorts can require memory grants, and larger row widths make the sort more expensive.

A nonclustered index can reduce both reads and sorting work when it matches the query:

CREATE INDEX IX_LastAccessDate_ID
ON dbo.Users(LastAccessDate, Id);

If the query only needs LastAccessDate and Id, this index can cover the query. A covering index means the index contains all columns needed by the query, so SQL Server does not need to go back to the base table.

Seek is not always good, scan is not always bad

An Index Seek means SQL Server can jump to a position in the index. But a seek can still read a very large range. An Index Scan means SQL Server starts reading through an object, but if the query needs most of the rows, a scan may be cheaper than many lookups.

The better question is:

• How many rows did SQL Server read?
• How many rows did it return?
• How many logical reads did it produce?
• Was the estimate close to the actual row count?

Key Lookup and the tipping point

A Key Lookup is required when a nonclustered index does not contain all the columns required by the query. SQL Server first uses the nonclustered index, then goes back to the clustered index to fetch missing columns.

This is fine for a small number of rows. But when many rows need lookups, the repeated lookup cost can become more expensive than scanning the clustered index once. This is the tipping point where SQL Server may choose a scan even though a nonclustered index exists.

You can inspect the Output List in the execution plan to see which columns are required. If a lookup is fetching columns such as DisplayName and Age, and those columns are frequently needed, you may consider widening the index.

INCLUDE columns

One way to avoid repeated key lookups is to include extra columns in the nonclustered index:

CREATE INDEX IX_LastAccessDate_Id_Includes
ON dbo.Users(LastAccessDate, Id)
INCLUDE(DisplayName, Age);

LastAccessDate and Id remain key columns, so they define the index order. DisplayName and Age are stored at the leaf level, so the query can read them without changing the key order. This is often better than making every selected column part of the key.

Statistics and cardinality estimation

When you create an index, SQL Server usually creates statistics with the same name. Statistics describe the distribution of values inside the index. SQL Server uses them to decide which index to use, whether to seek or scan, how to order table access, how many rows will match, and how much memory to request.

DBCC SHOW_STATISTICS('dbo.Users', 'IX_LastAccessDate_Id');

The plan is chosen before SQL Server reads the table. That means the optimizer is guessing based on statistics. This is cardinality estimation, and it is not perfect, especially with real-world T-SQL, functions in predicates, skewed data, and joins across multiple tables.

Partitioning

Partitioning divides a large table by a chosen key, often a date or range value. In SQL Server, partitioning usually involves a partition function and a partition scheme.

Partitioning is not automatically a SELECT performance feature. It is often most valuable for data lifecycle work, especially purging or switching old data out of a large table. If queries do not filter on the partition key, SQL Server may not be able to eliminate partitions, and the performance benefit can be limited. In other words, "make it partitioned for speed" is not a good requirement by itself. The better question is whether the table has a clear retention or purge pattern, and whether the important queries always include the partition key.

A partitioned table still has one logical table definition. Each partition follows the same table and index structure, and partitioned indexes need to stay aligned with the partitioning design. This matters during operations such as partition switch or truncating a specific partition. If the table and index structures are not aligned, SQL Server can reject the operation or force a more expensive maintenance path.

The common partitioning concepts are range, hash, list, and composite partitioning. In SQL Server work, range partitioning by date or another ordered key is the pattern people usually mean. The function defines the boundary values, with RANGE RIGHT putting the boundary value into the right partition and RANGE LEFT putting it into the left partition. The scheme maps those partitions to filegroups.

Partition pruning happens when SQL Server can exclude partitions that do not match the predicate. To benefit from this, important queries should include the partition key in their filters.

The final lesson is that tuning does not end when an index is created. You still need to check how many pages SQL Server reads, why the optimizer chose that plan, and whether the same plan will still make sense as data grows.

Korean notes

SQL Server 튜닝을 공부하다 보면 실행 계획에서 Scan, Seek, Key Lookup, Parallelism, Estimated Subtree Cost 같은 용어를 계속 보게 된다. 처음에는 Seek가 좋고 Scan이 나쁘다고 단순하게 외우기 쉽지만, 실제로는 그렇게 판단하면 위험하다.

이번 글은 실행 계획을 읽을 때 봐야 하는 지표, nonclustered index와 key lookup, statistics와 cardinality estimation, 그리고 partition을 왜 쓰는지까지 이어서 정리한다.

실행 계획 읽기: logical reads, cost, scan, seek

쿼리 튜닝할 때 실행 계획만 보는 것보다 SET STATISTICS IO, TIME ON 결과를 같이 보는 것이 중요하다.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

STATISTICS IO는 SQL Server가 몇 개의 8KB page를 읽었는지 보여준다. 여기서 중요한 값이 logical reads다.

예를 들어 logical reads가 7405라면 대략 이렇게 계산할 수 있다.

7405 pages * 8KB = 59,240KB
약 59MB

즉 logical reads는 "쿼리가 메모리/버퍼 캐시에서 얼마나 많은 데이터 페이지를 읽었는지"를 보는 지표다. 같은 결과를 반환하더라도 logical reads가 크게 줄면 보통 더 좋은 실행 계획일 가능성이 높다.

실행 계획은 오른쪽에서 왼쪽, 위에서 아래로 읽는다

SQL Server execution plan은 보통 오른쪽에서 왼쪽, 그리고 위에서 아래 방향으로 읽는다.

예를 들어 실행 계획에 Clustered Index Scan이 크게 나오고 비용이 96%로 표시된다면, SQL Server가 clustered index 전체를 넓게 읽고 있다는 뜻이다. 여기에 노란색 아이콘 안에 화살표 두 개가 보이면 parallelism이 들어간 것이다.

Parallelism은 SQL Server가 이 쿼리를 비싼 작업으로 보고, 일을 여러 worker에게 나눠 처리했다는 뜻이다. 사람이 여러 명 붙어서 일을 나눠 하는 것처럼 볼 수 있다. 다만 parallelism이 보인다고 무조건 나쁜 것은 아니다. SQL Server가 그만큼 읽을 데이터가 많다고 판단했다는 신호로 보면 된다.

Estimated subtree cost

Estimated Subtree Cost는 해당 쿼리를 수행하는 데 필요한 CPU와 I/O 작업량을 대략적으로 나타내는 값이다.

정확한 시간은 아니지만, 일반적으로 cost가 높을수록 더 많은 CPU/I/O가 필요하고 쿼리가 오래 걸릴 가능성이 커진다.

다만 cost만 보고 판단하면 안 된다. 실제 튜닝에서는 다음을 같이 본다.

• logical reads
• CPU time
• elapsed time
• 실제 읽은 row 수
• 실제 반환한 row 수
• index를 제대로 탔는지
• sort, hash, parallelism 같은 비싼 연산이 있는지

WHERE 조건에 맞는 인덱스가 없으면 많이 읽는다

WHERE 조건이 있어도, 그 조건을 지원하는 인덱스가 없으면 SQL Server는 결국 전체 데이터를 훑어야 할 수 있다.

SELECT Id, LastAccessDate
FROM dbo.Users
WHERE LastAccessDate > '2014-07-01';

LastAccessDate에 맞는 인덱스가 없다면 SQL Server는 clustered index나 table을 scan하면서 조건에 맞는 row를 찾는다. parallel query에서는 worker들이 나눠 읽으면서 추가 read가 조금 생길 수도 있다.

ORDER BY는 생각보다 비싸다

ORDER BY를 쓰면 SQL Server는 단순히 조건에 맞는 row를 찾는 것에서 끝나지 않는다.

대략 이런 작업이 필요하다.

1. 전체 page를 훑으면서 LastAccessDate > '2014-07-01' 조건에 맞는 row를 찾는다.
2. 찾은 row들을 LastAccessDate 기준으로 정렬한다.

정렬은 비싼 작업이다. 정렬할 row가 많고, 선택한 column이 많을수록 더 비싸진다. SQL Server는 정렬 중간 결과를 담기 위해 memory grant를 받을 수 있다.

여기서 중요한 점은 SQL Server가 query output 자체를 캐싱하는 것이 아니라 data page를 캐싱한다는 것이다. 같은 데이터를 읽더라도 정렬 작업은 다시 필요할 수 있다.

Nonclustered index는 필요한 컬럼의 복사본이다

Nonclustered index는 원본 테이블과 별도로 저장되는 정렬된 복사본처럼 볼 수 있다. 원하는 순서로 저장하고, 쿼리에 필요한 컬럼을 포함할 수 있다.

예를 들어 다음 인덱스를 만든다고 하자.

CREATE INDEX IX_LastAccessDate_ID
ON dbo.Users(LastAccessDate, Id);

이 인덱스는 LastAccessDate 기준으로 정렬되어 있고, Id도 같이 가지고 있다. 그래서 다음처럼 LastAccessDate, Id만 필요한 쿼리라면 원본 테이블을 다시 찾아가지 않아도 된다.

SELECT Id, LastAccessDate
FROM dbo.Users
WHERE LastAccessDate > '2014-07-01'
ORDER BY LastAccessDate;

이런 경우를 covering index라고 부른다. 쿼리에 필요한 컬럼을 인덱스가 모두 가지고 있어서 원본 테이블 lookup 없이 처리할 수 있기 때문이다.

실제로 nonclustered index를 쓰기 전후로 CPU 시간과 logical reads가 크게 차이 날 수 있다. 예를 들어 같은 조건에서 logical reads가 7778에서 335로 줄었다면, 읽은 8KB page 수가 크게 줄어든 것이다.

Seek가 항상 좋고 Scan이 항상 나쁜 것은 아니다

실행 계획에서 Index Seek가 보이면 좋아 보이고, Index Scan이 보이면 나빠 보일 수 있다. 하지만 그렇게 단순하게 보면 안 된다.

Seek는 특정 위치로 jump해서 읽기 시작한다는 뜻이다.

I am going to jump to a row and start reading.

그런데 seek로 시작했다고 해서 항상 적게 읽는 것은 아니다. 특정 위치로 들어간 뒤 조건에 따라 거의 전체 index를 읽을 수도 있다.

반대로 scan은 한쪽 끝에서 읽기 시작한다는 뜻이다.

I am going to start at one end of the object and start reading.

하지만 scan도 항상 전체를 다 읽는 것은 아니다. 경우에 따라 필요한 row를 찾고 얼마 안 읽고 끝날 수도 있다.

그래서 중요한 질문은 이것이다.

• Seek냐 Scan이냐?
• 보다 더 중요한 것은, 몇 row를 읽었고 몇 row를 반환했는가?

실행 계획 tooltip에서 Number of Rows Read, Actual Number of Rows, Estimated Number of Rows to be Read 같은 값을 같이 봐야 한다.

예를 들어 Index Seek (NonClustered)가 떠도 실제로 148,328 rows를 읽고 148,328 rows를 반환했다면, 이름은 seek지만 넓은 범위를 읽은 것이다. 이 경우 seek라고 해서 무조건 좋은 계획이라고 볼 수 없다.

정리

이번 메모에서 기억할 것은 다음이다.

• SET STATISTICS IO ON은 읽은 8KB page 수를 보여준다.
• SET STATISTICS TIME ON은 CPU time과 elapsed time을 보여준다.
• WHERE 조건을 지원하는 인덱스가 없으면 scan이 발생하기 쉽다.
• ORDER BY를 지원하는 인덱스가 없으면 sort 비용이 생긴다.
• Nonclustered index가 쿼리에 필요한 컬럼을 모두 포함하면 covering index가 된다.
• Covering index는 logical reads와 CPU 시간을 크게 줄일 수 있다.
• Seek는 항상 좋은 것이 아니고, Scan도 항상 나쁜 것이 아니다.
• 결국 읽은 row 수, 반환한 row 수, logical reads, CPU time을 같이 봐야 한다.

Key lookup과 tipping point

Key Lookup은 nonclustered index만으로 쿼리에 필요한 컬럼을 모두 가져올 수 없을 때 발생한다.

예를 들어 nonclustered index에는 LastAccessDate, Id만 있는데 쿼리에서 DisplayName, Age까지 필요하면 SQL Server는 먼저 nonclustered index를 seek한 뒤, clustered index로 다시 가서 부족한 컬럼을 찾아와야 한다.

Nonclustered Index Seek
    -> Key Lookup on Clustered Index

이 구조는 결과 row가 적을 때는 괜찮다. 필요한 row 몇 개만 찾아서 lookup하면 되기 때문이다. 하지만 결과 row가 많아지면 이야기가 달라진다. row마다 lookup을 반복해야 하므로 비용이 커진다.

이때 SQL Server는 선택해야 한다.

• 전체 clustered index/table을 한 번 scan할 것인가
• nonclustered index seek 후 key lookup을 반복할 것인가

작은 데이터면 index seek + key lookup이 유리할 수 있고, 큰 데이터면 그냥 한 번 scan하는 것이 더 나을 수 있다. 이 경계점을 흔히 tipping point처럼 볼 수 있다.

그래서 인덱스가 있어도 SQL Server가 scan을 선택할 수 있다. 인덱스가 있는데도 scan이 나왔다고 해서 무조건 이상한 것은 아니다. SQL Server가 보기에는 lookup을 많이 반복하는 것보다 한 번 넓게 읽는 편이 싸다고 판단했을 수 있다.

Output List로 필요한 컬럼 확인하기

실행 계획에서 operator tooltip의 Output List를 보면 해당 단계에서 어떤 컬럼을 필요로 하는지 알 수 있다.

Key Lookup tooltip에 Age, DisplayName 같은 컬럼이 보이면, nonclustered index에 그 컬럼이 없어서 clustered index까지 다시 찾아간다는 뜻이다.

이때 자주 쓰는 컬럼이고, 조회 성능이 중요하다면 nonclustered index를 넓히는 것을 검토할 수 있다.

Statistics와 cardinality estimation

인덱스를 만들면 보통 같은 이름의 statistics도 같이 생긴다. Statistics는 인덱스 안에 어떤 값들이 어떤 분포로 들어있는지에 대한 메타데이터다. 대략 8KB page 하나 정도의 metadata라고 이해하면 쉽다.

SQL Server는 statistics를 보고 다음을 결정한다.

• 어떤 index를 사용할지
• table/index를 어떤 순서로 처리할지
• seek를 할지 scan을 할지
• 조건에 몇 row가 걸릴지
• 쿼리에 memory를 얼마나 줄지

statistics는 다음처럼 확인할 수 있다.

DBCC SHOW_STATISTICS('dbo.Users', 'IX_LastAccessDate_Id');

여기서 중요한 점은 SQL Server가 테이블을 읽기 전에 먼저 실행 계획을 선택해야 한다는 것이다. 즉 실제로 몇 row가 나올지 읽어보고 결정하는 것이 아니라, statistics를 보고 추정한다.

이 추정을 cardinality estimation이라고 한다. 문제는 이 추정이 항상 맞지는 않는다는 점이다. 특히 real-world T-SQL처럼 조건이 복잡하거나 여러 테이블을 join하면 추정이 틀어질 수 있다.

같은 결과를 반환하는 쿼리라도 조건 작성 방식에 따라 다른 plan이 나올 수 있다. 예를 들어 날짜 조건을 그대로 쓰는 쿼리와 CAST(LastAccessDate AS DATE)를 씌운 쿼리는 결과는 비슷해도 SQL Server가 읽는 방식과 plan이 달라질 수 있다.

Wider index와 INCLUDE 컬럼

Key Lookup이 너무 많이 발생하면 nonclustered index를 더 넓히는 방법을 생각할 수 있다.

처음에는 이렇게 만들 수 있다.

CREATE INDEX IX_LastAccessDate_Id_Includes
ON dbo.Users(LastAccessDate, Id, DisplayName, Age);

이렇게 하면 LastAccessDate, Id, DisplayName, Age가 모두 index key에 들어간다. 쿼리에 필요한 컬럼을 index가 가지고 있으므로 key lookup을 줄일 수 있다.

하지만 모든 컬럼을 key로 넣는 것은 부담이 있다. key column은 정렬 구조에 포함된다. 예를 들어 Age가 key column이면 Age 값이 바뀔 때 index 정렬 구조에도 영향을 준다.

그래서 조회에는 필요하지만 정렬/탐색 기준은 아닌 컬럼은 INCLUDE로 넣는 방식이 더 적절할 수 있다.

CREATE INDEX IX_LastAccessDate_Id_Includes
ON dbo.Users(LastAccessDate, Id)
INCLUDE(DisplayName, Age);

이렇게 하면 LastAccessDate, Id는 index key로 사용하고, DisplayName, Age는 leaf level에 포함된다. 즉 쿼리에 필요한 컬럼은 index 안에서 해결할 수 있지만, Age가 바뀐다고 해서 key 순서를 다시 잡는 부담은 줄어든다.

정리하면 다음과 같다.

• Index Seek + Key Lookup이 많이 반복되면 wider index가 필요할 수 있다.
• 자주 조회하지만 검색/정렬 기준이 아닌 컬럼은 INCLUDE 후보가 될 수 있다.
• Statistics는 SQL Server가 index, join 순서, seek/scan, memory grant를 고르는 데 사용된다.
• Cardinality estimation은 완벽하지 않다.
• 같은 결과를 반환하는 쿼리도 다른 plan을 가질 수 있다.

Partition 기본 개념

Partition은 기간별 또는 특정 기준별로 데이터를 나누어 저장하는 방식이다. 데이터가 많은 테이블을 하나의 큰 덩어리로만 관리하면 조회, 삭제, 보관 작업이 무거워질 수 있다. 이때 partition을 나누면 특정 범위만 대상으로 작업할 수 있다.

중요한 점은 partition table도 같은 논리 테이블이라는 것이다. partition 1, partition 2처럼 나뉘어 있어도 같은 구조의 index를 가진다. 내부적으로는 partition별로 B-tree 구조를 가진다고 이해하면 된다.

다만 partition을 "속도 개선 기능"으로만 보면 안 된다. partition의 핵심 목적은 대량 데이터를 기준별로 관리하기 쉽게 만드는 데 있고, 실무에서는 특히 데이터 purge와 보관 정책에서 가치가 크다. SELECT 성능은 partition key가 조건절에 들어가서 partition pruning이 일어날 때 의미가 있다.

Partition table을 만들 때 필요한 것

SQL Server에서 partition table을 구성하려면 보통 두 가지가 필요하다.

1. Partition function
2. Partition scheme

Partition function은 어떤 경계값을 기준으로 데이터를 나눌지 정의한다. 함수만 있다고 partition table이 만들어지는 것은 아니고, partition scheme과 table/index 매핑이 필요하다.

Partition function에서는 RANGE LEFT, RANGE RIGHT를 정할 수 있다.

• RANGE RIGHT: 경계값이 오른쪽 partition에 포함된다.
• RANGE LEFT: 경계값이 왼쪽 partition에 포함된다.

Partition scheme은 partition function과 filegroup을 연결한다. 즉 각 partition을 어느 filegroup에 둘지 지정하는 역할을 한다.

예를 들어 기간 기준으로 데이터를 나눈다면 partition function은 "어떤 날짜 경계로 나눌 것인가"를 정의하고, partition scheme은 "각 partition을 어떤 filegroup에 둘 것인가"를 연결한다. 그 다음 table이나 index를 이 scheme에 매핑해야 실제 partitioned table로 동작한다.

Partition은 purge에 유리하다

Partition은 단순히 SELECT 성능만을 위해 쓰는 기능이 아니다. 오히려 대량 데이터 purge에서 장점이 크다.

partition 없이 오래된 데이터를 지우려면 DELETE TOP (1000) 같은 방식으로 조금씩 지우는 경우가 많다. 하지만 이런 방식은 log가 많이 쌓이고 오래 걸릴 수 있다.

반대로 날짜나 기준값으로 partition이 나뉘어 있으면 오래된 partition 하나를 통째로 비우거나 switch/truncate하는 방식으로 훨씬 빠르게 정리할 수 있다.

예를 들어 operational table TC_TRAY의 오래된 데이터를 history table TH_TRAY로 옮긴 뒤, 기존 table에서는 해당 데이터를 삭제하는 식의 purge 시나리오를 생각할 수 있다. 두 테이블의 구조와 index가 맞아야 partition switch 같은 작업을 안정적으로 할 수 있다.

다만 partition을 truncate하거나 switch할 때 index 구조가 맞지 않으면 에러가 발생할 수 있다. partitioned table과 관련된 index도 partition alignment가 중요하다.

예를 들어 partition 1, partition 2, partition 3이 있고 partition 1만 비우고 싶다고 하자. 단순히 전체 테이블에서 row를 조금씩 DELETE하면 transaction log가 크게 쌓이고 시간이 오래 걸릴 수 있다. 반대로 partition 단위로 데이터를 분리해두면 오래된 partition만 switch out 하거나 truncate하는 식으로 훨씬 깔끔하게 purge할 수 있다.

하지만 partitioned table에 걸린 index가 partition 구조와 맞지 않으면 문제가 생긴다. partition 하나만 truncate하거나 switch하려는 작업이 index 때문에 막힐 수 있고, 경우에 따라 index를 먼저 정리하거나 다시 만들어야 하는 상황이 생긴다. 그래서 partition 설계에서는 table만 보는 것이 아니라 clustered index, nonclustered index, filegroup, history table 구조까지 같이 맞춰야 한다.

Partition key를 조건에 넣어야 효과가 난다

partition을 했다고 모든 쿼리가 빨라지는 것은 아니다.

SELECT 문에서 partition 기준 column을 조건절에 사용하지 않으면 SQL Server가 어떤 partition을 제외해도 되는지 판단하기 어렵다. 그러면 partition을 나눠도 성능 이점이 거의 없을 수 있다.

그래서 partition key는 대부분의 중요한 쿼리에 들어가는 기준 column으로 잡는 것이 좋다.

예를 들어 TC_TRAY 테이블에서 대부분의 쿼리가 TRAY_NO를 조건으로 사용한다면 TRAY_NO가 partition key 후보가 될 수 있다. 반대로 거의 조건에 쓰이지 않는 컬럼으로 partition을 나누면 관리 복잡도만 늘고 조회 성능에는 도움이 안 될 수 있다.

즉 "속도 때문에 partition 해주세요"라고 단순히 말하기보다는, purge 목적이 있는지, 어떤 column이 항상 조건에 들어가는지, partition pruning이 가능한지를 먼저 봐야 한다.

예를 들어 TC_TRAY 관련 대부분의 쿼리에 TRAY_NO가 항상 들어간다면 TRAY_NO는 partition key 후보가 될 수 있다. 반대로 어떤 column이 데이터 분리 기준으로는 그럴듯해 보여도 실제 WHERE 조건에 거의 들어가지 않는다면, SQL Server는 불필요한 partition을 제외하기 어렵다. 그러면 partition을 나눴는데도 조회 성능에는 큰 도움이 없고 관리 복잡도만 늘 수 있다.

Partition 종류

partition 방식은 크게 다음처럼 정리할 수 있다.

1. Range partitioning
   • 날짜나 기간처럼 연속된 범위로 나눈다.
2. Hash partitioning
   • partition key 값에 hash function을 적용해서 partition에 매핑한다.
3. List partitioning
   • 지역, 코드값처럼 불연속적인 값 목록으로 나눈다.
4. Composite partitioning
   • range나 list partition 안에서 다시 sub-partition을 구성한다.

SQL Server에서 실무적으로 가장 자주 보는 것은 날짜 기준 range partition이다.

Partition pruning

Partition pruning은 optimizer가 SQL의 대상 table과 WHERE 조건을 보고, 읽지 않아도 되는 partition을 액세스 대상에서 제외하는 것이다.

예를 들어 날짜 기준으로 월별 partition이 나뉘어 있고, WHERE 조건이 특정 월만 조회한다면 SQL Server는 해당 월 partition만 읽고 나머지는 제외할 수 있다.

partition의 성능 이점은 여기서 나온다. 전체 테이블을 모두 읽지 않고 필요한 partition만 읽을 수 있기 때문이다.

최종 정리

SQL Server 튜닝에서 중요한 것은 operator 이름 하나가 아니라 전체 흐름이다.

• logical reads는 SQL Server가 읽은 8KB page 수다.
• 실행 계획은 오른쪽에서 왼쪽, 위에서 아래로 읽는다.
• ORDER BY는 sort와 memory grant 때문에 비싸질 수 있다.
• nonclustered index가 필요한 컬럼을 모두 포함하면 covering index가 된다.
• Key Lookup이 많이 반복되면 wider index나 INCLUDE 컬럼을 검토한다.
• statistics는 SQL Server가 index, join 순서, seek/scan, memory grant를 고르는 근거다.
• cardinality estimation은 완벽하지 않으므로 실제 row 수와 추정 row 수를 같이 봐야 한다.
• partition은 단순 조회 속도보다 purge와 partition pruning 관점에서 봐야 한다.

결국 튜닝은 "인덱스를 만들었다"에서 끝나는 작업이 아니다. SQL Server가 얼마나 읽었는지, 왜 그 plan을 골랐는지, 그리고 데이터가 커졌을 때도 같은 선택이 유효한지를 계속 확인하는 과정이다.

References

1. Display and save execution plans
2. SET STATISTICS IO (Transact-SQL)
3. SET STATISTICS TIME (Transact-SQL)
4. DBCC SHOW_STATISTICS (Transact-SQL)
5. Partitioned tables and indexes