고성능 SQL Server 인스턴스 만들기

디스크로 Compute Engine VM 만들기

고성능 SQL Server 인스턴스를 만들려면 먼저 SQL Server와 두 개의 영구 디스크를 사용하여 VM 인스턴스를 만들어야 합니다.

영구 디스크 고려사항

VM의 영구 디스크 유형을 선택하려면 다음 고려사항을 검토하세요.

• 로컬 SSD는 tempdb와 Windows 페이지 파일을 위한 고성능 위치를 제공합니다.

  로컬 SSD를 사용할 때는 고려해야 하는 몇 가지 중요한 사항이 있습니다. Windows에서 인스턴스를 종료하거나 API를 사용하여 인스턴스를 재설정하면 로컬 SSD가 삭제됩니다. 이로 인해 인스턴스를 부팅할 수 없게 됩니다. 머신을 다시 실행하려면 영구 디스크를 분리하고 이 디스크로 새 인스턴스를 만든 다음 새 로컬 SSD를 정의해야 합니다. 시작 후에는 새 디스크를 포맷하고 재부팅해야 합니다. 따라서 중요한 데이터를 로컬 SSD에 영구 저장하면 안 되며, 인스턴스를 재구성할 준비가 되어 있지 않으면 인스턴스의 전원을 끄지 말아야 합니다.

• SSD 영구 디스크는 데이터베이스 파일을 위한 고성능 스토리지를 제공합니다.

  영구 디스크 성능은 CPU 수와 디스크 크기를 사용하여 계산합니다. 32개의 vCPU와 하나의 1TB 디스크를 사용할 경우 최고 성능은 초당 작업 수(operations per second, ops)를 기준으로 40,000번의 읽기 ops 및 30,000번의 쓰기 ops입니다. 읽기 및 쓰기의 총 지속 처리량은 각각 초당 800MB 및 초당 400MB입니다. 이 측정값은 C:\ 드라이브를 포함하여 가상 머신에 연결된 모든 영구 디스크의 합계를 나타냅니다. 따라서 로컬 SSD를 만들어 페이징 파일, tempdb, 스테이징 데이터 및 백업에 필요한 모든 IOPS를 오프로드해야 합니다.

디스크 성능에 대한 자세한 내용은 성능 요구사항을 충족하도록 디스크 구성을 참조하세요.

디스크로 Compute Engine VM 만들기

Windows Server 2022에 SQL Server 2022 Standard가 사전 설치된 VM을 만들려면 다음 단계를 따르세요.

1. Google Cloud 콘솔에서 인스턴스 만들기 페이지로 이동합니다.

   인스턴스 만들기로 이동

2. 이름에 ms-sql-server를 입력합니다.

3. 머신 구성 섹션에서 범용을 선택한 후 다음을 수행합니다.

   1. 계열 목록에서 N2를 클릭합니다.
   2. 머신 유형 목록에서 n2-highmem-16(vCPU 16개, 128GB 메모리)을 클릭합니다.

4. 부팅 디스크 섹션에서 변경을 클릭한 후 다음을 수행합니다.

   1. 공개 이미지 탭에서 운영체제 목록을 클릭한 다음 SQL Server on Windows Server를 선택합니다.
   2. 버전 목록에서 SQL Server 2022 Standard on Windows Server 2022 Datacenter를 클릭합니다.
   3. 부팅 디스크 유형 목록에서 표준 영구 디스크를 클릭합니다.
   4. 크기(GB) 필드에서 부팅 디스크 크기를 50GB로 설정합니다.
   5. 부팅 디스크 구성을 저장하려면 선택을 클릭합니다.

