• 아티클

SQL Q&A메모리 구성, 성능 프로파일링, 채우기 비율 설정 및 기타 정보

편집자: Nancy Michell

메모리 구성

팁: 보다 쉬운 프로파일링

이제 SQL Server 2005 프로파일러와 PerfMon을 연결할 수 있습니다.

SQL Server 시스템의 성능 모니터에서 급격한 CPU 사용량 증가, 과도한 메모리 사용 또는 전체적인 성능 저하를 발견하고 이러한 성능 이상의 원인이 궁금한 적이 있을 것입니다. SQL Server 2005 이전 버전에서는 프로파일러를 사용하여 추적을 캡처한 다음 엔터프라이즈 관리자에서 sysprocess를 관찰하고 성능 모니터 로그를 캡처해야 했습니다. 물론 이렇게 하려면 PerfMon을 실행해야 했습니다. 이러한 도구를 모두 사용하여 작업한 뒤에도 이벤트를 도구 간에 수동으로 조정하여 성능 문제의 원인을 찾아야 했습니다. 즉, 모든 로그를 하나씩 검사해야 했습니다. 결코 재미있는 작업은 아니지만 성능 문제의 근본 원인을 밝히려면 이런 작업을 해야만 했습니다.

SQL Server 2005에서도 추적을 캡처하고 PerfMon 로그를 검사해야 하지만 이제는 프로파일러에서 이를 연결할 수 있습니다. T-SQL 문을 스크롤하면 어떤 작업이 수행되었는지 자동으로 표시됩니다. 프로파일러에서 성능 모니터 사용자 인터페이스를 클릭하면 해당 타임스탬프와 연결된 문으로 곧바로 이동할 수 있습니다. 이를 통해 SQL Server 환경의 문제 해결에 소요되는 시간이 크게 절약됩니다.

PerfMon 로그를 프로파일러에 연결하는 방법을 알아보겠습니다.

  1. 성능 모니터를 시작하고 데이터베이스 서버의 정보를 캡처합니다.
  2. 성능 로그 및 경고 아래에 새 카운터 로그를 만들고 새 로그의 이름을 입력합니다.
  3. % 프로세서 시간과 같은 새 카운터를 추가합니다. 로깅을 수동으로 시작하거나 일정 탭을 사용하여 일정에 따라 시작되도록 할 수 있습니다.
  4. 확인을 클릭하고 수동 옵션을 선택한 경우에는 로깅을 시작합니다.
  5. 프로파일러를 통해 SQL Server에 대한 추적을 설정합니다. 파일 메뉴에서 새 추적을 클릭하여 이를 수행할 수 있습니다. 추적에 StartTime 및 EndTime을 포함한 다음 추적의 이름을 지정하고 파일로 저장하도록 설정합니다. 마지막으로, 서버에서 일부 트랜잭션 활동을 시뮬레이션한 다음 성능 모니터와 프로파일러 데이터 캡처를 모두 중지합니다.
  6. 프로파일러의 파일 메뉴에서 성능 데이터 가져오기를 선택합니다. 그런 다음 PerfMon 로그를 저장한 위치를 선택하고 파일, 열기, 추적을 차례로 선택합니다. 마지막으로, 프로파일러 추적을 저장한 위치를 선택합니다.

이렇게 하면 특정 SQL 문이 처리 시간에 미치는 영향을 매우 쉽게 파악할 수 있습니다.

Q: SQL Server™ 시스템의 최적 메모리 구성을 알아보려고 합니다. 저의 전임 관리자는 SQL Server 2000 2노드 클러스터에서 12GB RAM이 장착된 각 시스템의 Boot.ini를 Yes /PAE NO /3GB(SQL Server에는 AWE를 설정하지 않음)로 설정했습니다. 12GB RAM을 사용할 수 있는 경우 Boot.ini의 /3GB 스위치를 제거하고, AWE를 설정하고, SQL Server에 12GB 중 10GB를 할당해야 할까요? 이 시스템에서는 SQL Server만 실행하므로 다른 응용 프로그램에는 메모리가 필요하지 않습니다.

