Membuat instance SQL Server berperforma tinggi

Tutorial ini menunjukkan cara membuat instance VM Compute Engine yang menjalankan SQL Server yang dioptimalkan untuk performa. Tutorial ini akan memandu Anda dalam membuat instance, lalu mengonfigurasi SQL Server untuk mendapatkan performa yang optimal di Google Cloud. Anda akan mempelajari sejumlah opsi konfigurasi yang tersedia untuk membantu Anda menyesuaikan performa sistem.

Tutorial ini menggunakan SQL Server Standard Edition 2022, sehingga tidak semua opsi konfigurasi yang ditampilkan dalam panduan ini berfungsi untuk semua orang, dan tidak semuanya memberikan manfaat performa yang signifikan untuk setiap workload.

Tujuan

  • Menyiapkan instance dan disk Compute Engine.
  • Mengonfigurasi sistem operasi Windows.
  • Mengonfigurasi SQL Server.

Biaya

Tutorial ini menggunakan komponen Google Cloud yang dapat ditagih, termasuk:

  • Instance bermemori tinggi Compute Engine
  • Penyimpanan Persistent Disk SSD Compute Engine
  • Penyimpanan disk SSD Lokal Compute Engine
  • Image SQL Server Standard yang telah dikonfigurasi sebelumnya

Kalkulator Harga dapat membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda. Link yang diberikan menunjukkan perkiraan biaya untuk produk-produk yang digunakan dalam tutorial ini, yang biayanya lebih dari 4 dolar (AS) per jam dan lebih dari 3.000 dolar per bulan. Pengguna Google Cloud baru mungkin memenuhi syarat untuk mendapatkan uji coba gratis.

Sebelum memulai

  1. Login ke akun Google Cloud Anda. Jika Anda baru menggunakan Google Cloud, buat akun untuk mengevaluasi performa produk kami dalam skenario dunia nyata. Pelanggan baru juga mendapatkan kredit gratis senilai $300 untuk menjalankan, menguji, dan men-deploy workload.

  2. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

    Buka pemilih project

  3. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

  4. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

    Buka pemilih project

  5. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

Membuat VM Compute Engine dengan disk

Untuk membuat instance SQL Server berperforma tinggi, Anda harus terlebih dahulu membuat instance VM dengan SQL Server dan dua persistent disk.

Pertimbangan persistent disk

Guna memilih jenis persistent disk untuk VM Anda, tinjau pertimbangan berikut:

  • SSD lokal menyediakan lokasi berperforma tinggi untuk tempdb dan pagefile Windows.

    Ada beberapa pertimbangan penting yang perlu diperhatikan saat menggunakan SSD lokal. Saat Anda menonaktifkan instance dari Windows atau meresetnya menggunakan API, SSD lokal akan dihapus. Tindakan ini akan merender instance menjadi tidak dapat di-booting. Agar mesin dapat berjalan kembali, Anda harus melepas persistent disk, membuat instance baru menggunakan disk tersebut, lalu menentukan SSD lokal baru. Setelah memulai, Anda juga perlu memformat disk baru dan memulai ulang. Oleh karena itu, Anda tidak boleh menyimpan data penting secara permanen di SSD lokal, atau mematikan instance, kecuali jika Anda siap untuk membangun ulang instance tersebut.

  • Persistent Disk SSD menyediakan penyimpanan berperforma tinggi untuk file database.

    Performa Persistent Disk didasarkan pada penghitungan yang menggunakan jumlah CPU dan ukuran disk. Dengan 32 vCPU dan disk berukuran 1 TB, performa akan mencapai puncaknya pada 40.000 operasi baca per detik (operasi) dan 30.000 operasi tulis. Total throughput berkelanjutan untuk operasi baca dan tulis masing-masing adalah 800 MB per detik dan 400 MB per detik. Pengukuran ini mewakili penjumlahan dari semua persistent disk yang terpasang pada mesin virtual, termasuk drive C:\. Inilah sebabnya Anda harus membuat SSD lokal untuk memindahkan semua IOPS yang diperlukan untuk file paging, tempdb, data staging, dan cadangan.

Untuk membaca tentang performa disk lebih lanjut, lihat Mengonfigurasi disk untuk memenuhi persyaratan performa.

