English version
This note is about reading a SQL Server execution plan from the amount of data SQL Server actually reads, not just from whether the operator name says Seek or Scan.
The key metric is logical reads. STATISTICS IO reports logical reads as the number of 8KB pages SQL Server read from the buffer cache. For example:
7405 logical reads * 8KB = 59,240KB
about 59MB
That number matters because a query can return a small result while still reading a large amount of data.
When a WHERE predicate does not match a useful index, SQL Server may scan the clustered index or table to find matching rows. If the query is expensive enough, SQL Server may also use parallelism and split the work across multiple workers. Parallelism is not automatically bad, but it is a signal that SQL Server thinks the query has a lot of work to do.
Execution plans are usually read from right to left and top to bottom. If a Clustered Index Scan takes most of the estimated cost, SQL Server expects most of the query work to come from reading that clustered index. Estimated Subtree Cost is a rough optimizer estimate of CPU and I/O work. It is useful, but it is not the same as real elapsed time.
ORDER BY can also change the cost sharply. SQL Server may need to read all matching rows, write down the result columns, and then sort the rows by the requested column. Sorting is expensive, and wider result rows make it worse. SQL Server can cache data pages, but it does not simply cache the final query output in a way that removes the sort work next time.
Reference: How to Think Like the SQL Server Engine.
Korean notes
SQL Server 실행 계획을 볼 때 Seek인지 Scan인지도 중요하지만, 그보다 먼저 봐야 하는 값이 있다. 바로 logical reads다.
Logical reads란?
logical reads는 SQL Server가 읽은 8KB page 수다.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
예를 들어 logical reads가 7405로 나왔다면 대략 이렇게 계산할 수 있다.
7405 pages * 8KB = 59,240KB
약 59MB
즉 이 쿼리는 결과 row가 몇 개였는지와 별개로, 내부적으로 약 59MB 정도의 data page를 읽은 것이다. 같은 결과를 반환하는 두 쿼리라도 logical reads가 크게 다르면 SQL Server가 실제로 읽은 양이 다르다는 뜻이다.
그래서 실행 계획을 볼 때는 operator 이름만 보지 말고, 실제로 얼마나 많은 page를 읽었는지 같이 봐야 한다.
WHERE 조건에 맞는 index가 없으면 scan이 발생한다
WHERE 조건을 썼다고 해서 SQL Server가 자동으로 필요한 row만 바로 찾아가는 것은 아니다.
SELECT Id,
LastAccessDate
FROM dbo.Users
WHERE LastAccessDate > '2014-07-01';
만약 LastAccessDate 조건을 지원하는 index가 없다면 SQL Server는 clustered index나 table을 넓게 scan하면서 조건에 맞는 row를 찾아야 한다.
이런 경우 실행 계획에서는 Clustered Index Scan이 크게 보일 수 있다. 비용이 96%처럼 높게 잡혀 있다면 SQL Server가 이 scan 작업을 쿼리의 대부분의 일로 보고 있다는 뜻이다.
parallel query에서는 worker들이 나눠서 읽기 때문에 추가 read가 조금 생길 수도 있다. 이것도 실행 계획과 STATISTICS IO를 같이 봐야 하는 이유다.
실행 계획은 오른쪽에서 왼쪽, 위에서 아래로 읽는다
SQL Server execution plan은 보통 오른쪽에서 왼쪽, 그리고 위에서 아래 방향으로 읽는다.
처음에는 아이콘이 많아서 복잡해 보이지만, 흐름은 "데이터를 어디서 읽기 시작했는가"를 따라가면 된다.
예를 들어 오른쪽에 Clustered Index Scan이 있고 그 비용이 대부분을 차지한다면, SQL Server가 clustered index 전체를 넓게 읽는 쪽으로 계획을 잡았다고 볼 수 있다.
Parallelism은 무슨 뜻인가?
실행 계획에서 노란색 아이콘 안에 화살표 두 개가 보이면 parallelism operator가 들어간 것이다.
이것은 SQL Server가 이 쿼리를 비싼 작업으로 보고, 일을 여러 worker에게 나눠 처리했다는 뜻이다. 사람이 여러 명 붙어서 일을 나눠 하는 것처럼 볼 수 있다.
Parallelism이 보인다고 무조건 나쁜 것은 아니다. 다만 SQL Server가 "이 쿼리는 할 일이 많다"고 판단했다는 신호다. 실제로는 logical reads, CPU time, elapsed time, 실제 row 수를 같이 확인해야 한다.
Estimated Subtree Cost
Estimated Subtree Cost는 해당 쿼리를 수행하는 데 필요한 CPU와 I/O 작업량을 SQL Server가 대략적으로 계산한 값이다.
정확한 실행 시간은 아니지만, cost가 높을수록 SQL Server가 더 많은 CPU/I/O 작업이 필요하다고 추정했다는 뜻이다.
다만 cost만 보고 튜닝 결정을 내리면 안 된다. 실제 튜닝에서는 다음 값을 같이 본다.
- logical reads
- CPU time
- elapsed time
- 실제 읽은 row 수
- 실제 반환한 row 수
- estimated row와 actual row의 차이
- sort, hash, parallelism 같은 비싼 operator
ORDER BY는 생각보다 비싸다
ORDER BY를 추가하면 SQL Server는 단순히 row를 찾는 것에서 끝나지 않는다.
대략 이런 작업이 필요하다.
- 전체 page를 훑으면서
LastAccessDate > '2014-07-01'조건에 맞는 row를 찾는다. - 조건에 맞는 row의 필요한 field를 적어둔다.
- 그 결과를
LastAccessDate기준으로 다시 정렬한다.
정렬은 비싼 작업이다. 정렬할 row가 많을수록, 그리고 선택한 field가 많아서 row width가 넓을수록 더 비싸진다.
이때 SQL Server는 정렬 중간 결과를 담기 위해 memory grant를 받을 수 있다. 그래서 ORDER BY 하나를 추가했을 뿐인데 cost가 2배 가까이 올라가는 경우도 있다.
SQL Server는 query output이 아니라 data page를 캐싱한다
중요한 점은 SQL Server가 query output 자체를 캐싱하는 것이 아니라 data page를 캐싱한다는 것이다.
즉 한 번 실행한 SELECT 결과를 그대로 저장해두고 다음에 바로 돌려주는 방식으로 이해하면 안 된다. 같은 data page가 buffer cache에 있으면 physical read는 줄 수 있지만, 조건 판단이나 정렬 같은 작업은 다시 필요할 수 있다.
그래서 실행 계획을 볼 때는 "결과가 빨리 나왔나"만 보지 말고, SQL Server가 실제로 어떤 page를 얼마나 읽었고, 어떤 연산을 다시 수행했는지 봐야 한다.
정리
이번 메모에서 기억할 점은 다음이다.
logical reads는 SQL Server가 읽은 8KB page 수다.logical reads = 7405라면 약 59MB의 data page를 읽은 것이다.WHERE조건에 맞는 index가 없으면 SQL Server는 clustered index나 table을 scan할 수 있다.- 실행 계획은 보통 오른쪽에서 왼쪽, 위에서 아래로 읽는다.
- Parallelism은 SQL Server가 비싼 작업을 여러 worker로 나눴다는 뜻이다.
Estimated Subtree Cost는 CPU와 I/O 작업량에 대한 rough estimate다.ORDER BY는 sort와 memory grant 때문에 비용이 크게 늘 수 있다.- SQL Server는 query output이 아니라 data page를 캐싱한다.
참고 링크: How to Think Like the SQL Server Engine