5. 고급 옵션 섹션을 펼친 후 다음을 수행합니다.

   1. 디스크 섹션을 펼칩니다.

   2. 로컬 디스크를 만들려면 로컬 SSD 추가를 클릭한 후 다음을 수행합니다.

      1. 인터페이스 목록에서 시스템 성능 요구사항을 충족하는 프로토콜을 선택합니다.
      2. 디스크 용량 목록에서 tempdb 파일의 예상 크기를 지원하는 디스크 용량을 선택합니다.
      3. 저장을 클릭하여 디스크 만들기를 마칩니다.

   3. 추가 디스크를 만들려면 새 디스크 추가를 클릭합니다.

      1. 이름 필드는 변경하지 않고 그대로 둡니다.
      2. 디스크 소스 유형 목록에서 빈 디스크를 선택합니다.
      3. 디스크 유형 목록에서 SSD 영구 디스크를 선택합니다.
      4. 크기 필드에 데이터베이스 크기를 수용할 수 있는 디스크 크기를 입력합니다.
      5. 두 번째 디스크 만들기를 완료하려면 저장을 클릭합니다.

6. VM을 만들려면 만들기를 클릭합니다.

Windows 구성

이제 SQL Server를 실행하는 작동하는 인스턴스가 있으므로 인스턴스에 연결하고 Windows 운영체제를 구성합니다. 그런 다음 뒤에 나오는 섹션에서 SQL Server를 구성하는 방법을 학습합니다.

내 인스턴스에 연결

1. Google Cloud Console에서 VM 인스턴스 페이지로 이동합니다.

   VM 인스턴스로 이동

2. 이름 열에서 인스턴스 이름 ms-sql-server를 클릭합니다.

3. 인스턴스의 세부정보 페이지 상단에서 Windows 비밀번호 설정 버튼을 클릭합니다.

4. 사용자 이름을 지정합니다.

5. 설정을 클릭하여 이 Windows 인스턴스의 새 비밀번호를 만듭니다.

6. 인스턴스에 로그인할 수 있도록 사용자 이름과 비밀번호를 기록합니다.

7. RDP를 사용하여 인스턴스에 연결합니다.

디스크 볼륨 설정

볼륨을 생성하여 포맷합니다.

1. 시작 메뉴에서 '컴퓨터 관리'를 검색하여 엽니다.
2. 스토리지 섹션에서 디스크 관리를 선택합니다.
3. 디스크를 초기화하라는 메시지가 표시되면 기본 선택을 수락하고 확인을 클릭합니다.

4. 로컬 SSD 디스크의 파티션을 만듭니다.

   로컬 SSD 디스크를 찾으려면 디스크를 마우스 오른쪽 버튼으로 클릭하고 속성을 선택합니다. 로컬 SSD 디스크 속성 이름은 SCSI 인터페이스의 경우 Google EphemeralDisk, NVMe 인터페이스의 경우 nvme_card입니다. 로컬 SSD와 영구 SSD 모두 Unallocated 파티션이 있는 것으로 표시됩니다.

   1. VM에 로컬 SSD 드라이브가 1개만 포함된 경우 다음 단계를 따르세요.

      1. 디스크 드라이브 목록에서 374.98GB 로컬 SSD 디스크를 마우스 오른쪽 버튼으로 클릭하고 새 단순 볼륨을 선택합니다.
      2. 시작 화면에서 다음을 클릭하여 디스크 볼륨 마법사를 시작합니다.
      3. 볼륨 크기 지정 단계에서 볼륨 크기를 기본값으로 두고 다음을 클릭하여 계속 진행합니다.
      4. 드라이브 문자 또는 경로 할당 단계에서 드라이브 문자로 P:를 선택하고 다음을 클릭하여 계속합니다.

      5. 볼륨 포맷 단계에서 할당 단위 크기를 8192로 변경하고 볼륨 라벨에 'pagefile'을 입력합니다. 다음을 클릭하여 계속합니다.

         

      6. 마침을 클릭하여 디스크 볼륨 마법사를 완료합니다.

   2. VM에 여러 로컬 SSD 드라이브가 포함된 경우 다음 단계를 따르세요.

      1. 디스크 드라이브 목록에서 처음 374.98GB 로컬 SSD 디스크를 마우스 오른쪽 버튼으로 클릭하고 새 스트라이프 볼륨을 선택합니다.
      2. 시작 화면에서 다음을 클릭하여 디스크 볼륨 마법사를 시작합니다.

      3. 디스크 선택 단계에서 크기가 383,982MB인 가용 디스크를 모두 선택된 섹션에 추가합니다. 다음을 클릭하여 계속합니다.

         

      4. 드라이브 문자 또는 경로 할당 단계에서 드라이브 문자로 P:를 선택하고 다음을 클릭하여 계속합니다.

      5. 볼륨 포맷 단계에서 할당 단위 크기를 8192로 변경하고 볼륨 라벨에 'pagefile'을 입력합니다. 다음을 클릭하여 계속합니다.

         

      6. 마침을 클릭하여 디스크 볼륨 마법사를 완료합니다.