Membuat VM Compute Engine dengan disk

Untuk membuat VM dengan SQL Server 2022 Standard yang telah diinstal sebelumnya di Windows Server 2022, ikuti langkah-langkah berikut:

  1. Di konsol Google Cloud, buka halaman Create an instance.

    Buka Create an instance

  2. Untuk Name, masukkan ms-sql-server.

  3. Di bagian Machine configuration, pilih General Destination, lalu lakukan hal berikut:

    1. Dalam daftar Seri, klik N2.
    2. Dalam daftar Machine type, klik n2-highmem-16 (16vCPU, 128 GB memory).

  4. Di bagian Boot disk, klik Change, lalu lakukan hal berikut:

    1. Di tab Public images, klik daftar Operating system, lalu pilih SQL Server on Windows Server.
    2. Dalam daftar Version, klik SQL Server 2022 Standard di Windows Server 2022 Datacenter.
    3. Pada daftar Boot disk type, klik Standard persistent disk.
    4. Di kolom Size (GB), tetapkan ukuran boot disk ke 50 GB.
    5. Untuk menyimpan konfigurasi boot disk, klik Select.

  5. Luaskan bagian Advanced options, dan lakukan hal berikut:

    1. Luaskan bagian Disks.

    2. Untuk membuat disk lokal, klik Add Local SSD, lalu lakukan langkah berikut:

      1. Dalam daftar Interface, pilih protokol yang memenuhi persyaratan performa sistem Anda.
      2. Di daftar DiskCapacity, pilih kapasitas disk yang mendukung ukuran file tempdb yang diharapkan.
      3. Untuk menyelesaikan pembuatan disk ini, klik Save.

    3. Untuk membuat disk tambahan, klik Add New Disk.

      1. Jangan ubah kolom Name.
      2. Dalam daftar Disk source type, pilih Blank disk.
      3. Dalam daftar Disk type, pilih Persistent disk SSD.
      4. Di kolom Size, masukkan ukuran disk yang dapat mengakomodasi ukuran database.
      5. Untuk menyelesaikan pembuatan disk kedua, klik Save.

  6. Untuk membuat VM, klik Create.

Mengonfigurasi Windows

Setelah Anda memiliki instance yang berfungsi yang menjalankan SQL Server, hubungkan ke instance Anda dan konfigurasikan sistem operasi Windows. Setelah itu, Anda akan belajar mengonfigurasi SQL Server di bagian selanjutnya.

Menghubungkan ke instance

  1. Di Konsol Google Cloud, buka halaman VM instances.

    Buka VM instances

  2. Di kolom Name, klik nama instance Anda, ms-sql-server.

  3. Di bagian atas halaman detail instance, klik tombol Set Windows Password.

  4. Tentukan nama pengguna.

  5. Klik Set untuk membuat kata sandi baru untuk instance Windows ini.

  6. Simpan nama pengguna dan sandi sehingga Anda dapat login ke instance.

  7. Hubungkan ke instance menggunakan RDP.

Menyiapkan volume disk

