MSSQL 프로시저 캐시 / 버퍼 메모리

데이터베이스 작업을 하면서 주어진 자원을 최대한 효율적으로 사용하는 것은 하나의 중요한 포인트이다.

따라서 SQL문을 최적화하기 위해서 SQL튜닝을 진행하는데, SQL 튜닝을 진행하기 앞서 SQL에 대한 이해와 SQL 데이터의 분석이 선행되어야 한다.

 

프로시저 캐시(Procedure cache/Plan cache)

프로시저 캐시는 쿼리 플랜이 저장되는 메모리 공간으로 쿼리 플랜이 재사용될 수 있도록 함으로써 쿼리문을 컴파일하는데 드는 비용을 최소화하기 위해 사용된다.

일반적으로 SP(Stored Procesure)의 사용을 권장하는 이유가 쿼리 플랜을 재사용하여 성능 향상을 기대할 수 있기 때문인데, 프로시저 캐시도 비슷한 역할을 하는 것이다.

하지만 프로시저 캐시에 저장되는 ad-hoc 타입의 쿼리문의 경우 쿼리문 전체가 동일하지 않으면 쿼리 플랜을 재사용할 수 없기 때문에, 조건(혹은 변수)이 계속 바뀌는 쿼리문의 경우에는 프로시저 캐시의 쿼리 플랜 재사용을 통한 성능 향상을 기대하기는 어렵다.

프로시저 캐시는 sys.dm_exec_sql_text에서 확인할 수 있으며, DBCC FREEPROCCACHE 명령어를 통해 리소스 풀에 연결된 모든 프로시저 캐시를 지울 수 있다.

 

버퍼 메모리(Buffer)

데이터베이스로 SQL 요청을 보내면 DBMS를 통해 디스크에서 데이터를 불러와서 작업을 처리한다.

여기서 디스크의 연산속도는 메모리의 연산속도보다 현저히 느리기 때문에 디스크 입출력 횟수를 줄이기 위해 디스크 입출력 결과를 버퍼 메모리로 관리하는 방식을 사용한다.

SQL Server DB에서 Buffer manager는 데이터를 읽거나 쓸 때마다 이를 메모리로 복사하고, 또 오래되거나 덜 사용되는 데이터는 제거하는 작업을 수행한다.

버퍼 메모리를 통해 성능을 크게 향상시킬 수 있기 때문에 작업한 모든 결과를 버퍼 메모리로 저장하면 좋겠지만, 메모리 공간은 디스크 공간보다 작고 한정적이어서 버퍼에 있는 페이지들을 잘 관리할 필요가 있다.

버퍼 메모리는 sys.dm_os_buffer_descriptors에서 확인할 수 있으며, DBCC DROPCLEANBUFFERS 명령어를 통해 버퍼 풀의 모든 데이터 페이지를 지울 수 있다.

 

DB작업을 하다보면 똑같은 쿼리에 대해 첫 실행에서는 수 초가 소요하지만 다음 실행부터는 실행에 소요하는 시간이 줄어드는 경우를 마주하게 되는데, 이는 버퍼풀의 데이터를 바로 불러와 작업을 실행하기 때문이다. 이러한 현상이 발견되면 FREEPROCCACHE 혹은 DROPCLEANBUFFERS 명령어를 사용하면서 어떠한 개념이 작용을 한 것인지 확인하는 배움에 있어 큰 도움이 된다.