SqlServerIndexTuning

UserPreferences

1. Table and Index Structure
1. Page, Extent, Heap
2. Index
2. Index Access Method
1. Index Access Method
2. Included Non-key Columns
3. Index Intersection
4. Indexed Views
3. Index Optimization Scale
1. Index Access Method Performace/Selectivity
2. 인덱스 사용량/IO 및 잠금 체크
4. Index Fragmentation
1. 조각화의 종류
2. 조각화 체크 및 해결
3. 조각화 방지
5. Partitioning
6. 링크

1 Table and Index Structure

1.1 Page, Extent, Heap

• Page : 8KB. IO 최소 단위. 아무리 작은 행을 읽어들인다고 하더라도 페이지 단위로 읽어들이게 된다.
  
• Extent : 8 Page, 즉 64 KB. 페이지 공간이 모자라는 경우, 익스텐트 단위로 할당을 하게 된다. 객체가 64KB보다 작을 경우에는 예외. 몇몇 쿼리는 익스텐트 단위로 동작을 하게 되는데, 이런 넘들은 로그가 최소한으로 남기 때문에 빠르다. 테이블 드랍 vs 각각의 행을 따로 지우기를 생각하면 된다.
  
• Heap : 클러스터드 인덱스를 가지지 않은 테이블. 힙 안에 뭐가 들었는지는 IAM(Index Allocation Map)을 통해서 관리. IAM 자체는 링크드 리스트. 힙과 반대되는 테이블, 즉 클러스터드 인덱스를 가지는 테이블을 클러스터드 테이블이라고 부른다.
  
  

1.2 Index

• Clustered Index : 리프 노드에 실제 데이터를 가지는 트리.
  
• Nonclustered Index : 리프 노드에 실제 데이터를 가지지 않고, row locator 값을 가지는 트리. Row locator 안에 있는 내용은 테이블이 힙이냐, 클러스터드 테이블이냐에 따라 틀리다.
  
• 힙의 경우, row locator는 RID, 즉 데이터가 있는 곳의 물리적인 주소를 가르키는 포인터를 가진다. Lookup 과정을 통해 실제 데이터를 읽어오기 위해서는 페이지 하나를 읽어들여야 한다.
  
• 클러스터드 테이블의 경우, row locator는 실제 데이터가 있는 곳을 가르키는 것이 아니라, clustering key라는 논리적인 값을 가진다. Clustering key는 테이블이 삽입이 일어나서, 페이지 분할이 일어나도 바뀌지 않는 값이다. 쉽게 생각하자면, 클러스터드 인덱스의 노드 ID를 가르키고 있다고 생각하면 된다. Lookup 과정을 통해 데이터를 읽어오기 위해서는 clustering key를 이용해 트리를 검색해야 한다. 즉 트리의 높이가 3이라면 3개의 페이지를 읽어들여야 한다. 그렇다면 클러스터드 테이블은 무조건 피해야 하는 게 아닌가라고 생각할 수 있는데, 클러스티드 인덱스에서 리프가 아닌 노드들은 대부분 캐쉬에 저장된다. 그러므로 물리적인 읽기 작업은 그렇게 많이 일어나지 않는다. 음 미묘하군.
  
• Covered Index : SELECT 구문에서 조회하는 모든 컬럼이 인덱스에 포함될 때를 의미한다. 즉 커버드 인덱스는 특별한 종류의 인덱스가 아니라, 쿼리가 어떤 것이냐에 따라 커버드/언커버드가 나뉘게 된다는 말이다.
  
  
  

2 Index Access Method

2.1 Index Access Method

기본적으로 인덱스는 B-TREE 구조를 가진다. 여기에 추가로 리프 레벨 노드들간의 더블 링크드 리스트가 존재한다.


• Table Scan/Unordered Clustered Index Scan
  인덱스가 나발이고 없이 걍 IAM을 통해 주욱 읽어들이는 작업이다. Read-ahead 기능을 통해 익스텐트 단위로 IO를 하기도 하는 모양이다.
  
• Unordered Covering Nonclustered Index Scan
  필요로 하는 컬럼이 인덱스 내부에 있다는 것을 제외하고는 테이블 스캔과 같은 작업이다.
  
• Ordered Clustered Index Scan
  클러스터드 인덱스의 리프 노드를 링크드 리스트 따라 주욱 도는 작업이다. Unordered Clustered Index Scan과 달리 인덱스의 조각화가 심한 경우 성능이 떨어지게 된다.
  