Buat dan format volume:

  1. Dari menu Start, telusuri "Computer Management", lalu buka.
  2. Di bagian Penyimpanan, pilih Disk Management.
  3. Saat diminta untuk menginisialisasi disk, terima pilihan default, lalu klik OK.

  4. Buat partisi untuk disk SSD lokal:

    Untuk menemukan disk SSD lokal, klik kanan pada disk, lalu pilih Properties. Nama properti disk SSD lokal akan menjadi Google EphemeralDisk untuk antarmuka SCSI atau nvme_card untuk antarmuka NVMe. SSD lokal dan SSD persisten ditandai memiliki partisi Unallocated.

    1. Jika VM hanya berisi 1 drive SSD lokal, ikuti langkah-langkah berikut:

      1. Pada daftar disk drive, klik kanan disk SSD lokal 374,98 GB, lalu pilih New Simple Volume.
      2. Di layar Selamat Datang, klik Next untuk memulai wizard volume disk.
      3. Pada langkah Specify Volume Size, biarkan ukuran volume pada nilai default, lalu klik Next untuk melanjutkan.
      4. Pada langkah Assign Drive Letter or Path, pilih P: untuk huruf drive, lalu klik Next untuk melanjutkan.

      5. Di langkah Format Volume, ubah Allocation unit size menjadi 8192 dan masukkan "pagefile" untuk Volume label. Klik Berikutnya untuk melanjutkan.

        New Volume Wizard

      6. Klik Finish untuk menyelesaikan wizard volume disk.

    2. Jika VM berisi beberapa drive SSD lokal, ikuti langkah-langkah berikut:

      1. Di daftar disk drive, klik kanan disk SSD lokal 374,98 GB pertama, lalu pilih New Striped Volume.
      2. Di layar Selamat Datang, klik Next untuk memulai wizard volume disk.

      3. Pada langkah Select Disks, tambahkan semua disk yang tersedia dengan ukuran 383.982 MB ke bagian Selected. Klik Berikutnya untuk melanjutkan.

        Menambahkan striped disk

      4. Pada langkah Assign Drive Letter or Path, pilih P: untuk huruf drive, lalu klik Next untuk melanjutkan.

      5. Di langkah Format Volume, ubah Allocation unit size menjadi 8192 dan masukkan "pagefile" untuk Volume label. Klik Berikutnya untuk melanjutkan.

        New Volume Wizard

      6. Klik Finish untuk menyelesaikan wizard volume disk.

  5. Ulangi langkah sebelumnya untuk membuat New Simple Volume untuk disk SSD, dengan tiga perubahan berikut:

    • Pilih D: untuk huruf drive.

    • Tetapkan Allocation unit size ke 64k.

      Untuk mengetahui detail tentang cara memilih ukuran unit alokasi, lihat Praktik terbaik untuk instance SQL Server.

    • Masukkan sqldata untuk Volume label.

Memindahkan file paging Windows

Setelah volume baru dibuat dan dipasang, pindahkan file paging Windows ke SSD lokal, yang akan mengosongkan Persistent Disk IOPS dan meningkatkan waktu akses memori virtual Anda.

  1. Dari menu Start, telusuri View advanced system settings, lalu buka dialog.
  2. Klik tab Advanced, lalu di bagian Performance, klik Settings.
  3. Di bagian Virtual memory, klik tombol Change.
  4. Hapus centang pada kotak Kelola ukuran file paging secara otomatis untuk semua drive. Sistem seharusnya sudah menyiapkan file paging di drive C:\, dan Anda perlu memindahkannya.
  5. Klik C: lalu klik tombol pilihan No paging file.
  6. Klik tombol Set.
  7. Untuk membuat file paging baru, klik drive P:, lalu klik tombol pilihan System managed size.
  8. Klik tombol Set.

  9. Klik OK tiga kali untuk keluar dari properti sistem lanjutan.

    Dukungan Microsoft telah memublikasikan tips tambahan untuk setelan memori virtual.

Menyetel profil daya

Setel profil daya ke High-Performance, bukan Balanced.

  1. Dari menu Start, telusuri "Choose a Power Plan", lalu buka opsi daya.
  2. Pilih tombol pilihan High Permormance.
  3. Keluar dari dialog.

Mengonfigurasi SQL Server

Gunakan SQL Server Management Studio untuk melakukan sebagian besar tugas administratif. Image yang telah dikonfigurasi untuk SQL Server dilengkapi dengan Management Studio yang sudah terinstal. Luncurkan Management Studio, lalu klik Connect untuk terhubung ke database default.

Memindahkan data dan file log

Image yang telah dikonfigurasi untuk SQL Server dilengkapi dengan semua yang diinstal di drive C:\, termasuk database sistem. Untuk mengoptimalkan penyiapan Anda, pindahkan file tersebut ke drive D:\ baru yang Anda buat. Jangan lupa juga untuk membuat semua database baru di drive D:\. Karena menggunakan SSD, Anda tidak perlu menyimpan file data dan file log di partisi disk terpisah.

Ada dua cara untuk memindahkan penginstalan ke disk sekunder: menggunakan penginstal atau memindahkan file secara manual.

Menggunakan penginstal

Untuk menggunakan penginstal, jalankan c:\setup.exe dan pilih jalur penginstalan baru di disk sekunder Anda.

Memindahkan file secara manual

