CACHE

테이블마다 정보를 가지고있고 이 정보로 통계를만들고 이것으로 계획을 짠다.

-- * PLAN CACHE
SQL Server는 한번 실행된 쿼리는 처음 만들어진 실행계획을 플랜캐시에 등록하여 이후에 재사용.
특정 조건을 만족하여 쿼리의 실행계획이 재 컴파일 되기 전까지는 말이다.
실행계획이 재 컴파일 되어 새로운 실행계획이 만들어지는경우 기존의 실행계획은 삭제되어 확인할 수 없다.

그럼 현재 플랜캐시에 있는 실행계획을 확인해 보도록 하겠다.
플랜캐시에서는 아래와 같은 정보를 확인할 수 있다.

- 쿼리 구문
- 수행 횟수
- CPU 시간
- 수행 시간
- 논리적 읽기
- 논리적 쓰기
- 쿼리 플랜


자주쓰는 쿼리문을 컴파일 후 올려놓고 키값으로 매핑한다
A쿼리는 1번키, B쿼리는 2번키... 사용자가 2번키를 호출하면 그 값을 가져온다
-> 즉, 플랜캐시는 섹션별로 인스턴스를 생성해놓는다.-> 키값별로 변수를 가지고있는데 힙형식으로 가지고있다.
-> 즉 플랜캐시는 메모리를 힙에 놓고 쓴다.
(힙은 때가되면 정리된다. 가비지수집기가....)

-- * CACHE MEMORY
캐시메모리는 CPU에 붙어있다.
저장프로시저는 캐시메모리를 활용하지만
클랜캐시는 메모리만 사용한다.

 

 

 

-- PLAN CACHE 관리
키 목록을 힙과 매핑하여 관리하는 곳이 있는데 여기에서 또다시 오래된것, 사용비율이 적은것만 별도로
관리하는곳이 있다. 여기서 이것들을 안쓴다고 판단될 경우 힙에서 삭제해버린다. 
(sp_recompile 을 통해서도 지울 수 있다- 개체 한개씩 지울 때, DBCC는 전체를 지울때)

-- SELECT문에서 PLAN CACHE를 안쓰는게 있다면 개체이름을 임시로 만들어 써보는것도 좋다.

-- PLAN CACHE 효율성 최대화
32기가, 64기가 메모리만큼 운영체제가 쓰는 메모리에 사용자가 몇명이 붙어 어떤테이블을
주로쓰는지를 파악하고, 그에맞춰 밸런스를 찾아 PLAN CACHE를 극대화 한다

-방법
미리 자주쓰는 매개변수로 던질 것(매개변수를 미리 자동화한다-> 즐겨찾기를 만들어 놓는다)을 만들어놓는다
캐시값으로 주로 무엇을 쓰는지 갖고있으므로, 사전에 데이터베이스에서 이런것들을 
호출 할 수 있도록 알려준다. 

데이터베이스 API (ODBC-> 느림, OLEDB)에서 사용

매개 변수 스니핑주의(SQL INJECTION 공격주의랑 비슷)

 

 

 

---

 

 

-- PLAN CACHE 검토 (DMV MS관리를위한 동적 VIEW-> sys.뭐뭐)

sys.dm에서 dm은 다이나믹 가상머신을 지칭

 

-PLANCACHE에대한 DMV 명령어들

 

 

sys.dm_exec_cached_plans

빠른 쿼리 실행을 위해 캐시하는 각 쿼리 계획에 대한 행을 반환합니다. 
이 동적 관리 뷰를 사용하여 캐시된 쿼리 계획, 캐시된 쿼리 텍스트, 캐시된 계획이
사용한 메모리 양, 캐시된 계획의 재사용 횟수를 찾을 수 있습니다.

 

 sys.dm_exec_query_plan

계획 핸들로 지정한 일괄 처리에 대한 XML 형식의 실행 계획을 반환합니다. 
계획 핸들로 지정된 계획은 캐시되거나 현재 실행 중일 수 있습니다.

 

 

sys.dm_exec_text_query_plan

Transact-SQL 일괄 처리 또는 일괄 처리 내 특정 문에 대한 텍스트 형식의 실행 계획을 반환합니다.
계획 핸들로 지정된 쿼리 계획은 캐시되거나 현재 실행 중일 수 있습니다. 이 테이블 반환 함수는 
dm_exec_query_plan (transact-sql)와 유사 하지만 다음과 같은 차이점이 있습니다.
- 쿼리 계획의 출력은 텍스트 형식으로 반환됩니다.
- 쿼리 계획의 출력 크기는 제한되지 않습니다.
- 일괄 처리 내 개별 문을 지정할 수 있습니다.

 

 

sys.dm_exec_plan_attributes

계획 핸들로 지정한 계획의 각 계획 특성에 대해 행을 하나씩 반환합니다. 이 테이블 반환 함수를 사용하여
계획의 현재 동시 실행 수 또는 캐시 키 값과 같은 특정 계획에 대한 정보를 가져올 수 있습니다

 

 

 

sys.dm_exec_query_stats 

SQL Server에서 캐시된 쿼리 계획에 대한 집계 성능 통계를 반환합니다. 이 뷰에는 캐시된 계획 내의
쿼리 문당 하나의 행이 포함되어 있습니다. 행의 유효 기간은 계획 자체와 연결되어 있습니다. 
캐시에서 계획이 제거되면 이 뷰에서도 해당 행이 제거됩니다.

 

 

sys.dm_exec_procedure_stats

캐시된 저장 프로시저에 대한 집계 성능 통계를 반환합니다. 이 뷰에는 캐시된 각 저장 프로시저
계획에 대해 하나의 행이 반환되며 행의 유효 기간은 저장 프로시저가 캐시에 남아 있는 기간과
같습니다. 캐시에서 저장 프로시저가 제거되면 이 뷰에서도 해당 행이 제거됩니다.
이때 Performance Statistics SQL 추적 이벤트가 sys.dm_exec_query_stats와 유사하게 발생합니다.

 

 

sys.dm_os_memory_cache_clock_hands

Internal Clock Hand/External Clock Hand가 어떤 CacheStore에서 얼마나 실행되었는지를 확인해 볼 수 있습니다.
External Clock Hand(HAND_EXTERNAL)는 전체 CacheStore에 대해서 실행되기 때문에 모든
CacheStore에 대해 항상 동일한 값을 갖게 됩니다.

 

 

 

 

---

-- 스키마를 변경하게되면 PLAN CACHE의 모든것이 무효가 된다고 보면된다.
(한국은 모든스키마가 DBO이므로 변경 할 일이 없음)
-- 통계를 업데이트해도 저장프로시저도 다 날아가고 PLAN CACHE도 무효가 된다.


--PLAN CACHE의 문제
CACHE HIT RATE율 : 보통 100~90  밑으로 내려가지 않음. 
속도가 떨어지면 스크립트를 사용하여 메모리를 날림(메모리추가가 어려우니까)
WINDOWS성능 카운터