Mengoptimalkan penyimpanan database

  • 5 menit

Untuk mengoptimalkan penyimpanan database, Anda harus mempertimbangkan konfigurasi tempdb dan pengisian proporsional.

Memahami performa I/O

Kinerja I/O dapat sangat penting bagi aplikasi database. Azure SQL mengabstraksi Anda dari penempatan file fisik, tetapi ada metode untuk memastikan Anda mendapatkan kinerja I/O yang Anda butuhkan.

Input/output per detik (IOPS) mungkin penting bagi aplikasi Anda. Pastikan Anda telah memilih tingkat layanan dan vCore yang tepat untuk kebutuhan IOPS Anda. Pahami cara mengukur IOPS untuk kueri Anda secara lokal jika Anda bermigrasi ke Azure. Jika Anda memiliki batasan pada IOPS, Anda mungkin melihat I/O panjang menunggu. Dalam model pembelian vCore, Anda dapat meningkatkan vCore atau pindah ke Business Critical atau Hyperscale jika Anda tidak memiliki cukup IOPS. Untuk beban kerja produksi, saat menggunakan DTU, sebaiknya pindah ke tingkat Premium.

Latensi I/O adalah komponen kunci lain untuk kinerja I/O. Untuk latensi I/O yang lebih cepat untuk Azure SQL Database, pertimbangkan Business Critical atau Hyperscale. Untuk latensi I/O yang lebih cepat untuk SQL Managed Instance, pindah ke Business Critical atau menambah ukuran file atau jumlah file untuk database. Meningkatkan latensi log transaksi mungkin mengharuskan Anda menggunakan transaksi multistatement.

Berkas dan kelompok berkas

Para profesional SQL Server sering menggunakan file dan grup file untuk meningkatkan kinerja I/O melalui penempatan file fisik. Azure SQL tidak memperbolehkan pengguna untuk menempatkan file pada sistem disk tertentu. Namun, Azure SQL memiliki komitmen sumber daya untuk performa I/O mengenai tingkat, IOPS, dan latensi. Dengan cara ini, mengabstraksi pengguna dari penempatan file fisik dapat menjadi bermanfaat.

Azure SQL Database hanya memiliki satu file database (Hyperscale biasanya memiliki beberapa file), dan ukuran maksimum dikonfigurasi melalui antarmuka Azure. Tidak ada fungsionalitas untuk membuat lebih banyak file.

Azure SQL Managed Instance mendukung penambahan file database dan mengonfigurasi ukuran, tetapi bukan penempatan fisik file. Anda dapat menggunakan jumlah file dan ukuran file untuk SQL Managed Instance untuk meningkatkan performa I/O. Selain itu, grup file yang ditentukan pengguna didukung untuk SQL Managed Instance untuk tujuan pengelolaan.

Menjelaskan isi proporsional

Saat menyisipkan 1 gigabyte data ke dalam database SQL Server dengan dua file data, Anda mungkin mengharapkan setiap file meningkat sekitar 512 megabyte. Namun, ini tidak selalu terjadi. SQL Server mendistribusikan data berdasarkan ukuran setiap file. Misalnya, jika kedua file data 2 gigabyte, data akan didistribusikan secara merata. Tetapi jika satu file adalah 10 gigabyte dan yang lain adalah 1 gigabyte, sekitar 900 MB akan masuk ke file yang lebih besar dan 100 MB ke yang lebih kecil. Perilaku ini umum dalam database apa pun, tetapi dalam tempdb intensif penulisan, pola tulis yang tidak merata dapat membuat hambatan dalam file terbesar, karena menangani lebih banyak penulisan.

Mengonfigurasi Tempdb di SQL Server

SQL Server mendeteksi jumlah CPU yang tersedia selama penyiapan dan mengonfigurasi jumlah file yang sesuai, hingga delapan, dengan ukuran genap. Selain itu, perilaku bendera pelacakan 1117 dan 1118 diintegrasikan ke dalam mesin database, tetapi hanya untuk tempdb. Untuk beban kerja tempdb-heavy, mungkin bermanfaat untuk meningkatkan jumlah file tempdb melebihi delapan, cocok dengan jumlah CPU di komputer Anda.

Anda menggunakan tempdb dengan cara yang sama untuk SQL Server dan Azure SQL. Namun, perhatikan bahwa kemampuan Anda untuk mengonfigurasi tempdb berbeda, termasuk penempatan file, jumlah dan ukuran file, dan tempdb opsi konfigurasi.