Pindahkan database sistem dan konfigurasikan SQL Server untuk menyimpan data dan file log pada volume yang sama:

  1. Buat folder baru bernama D:\SQLData.
  2. Buka Jendela Perintah.

  3. Masukkan perintah berikut untuk memberikan akses penuh ke NT Service\MSSQLSERVER:

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

  4. Gunakan Management Studio dan panduan berikut untuk memindahkan database sistem Anda dan mengubah lokasi file default untuk database baru.

  5. Jika Anda berencana menggunakan fitur Report Server, pindahkan file ReportServer dan ReportServerTempDB juga.

Setelah Anda memindahkan file database konfigurasi utama dan memulai ulang, Anda harus mengonfigurasi sistem agar mengarah ke lokasi baru untuk model dan database MSDB. Berikut adalah skrip helper untuk dijalankan di Management Studio:

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' )

Setelah Anda menjalankan perintah ini:

  1. Gunakan snap-in services.msc untuk menghentikan layanan database SQL Server.
  2. Gunakan file explorer Windows untuk memindahkan file fisik dari drive C:\ tempat database master berada ke direktori D:\SQLData.
  3. Mulai layanan database SQL Server.

Menyetel izin sistem

Setelah memindahkan database sistem, ubah beberapa setelan tambahan, dimulai dengan izin untuk akun pengguna Windows yang dibuat untuk menjalankan proses SQL Server Anda, yang bernama NT Service\MSSQLSERVER.

Memberikan izin Lock Pages in Memory

Izin Lock Pages in Memory kebijakan grup mencegah Windows memindahkan halaman dalam memori fisik ke memori virtual. Agar memori fisik tetap kosong dan teratur, Windows mencoba menukar halaman lama yang jarang dimodifikasi ke file paging memori virtual di disk.

SQL Server menyimpan informasi penting dalam memori, seperti struktur tabel, rencana eksekusi, dan kueri yang disimpan dalam cache. Beberapa informasi ini jarang berubah, sehingga menjadi target untuk file paging. Jika informasi ini dipindahkan ke file paging, performa SQL Server dapat menurun. Memberikan izin Lock Pages in Memory kebijakan grup untuk akun layanan SQL Server akan mencegah pertukaran ini.

Ikuti langkah-langkah berikut:

  1. Klik Start, lalu telusuri Edit Group Policy untuk membuka konsol.
  2. Perluas Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.
  3. Telusuri, lalu klik dua kali Lock pages in memory.
  4. Klik Add User or Group.
  5. Telusuri "NT Service\MSSQLSERVER".
  6. Jika Anda melihat beberapa nama, klik dua kali nama MSSQLSERVER.
  7. Klik OK dua kali.
  8. Biarkan konsol Group Policy Editor tetap terbuka.

Lock pages

Memberikan izin Perform volume maintenance tasks

Secara default, saat aplikasi meminta sepotong ruang disk dari Windows, sistem operasi akan mencarikan potongan ruang disk dengan ukuran yang tepat, lalu menghilangkan seluruh potongan disk, sebelum menyerahkannya kembali ke aplikasi tersebut. Karena SQL Server suka menambah file dan mengisi ruang disk, perilaku ini tidak optimal.

Terdapat API terpisah untuk mengalokasikan kapasitas disk ke aplikasi, yang sering disebut sebagai inisialisasi file instan. Sayangnya, setelan ini hanya berfungsi untuk file data, tetapi Anda akan mempelajari pertumbuhan file log di bagian mendatang. Inisialisasi file instan memerlukan akun layanan yang menjalankan proses SQL Server untuk memiliki izin kebijakan grup lain, yang disebut Perform volume maintenance tasks.

  1. Di Group Policy Editor, telusuri "Perform volume maintenance tasks".
  2. Tambahkan akun "NT Service\MSSQLSERVER" seperti yang Anda lakukan di bagian sebelumnya.
  3. Mulai ulang proses SQL Server untuk mengaktifkan kedua pengaturan.

Menyiapkan tempdb

Sebelumnya, praktik terbaik ini ditujukan untuk mengoptimalkan penggunaan CPU SQL Server dengan membuat satu file tempdb per CPU. Namun, karena jumlah CPU terus bertambah dari waktu ke waktu, mengikuti panduan ini dapat menyebabkan penurunan performa. Sebagai awal yang baik, gunakan 4 file tempdb. Saat mengukur performa sistem, dalam kasus yang jarang terjadi, Anda mungkin perlu meningkatkan jumlah file tempdb secara bertahap hingga maksimum 8.

