#3. 첫 번째 수업 - How to Think Like the Engine

<How to Think Like the Engine>

(* 직접 읽고 듣고 해석한 것이기 때문에 오류 많을 수 있음 주의)

 

영상에서 사용할 테이블은 StackOverflow2010.dbo.Users 테이블이다.

 

 

PK는 Id이다. clustered index가 Id로 잡혀있다는 것이고, 사실상 clustered index = 테이블 자체 라고 생각하면 된다고 한다.

 

 

브렌트 아저씨께서 올려주신 pdf 의 첫 장을 보면 clustered index의 데이터가 Id로 정렬되어 있는 것을 확인할 수 있다. 위 파일을 봤을 때 clustered index는 Id로 정렬이 되어 있고 그 외의 컬럼들의 값도 모두 가지고 있는 것처럼 보인다.

→ 정확하게 따지자면 이 표현은 맞지 않는 것이라고 하는데 일단 이렇게 설명해두신다고 한다. AboutMe 컬럼이 좋은 예다. 이 컬럼을 보면 일단 내용이 길다. 8KB (페이지 사이즈) 보다 데이터가 많을 수도 있어 보인다. 이런 경우 SQL Server는 데이터가 저장되어 있는 페이지의 포인터를 저장하게 된다. varchar(max), nvarchar(max), xml, json 같은 데이터타입을 사용할 때 요런 일이 발생한다.

 

SQL Server는 데이터를 저장하는 최소 단위로 8KB 짜리 페이지를 사용한다. 이 페이지라는 것은 메모리에 있든 디스크에 있든 동일하다. 

예를 들어, Users 테이블에서 누군가의 LastAccessDate를 업데이트하고 싶다고 할 때, 나는 그 사람의 LastAccessDate를 가지고 있는 페이지를 가져와서 메모리에 올려놓고, 수정을 한 다음 다시 디스크에 갖다놔야 한다. SQL Server는 row 단위나 테이블 단위로 캐싱하지 않는다. (페이지 단위로만 함) 

 

이제 앞으로 해야할 것은 내가 SQL Server가 되었다고 생각하고 사용자가 보낸 쿼리에 대해 어떻게 동작할지를 롤 플레잉해보는 것이다. (부담;)

 

■ 첫 번째 쿼리:

 

SELECT Id FROM dbo.Users;

 

일단 상황을 만들어보자. 나는 인사팀 소속이고 직원들의 인사기록을 관리한다. 이 인사기록 정보는 종이로 프린트되어 창고 캐비넷에 박혀있다. 그리고 사원이 심각하게 많다. 그리고 인사팀 팀장님께서 이 인사기록을 보고 싶으실 때마다 나한테 인사기록지를 갖고오라고 시킨다. 나의 일은 이 심각하게 많은 사원의 인사기록지 중에서 필요한 것들을 빨리 찾아서 팀장님께 갖다드리는 것이다.

 

위의 쿼리대로라면 팀장님께서는 전 사원의 Id를 알고 싶으신 것 같다. 그럼 나는 어떻게 해야 할까? 창고에 있는 캐비넷으로 가서 한 명 한 명씩 잡히는 대로 Id를 찾아서 팀장님 들리도록 외친다. (진짜 사무실에서는 그러지 말도록 하자.) 

 

 

얘도 대충 비슷하게 하는 것 같다. 그럼 나는 이제 캐비넷에서 내가 눈으로 읽어야 하는 종이가 대체 몇 장인지도 알고 싶다. → SET STATISTICS IO ON 

 

 

여기서 '논리적 읽기 수(Logical reads)'란 SQL Server가 읽은 8KB 페이지 개수이다.

→ 7,405 * 8KB = 59MB 

 

 

■ 두 번째 쿼리:

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

팀장님은 이번에는 LastAccessDate가 2014년 7월 1일 이후인 사원들의 Id를 모조리 보고 싶으시다. 이 경우는 어떻게 해야할까? 캐비넷에 가서 인사기록지를 한 장씩(=한 페이지씩) 꺼내서 모든의 LastAccessDate를 확인할 것이다. 그리고 LastAccessDate가 조건에 맞는 사원의 Id를 외칠 것이다.

 

 

아, 병렬처리를 한다. 영상에는 병렬 처리 없는 실행계획을 보여주고 있으니까 일단 똑같이 맞춰본다.

 

첫 번째 쿼리와 I/O를 비교해보자.

 

 

결과 건수는 거의 반으로 줄었지만 logical reads 수는 동일하다. 왜냐면 첫 번째 경우에도 전체 인사기록지를 뒤졌을 것이고, 두 번째 경우에도 모든 사원의 LastAccessDate가 조건에 맞는지 찾기 위해 전 사원의 인사기록지를 뒤졌을 것이기 때문이다.

 

