 |
이름: 송혁 |
|
| ||||||||||||||||||||||||||||||||||||||||||||||
|
1. SQL Server의 재 컴파일이란? SQL Server 는 비용 기반의 최적화(cost based optimizer)를 기반으로 쿼리의 실행 계획을 생성하며, 생성시 통계 정보, 테이블 스키마 정보 및 쿼리에 대한 내용을 바탕으로 실행 계획을 생성합니다. 이 작업은 대부분 CPU 리소스를 사용하고 있습니다. 매번 쿼리 요청이 될 때 마다 쿼리의 실행 계획을 계속 생성한다는 작업한다면 많은 성능 적 문제를 야기 할 수 있어 SQL Server에는 프로시져 캐시라는 메모리 공간에 쿼리가 수행된 실행 계획을 메모리에 적재 후 추후 동일한 쿼리요청이 들어온다면 캐시에 있는 내용을 재사용을 하게 됩니다. 이러한 것을 실행 계획 재사용이라고 부르며, 기존의 실행계획을 사용하면 안될 경우 및 사용할 수 없는 경우에 새로운 실행 계획을 만들기 위해 재 컴파일이라는 것을 하게 됩니다. 2. OLTP 서비스 환경에서의 재 컴파일이 주는 장점 및 단점 OLTP환경 즉 많은 트랙잭션이 빠른 시간 안에 처리되어야 하는 환경에서는 OLAP환경 보다 재 컴파일에 대해서 보다 민감하고, 성능에 많은 영향을 줄 수 있습니다. 위에서 설명 하고 있듯이 SQL Server는 프로시져 캐시라는 곳에 실행계획을 저장하고 있어 재사용을 할 수 있습니다. 재사용으로 인해 매번 실행계획을 다시 생성하지 않아도 되어 보다 적은 리소스로 쿼리에 대한 응답을 할 수 있습니다. 그러나 예전에 생성된 실행 계획을 재 사용할 경우 문제가 발생 할 수도 있습니다. 기존에 실행계획은 최초 실행계획의 생성시 받는 매개변수에 대해서 실행 계획을 만드는 것이 일반적이며, 기존에 실행계획을 생성한 매개변수와 나중에 수행되는 매개변수에 대한 카디널리티 편차가 크다면 실행 계획 재사용으로 인해 적절한 실행 계획을 찾지 못하고 기존의 실행 계획을 이용하여 성능상 문제를 야기 할 수 있습니다. 실행계획의 재사용이란 항상 좋은 것만이 아닌 필요에 따라서는 재 컴파일을 통해 새로운 실행 계획을 생성 하는 것이 성능상 이점을 가질 수 있습니다. 아래의 링크에서 이러한 실행계획의 재사용으로 인해서 발생 될 수 있는 문제에 대해서 간략히 설명 하고 있습니다. 프로시져 parameter sniffing에 의한 실행계획의 차이점 3. 강제 매개변수화, 자동 매개변수화란. SQL Server 2005에서는 자동 매개 변수화 만이 아닌 강제매개변수화 라는 기능이 추가 되었습니다. 강제 매개변수화의 경우 실행 계획을 생성할 때 사용자가 설정한 매개변수로 해당 실행계획을 생성할 수 있습니다. 이러한 강제 매개변수화를 잘 사용한다면 특정 환경에서 카디널리티 편차가 심한 데이터에 대해서 재 컴파일로 최적의 실행 계획을 만드는 것이 아닌 변수의 분포도에 따라 몇 가지 실행 계획을 생성 하여 보다 효율적으로 작성 할 수 도 있습니다. SQL Server 2005 예전 버전에도 존재하던 기능 중에 하나인 자동매개변수화(단순매개변수화)는 실행 계획을 재사용하기 위해서 단순한 구문 및 아래의 조건에 매칭되지 않는 경우에 대해서 상수를 변수로 변환하여 실행계획을 생성 할 수 도 있습니다. 이러한 내용들은 보면 최대한 재 컴파일을 막기 위한 SQL Server 개발진들의 노력이 보입니다. 이러한 노력을 보면 재 컴파일이 성능에 얼만큼 영향을 주는 부분인지 단편적으로 생각 해 볼 수 있을 것 같습니다. 자동 매개변수화가 될 수 없는 조건 1. BULK INSERT 2. IN 구문 또는 OR 3. UPDATE 절에 SET @변수 = col 4. SELECT 절에 UNION구문 5. SELECT 절에 INTO구문 6. SELECT/UPDATE 절에 FOR BROWSE 7. SELECT/UPDATE/DELETE 에 OPTION 쿼리 힌트 8. SELECT 절에 DISTINCT 구문 9. SELECT/UPDATE/DELETE/INSERT절에 TOP구문 10. WAITFOR 구문 11. SELECT절에 GROUP BY, HAVING, COMPUTE 구문 12. DELETE/UPDATE 절에 FROM CLAUSE 구문 13. 전체 검색, 연결된 서버, 테이블 변수 참조 14. 서브쿼리 15. 조건 절에 <> 상수 16. 조인이 포함된 구문 17. 두 개 이상의 테이블을 참조 18. DELETE, UPDATE 에 FROM절 4. SQL Server 2005에서 변경된 구문단위 재 컴파일 보통 SQL Server을 사용할 때 주로 프로시져 단위로 SQL구문을 작성 하여 개발 하는 경우가 많습니다. SQL Server 2000에서는 프로시져 내부에 재 컴파일이 해야 하는 경우 프로시져 내부에 존재 하는 모든 구문에 대해서 재 컴파일을 하였습니다. 만약 수만 줄의 쿼리 가 있었다면 이것을 재 컴파일 한다는 것은 그 작업만으로도 상당한 리소스를 소비 하게 됩니다. 그래서 이러한 경우를 대처 하기 위해서 자주 재 컴파일 되는 부분을 다른 프로시져로 만들고 원본 프로시져에서 호출 하는 방식을 사용하였습니다. 이러한 방법으로 전체 재 컴파일이 되는 것을 막을 수 는 있었지만 많이 번거로운 작업 이었습니다. 그러나 SQL Server 2005에서는 구문 단위의 재 컴파일이 도입 되어 SQL Serve 2000에서 사용하던 것처럼 따로 프로시져를 만들어 호출 하지 않아도 구문단위로 재 컴파일을 할 수 있습니다. 테스트!! 5. 프로시져 캐시 에서의 지연기록기의 역할 SQL Server에서는 프로시져 캐시라는 곳에 예전에 실행된 실행 계획을 가지고 있어 재사용한다고 하였습니다. 그렇다면 이 프로시져 캐시에 존재 하는 것들에 대해서 사용되지 않거나 사용빈도가 낮은 것에 대해서는 메모리에서 제거를 해야 하는 작업이 필요합니다. 만약 이러한 작업이 없다면 계속 프로시져 캐시의 증가로 인해 서버 메모리에 문제가 발생 할 수 있습니다. 그러나 SQL Server에서는 이러한 문제가 거의 발생 되지 않습니다. 지연기록기(LazyWriter)라는 백그라운드 프로세스가 존재 하기 때문입니다. 지연기록기 는 free page를 확보하기 위해 주기적으로 버퍼캐시를 검색 합니다. 이러한 작업을 수행 하면서 해당 조건에 보다 낮거나 같은 수치를 가지는 것을 캐시영역에서 제거 하여 free page를 확보 하게 됩니다. 그리고 지연기록기가 하는 일중 다른 하나는 운영체제의 메모리가 부족 하다면 지연기록기에서 SQL Server에서 사용되는 버퍼 풀 메모리의 일부분을 free하여 운영체제에게 반환하는 역할을 합니다. SQL Server 2005에서 지원이 보다 강화된 NUMA(Non-Uniform Memory Access )에 대해서는 각 노드에 대해 지연기록기 프로세스가 존재 하게 됩니다. NUMA(Non-Uniform Memory Access) 이해 (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/2a77699c-e06b-4377-8acf-4d565d225f3c.htm) 아래의 DMV를 이용하여 현재 재사용 되는 실행계획의 개수 및 재사용되는 상위 50개의 구문이 확인이 가능 합니다.
6. 재 컴파일의 원인 및 프로시져 캐시 사라지는 경우 위에서 보듯이 쿼리의 자주 실행 되는 것이 아니라면 지연기록기에 의해 프로시져 캐시에서 실행계획이 제거 될 수 있습니다. 이렇게 되면 다시 쿼리가 호출 되면 다시 컴파일을 수행 하여야 합니다. 여기서는 지연기록기에 프로시져 캐시가 사라지는 경우가 아닌 다른 작업으로 사라지는 경우와, 기존의 실행계획이 있지만 다시 재 컴파일을 해야 하는 경우 대해서 알아보도록 하겠습니다. 재 컴파일의 원인은 아래와 같이 스키마 변경, 통계정보 업데이트 등으로 발생 될 수 있으며, ALTER DATABASE구문이나 DBCC CHECKDB구문 등으로 프로시져 캐시의 내용이 모두 사라져 모든 쿼리에 대해서 다시 컴파일을 하여야 합니다. 하나의 테이블에 통계정보가 변경되어 해당 테이블과 연관된 프로시져를 재 컴파일을 하는 것은 오히려 좋을 수 있습니다. 보다 최신의 통계정보를 바탕으로 오히려 예전 보다 더욱 효율적인 실행계획을 가지고 처리 할 수 있기 때문입니다. 그러나 ALTER DATABASE, RESTORE DATEBASE같은 경우는 서비스 운영 중에 많이 사용하고 있지 않는 구문이지만 만약 이 구문들이 사용된다면 전체 프로시져 캐시에 존재하는 실행계획이 사라져서 다시 모든 쿼리에 대해서 컴파일을 하여야 합니다. 많은 트랜잭션을 가지고 있는 사이트라면 순간적으로 CPU 리소스가 높이 사용 될 것입니다. 그러기에 이러한 동작을 유발 하는 구문에 대해서 서비스 중 작업이 필요하다면 충분한 주의가 필요 합니다. 재 컴파일 원인
프로시져 캐쉬가 사라지는 작업
SQL Server 2005 SP2에서는 프로시져 캐시가 사라지는 작업 시 SQL 에러로그에 아래와 같은 메시지가 추가 되었습니다.
7. 재 컴파일 발생 원인 및 빈도 찾기 이러한 재 컴파일이 문제가 될 수 있다는 것을 위에서 알아 보았습니다. 그러면 이제는 현재 운영하고 있는 사이트에서 얼마나 많은 재 컴파일이 발생하는지 그리고 이유는 무엇인지 확인할 필요가 있습니다. 많은 트랜잭션이 발생하지 않는다면, 요즘은 워낙 CPU사양도 좋고 해서 재 컴파일에 의한 문제는 그리 크게 사람에게 다가오지 않을 수도 있습니다. 그러나 많은 트랜잭션이 발생 하는 환경이라면 분명 확인 할 가치는 있을 겁니다. 보통 재 컴파일에 대한 문제로 원인 및 빈도를 확인 하기 위해서는 프로파일러와 성능 모니터를 통해 확인 합니다. 프로파일러의 경우 모든 SQL Server로 들어오는 쿼리에 대해서 재 컴파일이 발생 되었다면 SP: Recompile, SQL: StmtRecompile 이벤트 및 EventSubClass컬럼을 추가 하여 원인 및 빈도를 확인 할 수 있습니다. 그렇지만 프로파일러를 서비스중인 서버에 사용하는 것도 힘든 곳도 많을 것입니다. 그리고 구문단위까지 이벤트를 걸게 되면 추적 파일 크기는 금방 커질 것 입니다. 비쥬얼하게 보여지는 프로파일러 대신 SQLDIAG나 저장 프로시져를 사용하는 것이 서버의 리소스 소비에 대해서 보다 효과 적입니다. SQLDIAG는 기존 SQL Server 2000에도 존재 하였지만 일반적인 에러로그 및 수행 당시의 시스템 테이블 정보 등 이었지만 SQL Server 2005의 SQLDIAG는 기존에 Microsoft PSS팀에서 사용하던 PSSDIAG의 기능을 포함 하고 있어 운영체제 이벤트 로그, 추적, 성능 카운터 등을 한번에 수집이 가능 하여 보다 쉽게 관련 데이터를 수집 할 수 있습니다. 그러나 이러한 이벤트나 카운터를 수정 해야 한다면 XML파일을 직접 수정 해야 하는 번거로움은 있습니다. 단순히 재 컴파일에 대해서 어떠한 원인으로 발생 되었는지 확인 하려면 아래와 같이 열 필터의 EventSubClass의 “값이 없는 행 제외”를 추가하여 추적하여 성능 모니터의 수치와 비교해 본다면 보다 쉽게 재 컴파일의 원인을 확인 할 수 있습니다. 성능 모니터를 통해 재 컴파일의 빈도를 찾기 위해서는 아래에 있는 카운터 중 SQL Re-Compilations/sec, Batch Requests/sec 을 추가하여 빈도를 확인 할 수 있습니다. SQLDIAG, 저장 프로시져를 이용하여 추적 하는 방법은 온라인 설명서를 참조하시면 됩니다. SQLdiag 유틸리티(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/sqlcmpt9/html/45ba1307-33d1-431e-872c-a6e4556f5ff2.htm) SQL Server 프로파일러 저장 프로시저(Transact-SQL)(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/8c99c3db-0b04-46c3-aa59-d6f680522fdd.htm) 
<그림 : 열 필터 설정> 
<그림: 열 필터 설정 후 프로파일러 모습>
<EventSubClass 컬럼 설명> 성능 모니터 (perfmon)의 SQL Statistics 카운터를 통해 분석 및 빈도를 확인 할 수 있습니다.
드디어 “SQL Server의 재 컴파일이란?” 아티클에 마지막입니다. 여기서 제가 가장 하고 싶은 말은 지연기록기가 어떠한 연산을 하여 실행계획을 제거되고 서버의 메모리 중 얼마만큼 프로시져 캐시로 사용할 수 있는 그러한 내용이 아닌 “환경에 따라 독이 될 수도 약이 될 수도 있는 것이 재 컴파일이다.” 입니다. 그러나 여기서는 재 컴파일이 발생 하지 않게 모니터링 방법 등만을 기술 하였습니다. 이것은 대부분의 환경에서는 재 컴파일이라는 것이 약 이 되는 경우가 많다는 것이며 항상 재 컴파일이 나쁘다는 것은 아닙니다. 그렇지만 재 컴파일이라는 것을 약 또는 독을 만드는 것은 사람이라고 생각합니다. 만약 독이 라면 어떻게 찾고 해결할까? 그렇지만 찾는 방법만 있지 이것을 여기서 해결 하는 방법은 기술 되지 않았습니다. 이 부분은 직접 테스트를 해보면서 배우고 느끼는 것이 가장 좋다고 생각하는 개인적인 의견입니다. ^^ 저는 SQL Server라는 DBMS를 접하게 된지 그리 오래 되지 않았습니다. 그래서 아직은 배워야 할 것 도 많다고 생각합니다. 그래서 더욱 재미있다고 생각합니다. DBMS라는 것에 대해서는 무엇보다 사람이 중요성이 다른 무엇보다 크다고 생각하기 때문입니다. 나중에 SQL Server 3000이 나와서 아무리 옵티마이져가 똑똑해졌다고 한다고 하더라도 사람이 해야 할 부분까지 SQL Server단에서 처리 하기는 힘들 거라고 생각합니다. 환경 및 비즈니스 로직에 따라서 사람의 단 하나의 판단으로 많은 차이가 날 수 있을 수 있기 때문입니다. 길지 않은 아티클을 작성하는데 생각보다 오랜 시간이 걸린 듯합니다. 지금 다시 보면 별 내용이 없는 것 같아 괜히 부끄러워지지만, 그래도 짧은 시간 동안 많은 공부를 할 수 있었고 정리를 하는 시간을 가져서 참 좋았습니다. 솔직히 이 재 컴파일이 주제가 정하기 전 여러 주제에 대해서 생각을 해보고 작성도 해보았지만 마음에 들지 않아 주제를 변경하기도 여러 번..아마 그 주제 들은 어딘가의 폴더 안에서 곤히 잠들고 있을 겁니다. 만약 다음에도 아티클로 다시 뵐 수 있는 기회가 온다면 더욱 열심히 준비하고 공부해서 좋은 아니 괜찮은 아티클로 다시 찾아 뵙겠습니다. |
'데이터베이스 > SQL Server' 카테고리의 다른 글
| 최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1) (0) | 2008.05.07 |
|---|---|
| 최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2) (0) | 2008.05.07 |
| 테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리 (0) | 2008.05.07 |
| 송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2) (0) | 2008.05.07 |
| master db 복구 SQL 2005 (0) | 2008.05.07 |