SQL Server menggunakan tempdb untuk berbagai tugas di luar hanya menyimpan tabel sementara yang ditentukan pengguna. Ini digunakan untuk tabel kerja yang menyimpan hasil kueri perantara, operasi pengurutan, dan penyimpanan versi untuk penerapan versi baris, di antara tujuan lainnya. Karena pemanfaatan yang luas ini, sangat penting untuk menempatkan tempdb pada penyimpanan latensi terendah yang tersedia dan untuk mengonfigurasi file datanya dengan benar.

File tempdb database selalu disimpan secara otomatis pada drive SSD lokal, sehingga performa I/O seharusnya tidak menjadi masalah.

Profesional SQL Server sering menggunakan lebih dari satu file database untuk mempartisi alokasi untuk tempdb tabel. Untuk Azure SQL Database, jumlah file diskalakan dengan jumlah vCore (misalnya, dua vCore sama dengan empat file) dengan maksimum 16. Jumlah file tidak dapat dikonfigurasi melalui T-SQL terhadap tempdb, tetapi Anda dapat mengonfigurasinya dengan mengubah opsi penyebaran. Ukuran tempdb maksimum diskalakan per jumlah vCore. Anda mendapatkan 12 file dengan SQL Managed Instance, terpisah dari vCores.

Opsi MIXED_PAGE_ALLOCATION database diatur ke NONAKTIF, dan AUTOGROW_ALL_FILES diatur ke AKTIF. Anda tidak dapat mengonfigurasi ini, tetapi, seperti halnya SQL Server, ini adalah default yang direkomendasikan.

Fitur tempdb pengoptimalan metadata yang diperkenalkan di SQL Server 2019, yang dapat meringankan ketidakcocokan kait berat, saat ini tidak tersedia di Azure SQL Database atau Azure SQL Managed Instance.

Konfigurasi database

Umumnya, Anda mengonfigurasi database dengan T-SQL ALTER DATABASE dan ALTER DATABASE SCOPED CONFIGURATION pernyataan. Banyak opsi konfigurasi untuk kinerja tersedia untuk Azure SQL. Lihat referensi ALTER DATABASE dan ALTER DATABASE SCOPED CONFIGURATION T-SQL untuk perbedaan antara SQL Server, Azure SQL Database, dan Azure SQL Managed Instance.

Di Azure SQL Database, model pemulihan default adalah pemulihan penuh, yang memastikan bahwa database Anda dapat memenuhi perjanjian tingkat layanan (SLA) Azure. Ini berarti bahwa pengelogan minimal untuk operasi massal tidak didukung, kecuali untuk tempdb, di mana pengelogan minimal diizinkan.

Konfigurasi MAXDOP

Tingkat paralelisme maksimum (MAXDOP) dapat mempengaruhi kinerja kueri individu. SQL Server dan Azure SQL menangani MAXDOP dengan cara yang sama. Ketika MAXDOP diatur ke nilai yang lebih tinggi, utas yang lebih paralel digunakan per kueri, berpotensi mempercepat eksekusi kueri. Namun, paralelisme yang meningkat ini membutuhkan sumber daya memori tambahan, yang dapat menyebabkan tekanan memori dan memengaruhi performa penyimpanan. Misalnya, saat mengompresi grup baris ke dalam penyimpan kolom, paralelisme membutuhkan lebih banyak memori, yang dapat mengakibatkan tekanan memori dan pemangkasan grup baris.

Sebaliknya, mengatur MAXDOP ke nilai yang lebih rendah dapat mengurangi tekanan memori, memungkinkan sistem penyimpanan untuk berkinerja lebih efisien. Ini penting di lingkungan dengan sumber daya memori terbatas atau tuntutan penyimpanan yang tinggi. Dengan mengonfigurasi MAXDOP dengan cermat, Anda dapat menyeimbangkan performa kueri dan efisiensi penyimpanan, memastikan penggunaan CPU dan sumber daya penyimpanan yang optimal.

Anda dapat mengonfigurasi MAXDOP di Azure SQL, mirip dengan SQL Server, dengan menggunakan teknik berikut:

  • ALTER DATABASE SCOPED CONFIGURATION untuk mengonfigurasi MAXDOP didukung untuk Azure SQL.
  • Prosedur sp_configure tersimpan untuk "tingkat paralelisme maksimum" didukung untuk SQL Managed Instance.
  • MAXDOP petunjuk kueri didukung sepenuhnya.
  • Mengonfigurasi MAXDOP dengan Resource Governor didukung untuk SQL Managed Instance.