Mengonfigurasi tingkat paralelisme maksimum (MAXDOP) di Azure SQL Database dan database SQL di Fabric

Berlaku untuk:Database Azure SQLDatabase SQL di Fabric

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

Note

Konten ini difokuskan pada Azure SQL Database dan database SQL di Fabric. 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 dan Azure SQL Managed Instance, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme.

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 dan database SQL di Fabric, 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. Biasanya tidak perlu mengonfigurasi MAXDOP lebih lanjut dalam beban kerja Azure SQL Database, meskipun mungkin memberikan manfaat sebagai latihan penyetelan performa tingkat lanjut.

Note

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	Behavior
= 1	Mesin database menggunakan satu utas seri untuk menjalankan kueri. Utas paralel tidak digunakan.
> 1	Mesin basis data menetapkan jumlah penjadwal tambahan yang akan digunakan oleh utas paralel ke nilai MAXDOP, atau jumlah total prosesor logika, yang mana pun lebih kecil.
= 0	Mesin database menetapkan jumlah penjadwal tambahan untuk digunakan oleh utas paralel pada jumlah total prosesor logika atau 64, yang mana pun lebih kecil.

Note

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 utas paralel mungkin dijalankan pada penjadwal yang sama, jumlah total utas yang digunakan untuk menjalankan kueri bisa lebih tinggi dari nilai MAXDOP yang ditentukan atau jumlah total prosesor logis. Untuk informasi selengkapnya, lihat Menjadwalkan tugas paralel.

Considerations

• Di Database Azure SQL dan database SQL di Fabric, Anda dapat mengubah nilai MAXDOP default:

  • Pada tingkat kueri, menggunakan petunjuk kueri MAXDOP.
  • Pada tingkat database, menggunakan konfigurasi lingkup databaseMAXDOP.

• Pertimbangan dan rekomendasi MAXDOP SQL Server yang sudah lama digunakan berlaku untuk Azure SQL Database dan database SQL di Fabric.

• 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 cakupan database untuk MAXDOP juga mengontrol paralelisme pernyataan lain yang dapat menggunakan eksekusi paralel, seperti DBCC CHECKTABLE, , DBCC CHECKDBdan DBCC CHECKFILEGROUP.

Recommendations

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 bersifat rumit, dan tergantung pada banyak faktor.

Beberapa beban kerja puncak yang bersamaan mungkin beroperasi lebih baik dengan MAXDOP yang berbeda dibandingkan yang lain. MAXDOP yang dikonfigurasi dengan benar harus mengurangi risiko insiden performa dan ketersediaan, dan dalam beberapa kasus dapat mengurangi biaya dengan dapat menghindari pemanfaatan sumber daya yang tidak perlu, dan dengan demikian menurunkan skala ke tujuan layanan yang lebih rendah.

Paralelisme yang berlebihan

MAXDOP yang lebih tinggi sering mengurangi waktu yang dibutuhkan untuk kueri yang memerlukan banyak CPU. Namun, paralelisme yang berlebihan dapat memperburuk kinerja beban kerja bersamaan lainnya dengan membuat kueri lain kekurangan sumber daya CPU dan thread 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 karena paralelisme yang berlebihan jika database ditingkatkan skalanya, atau jika konfigurasi perangkat keras di masa mendatang menyediakan lebih banyak inti untuk tujuan layanan database yang sama.

Mengubah MAXDOP

Jika Anda menentukan bahwa pengaturan MAXDOP yang berbeda dari default optimal untuk beban kerja Anda, 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 memberikan keuntungan hanya pada sebagian kecil kueri dalam beban kerja, Anda dapat mengubah MAXDOP di tingkat kueri dengan menambahkan OPTION (MAXDOP) hint. Misalnya, lihat Contoh menggunakan Transact-SQL.

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 read scale-out Azure SQL Database, geo-replikasi, dan replika sekunder tingkat layanan Hyperscale. Secara default, semua replika sekunder mewarisi konfigurasi MAXDOP dari replika utama.

Permissions

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

Examples

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

PowerShell

Konfigurasi yang dicakup database MAXDOP

Contoh ini menunjukkan cara menggunakan pernyataan KONFIGURASI LINGKUP ALTER DATABASE untuk mengatur konfigurasi MAXDOP ke 2. Pengaturan berlaku segera 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 berskala-baca diaktifkan, replikasi geografis, dan replika sekunder Azure SQL Database Hyperscale. Sebagai contoh, replika utama diatur dengan MAXDOP default yang berbeda dibandingkan dengan replika sekunder, mengantisipasi bahwa mungkin ada perbedaan antara beban kerja baca-tulis dan beban kerja baca-saja.

$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 Microsoft Azure untuk Azure SQL Database, SQL Server Management Studio (SSMS), ekstensi MSSQL untuk Visual Studio Code, atau editor kueri SQL di portal Fabric untuk menjalankan kueri T-SQL.

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 yang diaktifkan untuk penskalaan baca, geo-replication, dan replika sekunder Hyperscale di Azure SQL Database. Sebagai contoh, replika utama diatur dengan MAXDOP yang berbeda dari replika sekunder, dengan memperkirakan adanya perbedaan antara beban kerja baca-tulis dan baca-saja. 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 membangun ulang indeks menggunakan opsi indeks untuk memaksa max degree of parallelism ke 12.

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

Langkah selanjutnya

Monitor dan Selaraskan Kinerja

Konten terkait

• MENGUBAH KONFIGURASI BERDASARKAN CAKUPAN DATABASE (Transact-SQL)
• sys.database_scoped_configurations (Transact-SQL)
• Mengonfigurasi Operasi Indeks Paralel
• Petunjuk Kueri (Transact-SQL)
• Atur Opsi Indeks
• Memahami dan mengatasi masalah pemblokiran