English version
SQL Server tuning eventually comes back to storage structures: pages, extents, heaps, clustered indexes, nonclustered indexes, and lookups. Execution plans are easier to read when you understand what SQL Server is physically reading underneath the query.
A page is SQL Server's basic storage and read unit. Each page is 8KB. This is why logical reads are so important: they tell you how many 8KB pages SQL Server read from the buffer cache. A query can return only a few rows but still read many pages if the access path is inefficient.
An extent is a group of eight pages, or 64KB. SQL Server uses extents to manage allocation. A uniform extent belongs to one object. A mixed extent can contain pages from multiple objects. Older SQL Server explanations often describe small objects starting on mixed extents and moving toward uniform extents as they grow. In modern SQL Server versions, the default allocation behavior changed, so treat mixed/uniform extents as an allocation concept, not as a rule that every new user table follows.
Allocation map pages track how pages and extents are used. PFS, or Page Free Space, tracks page allocation and fullness. GAM, or Global Allocation Map, tracks whether extents are free or allocated. SGAM, or Shared Global Allocation Map, tracks mixed extents with available pages. IAM, or Index Allocation Map, tracks which extents belong to a table or index allocation unit.
Tables are stored either as heaps or clustered indexes. A heap has no clustered index, so rows are not stored in clustered key order. A clustered index stores the table data itself in a B-tree ordered by the clustered key. This is why the clustered index is not just an extra helper structure. It is the table's main storage structure.
A nonclustered index is a separate B-tree. It stores index keys plus row locators. If a query needs columns that are not present in the nonclustered index, SQL Server may perform a lookup back to the heap or clustered index to fetch the missing columns.
The common execution plan terms are easier to interpret from this storage model. A scan reads a broad part of a table or index. A seek uses an index tree to jump to a narrower range. A lookup returns to the base row structure to get missing columns. Seek is not always good, and scan is not always bad. You still need to compare rows returned, pages read, and whether the index covers the query.
Included columns can reduce lookups by storing non-key columns at the leaf level of a nonclustered index. This can make read-heavy queries faster, but it also increases index size and DML maintenance cost. The right question is not “can I add an include column?” but “does it reduce logical reads enough to justify the write cost?”
The practical lesson is that index tuning is storage tuning. Pages, extents, B-trees, and lookups explain why two queries with similar SQL text can have very different I/O profiles.
Korean notes
SQL Server의 실행 계획을 보다 보면 결국 page, extent, heap, clustered index, nonclustered index 같은 저장 구조로 돌아오게 된다. 예전 Velog 노트에서는 이 개념들이 흩어져 있었는데, 블로그에서는 하나의 흐름으로 묶는 게 더 이해하기 쉽다.
Page는 SQL Server의 기본 읽기 단위다
SQL Server에서 page는 기본적인 데이터 저장 및 읽기 단위다. 한 page의 크기는 8KB다.
logical reads가 중요한 이유도 여기에 있다. SQL Server가 몇 row를 반환했는지가 아니라 몇 개의 8KB page를 읽었는지를 보면 실제 I/O 부담을 더 잘 볼 수 있다.
Extent
Extent는 8개의 page를 묶은 단위다. 즉 한 extent는 64KB다.
Extent에는 크게 두 종류가 있다.
- Uniform extent: 하나의 object가 전체 extent를 사용한다.
- Mixed extent: 여러 object가 같은 extent 안의 page를 나눠 쓴다.
예전 SQL Server 설명에서는 작은 object가 처음 만들어질 때 mixed extent를 사용하고, 커지면 uniform extent 쪽으로 넘어간다고 설명하는 경우가 많다. 다만 최신 SQL Server에서는 기본 allocation 방식이 바뀐 부분이 있으므로, 이 문장은 “항상 모든 새 table이 이렇게 동작한다”가 아니라 mixed/uniform extent 개념을 이해하기 위한 설명으로 보는 게 안전하다.
Allocation map pages
SQL Server는 page와 extent가 어떻게 할당되었는지 관리하기 위해 여러 allocation map page를 사용한다.
PFS
PFS는 Page Free Space의 약자다. 각 page의 할당 상태와 여유 공간 정보를 기록한다.
예를 들어 page가 1에서 50% 찼는지, 51에서 80% 찼는지, 96에서 100% 찼는지 같은 정보를 관리한다.
GAM
GAM은 Global Allocation Map이다. extent가 비어 있는지, 이미 할당되었는지 기록한다.
- 1: extent가 비어 있음
- 0: extent가 할당됨
SGAM
SGAM은 Shared Global Allocation Map이다. mixed extent로 사용 중인 extent를 추적한다.
IAM
IAM은 Index Allocation Map이다. 특정 table이나 index가 어떤 extent를 사용하는지 추적한다.
DBCC PAGE나 DBCC IND를 볼 때 IAM, data page, index page가 왜 같이 나오는지 이해하려면 이 allocation map 개념이 필요하다.
Heap과 clustered index
Table 저장 구조는 크게 두 가지로 볼 수 있다.
- Heap
- Clustered index
Heap은 clustered index가 없는 table이다. 데이터가 정렬된 순서로 저장된다고 보장하지 않고, page끼리의 논리적인 정렬 순서도 없다.
Clustered index는 table 데이터를 index key 기준으로 정렬된 B-tree 구조로 저장한다. 그래서 clustered index는 단순히 별도의 보조 구조가 아니라 table 자체의 저장 구조에 가깝다.
Nonclustered index
Nonclustered index는 table과 별도로 만들어지는 B-tree 구조다. index key와 row locator를 가지고 있고, 필요한 row를 찾은 뒤 heap이나 clustered index로 돌아가 추가 column을 가져올 수 있다.
이 추가 탐색이 lookup이다.
Scan, seek, lookup
실행 계획에서 자주 보는 세 가지를 정리하면 이렇다.
- Scan: table 전체 또는 index leaf level을 넓게 읽는다.
- Seek: index tree를 타고 조건에 맞는 범위로 바로 접근한다.
- Lookup: nonclustered index에 없는 column을 가져오기 위해 heap이나 clustered index로 다시 접근한다.
Seek가 항상 좋고 scan이 항상 나쁜 것은 아니다. 반환 row 수, 읽은 page 수, index coverage를 같이 봐야 한다.
Include column
Include index는 key column이 아닌 column을 nonclustered index의 leaf level에 함께 저장하는 방식이다.
장점은 lookup을 줄일 수 있다는 것이다. query가 필요한 column을 nonclustered index 안에서 모두 해결할 수 있으면 heap이나 clustered index로 다시 가지 않아도 된다.
하지만 include column을 많이 넣으면 index 크기가 커진다. SELECT에는 유리할 수 있지만 INSERT, UPDATE, DELETE 비용은 증가할 수 있다.
그래서 include index를 만들 때는 다음을 봐야 한다.
- 이 query가 자주 실행되는가?
- SELECT 중심인가, DML이 많은 table인가?
- lookup 비용이 실제로 큰가?
- include column을 넣었을 때 logical reads가 줄어드는가?
정리
SQL Server index 튜닝은 결국 저장 구조를 이해하는 일이다.
- SQL Server page는 8KB다.
- Extent는 8개의 page를 묶은 64KB 단위다.
- PFS, GAM, SGAM, IAM은 page와 extent allocation을 관리한다.
- Heap은 clustered index가 없는 table이다.
- Clustered index는 table 자체를 B-tree 구조로 저장한다.
- Nonclustered index는 별도 B-tree이며 lookup이 발생할 수 있다.
- Include column은 lookup을 줄일 수 있지만 index 크기와 DML 비용을 증가시킨다.