SQL 서버 인스턴스를 위한 권장사항

여러 권장사항을 적용하여 Microsoft SQL Server를 실행하는 Google Compute Engine 인스턴스를 최적화할 수 있습니다. 고성능 SQL Server 인스턴스를 설정하는 방법을 알아보려면 고성능 SQL Server 인스턴스 만들기를 참조하세요.

Windows 구성

이 섹션에서 다루는 구성 주제는 Compute Engine에서 실행 시 SQL Server 성능에 맞춰 Microsoft Windows 운영체제를 최적화하는 방법에 대한 것입니다.

Windows 방화벽 설정

권장사항: Windows Server 고급 방화벽을 사용하고 클라이언트 컴퓨터의 IP 주소를 지정하세요.

Windows 고급 방화벽은 Windows Server에서 중요한 보안 구성요소입니다. 다른 클라이언트 컴퓨터의 데이터베이스에 연결할 수 있도록 SQL Server 환경을 설정할 때 수신 트래픽을 허용하도록 방화벽을 구성하세요.

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

이 방화벽 규칙을 사용할 때는 클라이언트 컴퓨터의 IP 주소를 지정하는 것이 좋습니다. [LOCAL_SUBNET] 대신 remoteip 매개변수를 공백 없이 사용해 쉼표로 구분된 IP 주소 목록을 지정합니다. 사용하는 SQL Server 버전에 따라 program 매개변수의 경로가 달라질 수 있으니 유의하세요.

SQL Server 애플리케이션 이미지에는 SQL Server Windows 방화벽 규칙이 포함되어 있습니다. 이 규칙에는 제한이 적용되지 않으므로 시스템을 프로덕션으로 전환하기 전에 사용 중지하는 것이 좋습니다.

네트워크 연결 조정

권장사항: 운영체제의 기본 네트워크 설정을 사용하세요.

일반적으로 운영체제의 기본 네트워크 설정은 적당히 빠른 속도의 네트워크에 연결된 소형 컴퓨터의 연결에 맞춰 구성되어 있습니다. 보통은 이러한 설정만으로도 충분합니다. 또한 이렇게 보수적으로 기본값을 설정하면 네트워크 및 연결된 컴퓨터에 과도한 네트워크 트래픽이 수신되는 것을 방지합니다.

Compute Engine에서 가상 머신 인스턴스는 용량 및 성능이 우수한 Google에서 설계한 네트워크에 연결됩니다. Compute Engine 인스턴스를 실행하는 물리적 서버는 이 같은 네트워크 용량을 활용하도록 최적화되어 있습니다. 인스턴스의 가상 네트워크 드라이버 역시 최적화되어 있어 대부분의 사용 사례에 기본값이면 충분합니다.

바이러스 백신 설치

권장사항: Microsoft의 바이러스 백신 소프트웨어 안내를 따르세요.

Windows를 실행하는 경우 바이러스 백신 소프트웨어를 실행해야 합니다. 멀웨어 및 소프트웨어 바이러스는 네트워크에 연결된 모든 시스템에 심각한 위험을 초래합니다. 이러한 위협으로부터 데이터를 보호할 수 있는 간단한 완화 조치가 바로 바이러스 백신 소프트웨어입니다. 하지만 바이러스 백신 소프트웨어가 적절하게 구성되지 않으면 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. Microsoft는 바이러스 백신 소프트웨어를 선택하는 방법에 관한 조언을 제공하고 있습니다.

성능 및 안정성 최적화

이 섹션에서는 Compute Engine에서 SQL Server 성능을 최적화하는 방법과 원활한 실행을 도와줄 작업 활동에 대한 정보를 제공합니다.

데이터 파일 및 로그 파일을 새 디스크로 이전하기

권장사항: 로그 및 데이터 파일은 별도의 SSD 영구 디스크에 보관하세요.

SQL Server용으로 사전 구성된 이미지는 기본적으로 부팅 영구 디스크에 설치되는 모든 항목과 함께 제공되어 `C:` 드라이브로 마운트됩니다. 보조 SSD 영구 디스크를 연결해 로그 파일과 데이터 파일을 새 디스크로 이전해 보세요.

로컬 SSD를 사용한 IOPS 개선

권장사항: 한 개 이상의 로컬 SSD로 새 SQL Server 인스턴스를 만들어 tempdb 및 Windows 페이징 파일을 저장하세요.

