Procedure Cache(또는, Plan Cache라고도 함)는 Query Plan이 저장되는 메모리 공간으로 Query Plan이 재사용될 수 있도록 함으로써 쿼리문을 Compile하는데 드는 비용을 최소화하기 위해 사용됩니다. 일반적으로 SP(Stored Procedure) 사용을 권장하는 가장 큰 이유는 SP에 대한 Query Plan의 재사용을 통해 성능 향상을 기대할 수 있기 때문입니다. 반면에, Adhoc 쿼리문은 쿼리문 전체가 동일하지 않은 이상 Query Plan이 재사용되지 못하기 때문에(SQL Server에서 제공하는 '자동 매개변수화(Auto Parameterization)' 기능을 배제했을 때) 실행될 때마다 Compile 비용이 발생하여 성능에 악영향을 미치게 됩니다. 또한, Adhoc 쿼리문을 과도하게 사용하는 환경에서는 Adhoc 쿼리문에 대한 Query Plan들에 의해서 Procedure Cache의 크기가 일정 수준 이상으로 커지는 현상이 발생하기도 하며 이로 인해 사용 가능한 Buffer Pool 메모리 영역의 크기가 감소하여 SQL 서버 전체의 성능이 나빠지는 현상이 발생할 수 있습니다.
SQL Server 2005부터는 X64 버전도 지원되어 64-bit 환경이 보편화되었습니다(IA64 버전은 SQL Server 2000부터 지원되었습니다). 64-bit 환경에서는 메모리 주소 공간(Address Space)의 크기가 늘어남에 따라 32-bit 환경에서는 잘 발생하지 않던 현상이 발생하기도 하는데 그 중 대표적인 것이 Procedure Cache의 크기가 수 GB 이상으로 늘어나는 현상입니다(실제 사례 중에는 Procedure Cache의 크기가 10GB를 초과하는 사례도 있었습니다). 이런 현상에 대한 해결책으로 SQL Server 2005 Service Pack 2 이후에는 Procedure Cache의 최대 크기에 대한 제약을 강화하였으며 이를 통해 어느 정도의 성능 개선을 가져왔습니다. 하지만, 이는 완전한 해결책은 아니며 근본적으로 SQL Server를 최적화된 상태에서 운영하려면 Procedure Cache를 최대한으로 활용할 수 있도록 쿼리문을 작성하여 사용해야 합니다.
Procedure Cache로 인해 발생하는 성능 문제를 해결하기 위해서는 물론이고, 문제 발생을 미연에 방지하고 SQL Server를 최적화하기 위해 Procedure Cache의 구조와 동작 방식을 이해하는 것은 중요합니다. 특히, SQL Server 2005/2008의 Procedure Cache는 SQL Server 2000과는 현저하게 다르므로 SQL Server 2000의 Procedure Cache에 대해 알고 있었다 해도 새롭게 이해해야 할 필요가 있습니다. 그럼, SQL Server 2005/2008의 Procedure Cache에 대해서 살펴보도록 하겠습니다.
SQL Server 2005/2008에서 Procedure Cache 메모리 영역은 다음과 같이 크게 5개의 영역으로 세분화됩니다. 이 중 CACHESTORE_OBJCP와 CACHESTORE_SQLCP가 Compiled Plan(또는, Query Plan)이 저장되는 영역이며 Procedure Cache의 대부분을 차지합니다. CACHESTORE_OBJCP에는 Stored Procedure, Trigger, Function 등의 Compiled Plan이 저장되고 CACHESTORE_SQLCP에는 Adhoc query, Prepared query 등에 대한 Compiled Plan이 저장됩니다.
Procedure Cache 메모리 영역
CACHESTORE_OBJCP : Object Plans
CACHESTORE_SQLCP : SQL Plans
CACHESTORE_PHDR : Bound Trees
CACHESTORE_XPROC : Extended Stored Procedures
CACHESTORE_TEMPTABLES : Temporary Tables & Table Variables
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형을 좀더 구체적으로 살펴 보면 다음과 같으며, 이 중 Auto-parameterized queries, sp_executesql procedure, Prepared queries에 대한 Compiled Plan은 'sys.dm_exec_cached_plans' DMV를 통해 조회했을 때 objtype이 ‘Prepared’로 표시되고 나머지 쿼리문 유형들에 대한 Compiled Plan은 objtype이 ‘Adhoc’으로 표시됩니다.
Procedure Cache 메모리 영역 안의
CACHESTORE_SQLCP 영역에 Compiled Plan이 저장되는 쿼리문의 유형
1. Ad-hoc queries : 하나의 DML(SELECT, INSERT, UPDATE, DELETE)로 이루어진 쿼리
2. Auto-parameterized queries : SQL서버에서 제공하는 Simple Parameterization이나 Forced Parameterization의 적용을 받는 쿼리
3. sp_executesql procedure : sp_executesql을 사용하여 Parametrized된 쿼리
4. Prepared queries : OLEDB나 ODBC에서 제공하는 SQLPrepare/SQLExecute 기능이 사용된 쿼리
5. Batches : 여러 개의 쿼리문이 하나의 Batch로 실행되는 경우
6. Dynamic SQL : EXEC(...)을 통해 실행된 쿼리
'sys.dm_os_memory_clerks' DMV를 조회하면 다음과 같이 Procedure Cache를 구성하는 5개의 영역에 대한 메모리 사용 정보를 확인해 볼 수 있습니다. 이런 정보는 'dbcc memorystatus' 실행 결과나 성능 카운터를 통해서도 확인할 수 있으며 이 정보를 통해 메모리를 많이 차지하는 Procedure Cache의 영역을 확인할 수 있습니다.
Procedure Cache가 과도하게 커져서 성능 문제가 발생했을 때, SQL Server 2000에서는 'dbcc freeproccache' 명령을 통해 Procedure Cache 전체를 비우는 방법을 사용했었습니다. 이 방법은 Procedure Cache에서 사용하던 메모리를 반환하는 효과는 있었지만 유형에 관계없이 모든 Query Plan이 다시 만들어 져야 하기 때문에 급격한 CPU 사용량 증가 등의 부작용을 유발하였습니다. SQL Server 2005/2008에서는 Procedure Cache를 세분화된 영역별로 비울 수 있는 방법이 추가적으로 제공되어 좀더 유연하게 대처할 수 있습니다. 예를 들어, Adhoc이나 Prepared Query에 대한 Compiled Plan이 저장되는 CACHESTORE_SQLCP 영역을 비우고자 할 때에는 다음의 명령을 실행하면 됩니다.
dbcc freesystemcache('SQL Plans')
SQL Server 2005/2008에서 Procedure Cache에 저장된 항목(Entry)들은 SQL Server가 메모리 부족을 경험하지 않는 이상 자동으로 지워지지 않습니다. 이런 이유로 물리적인 메모리가 큰 시스템(특히, 64-bit 시스템)에서는 Procedure Cache가 수십 GB 이상으로 커지는 현상이 발생할 수도 있었으며, 이로 인해 발생하는 성능 저하 현상을 예방하기 위해 SQL Server 2005 Service Pack 2 이후부터는 다음의 공식에 의해 계산된 값이 Procedure Cache의 Soft Limit 값으로 사용되도록 보완되었습니다. 예를 들어, SQL서버의 ‘max server memory’를 100GB로 설정한 시스템일 경우 Procedure Cache의 Soft Limit 값은 10.8GB(4*0.75 + 60*0.1 + 36*0.05)가 됩니다. Soft Limit 값은 단어가 의미하는 것처럼 Limit 값을 절대로 넘을 수 없는 것(Soft Limit과는 다른 개념으로 Hard Limit이 존재합니다)을 의미하는 것은 아니며 Limit 이상에 도달했을 때 Limit 아래로 내려가도록 조절하겠다는 의미입니다. SQL Server 2005/2008에서는 Procedure Cache 메모리가 Soft Limit에 도달하게 되면 Clock Algorithm에 의해서 Procedure Cache에 저장된 항목들이 제거되고 이를 통해 Procedure Cache에 의해 사용된 메모리를 Soft Limit 이하로 낮추게 됩니다.
Procedure Cache Memory Soft Limit = 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB
SQL Server 2005/2008에서는 Procedure Cache의 크기를 조절하기 위해 Internal Clock Hand와 External Clock Hand의 두 가지 Clock Algorithm이 사용됩니다. Internal Clock Hand는 일반 사용자 Thread에서 쿼리문 실행을 위해 Procedure Cache를 접근(Lookup, Insert 모두)할 때 조건이 발생하게 되면 실행되며 CacheStore별로 한번에 하나의 Thread에서만 Internal Clock Hand가 실행됩니다. External Clock Hand는 Resource Monitor에서 조건이 발생하게 되면 모든 CacheStore에 대해서 실행되며 각 CacheStore별로 실행되는 Internal Clock Hand와 동시에 실행될 수 있습니다. Internal Clock Hand와 External Clock Hand가 실행되는 조건은 다르지만, 두 경우 모두 동일하게 Cache로부터 비용이 0인 항목(Entry)을 지우는 작업이 수행됩니다(SQL Server 2000에서는 Lazy Writer에 의해 동일한 기능이 수행되었습니다).
[Internal Clock Hand]
각 CacheStore 단위(CACHESTORE_OBJCP, CACHESTORE_SQLCP, CACHESTORE_PHDR, CACHESTORE_XPROC, CACHESTORE_TEMPTABLES 별로)에서 다음의 조건 중 하나가 충족되면
해당 CacheStore에서 실행됨.
- 특정 CacheStore의 항목(Entry) 수가 SoftQuota(SQL Server 내부적으로 각 CacheStore별로 정의되어 있는 값으로 64-bit의 경우 CACHESTORE_SQLCP에 대해서 160,036이 지정되어 있음)에 도달
- 특정 CacheStore가 Single Page Allocator(Buffer Pool)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 75%에 도달
- 특정 CacheStore가 Multi Page Allocator(MemToLeave)에서 할당 받은 Memory가 Procedure Cache의 Soft Limit값의 50%에 도달
[External Clock Hand]
SQL Server나 시스템에서 메모리 부족이 발생하거나 Procedure Cache 전체 메모리가 Soft Limit에 도달할 때 Resource Monitor(SQL Server의 메모리 사용량을 조절하는 시스템 Task)에 의해서 모든 CacheStore에서 실행됨.
'sys.dm_os_memory_cache_clock_hands' DMV를 조회해 보면 다음과 같이 Internal Clock Hand/External Clock Hand가 어떤 CacheStore에서 얼마나 실행되었는지를 확인해 볼 수 있습니다. 다음의 결과에서 External Clock Hand(HAND_EXTERNAL)는 전체 CacheStore에 대해서 실행되기 때문에 모든 CacheStore에 대해 항상 동일한 값을 갖게 됩니다.
Internal Clock Hand/External Clock Hand는 Procedure Cache의 항목을 지울 때 비용을 기준으로 지우게 되며 비용이 0인 항목들을 지우게 됩니다. Clock Hand가 실행될 때, Cache의 항목들을 체크하여 비용이 0인 항목은 제거하고 비용이 0보다 큰 항목은 1/2만큼 감소시킵니다. 1/2만큼 감소된 결과로 비용이 0이 된 항목들(즉, 기존의 비용이 1이었던 항목들)은 다음 번 Clock Hand가 실행될 때 제거 대상이 됩니다.
Compiled Plan에 대한 비용은 기본적으로 다음과 같이 Compiled Plan을 만드는 데 소요된 I/O, CPU, 메모리를 기준으로 정해집니다. Cost 값은 아래의 기준에 의해서 각 항목들에 대한 Tick 값을 합산하여 결정되는데, 최대 31의 Tick 값을 가질 수 있습니다. 계산된 Tick 값은 Compiled Plan에 대한 비용 값으로 저장될 때 ‘2의 n승(2n)’으로 계산되어 저장되므로 Compiled Plan은 최대 2147483648(231)의 비용 값을 가질 수 있습니다. 참고적으로, 아래의 기준으로부터 I/O 비용이 가장 큰 비중을 차지(최대 19 ticks)하고 CPU 비용이 그 다음의 비중을 차지(최대 8 ticks)하며 메모리 비용이 제일 작은 비중을 차지(최대 4 ticks)함을 알 수 있습니다.
Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost
- Two I/Os cost 1 tick, with a maximum of 19 ticks.
- Two context switches cost 1 tick, with a maximum of 8 ticks.
- Sixteen memory pages (128 KB) cost 1 tick, with a maximum of 4 ticks.
Compiled Plan이 Cache에 저장될 때 type에 따라서 최대 비용이 제한되게 되는데, Adhoc은 비용이 0으로 설정되고 Prepared는 비용이 최대 256으로 제한되며 Proc은 제한 없이 계산된 비용이 그대로 사용됩니다. 이렇게 type별로 비용의 최대값을 다르게 함으로써 Procedure Cache에서 Clock Hand에 의해 Compiled Plan이 지워질 때, Adhoc이 우선적으로 지워지고 Prepared와 Proc의 순서로 지워지도록 합니다.
Compiled Plan에 대한 비용 값은 original_cost 값과 current_cost 값으로 설정되는데, Compiled Plan이 Cache에 처음 저장될 때 앞에서 설명 드린 방식에 의해서 계산된 비용 값(original_cost)이 current_cost로 설정됩니다. Clock Hand가 수행될 때마다 current_cost 값은 1/2씩 감소하게 되고 Compiled Plan이 재사용(usecounts가 증가)되게 되면 current_cost 값은 다시 original_cost 값으로 설정됩니다. Adhoc Compiled Plan의 경우에는 Prepared나 Proc과는 달리 original_cost와 current_cost가 0으로 시작하게 되며 재사용될 때마다 original_cost가 1부터 시작하여 2배씩 증가하여 최대 16까지 증가(original_cost가 1, 2, 4, 8, 16의 순으로 증가)하게 됩니다. 따라서, Adhoc의 경우에도 재사용이 많이 되는 Compiled Plan은 original_cost가 16이 될 수 있으며 좀더 오랫동안 Cache에 머물 수 있게 됩니다.
다음의 쿼리문을 실행하면 Procedure Cache에 저장된 모든 Compiled Plan에 대한 상세 정보를 확인해 볼 수 있습니다.
SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
상기의 쿼리를 실행하면 다음과 같은 결과를 확인할 수 있는데, objtype 칼럼이 Compiled Plan의 type을, usecounts 칼럼이 Compiled Plan의 재사용 횟수를, original_cost와 current_cost 칼럼이 Compiled Plan의 비용을 각각 나타냅니다.
지금까지 SQL Server 2005/2008의 Procedure Cache의 구조와 동작 방식에 대해서 살펴봤습니다. Procedure Cache의 동작 방식을 고려했을 때 Adhoc 쿼리문을 과도하게 사용하는 것은 SQL Server의 성능에 전혀 도움이 되지 않음을 알 수 있습니다. 따라서, 실행 횟수가 많은 쿼리문은 Stored Procedure를 사용하는 것을 우선적으로 고려하고 Stored Procedure 사용이 여의치 않을 경우에는 Prepared 쿼리문을 사용하는 것이 필요합니다. 간혹 Stored Procedure를 여러 개의 쿼리문이 하나의 Batch를 구성할 경우에만 사용하는 것으로 생각하여 단일 쿼리문은 Adhoc 쿼리문으로 처리하는 경우가 많은데 이는 잘못된 생각입니다. 단일 쿼리문이라고 하더라도 Stored Procedure로 처리함으로써 SQL Server의 성능 향상을 가져올 수 있다는 점을 명심하기 바랍니다.
SQL Server 2005/2008에서 Procedure Cache에 실행 계획이 저장/재사용/제거되는 방식에 대해서는 다음의 문서로부터 좀더 자세한 내용을 참고할 수 있습니다.
SQL Server 2008 Books Online - Execution Plan Caching and Reuse
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Plan Cache Internals
출처 : http://blog.daum.net/partsofmemory/28
