SQL Server 인스턴스 관련 권장사항

여러 권장사항을 적용하여 Microsoft SQL Server를 실행하는 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에서 가상 머신(VM) 인스턴스는 용량 및 성능이 우수한 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 영구 디스크에서 상당수의 I/O 작업이 오프로드됩니다. 이를 설정하는 방법에 대한 자세한 내용은 TempDB 설정을 참조하세요.

병렬 쿼리 처리

권장사항: max degree of parallelism을 8로 설정합니다.

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

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

이 설정은 max degree of parallelism과 함께 사용됩니다. 각 단위는 동시 실행 계획을 고려하기 전, 연속 실행 계획으로 쿼리를 수행할 때 필요한 CPU 및 I/O 작업의 조합을 나타냅니다. 기본값은 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 YOUR_DB
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

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

색인 분할 방지

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

테이블의 색인이 분할될 수 있으며 이 경우 해당 색인을 사용하는 쿼리의 성능이 저하될 수 있습니다. 가장 많이 수정되는 테이블의 색인을 재구성하는 작업을 정기적인 유지보수 일정에 포함하세요. 데이터베이스에 다음 Transact-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'

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

다른 위치의 데이터베이스를 Google Cloud로 복원하는 경우, 색인을 다시 빌드하거나 최소한 다시 구성한 후 사용을 업데이트해야 합니다. 여기서 YOUR_DB는 데이터베이스 이름으로 바꿉니다.

dbcc updateusage (YOUR_DB) GO USE YOUR_DB
GO

보조 디스크 포맷

권장사항: 보조 디스크를 64KB 할당 단위로 포맷합니다.

SQL Server는 익스텐트라는 스토리지 단위로 데이터를 저장합니다. 익스텐트의 크기는 64KB이며 크기가 8KB인 연속 메모리 페이지 8개로 구성됩니다. 64KB 할당 단위로 디스크를 포맷하면 SQL Server에서 익스텐트를 더 효율적으로 읽고 쓸 수 있으므로 디스크의 I/O 성능이 향상됩니다.

보조 디스크를 64KB 할당 단위로 포맷하려면 다음 PowerShell 명령어를 실행하여 시스템의 모든 새 디스크 및 초기화되지 않은 디스크를 검색하고 64KB 할당 단위로 디스크를 포맷합니다.

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

백업

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

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

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

모니터링

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

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

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

데이터 일괄 로드

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

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

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

설정 확인

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

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

SQL Server Enterprise 버전 최적화

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

압축 테이블 사용

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

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

버퍼 풀 확장 사용 설정

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

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

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

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

SQL Server 라이선스 최적화

동시 멀티스레딩(SMT)

권장사항: 대부분의 SQL Server 워크로드의 코어당 스레드 수를 1로 설정하세요.

일반적으로 Intel 프로세서에서 하이퍼 스레딩 기술(HTT)이라고 하는 동시 멀티스레딩(SMT)은 단일 CPU 코어를 2개의 스레드로 논리적으로 공유할 수 있는 기능입니다. Compute Engine에서 SMT는 기본적으로 대부분의 VM에 사용 설정됩니다. 즉, VM의 각 vCPU가 단일 스레드에서 실행되고 각 물리적 CPU 코어는 2개의 vCPU에서 공유됩니다.

Compute Engine에서는 코어당 스레드 수를 구성하여 SMT를 효과적으로 중지할 수 있습니다. 코어당 스레드 수를 1로 설정하면 vCPU가 물리적 CPU 코어를 공유하지 않습니다. 이 구성은 Windows Server 및 SQL Server의 라이선스 비용에 큰 영향을 줍니다. 코어당 스레드 수를 1로 설정하면 VM의 vCPU 수가 절반으로 줄어들어 필요한 Windows Server 및 SQL Server 라이선스의 절반이 줄어듭니다. 이렇게 하면 총 워크로드 비용이 크게 감소할 수 있습니다.

하지만 코어당 스레드 수를 구성하면 워크로드 성능에도 영향을 줍니다. 멀티 스레드로 작성된 애플리케이션은 컴퓨팅 작업을 여러 논리 코어에 예약된 작은 병렬화 가능한 청크로 분할하여 이 기능을 활용할 수 있습니다. 이러한 작업 동시 로드는 사용 가능한 코어 리소스를 더 효과적으로 활용하여 전체 시스템 처리량을 증가시키는 경우가 많습니다. 예를 들어 한 스레드가 중단되면 다른 스레드가 코어를 활용할 수 있습니다.

SMT 구현은 하드웨어 세대마다 다르므로 SMT가 SQL Server에 미치는 정확한 성능 영향은 워크로드 특성과 사용되는 하드웨어 플랫폼에 따라 다릅니다. OLTP 워크로드와 같이 소량의 트랜잭션이 많은 워크로드는 SMT를 활용하고 성능 향상이라는 이점을 얻을 수 있는 경우가 많습니다. 반면에 동시 로드가 적은 워크로드(예: OLAP)의 경우 SMT에 비해 이점이 적습니다. 일반적으로 이러한 패턴을 보였지만 워크로드당 SMT의 성능 영향을 평가하여 코어당 스레드 수를 1로 설정할 때의 영향을 결정하는 것이 좋습니다.

대부분의 SQL Server 워크로드에 대한 가장 비용 효율적인 구성은 코어당 스레드 수를 1로 설정하는 것입니다. 더 많은 VM을 활용하면 성능 저하를 상쇄할 수 있습니다. 대부분의 경우 라이선스 비용의 50% 감소는 더 증가한 VM의 비용보다 큽니다.

예: SQL Server가 `n2-standard-16` 구성에 배포되는 것 고려하기

기본적으로 운영체제에 표시되는 코어 수는 16개이므로 서버를 실행하려면 Windows Server의 vCPU 16개와 SQL Server 라이선스의 vCPU 16개가 필요합니다.

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

SQL Server에서 SMT를 사용 중지하는 단계를 완료하면 새 구성이 다음과 같이 됩니다.

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

이제 운영체제에 8개의 코어만 표시되므로 Windows Server 및 SQL Server를 실행하려면 8개의 vCPU만 서버에 필요합니다.