Bagikan melalui

Mengonfigurasi tingkat paralelisme maksimum (MAXDOP) dalam Azure SQL Database

  • Artikel

Berlaku untuk: Azure SQL Database

Artikel ini menjelaskan pengaturan konfigurasi tingkat maksimum paralelisme (MAXDOP) di Azure SQL Database.

Catatan

Konten ini difokuskan pada Azure SQL Database. Azure SQL Database didasarkan pada versi stabil terbaru dari mesin database Microsoft SQL Server, sehingga banyak konten serupa meskipun pilihan dan alat untuk menyelesaikan masalahnya mungkin berbeda. Untuk informasi selengkapnya tentang MAXDOP di SQL Server, lihat Mengonfigurasi tingkat maksimum opsi konfigurasi server paralelisme.

Gambaran Umum

MAXDOP mengontrol paralelisme intra-kueri di mesin database. Nilai MAXDOP yang lebih tinggi umumnya menghasilkan lebih banyak utas paralel per kueri, dan eksekusi kueri yang lebih cepat.

Di Azure SQL Database, pengaturan MAXDOP default untuk setiap database tunggal baru dan database kumpulan elastis adalah 8. Default ini mencegah pemanfaatan sumber daya yang tidak perlu, sementara masih memungkinkan mesin database untuk menjalankan kueri lebih cepat menggunakan utas paralel. MAXDOP biasanya tidak perlu dikonfigurasi lebih lanjut dalam beban kerja Azure SQL Database, meskipun konfigurasi dapat dimanfaatkan sebagai pelaksanaan penyetelan performa lanjutan.

Catatan

Pada bulan September 2020, berdasarkan telemetri bertahun-tahun dalam layanan Azure SQL Database, MAXDOP 8 dijadikan default untuk databasebaru sebagai nilai optimal untuk berbagai beban kerja pelanggan. Default ini membantu mencegah masalah kinerja karena paralelisme yang berlebihan. Sebelum itu, pengaturan default untuk database baru adalah MAXDOP 0. MAXDOP tidak secara otomatis diubah untuk database yang sudah ada yang dibuat sebelum September 2020.

Secara umum, jika mesin database memilih untuk menjalankan kueri menggunakan paralelisme, waktu eksekusi lebih cepat. Namun, paralelisme berlebih dapat menggunakan sumber daya prosesor tambahan tanpa meningkatkan performa kueri. Pada skala besar, paralelisme berlebih dapat berdampak negatif pada kinerja kueri untuk semua kueri yang dieksekusi pada instans mesin database yang sama. Umumnya, penetapan batas atas untuk paralelisme telah menjadi pelaksanaan penyetelan performa umum dalam beban kerja SQL Server.

Tabel berikut ini menjelaskan perilaku mesin database saat menjalankan kueri dengan nilai MAXDOP yang berbeda:

MAXDOP Perilaku
= 1 Mesin database menggunakan satu utas seri untuk menjalankan kueri. Utas paralel tidak digunakan.
> 1 Mesin database menetapkan jumlah penjadwal tambahan yang akan digunakan oleh utas paralel ke nilai MAXDOP, atau jumlah total prosesor logika, mana saja yang lebih kecil.
=0 Mesin database menetapkan jumlah penjadwal tambahan yang akan digunakan oleh utas paralel ke jumlah total prosesor logika atau 64, mana yang lebih kecil.

Catatan

Setiap kueri dijalankan dengan setidaknya satu penjadwal, dan satu utas pekerja pada penjadwal itu.

Kueri yang dijalankan dengan paralelisme menggunakan penjadwal tambahan, dan utas paralel tambahan. Karena beberapa thread paralel dapat dieksekusi pada penjadwal yang sama, jumlah utas yang digunakan untuk menjalankan kueri mungkin lebih tinggi dari nilai MAXDOP tertentu atau jumlah prosesor logika. Untuk informasi selengkapnya, lihat Menjadwalkan tugas paralel.