로컬 SSD 기술은 단기적인 특성으로 인해 중요한 데이터베이스 및 파일에 사용하기에는 부적절합니다. 하지만 tempdb 및 Windows 페이징 파일 모두 임시 파일이므로 로컬 SSD로 이전해도 전혀 문제가 없으며 SSD 영구 디스크에서 상당수의 IO 작업이 오프로드됩니다. 설정에 대한 자세한 내용은 여기를 참조하세요.

병렬 쿼리 처리

권장사항: max degree of parallelism8로 설정하세요.

max degree of parallelism에 권장되는 기본 설정은 서버의 CPU 수와 일치시키는 것입니다. 하지만 쿼리를 16개 또는 32개의 단위로 분할해 모두 서로 다른 vCPU에서 실행한 후 단일 결과로 다시 통합할 경우 하나의 vCPU에서만 쿼리를 실행할 때보다 훨씬 많은 시간이 소요됩니다. 실제로는 8이 적절한 기본값입니다.

권장사항: CXPACKET 대기를 모니터링하고 cost threshold for parallelism을 점차적으로 늘리세요.

이 설정은 max degree of parallelism과 함께 사용됩니다. 각 단위는 동시 실행 계획을 고려하기 전, 연속 실행 계획으로 쿼리를 수행할 때 필요한 CPU 및 IO 작업의 조합을 나타냅니다. 기본값은 5입니다. 기본값 변경에 대한 특별한 권장사항은 없지만 주시하고 있다가 필요한 경우 로드 테스트 중에 5씩 점차적으로 늘리는 것이 좋습니다. 이 값을 늘려야 할지 여부는 CXPACKET 대기의 존재로 알 수 있습니다. CXPACKET 대기가 있다고 해서 반드시 설정을 변경해야 한다는 뜻은 아니지만, 필요성을 알려주는 효과적인 지표입니다.

권장사항: 여러 대기 유형을 모니터링하고 전역 병렬 처리 설정을 조정하거나 개별 데이터베이스 수준으로 설정하세요.

데이터베이스마다 동시 로드 요건이 다를 수 있습니다. 이러한 경우 이 설정을 전역적으로 설정하고 Max DOP를 개별 데이터베이스 수준으로 설정하면 됩니다. 고유의 작업 부하를 관찰하고 대기를 모니터링한 후 그에 따라 값을 조정해야 합니다.

SQLSkills 사이트에서 데이터베이스 내부의 대기 통계에 대해 다룬 유용한 성능 가이드를 제공합니다. 이 가이드를 보면 대기 항목과 지연을 완화하는 방법을 이해할 수 있습니다.

트랜잭션 로그 처리

권장사항: 시스템의 트랜잭션 로그 증가를 모니터링하세요. 자동 증가를 사용 중지하고 일일 평균 로그 축적량에 따라 로그 파일을 고정된 크기로 설정하는 것이 좋습니다.

성능 손실 및 간헐적인 속도 저하의 원인으로 가장 간과되는 것 중 하나가 바로 트랜잭션 로그의 증가를 관리하지 않는 것입니다. Full 복구 모델을 사용하도록 데이터베이스를 구성하면 언제든 복원을 수행할 수 있지만 트랜잭션 로그가 금방 한도에 다다릅니다. 기본적으로 트랜잭션 로그 파일이 한도에 도달하면 SQL Server에서 트랜잭션을 더 작성할 수 있도록 파일 크기를 늘려 빈 공간을 추가하며 이 작업이 완료될 때까지 데이터베이스에서 모든 활동을 차단합니다. SQL Server에서는 최대 파일 크기파일 증가 설정을 바탕으로 각 로그 파일의 크기를 늘립니다.

최대 크기 한도에 도달하여 파일을 늘릴 수 없는 경우에는 시스템에서 9002 오류를 반환하고 데이터베이스를 읽기 전용 모드로 전환합니다. 파일을 늘릴 수 있을 때는 SQL Server에서 파일 크기를 확장하고 빈 공간을 없앱니다. 파일 증가 설정의 기본값은 현재 로그 파일 크기의 10%입니다. 파일이 커질수록 새로운 빈 공간을 만드는 데 오랜 시간이 걸리므로 성능 면에서는 효과적인 기본 설정이 아닙니다.

권장사항: 트랜잭션 로그의 정기 백업을 예약하세요.

