캐시와 실행계획

캐시는 더 빠른 응답 속도를 위해 디스크가 아닌 메모리에서 데이터를 가져오는 방식으로 적합한 DB의 예로는 In-Memory DB인 Redis가 있다.

 

Redis는 간단한 구조인 Key-Value 로 이루어져 있으며 API에서 캐시에 먼저 접근한 후 데이터가 없을 경우 디스크 서버 DB에 접근하여 값을 캐시 서버에 옮겨주는 방식으로 사용이 가능하다.

 

RDBMS에서도 실제로 데이터 읽기가 발생하면 메모리인 버퍼풀에 먼저 접근한다.

1. 데이터가 버퍼풀에 있는 경우 : 버퍼풀에서 데이터를 읽음

2. 데이터가 버퍼풀에 없는 경우 : 디스크에 접근하여 데이터를 읽은 후 버퍼풀에 복사 

 

최근 개발 과정 중 DEV, QA, LIVE에서는 중 특정 LEVEL의 서버에서 쿼리가 오래걸리는 현상이 있었다.

LIVE의 경우 하나의 서버에 하나의 인스턴스가 떠 있어서 메모리 크기를 충분히 지정해 주었지만,

DEV와 QA의 경우 하나의 서버에 여러개의 인스턴스가 올라가 있는 상황이었고 각 인스턴스마다 메모리 또한 다르게 지정이 되어 있었다.

이로인하여 CPU, IO 값은 낮은 쿼리지만 resource_semaphore wait type이 뜨면서 쿼리 시간이 오래 걸렸다.

각 인스턴스의 MIN, MAX Memory 의 값을 조정해주었고 쿼리 속도는 정상으로 돌아왔다.

 

SQL Server의 아키텍처는 다음과 같다.

 

 

실행계획은 한번 쿼리된 계획에 대하여 캐시에 남겨놓고 사용하는 방식이다. 실행 계획을 저장하는 데 사용되는 메모리 풀 부분을 계획 캐시라고 한다.

• 지속형 개체(저장 프로시저, 함수 및 트리거)에 관련된 계획에 사용되는 Object Plans 캐시 저장소(OBJCP).
• 자동으로 매개 변수화된 쿼리, 동적 쿼리 또는 준비된 쿼리에 관련된 계획에 사용되는 SQL Plans 캐시 저장소(SQLCP).

다만 실행되는 쿼리의 변수가 항상 바뀌는 경우 이를 옵티마이저는 동일한 쿼리라고 인식하지 못할 경우가 있다. 이럴 때는 sp_executesql 를 사용하면 좀 더 정확하게 실행계획을 매핑할 수 있다.

예)

DECLARE @MyIntParm INT
SET @MyIntParm = 1
EXEC sp_executesql
   N'SELECT * 
   FROM AdventureWorks2014.Production.Product 
   WHERE ProductSubcategoryID = @Parm',
   N'@Parm INT',
   @MyIntParm

Where 구문에 있는 @Parm이 각 순서와 동일하게 매핑되어 쿼리가 실행된다.