Pertimbangan

  • Di Azure SQL Database, Anda dapat mengubah nilai MAXDOP default:

  • Pertimbangan dan rekomendasi MAXDOP SQL Server yang telah lama berlaku untuk Azure SQL Database.

  • Operasi indeks untuk membuat atau membangun ulang indeks, atau membuang indeks berkluster, dapat memerlukan sumber daya intensif. Anda dapat mengganti nilai MAXDOP database untuk operasi indeks dengan menentukan opsi indeks MAXDOP dalam pernyataan CREATE INDEX atau ALTER INDEX. Nilai MAXDOP diterapkan ke pernyataan pada waktu eksekusi dan tidak disimpan dalam metadata indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

  • Selain kueri dan operasi indeks, opsi konfigurasi lingkup database untuk MAXDOP juga mengontrol paralelisme pernyataan lain yang dapat menggunakan eksekusi paralel, seperti DBCC CHECKTABLE, DBCC CHECKDB, dan DBCC CHECKFILEGROUP.

Rekomendasi

Mengubah MAXDOP untuk database dapat berdampak besar pada kinerja kueri dan pemanfaatan sumber daya, baik positif maupun negatif. Namun, tidak ada nilai MAXDOP tunggal yang optimal untuk semua beban kerja. Rekomendasi untuk pengaturan MAXDOP bernuansa, dan tergantung pada banyak faktor.

Beberapa beban kerja serentak puncak dapat beroperasi lebih baik dengan MAXDOP yang berbeda dari yang lain. MAXDOP yang dikonfigurasi dengan benar dapat mengurangi risiko insiden performa dan ketersediaan, dan di beberapa kasus, dapat mengurangi biaya dengan menghindari penggunaan sumber daya yang tidak perlu, yang menurunkan tujuan layanan ke level yang lebih rendah.

Paralelisme berlebihan

MAXDOP yang lebih tinggi sering mengurangi durasi untuk kueri intensif CPU. Namun, paralelisme yang berlebihan dapat memperburuk kinerja beban kerja bersamaan lainnya dengan melakukan starving kepada kueri lain dari sumber daya benang CPU dan pekerja. Dalam kasus ekstrim, paralelisme yang berlebihan dapat mengkonsumsi semua database atau sumber daya kumpulan elastis, menyebabkan kueri timeout, kesalahan, dan gangguan aplikasi.

Tip

Pelanggan disarankan untuk tidak menyetel MAXDOP ke 0 meskipun kelihatannya tidak menyebabkan masalah.

Paralelisme yang berlebihan menjadi paling bermasalah ketika ada permintaan yang lebih bersamaan daripada dapat didukung oleh CPU dan sumber daya utas pekerja yang disediakan oleh tujuan layanan. Hindari MAXDOP 0 untuk mengurangi risiko potensi masalah di masa mendatang akibat paralelisme yang berlebihan jika skala database ditingkatkan, atau jika konfigurasi perangkat keras di masa mendatang di Azure SQL Database menyediakan lebih banyak inti untuk tujuan layanan database yang sama.

Memodifikasi MAXDOP

Jika Anda merasa bahwa pengaturan MAXDOP yang berbeda dari default berdampak optimal pada beban kerja Azure SQL Database, Anda dapat menggunakan ALTER DATABASE SCOPED CONFIGURATION pernyataan T-SQL. Misalnya, lihat contoh menggunakan bagian Transact-SQL di bawah ini. Untuk mengubah MAXDOP menjadi nilai non-default untuk setiap database baru yang Anda buat, tambahkan langkah ini ke proses penyebaran database Anda.

Jika MAXDOP non-default hanya menguntungkan sebagian kecil kueri dalam beban kerja, Anda dapat mengganti MAXDOP pada tingkat kueri dengan menambahkan petunjuk OPTION (MAXDOP). Misalnya, lihat contoh menggunakan bagian Transact-SQL di bawah ini.

Uji secara menyeluruh perubahan konfigurasi MAXDOP Anda dengan pengujian beban yang melibatkan beban kueri bersamaan yang realistis.

MAXDOP untuk replika primer dan sekunder dapat dikonfigurasi secara independen jika pengaturan MAXDOP yang berbeda dianggap optimal untuk beban kerja read-write dan read-only Anda. Ini berlaku untuk Azure SQL Database read scale-out, geo-replication, dan replika sekunder Hyperscale. Secara default, semua replika sekunder mewarisi konfigurasi MAXDOP dari replika utama.

Keamanan

Izin