• Ordered Covering Nonclustered Index Scan
  필요로 하는 컬럼이 모두 인덱스 안에 존재하기 때문에, 읽어들일 페이지가 적다는 것 이외에는 Ordered Clustered Index Scan과 비슷한 작업이다.
  
• Nonclustered Index Seek + Ordered Partial Scan + Lookups
  쿼리의 결과로 반환되는 행 수가 적은 쿼리에서 일어나는 작업이다. 먼저 검색 범위(WHERE aaa BETWEEN min TO MAX)를 통해 인덱스에서 시작 노드와 끝 노드를 찾은 다음, 이 사이를 링크드 리스트를 따라 주욱 도는 작업이다. 커버드 인덱스를 이용하는 것이 아니므로, 각각의 결과 행마다 lookup 작업이 일어난다. 즉 테이블이 힙일 경우에는 페이지 하나, 테이블이 클러스터드일 경우에는 인덱스 레벨만큼의 페이지를 읽어들이게 된다. 즉 검색 범위가 넓을 경우, 성능이 현저하게 떨어지게 되는 작업이다.
  
• Unordered Nonclustered Index Scan + Lookups
  쿼리의 결과를 필터링하는 조건절이 인덱스와는 무관한 경우 발생하는 작업이다. 인덱스 리프 레벨을 그냥 주욱 돌면서 각각의 행에 대해 lookup 과정을 수행하게 된다.
  
• Clustered Index Seek + Ordered Partial Scan
  검색 조건이 인덱스에 첫번째 키컬럼일 경우에 일어나는 작업이다. 검색 조건의 min 값을 이용해 리프 레벨의 노드를 찾은 다음, max 값을 가지는 노드를 만날 때까지, 리프 레벨 링크드 리스트를 따라가는 작업이다. 클러스터드 테이블이므로 룩업 과정이 필요없다.
  
• Covering Nonclustered Index Seek + Ordered Partial Scan
  넌클러스터드 인덱스의 리프 레벨에 존재하는 컬럼 값을 이용(row locator 안에 이런 것도 있구나!)한다는 것을 제외하고는 Clustered Index Seek와 같은 작업이다.
  
  
결국 클러스터드 테이블이라고 무조건 좋은 것이 아니다라는 걸 알 수 있다. 프라이머리 키를 생성하는 경우, 따로 지정해주지 않으면 무조건 클러스터드 인덱스가 생기는 모양인데, 한번 생각해볼 문제인 것 같다.



2.2 Included Non-key Columns

커버링 인덱스를 만들 때, 인덱스에 들어가는 모든 컬럼을 키 컬럼으로 만들 필요는 없다. 각각의 키 컬럼들은 인덱스의 정렬 순서를 정의한다. 이 말은 인덱스의 키 컬럼 값이 바뀌면 트리의 정렬 순서 또한 변경된다는 말이다. 단순히 커버링이 목적인 경우에는 included non-key columns을 사용하는 것이 좋다. 이 컬럼들은 리프 레벨에만 들어가게 된다.


CREATE NONCLUSTERED INDEX idx_nc_sid_od_i_cid 
  ON dbo.Orders(shipperid, orderdate) 
  INCLUDE(custid); 




2.3 Index Intersection

쿼리 최적화기가 사용하는 트릭. 2가지 이상의 인덱스의 결과를 row locator 값을 기준으로 비교해서 하나로 만드는 기능. 그냥 이런 게 있다고만 알면 되는 것 같은데?



2.4 Indexed Views

...

3 Index Optimization Scale

3.1 Index Access Method Performace/Selectivity

                                     Fast <------------------> Slow 
-----------------------------------+--------------------------------- 
Table Scan/Unordered               | 
Clustered Index Scan               |                         O 
-----------------------------------+--------------------------------- 
Unordered Covering                 | 
Nonclustered Index Scan            |      O                      
-----------------------------------+--------------------------------- 
Unordered Covering                 | 
Nonclustered Index Scan + Lookups  |      <-------------------------> 
-----------------------------------+--------------------------------- 
Nonclustered Index Seek +          | 
Ordered Partial Scan + Lookups     | <------------------------------> 
-----------------------------------+--------------------------------- 
Clustered Index Seek               | 
Ordered Partial Scan               | <-----------------------> 
-----------------------------------+--------------------------------- 
Covering Nonclustered Index Seek + | 
Ordered Partial Scan               | <----> 
-----------------------------------+--------------------------------- 
                                      High              Low  
                                      Selectivity <---> Selectivity 
                                      0%                100% 