Lesson : Using WHERE without matching index means scanning all the data.

적절한 인덱스 없이 WHERE 절을 쓰는 것은 모든 데이터를 스캔하겠다는 의미이다. 

 

그리고 병렬처리 관련해서 잠시 짚고 넘어가자면, 병렬 처리 시 생기는 추가 read가 있을 수 있다.

 

 

첫 번째 쿼리의 실행계획을 보면 단일 스레드로 실행됨을 알 수 있다. 우리 팀에 짬 안 되는 애가 나밖에 없어서 나만 찾으러 갔다는 이야기다.

두 번째 쿼리의 실행계획을 보면 병렬처리(Parallelism)로 실행됨을 알 수 있다. 신입공채로 인사팀 신입을 몇 명 뽑았고 일이 너무 많아보이니까 사람을 여러 명 투입시켰다.

 

 

이렇게 병렬로 돌았을 경우 (MAXDOP 1 OPTION을 제거했을 때) logical reads가 더 늘어난 것을 확인할 수 있다. 여기에 대한 자세한 내용은 나중에 다른 수업시간 때 설명해주실 예정이라고 한다. 

 

실행 계획을 한 번 비교해 보자.

 

SELECT 부분의 세부 내용을 보면 Estimated Subtree Cost라는 것이 있다. 

 

 

두 쿼리의 Estimated Subtree Cost를 비교해보니, 두 번째 쿼리의 Estimated Subtree Cost가 첫 번째 쿼리의 Estimated Subtree Cost보다 살짝 낮다. 이러면 병렬처리를 한 쪽이 비용(cost)이 적게 든 걸까? 한 명이 할 일을 여러 명이 나눠서 하니까?

→ No

 

이 Estimated Subtree Cost라는 게 뭐냐면 SQL Server가 쿼리 돌려보기 전에 대충 CPU랑 I/O가 얼만큼 나올 것 같다고 예상하는 수치 정도로 생각하면 된다고 한다. 사실 SQL Server는 내 컴퓨터의 코어나 스토리지가 얼마나 빠른지도 모르고 메모리 정도만 대강 알고 있는데 그것도 정확히 알 고 있는 건 아니기 때문에 이를 바탕으로 예측한 결과도 그렇게 정확하지는 않다. 이 값은 그냥 how expensive SQL Server "thought" the query was going to be 의 값 정도로만 참고하면 될 것 같다고 한다.

 

■ 세 번째 쿼리:

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

이번에는 아까 그 데이터를 LastAccessDate 순으로 팀장님께 알려드려야 한다. 이번에는 어떻게 할까?

먼저 캐비넷으로 가서 한 장씩(=한 페이지씩) 꺼내보면서 조건에 맞는 (LastAccessDate > '2014/07/01') 사원을 찾고, 추후를 위해 포스트잇에 적어놓는다. 뭘 적을까? Id랑 LastAccessDate를 적는다. 그리고 마지막에 LastAccessDate로 정렬한 뒤 Id를 외치면 될 것 같다.

 

① Clustered Index Scan

- 이 단계에서는 조건에 맞는 데이터의 Id와 LastAccessDate를 다음 연산자에게 외친다. (알려준다.)

② Sort

- 앞의 연산자가 알려준 Id와 LastAccessDate를 가지고 종이에 적어가지고 정렬을 한다.

③ Parallelism (Gather Streams)

- 여러 명이 필터링하고 정렬해놓은 결과를 합친다.

 

정렬이 있을 때와 없을 때의 실행계획을 비교해보자.

 

 

정렬을 한 쪽의 Estimated Subtree Cost 값이 더 크다.

그리고 그 밑의 Memory Grant를 살펴보면 정렬이 있는 쪽의 값이 훨씬 더 큰 것을 알 수 있다. 이것의 의미는, We need space in order to write down our query results, 이다.

 

SQL Server가 주로 메모리를 사용하는 3 가지 큰 원인은 아래와 같다.

- Caching data pages : tries to cache pages in memory as long as it can, because storage can be super slow

- Query workspace : all these little post-it notes, which are coincidentally the same 8KB page size as regular data pages

- Execution plans : when SQL Server has to build one of these big complex maps of how it's gonna process your query, it caches this for a while so that it doesn't have to rebuild that same execution plan again whenever that query plan comes back

 

Memory Grant에 대한 부분을 좀 더 살펴보자.

• DesiredMemory : how much SQL Server would love to have 
• RequestedMemory : how much it actually asked for
• MaxUsedMamory : how much SQL Server actually consumed while it was running that query

 

To be continued...