5. 이전 단계를 반복하여 SSD 디스크에 새 단순 볼륨을 만들 때 다음 세 가지 변경사항을 적용합니다.

   • 드라이브 문자로 D:를 선택합니다.

   • 할당 단위 크기를 64k로 설정합니다.

     할당 단위 크기 선택에 대한 자세한 내용은 SQL Server 인스턴스 권장사항을 참조하세요.

   • 볼륨 라벨에 sqldata를 입력합니다.

Windows 페이징 파일 이동

새로운 볼륨이 생성되고 마운트되었으므로 이제 Windows 페이징 파일을 로컬 SSD로 이동합니다. 이렇게 하면 영구 디스크 IOPS가 해제되고 가상 메모리의 액세스 시간이 향상됩니다.

1. 시작 메뉴에서 고급 시스템 설정 보기를 검색한 다음 대화상자를 엽니다.
2. 고급 탭을 클릭하고 성능 섹션에서 설정을 클릭합니다.
3. 가상 메모리 섹션에서 변경 버튼을 클릭합니다.
4. 모든 드라이브에 대한 페이징 파일 크기 자동 관리 체크박스 선택을 취소합니다. 시스템의 C:\ 드라이브에 이미 페이징 파일이 설정되어 있어야 하며 이를 이동해야 합니다.
5. C:를 클릭한 다음 페이징 파일 없음 라디오 버튼을 클릭합니다.
6. 설정 버튼을 클릭합니다.
7. 새 페이징 파일을 만들려면 P: 드라이브를 클릭한 다음 시스템이 관리하는 크기 라디오 버튼을 클릭합니다.
8. 설정 버튼을 클릭합니다.

9. 확인을 세 번 클릭하여 고급 시스템 속성을 종료합니다.

   Microsoft 지원은 가상 메모리 설정과 관련한 추가 팁을 게시했습니다.

전원 프로필 설정

전원 프로필을 Balanced 대신 High-Performance로 설정합니다.

1. 시작 메뉴에서 '전원 관리 옵션 선택'을 검색하여 전원 옵션을 엽니다.
2. 고성능 라디오 버튼을 선택합니다.
3. 대화상자를 종료합니다.

SQL Server 구성

대부분의 관리 작업을 수행하는 데는 SQL Server Management Studio를 사용합니다. SQL Server의 사전 구성된 이미지에는 Management Studio가 이미 설치되어 있습니다. Management Studio를 시작한 다음 연결을 클릭하여 기본 데이터베이스에 연결합니다.

데이터 및 로그 파일 이동

SQL Server의 사전 구성된 이미지에는 시스템 데이터베이스를 비롯한 모든 구성요소가 C:\ 드라이브에 설치된 상태입니다. 설정을 최적화하려면 이 파일들을 새로 만든 D:\ 드라이브로 이동하세요. 또한 모든 새 데이터베이스도 D:\ 드라이브에 만들어야 합니다. SSD를 사용하기 때문에 별도의 디스크 파티션에 데이터 파일과 로그 파일을 저장할 필요는 없습니다.

설치를 보조 디스크로 이동하는 방법에는 두 가지 방법, 즉 설치 프로그램을 사용하거나 파일을 수동으로 이동하는 방법이 있습니다.

설치 프로그램 사용

설치 프로그램을 사용하려면 c:\setup.exe를 실행하고 보조 디스크에서 새 설치 경로를 선택합니다.

수동으로 파일 이동

시스템 데이터베이스를 이동하고 동일한 볼륨에 데이터 및 로그 파일을 저장하도록 SQL Server를 구성합니다.

1. D:\SQLData라는 새 폴더를 만듭니다.
2. 명령어 창을 엽니다.