Anda dapat menjalankan skrip Transact-SQL (T-SQL) di dalam SQL Server Management Studio untuk memindahkan file tempdb ke folder di drive `p:`.

  1. Buat direktori p:\tempdb.

  2. Berikan akses keamanan penuh ke akun pengguna "NT Service\MSSQLSERVER":

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

  3. Jalankan skrip berikut di dalam SQL Server Management Studio untuk memindahkan file data dan file log 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. Mulai ulang SQL Server.

  5. Jalankan skrip berikut untuk mengubah ukuran file dan membuat tiga file data tambahan untuk tempdb baru.

    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

    Jika Anda menggunakan SQL Server 2016, ada 3 file tempdb tambahan yang harus dihapus setelah Anda melakukan langkah sebelumnya:

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

  6. Mulai ulang SQL Server.

  7. Hapus file model, MSDB, master, dan tempdb dari lokasi asli di drive C:\.

Anda berhasil memindahkan file tempdb ke partisi SSD lokal. Pergerakan ini memiliki beberapa risiko, yang sudah disebutkan sebelumnya, tetapi jika hilang karena alasan apa pun, SQL Server akan membangun ulang file tempdb. Memindahkan tempdb akan memberi Anda performa tambahan untuk SSD lokal, dan mengurangi IOPS yang digunakan di persistent disk.

Setelan max degree of parallelism

Setelan default yang direkomendasikan untuk max degree of parallelism adalah mencocokkannya dengan jumlah CPU di server. Namun, ada kalanya saat menjalankan kueri dalam 16 atau 32 potongan paralel dan menggabungkan hasilnya jauh lebih lambat daripada menjalankannya dalam satu proses. Jika menggunakan instance 16 atau 32 core, Anda dapat menetapkan nilai max degree of parallelism ke 8 menggunakan T-SQL berikut:

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

Setelan max server memory

Setelan default ini memiliki jumlah yang sangat tinggi, tetapi Anda harus menetapkannya ke jumlah megabyte RAM fisik yang tersedia, dikurangi beberapa gigabyte untuk sistem operasi dan overhead. Contoh T-SQL berikut menyesuaikan max server memory hingga 100 GB. Ubah nilai untuk menyesuaikan nilai agar cocok dengan instance Anda. Tinjau dokumen Opsi konfigurasi server memori server untuk mengetahui informasi selengkapnya.

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

Penyelesaian

Mulai ulang instance sekali lagi untuk memastikan semua setelan baru dapat diterapkan. Sistem SQL Server Anda sudah dikonfigurasi dan Anda siap membuat database sendiri dan mulai menguji workload spesifik Anda. Tinjau panduan Praktik Terbaik SQL Server untuk mengetahui informasi selengkapnya tentang aktivitas operasional, pertimbangan performa lainnya, dan kemampuan Edisi Enterprise.

Pembersihan

Setelah menyelesaikan tutorial, Anda dapat membersihkan resource yang dibuat agar resource tersebut berhenti menggunakan kuota dan dikenai biaya. Bagian berikut menjelaskan cara menghapus atau menonaktifkan resource ini.

Menghapus project

Cara termudah untuk menghilangkan penagihan adalah dengan menghapus project yang Anda buat untuk tutorial.

Untuk menghapus project:

  1. Di konsol Google Cloud, buka halaman Manage resource.

    Buka Manage resource

  2. Pada daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
  3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

Menghapus instance

Untuk menghapus instance Compute Engine:

  1. Di konsol Google Cloud, buka halaman Instance VM.

    Buka VM instances

  2. Pilih kotak centang untuk instance yang ingin Anda hapus.
  3. Untuk menghapus instance, klik Tindakan lainnya, klik Hapus, lalu ikuti petunjuknya.

Menghapus persistent disk

Untuk menghapus Persistent Disk:

  1. Di Konsol Google Cloud, buka halaman Disks.

    Buka Disks

  2. Pilih kotak centang di samping nama disk yang ingin Anda hapus.

  3. Klik tombol Delete di bagian atas halaman.

Langkah selanjutnya