A: 예. AWE(Address Windowing Extensions)를 설정하고 SQL Server에 대한 RAM의 상한을 미리 할당해야 합니다. 12GB의 전용 SQL Server 시스템이면 10GB가 적당한 듯합니다. 사전 할당은 SQL Server 2000에서만 유효함을 유의하십시오. SQL Server 2005부터는 AWE 사용이 더 이상 정적이 아니며 계속 변경될 수 있습니다. /3GB와 /PAE 스위치를 모두 사용할지 아니면 /PAE만 사용할지에 대해서는 항상 논란거리였습니다. /PAE를 설정하고 AWE를 사용할 수 있도록 설정하기만 하면 되지만 두 스위치 모두 사용할 것을 제안합니다. 하지만 몇 가지 고려해야 할 사항이 있습니다.

/3GB 스위치의 사용 여부는 사용자 특정 상황에 필요한지 여부에 따라 결정해야 합니다. 즉, 가상 주소 공간의 처음 2GB 또는 3GB에 있어야 하는 MemToLeave 메모리 영역이 부족한지 여부와 이 스위치를 설정하는 경우 운영 체제 메모리가 부족해지는지 여부를 고려해야 합니다. 자세한 내용은 support.microsoft.com/kb/316739를 참조하십시오. 클러스터에서 작업할 때는 하나의 노드에는 /3GB를 설정하고 다른 노드에는 설정하지 않는 방법도 있습니다. 이렇게 해서 /3GB를 사용하여 테스트했을 때 문제가 발생할 경우 인스턴스를 신속하게 다른 노드로 장애 조치할 수 있습니다. 16GB보다 큰 RAM이 있을 경우에는 /3GB 스위치가 지원되지 않음을 염두에 두어야 합니다.

/3GB를 사용하면 VAS(가상 주소 공간)가 50% 증가하므로 데이터 캐시뿐만 아니라 VAS에 사용할 메모리가 많이 부족한 응용 프로그램에 큰 이점이 있습니다. 다행히 64비트 서버 IA64 및 x64 모두에서는 이러한 오해의 소지가 없습니다. 시스템이 SQL Server 전용인 경우에는 OS 메모리 부족과 관련된 문제가 해당되지 않습니다. OS용으로 2GB를 할당하는 것도 다소 과합니다. 이 서버가 최소의 표준 OS 서비스만 실행하는 SQL Server 전용 시스템인 경우에는 서버에 약 1.3GB의 여유 메모리가 남는 것을 확인할 수 있으므로 SQL Server가 이 여분의 1GB를 사용하도록 할 수 있습니다. 10GB부터는 PerfMon을 사용하여 장시간에 걸쳐 가용 메모리를 모니터함으로써 얼마나 많이 유휴 상태에 놓이는지 확인하여 그에 따라 조정하십시오. AWE는 SQL Server 2005에서처럼 동적이지 않으므로 SQL Server 2000에서 오버 커밋하면 스와핑이 발생할 수 있음을 유의하십시오. /3GB 스위치 사용 여부를 결정하는 데 있어 중요한 점은 실제 환경에서 테스트해봐야 한다는 점입니다.

복제 인스턴스 이름

Q: 이제 서버의 IP 주소를 SQL Server 2005 복제에 사용하여 복제할 인스턴스를 지정할 수 있습니까? "트러스트되지 않은 도메인에서 또는 인터넷을 통해 실행되는 컴퓨터 간 복제"(support.microsoft.com/kb/321822)에 따르면 SQL Server 2000에서 이렇게 하면 오류가 발생한다고 하지만 이 경우에도 해당하는지 알고 싶습니다.