Pernyataan ALTER DATABASE SCOPED CONFIGURATION harus dijalankan sebagai admin server, sebagai anggota peran database db_owner, atau pengguna yang telah diberi ALTER ANY DATABASE SCOPED CONFIGURATION izin.

Contoh

Contoh-contoh ini menggunakan database sampel terbaru AdventureWorksLT saat SAMPLE opsi dipilih untuk database tunggal baru Azure SQL Database.

PowerShell

Konfigurasi yang dicakup database MAXDOP

Contoh ini menunjukkan cara menggunakan pernyataan KONFIGURASI LINGKUP ALTER DATABASE untuk mengatur konfigurasi MAXDOP ke 2. Pengaturan langsung berlaku untuk kueri baru. Cmdlet PowerShell Invoke-SqlCmd menjalankan kueri T-SQL untuk mengatur dan mengembalikan konfigurasi lingkup database MAXDOP.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP = 8

$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP + ';
     SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Contoh ini untuk digunakan dengan Azure SQL Database dengan replika peluasan skala baca diaktifkan, replikasi geografis, dan replika sekunder Azure SQL Database Hyperscale. Sebagai contoh, replika utama diatur ke MAXDOP default yang berbeda sebagai replika sekunder, mengantisipasi bahwa mungkin ada perbedaan antara beban kerja baca-tulis dan hanya baca.

$dbName = "sample" 
$serverName = <server name here>
$serveradminLogin = <login here>
$serveradminPassword = <password here>
$desiredMAXDOP_primary = 8
$desiredMAXDOP_secondary_readonly = 1
 
$params = @{
    'database' = $dbName
    'serverInstance' =  $serverName
    'username' = $serveradminLogin
    'password' = $serveradminPassword
    'outputSqlErrors' = $true
    'query' = 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = ' + $desiredMAXDOP_primary + ';
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = ' + $desiredMAXDOP_secondary_readonly + ';
    SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'
  }
  Invoke-SqlCmd @params

Transact-SQL

Anda dapat menggunakan editor kueri portal Azure, SQL Server Management Studio (SSMS),atau Azure Data Studio untuk menjalankan kueri T-SQL terhadap Azure SQL Database Anda.

  1. Buka jendela kueri baru.

  2. Sambungkan ke database tempat Anda ingin mengubah MAXDOP. Anda tidak dapat mengubah konfigurasi lingkup database dalam master database.

  3. Salin dan tempel contoh berikut ke dalam jendela kueri dan pilih Jalankan.

Konfigurasi yang dicakup database MAXDOP

Contoh ini memperlihatkan cara menentukan konfigurasi lingkup database MAXDOP database saat ini menggunakan sys.database_scoped_configurations katalog sistem.

SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

Contoh ini menunjukkan cara menggunakan pernyataan KONFIGURASI LINGKUP ALTER DATABASE untuk mengatur konfigurasi MAXDOP ke 8. Pengaturan langsung berlaku.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

Contoh ini untuk digunakan dengan Azure SQL Database dengan replika penskalaan baca diaktifkan, geo-replication,dan replika sekunder hyperscale Azure SQL Database. Sebagai contoh, replika utama diatur ke MAXDOP default yang berbeda sebagai replika sekunder, mengantisipasi bahwa mungkin ada perbedaan antara beban kerja read-write dan read-only. Semua pernyataan dijalankan pada replika utama. Kolom value_for_secondary dari sys.database_scoped_configurations berisi pengaturan untuk replika sekunder.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 1;
SELECT [value], value_for_secondary FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

Petunjuk kueri MAXDOP

Contoh ini memperlihatkan cara menjalankan kueri menggunakan petunjuk kueri untuk memaksa max degree of parallelism ke 2.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM SalesLT.SalesOrderDetail  
WHERE UnitPrice < 5  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

Opsi indeks MAXDOP

Contoh ini memperlihatkan cara menjalankan kueri menggunakan petunjuk kueri untuk memaksa max degree of parallelism ke 12.

ALTER INDEX ALL ON SalesLT.SalesOrderDetail 
REBUILD WITH 
   (     MAXDOP = 12
       , SORT_IN_TEMPDB = ON
       , ONLINE = ON);

Lihat juga

Langkah berikutnya