Anda dapat menerapkan beberapa praktik terbaik untuk mengoptimalkan instance Compute Engine yang menjalankan Microsoft SQL Server. Untuk mempelajari cara menyiapkan instance Server SQL berperforma tinggi, baca Membuat instance SQL Server berperforma tinggi.
Mengonfigurasi Windows
Bagian ini membahas topik konfigurasi tentang cara mengoptimalkan sistem operasi Microsoft Windows untuk performa SQL Server saat berjalan di Compute Engine.
Menyiapkan firewall Windows
Praktik terbaik: Gunakan Windows Server Advanced Firewall, dan tentukan alamat IP komputer klien Anda.
Windows Advanced Firewall adalah komponen keamanan penting di Windows Server. Saat Anda menyiapkan lingkungan SQL Server agar dapat terhubung ke database dari mesin klien lainnya, konfigurasikan firewall untuk mengizinkan traffic masuk:
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
Saat menggunakan aturan firewall ini, sebaiknya tentukan alamat IP komputer klien Anda. Tentukan daftar alamat IP yang dipisahkan koma tanpa
spasi kosong untuk parameter remoteip sebagai pengganti
LOCAL_SUBNET. Selain itu,
perhatikan bahwa jalur untuk parameter program dapat berubah bergantung pada
versi SQL Server yang Anda gunakan.
Image aplikasi SQL Server menyertakan aturan firewall Windows SQL Server.
Aturan ini cukup tidak dibatasi, jadi pertimbangkan untuk menonaktifkannya sebelum sistem Anda beralih ke produksi.
Menyesuaikan koneksi jaringan
Praktik terbaik: Gunakan setelan jaringan default sistem operasi.
Setelan jaringan default pada sebagian besar sistem operasi dikonfigurasi untuk koneksi pada komputer kecil yang terhubung ke jaringan yang cukup cepat. Setelan tersebut biasanya sudah cukup. Selain itu, setelan default konservatif memastikan traffic jaringan tidak membebani jaringan dan komputer yang terhubung.
Di Compute Engine, instance mesin virtual (VM) terpasang ke jaringan yang dirancang oleh Google yang menawarkan kapasitas dan performa tinggi. Server fisik yang menjalankan instance Compute Engine Anda sangat dioptimalkan untuk memanfaatkan kapasitas jaringan ini. Driver jaringan virtual dalam instance Anda juga dioptimalkan, sehingga nilai default cukup untuk sebagian besar kasus penggunaan.
Menginstal antivirus
Praktik terbaik: Ikuti panduan Microsoft untuk software antivirus.
Jika Anda menjalankan Windows, Anda harus menjalankan beberapa software antivirus. Virus malware dan software memberikan risiko yang signifikan terhadap sistem yang terhubung ke jaringan, dan software antivirus adalah langkah mitigasi sederhana yang dapat Anda gunakan untuk melindungi data Anda. Namun, jika software antivirus tidak dikonfigurasi dengan benar, software tersebut dapat berdampak negatif pada performa database Anda. Microsoft memberikan saran tentang cara memilih software antivirus.
Mengoptimalkan performa dan stabilitas
Bagian ini memberikan informasi tentang cara mengoptimalkan performa SQL Server di Compute Engine dan menjelaskan aktivitas operasional untuk membantunya tetap berjalan lancar.
Memindahkan file data dan file log ke disk baru
Praktik terbaik: Gunakan persistent disk SSD terpisah untuk file log dan data.
Secara default, image yang telah dikonfigurasi untuk SQL Server dilengkapi dengan semua yang terinstal di persistent disk booting, yang dipasang sebagai drive `C:`. Pertimbangkan untuk memasang persistent disk SSD sekunder dan memindahkan file log serta file data ke disk baru.
Menggunakan SSD Lokal untuk meningkatkan IOPS
Praktik terbaik: Buat instance SQL Server baru dengan satu atau beberapa
SSD lokal untuk menyimpan
file paging tempdb dan Windows.
Sifat efemeral dari teknologi SSD lokal menjadikannya kandidat yang buruk untuk
digunakan dengan database penting dan file penting Anda. Namun, file paging tempdb dan Windows merupakan file sementara, sehingga keduanya merupakan kandidat tepat untuk dipindahkan ke SSD lokal. Tindakan ini mengurangi beban operasi I/O dalam jumlah yang signifikan
dari persistent disk SSD Anda. Untuk mengetahui informasi selengkapnya tentang cara menyiapkannya, lihat Menyiapkan TempDB.
Pemrosesan kueri paralel
Praktik terbaik: Tetapkan max degree of parallelism ke 8.
Setelan default yang direkomendasikan untuk max degree of parallelism adalah mencocokkannya
dengan jumlah CPU di server. Namun, ada titik saat memecah kueri menjadi 16 atau 32 bagian, mengeksekusi semuanya pada vCPU yang berbeda lalu menggabungkan semuanya kembali ke satu hasil membutuhkan waktu lebih lama dibandingkan jika hanya satu vCPU telah menjalankan kueri. Dalam praktiknya, 8 berfungsi sebagai nilai default yang baik.
Praktik terbaik: Pantau waktu tunggu CXPACKET dan tingkatkan
cost threshold for parallelism secara bertahap.
Setelan ini digunakan bersama dengan max degree of parallelism. Setiap unit mewakili
kombinasi pekerjaan CPU dan I/O yang diperlukan untuk menjalankan kueri dengan rencana eksekusi
seri sebelum dipertimbangkan untuk rencana eksekusi paralel. Nilai defaultnya adalah 5.
Meskipun kami tidak memberikan rekomendasi khusus untuk mengubah nilai default, sebaiknya Anda terus memantau dan, jika perlu, meningkatkannya secara bertahap sebesar 5 selama pengujian beban. Salah satu indikator utama bahwa nilai ini mungkin perlu ditingkatkan adalah
adanya waktu tunggu CXPACKET. Meskipun adanya tunggu CXPACKET bukan
selalu menunjukkan bahwa setelan ini harus berubah, ini adalah awal yang baik.
Praktik terbaik: Pantau berbagai jenis tunggu, dan sesuaikan setelan pemrosesan paralel global atau tetapkan pada level database individual.
Setiap database dapat memiliki kebutuhan paralelisme yang berbeda. Anda dapat menetapkan setelan ini secara global dan menetapkan Max DOP pada level database individual. Anda
harus mengamati workload unik Anda, memantau waktu tunggu, lalu menyesuaikan
nilainya sebagaimana mestinya.
Situs SQLSkills menawarkan panduan performa berguna yang mencakup statistik waktu tunggu di dalam database. Dengan mengikuti panduan ini, Anda dapat memahami apa yang menunggu dan cara mengurangi penundaan.
Menangani log transaksi
Praktik terbaik: Pantau pertumbuhan log transaksi di sistem Anda. Pertimbangkan untuk menonaktifkan autogrowth dan menyetel file log ke ukuran tetap, berdasarkan akumulasi log harian rata-rata Anda.
Salah satu sumber kehilangan performa dan perlambatan yang paling sering
diabaikan adalah pertumbuhan log transaksi yang tidak dikelola. Jika database dikonfigurasi untuk menggunakan model pemulihan Full, Anda dapat melakukan pemulihan ke titik waktu mana pun, tetapi log transaksi Anda akan terisi lebih cepat. Secara default, ketika
file log transaksi penuh, SQL Server akan meningkatkan ukuran file untuk menambahkan
lebih banyak ruang kosong untuk menulis lebih banyak transaksi dan memblokir semua aktivitas di
database hingga selesai. SQL Server mengembangkan setiap file log berdasarkan
Ukuran File Maksimum dan setelan File Growth.
Jika file telah mencapai batas ukuran maksimum dan tidak dapat bertambah, sistem akan menampilkan error 9002 dan mengalihkan database ke mode hanya baca. Jika ukuran file dapat bertambah, SQL Server akan memperluas ukuran file dan mengosongkan ruang kosong. Setelan untuk File Growth ditetapkan secara default ke 10% dari ukuran file log saat ini. Setelan ini bukan setelan default yang baik untuk performa karena semakin besar ukuran file Anda, semakin lama waktu yang diperlukan untuk membuat ruang baru yang kosong.
Praktik terbaik: Jadwalkan pencadangan log transaksi secara berkala.
Terlepas dari setelan ukuran dan pertumbuhan maksimum, jadwalkan pencadangan log transaksi reguler, yang, secara default, akan memotong entri log lama dan memungkinkan sistem menggunakan kembali ruang file yang sudah ada. Tugas perawatan sederhana ini dapat membantu menghindari penurunan performa pada waktu puncak traffic Anda.
Mengoptimalkan File Log Virtual
Praktik terbaik: Pantau pertumbuhan File Log Virtual dan ambil tindakan untuk mencegah fragmentasi file log.
File log transaksi fisik disegmentasikan ke dalam File Log Virtual (VLF). VLF baru dibuat setiap kali file log transaksi fisik harus berkembang. Jika Anda tidak menonaktifkan pertumbuhan otomatis, dan pertumbuhan terlalu sering terjadi, terlalu banyak VLF yang dibuat. Aktivitas ini dapat mengakibatkan fragmentasi file log, yang mirip dengan fragmentasi disk dan dapat berdampak buruk pada performa.
SQL Server 2014 memperkenalkan algoritma yang lebih efisien untuk menentukan jumlah VLF yang dibuat selama pertumbuhan otomatis. Umumnya, jika pertumbuhannya kurang dari 1/8 ukuran file log saat ini, SQL Server akan membuat satu VLF dalam segmen baru tersebut. Sebelumnya, metode ini akan membuat 8 VLF untuk pertumbuhan antara 64 MB dan 1 GB, dan 16 VLF untuk pertumbuhan di atas 1 GB. Anda dapat menggunakan skrip TSQL di bawah ini untuk memeriksa jumlah VLF yang dimiliki database Anda saat ini. Jika memiliki ribuan file, pertimbangkan untuk menyusutkan dan mengubah ukuran file log Anda secara manual.
--Check VLFs substitute your database name below USE YOUR_DB DECLARE @vlf_count INT DBCC LOGINFO SET @vlf_count = @@ROWCOUNT SELECT VLFs = @vlf_count
Anda dapat membaca lebih lanjut tentang VLF di situs Breent Ozar.
Menghindari fragmentasi indeks
Praktik terbaik: Defragmentasi indeks secara teratur pada tabel yang paling banyak diubah.
Indeks dalam tabel Anda dapat terfragmentasi, sehingga dapat menyebabkan performa yang buruk dari setiap kueri yang menggunakan indeks ini. Jadwal pemeliharaan rutin harus mencakup pengaturan ulang indeks pada tabel Anda yang paling banyak dimodifikasi.
Anda dapat menjalankan skrip Transact-SQL berikut untuk database guna menampilkan indeks dan persentase fragmentasinya. Anda dapat melihat dalam contoh hasil bahwa
indeks PK_STOCK 95% terfragmentasi. Dalam pernyataan 'SELECT' berikut, ganti 'YOUR_DB' dengan nama database Anda:
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
Jika indeks terlalu terfragmentasi, Anda dapat mengaturnya ulang menggunakan skrip ALTER dasar. Berikut adalah contoh skrip yang mencetak pernyataan ALTER yang dapat Anda jalankan untuk setiap indeks tabel:
SELECT 'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE; GO' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'
Pilih tabel dari kumpulan hasil yang memiliki fragmentasi tertinggi, lalu jalankan pernyataan tersebut secara bertahap. Pertimbangkan untuk menjadwalkan skrip ini atau skrip serupa sebagai salah satu tugas pemeliharaan reguler Anda.
Memformat disk sekunder
Praktik terbaik: Format disk sekunder dengan unit alokasi 64 KB.
SQL Server menyimpan data dalam unit penyimpanan yang disebut extent. Ekstensi berukuran 64 KB terdiri dari delapan halaman memori berdekatan yang juga berukuran 8 KB. Memformat disk dengan unit alokasi 64 KB memungkinkan SQL Server membaca dan menulis ekstensi secara lebih efisien, sehingga meningkatkan performa I/O dari disk.
Untuk memformat disk sekunder dengan unit alokasi 64 KB, jalankan perintah PowerShell berikut, yang menelusuri semua disk baru dan yang belum diinisialisasi dalam sistem serta memformat disk dengan unit alokasi 64 KB:
Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE
Pencadangan
Praktik terbaik: Buatlah rencana untuk pencadangan dan lakukan pencadangan secara teratur.
Situs Ola Hallengren memberikan titik awal yang baik untuk memahami cara menerapkan rencana pencadangan dan pemeliharaan yang solid.
Saat melakukan pencadangan database reguler, berhati-hatilah agar tidak menggunakan terlalu banyak IOPS persistent disk. Gunakan SSD lokal untuk mengatur cadangan Anda, lalu mengirimkannya ke bucket Cloud Storage.
Pemantauan
Praktik terbaik: Gunakan Cloud Monitoring.
Anda dapat menginstal agen Cloud Monitoring untuk Microsoft Windows untuk mengirim beberapa titik data pemantauan ke dalam sistem Cloud Monitoring.
Dengan menggunakan kemampuan pengumpulan data, Anda dapat menyesuaikan informasi yang ingin dipantau, dan mengirimkannya ke data warehouse pengelolaan bawaan. Data warehouse pengelolaan dapat berjalan di server yang sama dengan yang Anda pantau, atau data dapat di-streaming ke instance SQL Server lain yang menjalankan warehouse.
Memuat data secara massal
Praktik terbaik: Gunakan database terpisah untuk mengatur dan mengubah data massal sebelum memindahkannya ke server produksi.
Sepertinya Anda perlu memuat data dalam jumlah besar ke sistem Anda minimal satu kali, atau secara teratur. Ini adalah operasi yang memerlukan banyak resource dan Anda mungkin mencapai batas IOPS persistent disk saat melakukan pemuatan massal.
Ada cara mudah untuk mengurangi I/O disk dan konsumsi CPU dari operasi
pemuatan massal, dengan manfaat tambahan yaitu mempercepat waktu eksekusi
tugas batch Anda. Solusinya adalah membuat database yang sepenuhnya terpisah yang menggunakan model pemulihan Simple, lalu menggunakan database tersebut untuk staging dan mengubah set data massal sebelum memasukkannya ke dalam database produksi. Anda juga dapat menempatkan database baru ini di drive SSD lokal, jika Anda
memiliki ruang penyimpanan yang cukup. Penggunaan SSD lokal untuk database pemulihan akan mengurangi
penggunaan resource untuk operasi massal dan waktu yang diperlukan untuk menyelesaikan tugas.
Manfaat terakhirnya adalah tugas pencadangan Anda untuk data produksi tidak perlu mencadangkan semua operasi massal tersebut di log transaksi. Oleh karena itu, tugas pencadangan akan menjadi lebih kecil dan berjalan lebih cepat.
Memvalidasi penyiapan Anda
Praktik terbaik: Uji konfigurasi Anda untuk memvalidasi bahwa konfigurasi berfungsi seperti yang diharapkan.
Setiap kali menyiapkan sistem baru, Anda harus merencanakan untuk memvalidasi konfigurasi dan menjalankan beberapa pengujian performa. Prosedur tersimpan ini adalah resource yang bagus untuk mengevaluasi konfigurasi SQL Server Anda. Luangkan waktu untuk membaca tentang flag konfigurasi, dan jalankan prosedurnya.
Mengoptimalkan SQL Server Enterprise Edition
SQL Server Enterprise Edition memiliki banyak kemampuan tambahan dibandingkan Edisi Standar. Jika Anda memigrasikan lisensi yang ada ke Google Cloud, ada beberapa opsi performa yang dapat Anda pertimbangkan untuk diterapkan.
Menggunakan tabel terkompresi
Praktik terbaik: Aktifkan kompresi tabel dan indeks.
Mungkin terdengar tak masuk akal bahwa mengompresi tabel dapat membuat performa sistem Anda lebih cepat, tetapi, dalam banyak kasus, itulah yang terjadi. Konsekuensinya adalah menggunakan sejumlah kecil siklus CPU untuk mengompresi data dan menghilangkan I/O disk tambahan yang diperlukan untuk membaca dan menulis blok yang lebih besar. Umumnya, semakin sedikit I/O disk yang digunakan sistem, semakin baik performanya. Petunjuk untuk memperkirakan dan mengaktifkan kompresi tabel dan indeks tersedia di situs MSDN.
Mengaktifkan ekstensi kumpulan buffer
Praktik terbaik: Gunakan ekstensi kumpulan buffer untuk mempercepat akses data.
Kumpulan buffer adalah tempat sistem menyimpan halaman bersih. Secara sederhana, Cloud Storage menyimpan salinan data Anda, yang mencerminkan tampilannya di disk. Saat data berubah dalam memori, ini disebut halaman kotor. Halaman kotor harus dikosongkan ke disk untuk menyimpan perubahan. Jika database Anda lebih besar daripada memori yang tersedia, hal tersebut akan memberikan tekanan pada kumpulan buffer, dan halaman bersih mungkin akan dihapus. Jika halaman bersih dihapus, sistem harus membaca dari disk saat berikutnya sistem mengakses data yang hilang.
Fitur ekstensi kumpulan buffer memungkinkan Anda mengirim halaman bersih ke SSD lokal, bukan menjatuhkannya. Fungsinya sama dengan memori virtual, yaitu, denganmenukar, dan memberi Anda akses ke halaman bersih di SSD lokal, yang lebih cepat daripada membuka disk biasa untuk mengambil data.
Teknik ini tidak secepat memiliki memori yang cukup, tetapi dapat memberi Anda sedikit peningkatan throughput jika memori yang tersedia rendah. Anda dapat membaca lebih lanjut tentang ekstensi kumpulan buffer dan meninjau beberapa hasil benchmark di situs Brent Ozar.
Mengoptimalkan Pemberian Lisensi SQL Server
Multithreading Simultan (SMT)
Praktik terbaik: Menetapkan jumlah thread per core ke 1 untuk sebagian besar workload SQL Server
Multithreading simultan (SMT), yang umumnya dikenal sebagai Hyper-Threading Technology (HTT) pada prosesor Intel, adalah fitur yang memungkinkan satu inti CPU dibagikan secara logis sebagai dua thread. Di Compute Engine, SMT diaktifkan pada sebagian besar VM secara default. Artinya, setiap vCPU di VM berjalan pada satu thread dan setiap inti CPU fisik digunakan bersama oleh dua vCPU.
Di Compute Engine, Anda dapat mengonfigurasi jumlah thread per core, yang secara efektif menonaktifkan SMT. Jika jumlah thread per core ditetapkan ke 1, vCPU tidak akan berbagi core CPU fisik. Konfigurasi ini berdampak signifikan pada biaya lisensi untuk Windows Server dan SQL Server. Jika jumlah thread per inti ditetapkan menjadi 1, jumlah vCPU dalam VM akan dibagi dua, yang juga mengurangi jumlah lisensi Windows Server dan SQL Server yang diperlukan. Hal ini dapat mengurangi total biaya workload secara signifikan.
Namun, mengonfigurasi jumlah thread per core juga memengaruhi performa workload. Aplikasi yang ditulis menjadi multi-thread dapat memanfaatkan fitur ini dengan memecah pekerjaan komputasi menjadi bagian-bagian kecil yang dapat diparalelkan dan dijadwalkan di beberapa core logis. Paralelisasi pekerjaan ini sering kali meningkatkan throughput sistem secara keseluruhan dengan memanfaatkan resource inti yang tersedia dengan lebih baik. Misalnya, saat satu thread terhenti, thread lainnya dapat menggunakan inti.
Dampak performa yang tepat dari SMT pada SQL Server bergantung pada karakteristik workload dan platform hardware yang digunakan karena implementasi SMT berbeda antar-generasi hardware. Workload dengan volume transaksi kecil yang tinggi, misalnya workload OLTP, sering kali dapat memanfaatkan SMT, dan mendapatkan manfaat dari peningkatan performa yang lebih besar. Sebaliknya, workload yang kurang dapat diparalelkan, misalnya workload OLAP, akan lebih sedikit menerima manfaat dari SMT. Meskipun pola ini telah diketahui secara umum, pertimbangkan untuk mengevaluasi dampak performa SMT berdasarkan workload untuk menentukan dampak penetapan jumlah thread per inti ke 1.
Konfigurasi yang paling hemat biaya untuk sebagian besar workload SQL Server melibatkan penetapan jumlah thread per inti ke 1. Setiap penurunan performa dapat diimbangi dengan memanfaatkan VM yang lebih besar. Dalam sebagian besar kasus, penurunan 50% pada biaya lisensi lebih besar daripada kenaikan biaya VM yang lebih besar.
Contoh: Pertimbangkan SQL Server yang di-deploy dalam konfigurasi n2-standard-16
Secara default, jumlah core yang terlihat dalam sistem operasi adalah 16, yang berarti diperlukan 16 vCPU Windows Server dan 16 vCPU lisensi SQL Server untuk menjalankan server.
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
Setelah mengikuti langkah-langkah untuk menonaktifkan SMT di SQL Server, konfigurasi barunya adalah:
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
Karena sekarang hanya 8 core yang terlihat dalam sistem operasi, server hanya memerlukan 8 vCPU untuk menjalankan Windows Server dan SQL Server.