3. 다음 명령어를 입력하여 NT Service\MSSQLSERVER에 대한 모든 액세스 권한을 부여합니다.

   icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

4. Management Studio와 다음 가이드를 사용하여 시스템 데이터베이스를 이동하고 새 데이터베이스의 기본 파일 위치를 변경합니다.

5. 보고서 서버 기능을 사용하려는 경우 ReportServer 및 ReportServerTempDB 파일도 함께 이동합니다.

기본 구성 데이터베이스 파일을 옮기고 다시 시작한 후에는 모델 및 MSDB 데이터베이스의 새 위치를 가리키도록 시스템을 구성해야 합니다. Management Studio에서 실행되는 helper 스크립트는 다음과 같습니다.

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

이러한 명령어를 실행한 후 다음 안내를 따르세요.

1. services.msc 스냅인을 사용하여 SQL Server 데이터베이스 서비스를 중지합니다.
2. Windows 파일 탐색기를 사용하여 실제 파일을 master 데이터베이스가 있던 C:\ 드라이브에서 D:\SQLData 디렉터리로 이동합니다.
3. SQL Server 데이터베이스 서비스를 시작합니다.

시스템 권한 설정

시스템 데이터베이스를 이동한 후에는 NT Service\MSSQLSERVER라는 SQL Server 프로세스를 실행하기 위해 만든 Windows 사용자 계정의 권한부터 시작해서 몇 가지 추가 설정을 수정합니다.

Lock Pages in Memory 권한 부여

그룹 정책 Lock Pages in Memory 권한은 Windows가 실제 메모리의 페이지를 가상 메모리로 이동하지 못하게 합니다. 실제 메모리를 여유 있고 체계적인 상태로 유지하기 위해 Windows는 오래되고 거의 수정되지 않은 페이지를 디스크의 가상 메모리 페이징 파일로 교체하려고 시도합니다.

SQL Server는 테이블 구조, 실행 계획, 캐시된 쿼리와 같은 중요한 정보를 메모리에 저장합니다. 이러한 정보 중 일부는 거의 변경되지 않으므로 페이징 파일의 대상이 됩니다. 이 정보가 페이징 파일로 이동하면 SQL Server 성능이 저하될 수 있습니다. SQL Server의 서비스 계정에 그룹 정책 Lock Pages in Memory 권한을 부여하면 이러한 바꾸기가 방지됩니다.

다음 단계를 따르세요.

1. 시작을 클릭한 다음 그룹 정책 편집을 검색하여 콘솔을 엽니다.
2. 로컬 컴퓨터 정책 > 컴퓨터 구성 > Windows 설정 > 보안 설정 > 로컬 정책 > 사용자 권한 할당을 펼칩니다.
3. 메모리에 페이지 잠그기를 찾아서 더블클릭합니다.
4. 사용자 또는 그룹 추가를 클릭합니다.
5. 'NT Service\MSSQLSERVER'를 검색합니다.
6. 여러 이름이 표시되면 MSSQLSERVER 이름을 더블클릭합니다.
7. 확인을 두 번 클릭합니다.
8. 그룹 정책 편집기 콘솔을 열어 둡니다.

Perform volume maintenance tasks 권한 부여

기본적으로 애플리케이션이 Windows의 디스크 공간을 요청하면 운영체제는 적절한 크기의 디스크 공간을 찾은 다음 애플리케이션에 전달하기 전에 전체 디스크 청크를 0으로 설정합니다. SQL Server를 사용하면 쉽게 파일이 증가하고 디스크 공간이 소모되므로 이러한 동작은 최적의 대응이 아닙니다.

애플리케이션에 디스크 공간을 할당하기 위한 별도의 API가 있으며, 이러한 할당을 빠른 파일 초기화라고 합니다. 이 설정은 데이터 파일에만 적용되지만, 다음 섹션에서는 로그 파일 증가에 대해 알아봅니다. 인스턴트 파일을 초기화하려면 SQL Server 프로세스를 실행하는 서비스 계정에 Perform volume maintenance tasks라는 또 다른 그룹 정책 권한이 있어야 합니다.

