MSSQL 실습

select c.DepartmentCode, c.CourseNumber, c.CourseTitle, c.Credits, ce.Grade from CourseEnrollments ce
inner join CourseOfferings co on co.CourseOfferingId = ce.CourseOfferingId
inner join Courses c on co.DepartmentCode = c.DepartmentCode And co.CourseNumber = c.CourseNumber
where ce.StudentId = 29717

실행 후 다른 테이블과 INNER JOIN한 공통된 값에서 StudentId가 29717인 값의 각 해당 컬럼의 이름을 가져온다.

왼쪽에서 체크 박스 옆 ctrl + L하면 excution plan을 볼 수 있다.

 

 

Execution Plan?

 

실행 계획이란, 말 그대로 SQL 문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로를 의미합니다.

 

하나 지정하고 마우스 대면 cpu가 해당 쿼리를 수행하는데 소요하는 비용 등등이 나온다.....

해당 쿼리 문을 실행 했을 때 계획과 이것을 알고 있으면 쿼리문을 어떻게 빠르게 실행을 시킬지 계획을 세울수 있다.

 

 

해당 excution plan을 읽는 법은 오른 쪽에서 시작해서 왼쪽으로 쿼리 구문이 어떻게 작동하는지 보여준다. 오른쪽 -> 왼쪽 

가장 오른쪽에 마우스를 올리면 hover한 창이 나온다. 첫번째로 인댁스를 scanning을 하고 대략적인 cost cpu가 얼마나 사용하는지 얼마나 많은 rows를 대략적으로 scanning 할 것인지 보여준다.

 

테이블에 있는 rows들을 다 본다는 뜻이 scanning을 하겠다는 것이다.

 

위에 predicate는 어떤 것을 찾을 지 보여준다 courseenrollemnets 테이블에서 where절에 우리가 쿼리문에 studentid를 찾는다라고 적어 놨다. predicate에서는 이렇게 우리가 어떤 테이블 밑 제약 조건에 걸어놓은것을 찾을지 알려준다...

 

select 문에서도 케시를 어느 정도 우릭 사용을 할지 subtree에서 사용이 되는 비용 등등 select할 때 얼마나 rows들을 탐색을 할지 비용등을 보고 쿼리문을 효과적으로 짤지 생각을 해볼수 있다.

 

다른 블로그에서 퍼온 것인데 병렬처리의 개념을 알고가자

쿠리가 한 개의 cpu에 일하는 것이 아니라 cpu에 여러 코어로 분산을 시켜서 일을 한다는 뜻

즉 병렬처리는 쿼리문이 빠르게 동작을 하도록 향상을 시켜준다.

하지만 불필요한 병렬처리는 cpu 사용을 낭비하게 만든다....

 

 

이런식으로 excution plan을 보고 쿼리문을 어떻게 튜닝을 할지 어디서 비용이 많이 드는지

ssms 에서 확인이 가능하다 단축키 ctrl + L

 

 

 

SET STATISTICS IO ON
SET STATISTICS TIME ON

select c.DepartmentCode, c.CourseNumber, c.CourseTitle, c.Credits, ce.Grade from CourseEnrollments ce
inner join CourseOfferings co on co.CourseOfferingId = ce.CourseOfferingId
inner join Courses c on co.DepartmentCode = c.DepartmentCode And co.CourseNumber = c.CourseNumber
where ce.StudentId = 29717

STATISTICS IO 옵션을 ON으로 설정하면 통계 정보가 표시됩니다. 

 

oupt 정리

 

테이블 : 테이블 이름입니다.
검색 수 : 실행된 검색 수입니다.
논리적 읽기 수 : 데이터 캐시에서 읽은 페이지 수입니다.
물리적 읽기 수 : 디스크에서 읽은 페이지 수입니다.
미리 읽기 수: 쿼리에 대해 캐시에 넣어진 페이지 수입니다. 
LOB 논리적 읽기 수 : 데이터 캐시에서 읽은 text, ntext, image 또는 큰 값 유형(varchar(max), nvarchar(max), varbinary(max))의 페이지 수입니다.
LOB 물리적 읽기 수 : 디스크에서 읽은 text, ntext, image 또는 큰 값 유형의 페이지 수입니다.
LOB 미리 읽기 수 : 쿼리에 대해 캐시에 넣어진 text, ntext, image 또는 큰 값 유형의 페이지 수입니다.

 

 

 

 

SQL Server 구문 분석 및 컴파일 시간: 
   CPU 시간 = 0ms, 경과 시간 = 6ms.

(40 row(s) affected)
테이블 'CourseEnrollments'. 검색 수 17, 논리적 읽기 수 12197, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Courses'. 검색 수 0, 논리적 읽기 수 80, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'CourseOfferings'. 검색 수 0, 논리적 읽기 수 120, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 SQL Server 실행 시간: 
 CPU 시간 = 250ms, 경과 시간 = 64ms

위의 쿼리문실행 결과 논리적 읽기수 물리적 읽기 수 이런 쿼리문 안에서 읽기 작업이 많을 수록 쿼리 수행 시간이 짧아 진다,. 아래에 보면 총 sql을 실행을 하는데 소요한 시간과 경과 시간 등이 얼마나 걸렸는지 우리는 확인이 가능하다.....

 

 

여기서 보면 첫번째 scan 하는 단계에서 이미 cost가 96프로나 발생을 한다. ssms를 보면 missing statement를 확인 가능하다.

 

 

 

우클릭 후 missing index detials 확인

 

 

팝업 창에서 뜬다 우리는 students db를 쓰고 학생 id 및 course등록으로 쿼리문을 작성을 했다

 

주석 처리를 풀고 해당 구문을 실행시켜서 courseEnrollments에 해당 인덱스를 추가 한다.

 

 

해당 에디터에서 구문을 실행 시킨후 쿼리 속도가 성능이 개선이 된 것을 확인이 가능하다.. ctrl+ l 혹은 체크모양 버튼 옆 쿼리 플랜

 

 

해당 쿼리문을 실행을 시키고 인덱스를 찾는데 걸리는 코스트와 키를 찾는데 소요되는 코스트가 상당히 줄었다.....

 

 

쿼리문을 읽는 과정에서 작업이 얼마나 개선이 되고 줄었는지 해당 쿼리문 실행 후 알 수 있다.