Plan Caching

출처: SQLER 김민석님

좋은 글이 있어서 슬그머니 스크랩합니다;;

[2000 에서 확인 32bit without AWE]

dbcc memorystatus

 Dynamic Memory Manager         Buffers    
------------------------------ -----------
QueryPlan                      205678
205678 * 8KB = 1645424 KB (한강으로 GoGo~))

[2005 에서 확인 32bit with AWE]

AWE Allocated                                                   5349376

CACHESTORE_OBJCP (Total)                                          KB

SinglePage Allocator                                            31280

CACHESTORE_SQLCP (Total)                                          KB

SinglePage Allocator                                            740208 (한강으로 GoGo~)

 

 

[2008 에서 확인 쿼리 64bit 24GB MaxMemory]

SELECT TOP(20) [TYPE], SUM(SINGLE_PAGES_KB) AS [SPA MEM, KB]

  FROM SYS.DM_OS_MEMORY_CLERKS

 GROUP BY [TYPE] 

  WITH ROLLUP

 ORDER BY SUM(SINGLE_PAGES_KB) DESC;

제일 상단의 총 합계가 얼마인가요?

 4GB 좀 넘어요?  역쉬 (한강으로 GoGo~)

SQL Server 2000 32bit 를 쓰고 있는데 플랜 메모리가 1.6GB 이다.

SQL Server 2000, 2005, 2008 32bit AWE를 쓰고 있는데 플랜 메모리가 700MB 이다.

SQL Server 2005 ~ 2008 64bit 쓰고 있는데, 플랜 메모리가 4GB 정도 되더라.

이럼 개발자랑 DBA 랑 두손 부여잡고 한강으로 가시길 바랍니다. (농담 입니다. ^^;;)

위 양은 쓸수 있는 최대양을 다 쓰고 있는 모습 입니다.

 

이런 서버들은 대부분 Plan Cache 를 재활용 하지 못하는 구조 입니다.

원인은 다양한데요,

 

1.  top 절이 파라메터 바인딩 되지 못한다.

2. where 절 다음에 오는 predicate 가 파라메터 바인딩 되지 못한다.

3. dynamic sql 을 파라메터 바인딩 시키지 않고 그냥 exec (@sql) 로 돌린다.

4. 이외 영역의 이슈는 각 메모리 영역별로 리서치 해보시길 바랍니다.

 

등등의 이슈가 있습니다. (잘 튜닝된 서버의 플랜 캐시 메모리 사이즈는 100 MB를 넘지 않습니다. )

자기가 운영하는 서버가 이런 사황에  있다면,  빨리 고쳐 Memory / CPU / DISK Subsystem 을 보다 효율적으로 사용 할 수 있도록 고쳐 주세요 ~

 

더 상세한 내용은 다음을 참고 하세요

http://msdn.microsoft.com/en-us/library/ee343986.aspx