A: 서버 인스턴스가 복제에 참여하도록 지정할 때는 SQL Server에 등록된 인스턴스 이름을 제공해야 합니다. 예를 들어 Publisher 또는 Subscriber 매개 변수를 복제 저장 프로시저 또는 명령줄의 복제 에이전트 연결 설정에 지정할 때는 SQL Server 인스턴스 이름을 사용해야 합니다. SQL Server 인스턴스의 네트워크 이름이 등록된 인스턴스 이름과 다른 경우 에이전트에 의한 복제 연결이 실패합니다.

인스턴스의 네트워크 이름과 SQL Server 인스턴스 이름이 다른 경우에는 SQL Server 인스턴스 이름을 올바른 네트워크 이름으로 추가하는 방법을 고려하십시오. 대체 네트워크 이름을 설정하는 한 가지 방법은 로컬 호스트 파일에 이 이름을 추가하는 것입니다. 로컬 호스트 파일은 기본적으로 WINDOWS\system32\drivers\etc 또는 WINNT\system32\drivers\etc에 있습니다. 예를 들어 컴퓨터 이름이 comp1이고, 컴퓨터 IP 주소가 10.193.17.129이고, 인스턴스 이름이 inst1/instname이면 호스트 파일에 다음 항목을 추가합니다.

10.193.17.129 inst1

SQL Server Integration Services

Q: SQL Server 2005(두 개의 서버를 사용하는 64비트 Enterprise Edition)를 위한 액티브/액티브 클러스터를 설치하려고 하며 총 네 개의 SQL Server 2005 인스턴스를 사용하려고 합니다. 모든 인스턴스에 SSIS(SQL Server Integration Services)가 필요합니다. SSIS의 클러스터링에 대해 조언을 해주시고 유지 관리 계획에 어떤 영향을 미칠지 알려주십시오.

A: SSIS 서비스를 클러스터할 수는 있지만 반드시 그렇게 할 필요는 없습니다. 클러스터할 경우 위임이 지원되지 않고(msdn2.microsoft.com/aa337083 참조), 다중 인스턴스화되지 않는(한 번에 하나의 인스턴스만 노드에서 실행 가능) 등의 여러 문제가 발생할 수 있습니다.

이전에는 유지 관리 계획 마법사를 실행하려면 SSIS가 설치되어 있어야 했습니다(실행 중일 필요는 없고 설치만 되어 있으면 됨). 하지만 SQL Server 2005 SP1에서는 이러한 요구 사항이 없어졌습니다. SSIS가 실행되고 있지 않은 경우에는 SQL Server 에이전트에서 유지 관리 계획을 실행할 수 있습니다.

SSIS를 클러스터링하는 대신 독립 실행형 서비스로 실행되도록 하고 MsDtsSrvr.ini.xml을 편집하여 모든 실행 중인 인스턴스를 가리키도록 하는 방법을 고려할 수 있습니다. 이렇게 하면 모든 노드에서 패키지를 관리할 수 있으며 서비스 클러스터링과 관련된 문제 없이 대부분의 고객이 원하는 고가용성을 확보할 수 있습니다.

실패한 유지 관리 계획 작성에 대한 자세한 내용은 support.microsoft.com/kb/909036의 기술 자료 문서를 참조하십시오.

이상한 실행 시간

Q: SQL Server 2005 SP1 시스템에 대해 부하 테스트를 수행했더니 SQL Server 프로파일러가 여러 개의 음수 SP(저장 프로시저) 실행 값을 기록하고, SP 실행 시간이 종료 시간에서 시작 시간을 뺀 결과와 일치하지 않는 경우가 있었습니다.

A: SQL Server 프로파일러의 SP 실행 시간 및 기타 성능 시간 보고에 영향을 줄 수 있는 사항에는 여러 가지가 있습니다. SQL Server 2005에서는 실행 시간을 잴 때 밀리초를 사용하며, 측정 단위를 변경하는 기술을 사용하고 있으면 보고 및 실행 시간이 정확하지 않을 수 있습니다.