최대 크기 및 증가 설정과 상관없이 정기적인 트랜잭션 로그 백업을 예약하세요. 기본적으로 오래된 로그 항목을 자르고 시스템에서 기존 파일 공간을 재사용할 수 있습니다. 이 간단한 유지관리 작업을 통해 트래픽이 급증하는 시기에 성능이 저하되는 것을 방지할 수 있습니다.

가상 로그 파일 최적화

권장사항: 가상 로그 파일 증가를 모니터링하고 조치를 취해 로그 파일 분할을 방지하세요.

물리적 트랜잭션 로그 파일은 가상 로그 파일(VLF)로 분할됩니다. 물리적 트랜잭션 로그 파일을 늘려야 할 때마다 새로운 VLF가 생성됩니다. 자동 증가를 사용 중지하지 않은 상태에서 증가 빈도가 너무 잦으면 VLF가 과도하게 생성됩니다. 이 활동으로 로그 파일 분할이 발생할 수 있습니다. 로그 파일 분할은 디스크 분할과 유사하며, 성능에 부정적인 영향을 미칠 수 있습니다.

SQL Server 2014에는 자동 증가 중에 생성되는 VLF 수를 결정하는 보다 효율적인 알고리즘이 도입되었습니다. 일반적으로 증가가 현재 로그 파일 크기의 1/8에 못 미칠 경우 SQL Server에서 해당하는 새 세그먼트 안에 VLF 1개를 만듭니다. 이전에는 64MB에서 1GB 사이로 증가할 경우 8개의 VLF를 생성했고 1GB를 초과하여 증가할 경우에는 16개의 VLF를 생성했습니다. 아래의 TSQL 스크립트를 사용하면 현재 데이터베이스에 존재하는 VLF 수를 확인할 수 있습니다. 파일이 수천 개인 경우 직접 로그 파일을 축소해 크기를 조절하는 것이 좋습니다.

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

VLF에 대한 자세한 내용은 Brent Ozar 웹사이트를 참조하세요.

색인 분할 방지

권장사항: 가장 많이 수정되는 테이블의 색인을 정기적으로 조각 모음하세요.

테이블의 색인이 분할될 수 있으며 이 경우 해당 색인을 사용하는 쿼리의 성능이 저하될 수 있습니다. 가장 많이 수정되는 테이블의 색인을 재구성하는 작업을 정기적인 유지관리 일정에 포함시키세요. 데이터베이스에 다음 T-SQL 스크립트를 실행하면 색인과 분할 비율을 확인할 수 있습니다. 예제의 결과를 보면 PK_STOCK 색인이 95% 분할된 것을 알 수 있습니다. 다음 'SELECT' 문에서 '[YOUR_DB]'를 데이터베이스 이름으로 바꿉니다.

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

색인이 과도하게 분할된 경우 간단한 ALTER 스크립트를 사용해 재구성할 수 있습니다. 다음은 각 테이블 색인에 실행할 수 있는 ALTER 문을 보여주는 스크립트 예입니다.

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

결과 조합에서 분할 비율이 가장 높은 테이블을 선택한 후 이 문을 점차적으로 실행합니다. 이 스크립트 또는 유사한 스크립트를 정기 유지관리 작업 중 하나로 예약하는 것이 좋습니다.

백업

권장사항: 백업 계획을 세우고 정기적으로 백업을 수행하세요.

Ola Hallengren 사이트를 참고하면 탄탄한 백업 및 유지관리 계획을 구현하는 방법을 이해하는 데 도움이 됩니다.

정기적으로 데이터베이스 백업을 수행할 때는 영구 디스크 IOPS를 과도하게 사용하지 않도록 주의하세요. 로컬 SSD를 사용해 백업을 준비한 후 Cloud Storage 버킷에 푸시합니다.

모니터링

권장사항: Stackdriver Monitoring을 사용하세요.

Microsoft Windows용 Stackdriver Monitoring 에이전트를 설치해 여러 모니터링 데이터 요소를 Stackdriver 시스템에 전송할 수 있습니다.

데이터 수집 기능을 사용하면 모니터링할 정보를 조정하고 내장된 관리 데이터 웨어하우스에 전송할 수 있습니다. 관리 데이터 웨어하우스를 모니터링 중인 동일 서버에서 실행하거나 웨어하우스를 실행 중인 다른 SQL Server 인스턴스로 데이터를 스트리밍할 수도 있습니다.