3.2 인덱스 사용량/IO 및 잠금 체크

sys.dm_db_index_operational_stats ( 
    { database_id | NULL | 0 | DEFAULT } 
    , { object_id | NULL | 0 | DEFAULT } 
    , { index_id | 0 | NULL | -1 | DEFAULT } 
    , { partition_number | NULL | 0 | DEFAULT } 
) 
 
SELECT * FROM sys.dm_db_index_usage_stats u, sys.objects o 
WHERE u.object_id=o.object_id AND o.type in ('U', 'V'); 

카운터는 SqlServer를 시작할 때마다 초기화된다.

4 Index Fragmentation

4.1 조각화의 종류

• Logical scan fragmentation or Average fragmentation in percent or External fragmentation
  Out of order 페이지의 비율. 즉 논리적인 순서와 어긋나게 물리적으로 배치된 인덱스 페이지의 비율. Ordered scan 작업의 성능에 큰 영향을 미친다.
  
  
• Average page density or Internal fragmentation
  Logical scan framentation은 무조건 나쁜 것이지만, 페이지 밀도는 두 가지 측면을 가진다. 낮은 밀도를 가지는 인덱스에서 읽기 작업을 할 때에는 높은 밀도를 가지는 인덱스에서 작업하는 것보다 많은 페이지를 읽어들여야 하므로 불리하다. 하지만 쓰기 작업을 할 때에는 낮은 밀도를 가지는 쪽이 유리하다. 높은 밀도의 인덱스에 대해 쓰기 작업을 하는 경우, 새로운 행이 추가되면 페이지 스플릿, 즉 트리의 분할이 이루어질 가능성이 높기 때문이다. 즉 SELECT 작업을 주로 하는 테이블에서는 밀도가 높아야 하고, INSERT 작업을 자주 하는 OLTP 환경에서는 밀도가 낮아야 한다.
  
  

4.2 조각화 체크 및 해결

sys.dm_db_index_physical_stats (  
    { database_id | NULL | 0 | DEFAULT } 
    , { object_id | NULL | 0 | DEFAULT } 
    , { index_id | NULL | 0 | -1 | DEFAULT } 
    , { partition_number | NULL | 0 | DEFAULT } 
    , { mode | NULL | DEFAULT } 
) 

SELECT 
  OBJECT_NAME(p.object_id) as table_name,  
  i.name, 
  p.index_type_desc,  
  p.avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats(DB_ID('데이터베이스이름'), NULL, NULL, NULL, 'LIMITED') p, sys.indexes i 
WHERE p.object_id = i.object_id AND p.index_id = i.index_id;

조각화율이 30% 이하라면 ALTER INDEX REORGANIZE, 30% 초과라면 ALTER INDEX REBUILD 구문을 사용하는 것이 좋다고 한다.


ALTER INDEX 인덱스이름(또는 ALL) ON 테이블이름 REORGANIZE; 
ALTER INDEX 인덱스이름(또는 ALL) ON 테이블이름 REBUILD WITH (ONLINE=ON); 




4.3 조각화 방지

조각화를 아예 피하기 위해서는 어떤 일을 해야하는가?
from How to Detect Table Fragmentation in SQL Server 2000 and 2005


• When a database is created, make sure the data files are created with or assigned the largest values possible. You can do this by planning to use a value that can fit the maximum amount of data during a certain period (say, three years at least).
  
• Sometimes it is feasible to permit the data files to grow automatically while keeping a limit on the growth by specifying a maximum data file growth size that leaves some available space on the hard disk.
  
• After a period of time, ascertain and re-evaluate the expected maximum database size by adding more files or filegroups to the database, if required.
  
• Do not let the data files grow automatically if there many data files share the same disk partition. If the data files are heavily used then locate them in a different filegroup or on a different partition.
  
• Perform regular database maintenance tasks, such as DBCC DBREINDEX, and recompiling stored procedures and triggers.
  
• If the table row(s) are modified or deleted frequently then it is better to run intermittent UPDATE STATISTICS on the table, which will help it avoid any slow performance from the execution plan.
  
  
  

5 Partitioning

...뭔가 고급 주제인가 본데?

6 링크

• 나름대로 풀어본 쿼리문의 원리(INDEX)
  
• 귀하의 인덱스는 안전 하십니까?
  
• SQL SERVER 2000의 인덱스 이해
  

• see also SqlServer, SqlServerOptimization
  

• CategoryDatabase