예를 들어 다른 전원 구성표, CPU 스테핑 또는 AMD Cool 'n Quiet 기술을 사용하면 CPU 주파수가 변경되어 SQL Server 프로파일러가 실행 시간을 계산할 때 예측한 값과 일치하지 않게 됩니다.

기술 자료 문서 support.microsoft.com/kb/931279에서 증상, 여러 원인 및 몇 가지 해결 방법을 볼 수 있습니다.

팁: 채우기 비율 확인

물을 가득 채운 유리컵이 있는데 이 컵에 물을 더 채우려고 한다고 가정해 보십시오. 어떻게 될까요? 물이 넘칠 것입니다.

SQL Server의 경우에도 마찬가지입니다. 가득 찬 인덱스 페이지에 새 행을 추가할 때마다 SQL Server는 새 행을 위한 공간을 마련하기 위해 전체 행의 약 절반을 새 페이지로 이동합니다. 이를 페이지 분할이라고 합니다. 페이지 분할을 통해 새 레코드를 위한 공간이 생기지만 시간이 소요되며 리소스가 많이 소비됩니다. 또한 I/O 작업에 부정적 영향을 줄 수 있는 조각화를 초래할 수 있습니다. 이 문제를 피하려면 어떻게 해야 할까요?

이러한 문제를 피하려면 채우기 비율 값을 사전에 확인해야 합니다. 인덱스를 만들거나 다시 작성할 때는 이 채우기 비율 값에 따라 각 리프 수준 페이지에서 데이터로 채워질 공간의 비율이 결정되고 나머지는 향후 확장에 대비하여 예약됩니다. 예를 들어 채우기 비율 값이 60이면 각 리프 수준 페이지의 40%는 기본 테이블에 데이터가 추가될 때 인덱스가 확장될 수 있도록 비어 있게 됩니다.

기본 채우기 비율 값은 항상 0이며 이는 대부분의 상황에 적합합니다. 기본적으로 채우기 비율 값 0은 리프 수준이 거의 용량 한계까지 채워지지만 최소 하나의 추가 인덱스 행을 위해 약간의 공간이 남겨진다는 의미입니다. 채우기 비율 값 0과 100은 비슷합니다.

CREATE INDEX 또는 ALTER INDEX 문에서 개별 인덱스에 대한 채우기 비율 값을 설정하거나 새로 작성되는 모든 인덱스가 기본값을 사용하도록 서버 수준에서 이 값을 직접 구성할 수 있습니다.

다음 예제에서는 서버 수준에서 채우기 비율 값을 70%로 설정하여 향후 확장에 대비하여 여유 공간을 30% 확보합니다. 물론 프로덕션 환경에서는 이 옵션을 구현하기 전에 신중하게 테스트해야 합니다.

USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO --변경 사항을 적용하려면 SQL Server Engine을 다시 시작해야 합니다.

참고: 프로그래머 주석은 예제 프로그램 파일에는 영문으로 제공되며 기사에는 이해를 돕기 위해 번역문으로 제공됩니다. 개별 인덱스 수준에서 채우기 비율을 구성하려면 어떻게 해야 할까요? 다음 테이블을 작성한 후 Col_A라는 열에 채우기 비율 값을 70으로 설정하여 고유한 인덱스를 만들려고 한다고 가정하겠습니다. 이 경우 명령은 다음과 같습니다.

--Item 테이블을 만듭니다. USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;

--Item 테이블의 Col_A 열에 채우기 비율 값을 70으로 설정하여 고유한 인덱스를 만듭니다. CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO

각 인덱스에 대한 채우기 비율은 어떻게 확인할까요? 다음과 같이 sys.Indexes를 쿼리하면 데이터베이스의 모든 인덱스에 대한 채우기 비율 값을 확인할 수 있습니다.

USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..