Plan Cache

Query Optimization 는 CPU load를 일으킬 수 있는 리소스에 영향을 많이 받는 프로세스

Caching Plan으로 이 영향을 줄일 수 있음

 

Compiled plan : 전체 배치와 배치의 각 statement에 대한 plan

Execution plan : Query 가 실행될 때마다 Compiled Plan으로 부터 생성

 

* Execution plan은 runtime마다 실행되기 때문에 각 query 실행 마다 다르다. 만약 여러 개의 세션을 같은 compiled plan으로 실행한다면 세션마다 각각 execution plan이 생성된다.

 

Plan Chache는 memory를 사용하기 때문에 buffer pool 사이즈를 줄일 수 있다. 다시말하면 physical read 가 늘어나서 시스템 성능 저하에 영향을 줄 수 있다.

 

Memory 재사용을 위한 Plan이 저장 주기 

* ad-hoc : plan이 얼마나 재사용되는 지에 따라 다름

* 그 외 : plan 비용에 따라 다름

 

MSSQL 은 Caching 된 plan이 더 이상 유효하지 않을 때마다 Query를 recompile 한다.

- Create/Drop Column, Index, Constraint, trigger, 테이블 통계가 이에 속한다.

- 통계 정보가 오래됐을 경우 plan cache를 recompile

- 임시 테이블 사용은 recompile 수를 증가시킬 수 있음

 

보통 테이블의 통계 만료 시점은

- 빈 Table에 데이터가 추가될 경우

- 500행 미만의 테이블에 대해 500번의 변화가 있을 때마다

- 500행 이상일 경우 Compatibility 레벨이 130 이하일 경우 500+번의 변화가 있을 때마다 (혹은 전체 행의 20% 마다)

 

임시 테이블에서는

- 보통 테이블과 달리 6번의 변경마다 발생

- KEEP PLAN 쿼리 힌트로 보통 테이블과 같은 효과를 줄 수 있다. (KEEPFIXED PLAN의 경우 통계 만료 후에도 쿼리 recompile이 일어나지 않게 한다.)

 

ARITHABORT 옵션의 경우 ADO.Net의 경우 OFF/ SSMS에서는 ON으로 설정되어 있음

 

계획인 복제 되는 경우는 Object에 스키마를 추가하여 쿼리하지 않을 경우

ex) Select  * from Orders 대신 Select * from Sales.Orders 사용

 

Batch나 Object에 8KB 이상의 문자가 있을 경우 Caching 하지 않는다

ex) SELECT * FROM table WHERE col='8,192 이상의 문자를 삽입할 경우'