1. 그룹 정책 편집기에서 '볼륨 유지보수 작업 수행'을 검색합니다.
2. 이전 섹션에서 한 것처럼 'NT Service\MSSQLSERVER' 계정을 추가합니다.
3. SQL Server 프로세스를 다시 시작하여 두 설정을 모두 활성화합니다.

tempdb 설정

SQL Server CPU 사용량을 최적화하기 위해 CPU별로 tempdb 파일을 하나씩 만드는 것은 권장되는 방법이었습니다. 하지만 시간이 지남에 따라 CPU 수가 증가했기 때문에 이제는 이 지침을 따르면 성능이 저하될 수 있습니다. 처음에는 4개의 tempdb 파일을 사용하는 것으로 시작하면 좋습니다. 시스템 성능을 측정할 때 드물기는 하지만 tempdb 파일의 수를 최대 8개까지 점진적으로 늘려야 하는 경우도 있습니다.

SQL Server Management Studio에서 Transact-SQL(T-SQL) 스크립트를 실행하여 tempdb 파일을 `p:` 드라이브의 폴더로 이동할 수 있습니다.

1. p:\tempdb 디렉터리를 만듭니다.

2. 'NT Service\MSSQLSERVER' 사용자 계정에게 모든 보안 액세스 권한을 부여합니다.

   icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

3. SQL Server Management Studio에서 다음 스크립트를 실행하여 tempdb 데이터 파일과 로그 파일을 이동합니다.

   USE master
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
   GO
   

4. SQL Server를 다시 시작합니다.

5. 다음 스크립트를 실행하여 파일 크기를 수정하고 새 tempdb에 대한 3개의 추가 데이터 파일을 만듭니다.

   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   GO
   

   SQL Server 2016을 사용하는 경우 앞의 단계를 수행하고 나면 삭제할 tempdb 파일이 3개 있습니다.

   ALTER DATABASE [tempdb] REMOVE FILE temp2;
   ALTER DATABASE [tempdb] REMOVE FILE temp3;
   ALTER DATABASE [tempdb] REMOVE FILE temp4;
   

6. SQL Server를 다시 시작합니다.

7. model, MSDB, master, tempdb 파일을 C:\ 드라이브의 원래 위치에서 삭제합니다.

이제 tempdb 파일이 로컬 SSD 파티션으로 이동되었습니다. 이렇게 하면 앞에서 설명했듯이 다소 위험이 있지만, 어떤 이유로든 이 파일이 손실되면 SQL Server가 tempdb 파일을 다시 빌드합니다. tempdb를 이동하면 로컬 SSD의 성능이 향상되고 영구 디스크에서 사용되는 IOPS가 감소합니다.

max degree of parallelism 설정

max degree of parallelism에 권장되는 기본 설정은 서버의 CPU 수와 일치시키는 것입니다. 하지만 쿼리를 16개 또는 32개의 병렬 청크로 실행하고 결과를 병합하는 방식이 단일 프로세스로 실행하는 방식보다 훨씬 느립니다. 16개 또는 32개의 코어 인스턴스를 사용하는 경우 다음과 같은 T-SQL을 사용하여 max degree of parallelism 값을 8로 설정할 수 있습니다.

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

max server memory 설정

이 설정은 기본값이 매우 높지만 사용 가능한 실제 RAM의 MB 수에서 운영체제 및 오버헤드를 감안하여 몇 기가바이트를 뺀 값으로 설정해야 합니다. 다음 T-SQL 예시에서는 max server memory를 100GB로 조정합니다. 이 값을 수정하여 인스턴스에 맞게 조정하세요. 자세한 내용은 서버 메모리 서버 구성 옵션 문서를 참조하세요.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

완료

인스턴스를 한 번 더 다시 시작하여 새 설정이 모두 적용되도록 합니다. 이제 SQL Server 시스템이 구성되었으며 자체 데이터베이스를 만들고 특정 워크로드를 테스트할 준비가 되었습니다. 운영 활동, 기타 성능 고려사항, Enterprise Edition 기능에 대한 자세한 내용은 SQL Server 권장사항 가이드를 참조하세요.