데이터 일괄 로드

권장사항: 별도의 데이터베이스를 사용해 일괄 데이터를 준비 및 변환한 후 프로덕션 서버로 이전하세요.

정기적으로는 아니더라도 적어도 한 번은 시스템에 많은 양의 데이터를 로드해야 할 수 있습니다. 이는 리소스가 많이 필요한 작업으로 일괄 로드 시 영구 디스크 IOPS 한도에 도달할 수도 있습니다.

일괄 로드 작업의 디스크 IO 및 CPU 사용량을 줄이면서 일괄 작업의 실행 시간도 단축할 수 있는 간단한 방법을 소개합니다. 바로 Simple 복구 모델을 사용하는 별도의 데이터베이스를 만들고 이 데이터베이스를 사용해 일괄 데이터세트를 준비 및 변환한 후 프로덕션 데이터베이스에 삽입하는 것입니다. 공간이 충분한 경우 로컬 SSD 드라이브에 새 데이터베이스를 저장할 수도 있습니다. 그러면 일괄 작업의 리소스 사용량은 물론 작업을 완료하는 데 걸리는 시간도 줄어듭니다. 프로덕션 데이터의 백업 작업 시 트랜잭션 로그의 모든 일괄 작업을 백업할 필요가 없어 크기가 작고 실행 속도가 빠르다는 이점도 있습니다.

설정 확인

권장사항: 구성 테스트를 통해 예상했던 것처럼 작동하는지 확인하세요.

새 시스템을 설정할 때마다 구성 확인 및 성능 테스트 계획을 세워야 합니다. 여기에 저장된 절차는 SQL Server 구성을 평가하는 데 유용한 자료입니다. 나중에 시간이 될 때 구성 플래그에 대해 자세히 알아보고 절차를 실행하세요.

SQL Server Enterprise 버전 최적화

SQL Server Enterprise 버전에는 Standard 버전보다 더 많은 기능이 포함되어 있습니다. 기존 라이선스를 GCP로 마이그레이션하는 경우 구현을 고려해 볼 만한 성능 옵션이 몇 가지 있습니다.

압축 테이블 사용

권장사항: 테이블 및 색인 압축을 사용 설정하세요.

테이블을 압축하면 시스템 성능이 빨라질 것이라고 생각할 수 있는데 보통은 그렇지 않습니다. 대신 적은 양의 CPU 주기를 사용해 데이터를 압축하고 큰 블록을 읽고 쓰는 데 필요한 추가 디스크 IO를 제거하는 절충안을 권장합니다. 일반적으로 시스템에서 사용하는 디스크 IO가 적을수록 성능이 향상됩니다. 테이블 및 색인 압축의 추정 및 사용 설정에 대한 안내는 MSDN 웹사이트를 참조하세요.

버퍼 풀 확장 사용 설정

권장사항: 버퍼 풀 확장을 사용해 데이터 액세스 속도를 높이세요.

버퍼 풀은 시스템에서 클린 페이지를 저장하는 곳입니다. 간단히 말해 디스크의 데이터 상태를 그대로 반영한 데이터 복사본을 저장합니다. 메모리에서 데이터가 변경되면 더티 페이지라고 부르며 이 경우 디스크로 플러시해 변경사항을 저장해야 합니다. 데이터베이스가 가용 메모리보다 클 경우 버퍼 풀에 부담을 줘 클린 페이지가 삭제될 수 있습니다. 클린 페이지가 삭제되면 이후 시스템에서 삭제된 데이터에 액세스해야 할 경우 디스크에서 데이터를 읽어야 합니다.

버퍼 풀 확장 기능을 사용하면 클린 페이지를 삭제하는 대신 로컬 SSD로 푸시할 수 있습니다. 이는 교체라는 단어로 설명할 수 있으며, 가상 메모리와 동일한 방식으로 로컬 SSD의 클린 페이지에 대한 액세스를 제공하여 일반 디스크로 이동해 데이터를 가져오는 것보다 훨씬 빠릅니다.

이 기법은 메모리가 충분할 때만큼 속도가 빠르진 않지만 가용 메모리가 적을 경우에는 처리량이 소폭 증가할 수 있습니다. 버퍼 풀 확장에 대한 자세한 내용과 벤치마킹 결과는 Brent Ozar 사이트를 참조하세요.

다음 단계

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

Compute Engine 문서