Praktik terbaik untuk kumpulan SQL pada Azure Synapse Analytics

  • Artikel

Artikel ini menyediakan kumpulan praktik terbaik untuk membantu Anda mencapai performa optimal untuk kumpulan SQL khusus pada Azure Synapse Analytics. Jika Anda bekerja dengan kumpulan SQL tanpa server, lihat Praktik terbaik untuk kumpulan SQL tanpa server untuk panduan khusus. Di bawah ini, Anda akan menemukan panduan dasar dan area fokus penting saat membangun solusi Anda. Setiap bagian memperkenalkan konsep dan memfokuskan Anda ke artikel yang lebih rinci dengan konsep yang lebih mendalam.

Pemuatan kumpulan SQL khusus

Untuk panduan pemuatan kumpulan SQL khusus, lihat Panduan untuk memuat data.

Mengurangi biaya dengan jeda dan skala

Untuk informasi selengkapnya tentang mengurangi biaya melalui jeda dan penskalaan, lihat Kelola komputasi.

Mengelola statistik

Kumpulan SQL dapat dikonfigurasikan untuk mendeteksi dan membuat statistik secara otomatis pada kolom. Rencana kueri yang dibuat oleh pengoptimal sama bagusnya dengan kualitas statistik yang tersedia.

Sebaiknya aktifkan AUTO_CREATE_STATISTICS untuk database Anda dan terus memperbarui statistik setiap hari atau setelah setiap pemuatan untuk memastikan bahwa statistik pada kolom yang digunakan dalam kueri Anda selalu baru.

Untuk mempersingkat waktu pemeliharaan statistik, jadilah selektif terhadap kolom mana yang memiliki statistik atau perlu pembaruan yang paling sering. Sebagai contoh, Anda mungkin ingin memperbarui kolom tanggal, di mana nilai baru dapat ditambahkan setiap hari. Fokuslah pada mendapatkan statistik pada kolom yang terlibat dalam gabungan, kolom yang digunakan dalam klausa WHERE, dan kolom yang ditemukan di GROUP BY.

Informasi tambahan tentang statistik dapat ditemukan di artikel Kelola statistik tabel, CREATE STATISTICS dan UPDATE STATISTICS artikel.

Menyesuaikan performa kueri

Kelompokkan pernyataan INSERT ke dalam batch

Satu kali pemuatan ke tabel kecil dengan pernyataan INSERT seperti INSERT INTO MyLookup VALUES (1, 'Type 1') mungkin merupakan pendekatan terbaik tergantung pada kebutuhan Anda. Namun, jika Anda perlu memuat ribuan atau jutaan baris sepanjang hari, kemungkinan database tunggal INSERT tidak optimal.

Salah satu cara untuk mengatasi masalah ini adalah dengan mengembangkan satu proses yang menulis ke file, dan kemudian proses lain untuk secara berkala memuat file ini. Lihat INSERT artikel untuk informasi lebih lanjut.

Gunakan PolyBase untuk memuat dan mengekspor data dengan cepat

Kumpulan SQL khusus mendukung pemuatan dan ekspor data melalui beberapa alat termasuk Azure Data Factory, PolyBase, dan BCP. Untuk sejumlah kecil data di mana performa tidaklah penting, alat apa pun mungkin cukup untuk kebutuhan Anda.

Catatan

PolyBase adalah pilihan terbaik ketika Anda memuat atau mengekspor data dalam volume besar, atau Anda membutuhkan performa yang lebih cepat.

Beban PolyBase dapat dijalankan menggunakan CTAS atau INSERT INTO. CTAS akan meminimalkan pengelogan transaksi dan merupakan cara tercepat untuk memuat data Anda. Azure Data Factory juga mendukung beban PolyBase dan dapat mencapai performa yang mirip dengan CTAS. PolyBase mendukung berbagai format file termasuk file Gzip.

Untuk memaksimalkan throughput saat menggunakan file teks gzip, pisahkan menjadi 60 file atau lebih guna memaksimalkan paralelisme beban Anda. Untuk throughput total yang lebih cepat, pertimbangkan untuk memuat data secara bersamaan. Informasi tambahan yang relevan dengan bagian ini disertakan dalam artikel berikut:

Muat lalu kuerikan tabel eksternal

PolyBase tidak optimal untuk kueri. Tabel PolyBase untuk kumpulan SQL khusus saat ini hanya mendukung file blob Azure dan penyimpanan Azure Data Lake. File-file ini tidak memiliki sumber daya komputasi yang mendukungnya. Akibatnya, kumpulan SQL khusus tidak dapat membongkar pekerjaan ini dan harus membaca seluruh file dengan memuatnya ke tempdb agar dapat membaca data.

Jika Anda memiliki beberapa kueri untuk mengkueri data ini, lebih baik memuat data ini sekali saja dan meminta kueri menggunakan tabel lokal. Panduan PolyBase lebih lanjut disertakan dalam artikel Panduan untuk menggunakan PolyBase.

Hash mendistribusikan tabel besar

Secara default, tabel didistribusikan secara Round Robin. Default ini memudahkan pengguna untuk mulai membuat tabel tanpa harus memutuskan bagaimana tabel mereka harus didistribusikan. Tabel Round Robin mungkin berperforma cukup untuk beberapa beban kerja. Tetapi, dalam kebanyakan kasus, kolom distribusi memiliki performa yang lebih baik.

Contoh paling umum dari tabel yang didistribusikan oleh kolom yang mengungguli tabel round robin adalah ketika dua tabel fakta besar digabungkan.

Contohnya, jika Anda memiliki tabel pesanan yang didistribusikan oleh order_id, dan tabel transaksi juga didistribusikan oleh order_id, saat Anda menggabungkan keduanya di order_id, kueri ini menjadi permintaan kirim langsung. Operasi pergerakan data kemudian dihilangkan. Langkah yang lebih sedikit adalah kueri yang lebih cepat. Pergerakan data yang lebih sedikit juga membuat kueri lebih cepat.

Tip

Saat memuat tabel terdistribusi, data masuk Anda tidak boleh diurutkan pada kunci distribusi. Jika dilakukan, Anda akan memperlambat pemuatan.

Tautan artikel yang disediakan di bawah ini akan memberi Anda detail tambahan tentang meningkatkan performa dengan memilih kolom distribusi. Selain itu, Anda akan menemukan informasi tentang cara menentukan tabel terdistribusi dalam klausa WITH dari pernyataan CREATE TABLE Anda:

Jangan mempartisi berlebihan

Meskipun pemartisian data dapat mempertahankan data Anda secara efektif melalui pengalihan partisi atau mengoptimalkan pemindaian dengan eliminasi partisi, memiliki terlalu banyak partisi dapat memperlambat kueri Anda. Sering kali strategi partisi granularitas tinggi yang mungkin bekerja dengan baik di SQL Server mungkin tidak berfungsi dengan baik pada kumpulan SQL khusus.

Memiliki terlalu banyak partisi juga dapat mengurangi efektivitas indeks penyimpan kolom berkluster jika setiap partisi memiliki kurang dari 1 juta baris. Kumpulan SQL khusus secara otomatis membagi data Anda ke dalam 60 database. Jadi, jika Anda membuat tabel dengan 100 partisi, hasilnya akan menjadi 6000 partisi. Setiap beban kerja berbeda sehingga saran terbaik adalah bereksperimen dengan pemartisian untuk melihat apa yang paling sesuai dengan beban kerja Anda.

Salah satu opsi yang perlu dipertimbangkan adalah menggunakan granularitas yang lebih rendah dari apa yang telah Anda terapkan menggunakan SQL Server. Contohnya, pertimbangkan untuk menggunakan partisi mingguan atau bulanan daripada partisi harian.

Informasi lebih lanjut tentang partisi diperinci dalam Tabel partisi artikel.

Mengecilkan ukuran transaksi

Pernyataan INSERT, UPDATE, dan DELETE berjalan dalam transaksi. Ketika mereka gagal, mereka harus dibatalkan. Untuk mengurangi potensi pembatalan yang panjang, kecilkan ukuran transaksi jika memungkinkan. Ini dapat dilakukan dengan membagi pernyataan INSERT, UPDATE, dan DELETE menjadi beberapa bagian. Contohnya, jika Anda memiliki INSERT yang Anda harapkan memakan waktu 1 jam, Anda dapat memecah INSERT menjadi empat bagian, sehingga memperpendek setiap eksekusi menjadi 15 menit. Kemudian, setiap eksekusi alur akan dipersingkat menjadi 15 menit.

Tip

Manfaatkan kasus Minimal Logging khusus, seperti CTAS, TRUNCATE, DROP TABLE atau INSERT ke tabel kosong, untuk mengurangi risiko pembatalan.

Cara lain untuk menghilangkan pembatalan adalah dengan menggunakan operasi Metadata Only seperti pengalihan partisi untuk manajemen data. Misalnya, daripada menjalankan pernyataan DELETE untuk menghapus semua baris dalam tabel tempat order_date pada Oktober 2001, Anda dapat mempartisi data Anda setiap bulan. Kemudian, Anda dapat mengalihkan partisi dengan data untuk partisi kosong dari tabel lain (lihat contoh ALTER TABLE).

Untuk tabel yang tidak dipartisi, pertimbangkan untuk menggunakan CTAS untuk menulis data yang ingin Anda simpan dalam tabel daripada menggunakan DELETE. Jika CTAS membutuhkan jumlah waktu yang sama, itu adalah operasi yang jauh lebih aman karena memiliki pengelogan transaksi minimal dan dapat segera dibatalkan jika perlu.

Informasi lebih lanjut tentang konten yang terkait dengan bagian ini disertakan dalam artikel di bawah ini:

Kurangi ukuran hasil kueri

Mengurangi ukuran hasil kueri membantu Anda menghindari masalah sisi klien yang disebabkan oleh hasil kueri yang besar. Anda bisa mengedit kueri Anda untuk mengurangi jumlah baris yang dikembalikan. Beberapa alat pembuatan kueri memungkinkan Anda untuk menambahkan sintaks "N teratas" ke setiap kueri. Anda juga dapat CETAS hasil kueri ke tabel sementara lalu menggunakan ekspor PolyBase untuk pemrosesan tingkat bawah.

Gunakan ukuran kolom sekecil mungkin

Saat menentukan DDL Anda, menggunakan jenis data terkecil yang akan mendukung data Anda akan meningkatkan performa kueri. Rekomendasi ini sangat penting untuk kolom CHAR dan VARCHAR. Jika nilai terpanjang di kolom adalah 25 karakter, maka definisikan kolom Anda sebagai VARCHAR(25). Hindari mendefinisikan semua kolom karakter dengan panjang default yang besar. Selain itu, tentukan kolom sebagai VARCHAR ketika diperlukan daripada menggunakan NVARCHAR.

Silakan lihat Gambaran umum tabel, Tabel data jenis, dan CREATE TABLE artikel untuk tinjauan lebih rinci tentang konsep penting yang relevan dengan informasi di atas.

Gunakan tabel heap sementara untuk data sementara

Ketika Anda untuk sementara mendaratkan data pada kumpulan SQL khusus, tabel heap umumnya akan membuat proses keseluruhan menjadi lebih cepat. Jika Anda memuat data hanya untuk ditunjukkan sebelum menjalankan lebih banyak transformasi, memuat tabel ke tabel heap jauh lebih cepat daripada memuat data ke tabel penyimpan kolom berkluster.

Memuat data ke tabel sementara juga akan dimuat jauh lebih cepat daripada memuat tabel ke penyimpanan permanen. Tabel sementara dimulai dengan "#" dan hanya dapat diakses oleh sesi yang membuatnya. Akibatnya, mereka hanya dapat bekerja dalam skenario terbatas. Tabel heap didefinisikan dalam klausa WITH dari CREATE TABLE. Jika Anda menggunakan tabel sementara, ingatlah juga untuk membuat statistik pada tabel sementara tersebut.

Untuk informasi selengkapnya, lihat artikel Tabel sementara, BUAT TABEL, dan BUAT TABEL SEBAGAI PILIHAN.

Mengoptimalkan tabel penyimpanan kolom terkluster

Indeks penyimpan kolom berkluster adalah salah satu cara paling efisien untuk menyimpan data Anda pada kumpulan SQL khusus. Secara default, tabel dalam kumpulan SQL khusus dibuat sebagai Penyimpanan Kolom Terkluster. Untuk mendapatkan performa terbaik dari kueri pada tabel penyimpanan kolom, penting untuk memiliki kualitas segmen yang baik. Saat baris ditulis ke tabel penyimpanan kolom di bawah tekanan memori, kualitas segmen penyimpanan kolom akan menurun.

Kualitas segmen dapat diukur berdasarkan jumlah baris dalam grup baris terkompresi. Lihat Penyebab buruknya kualitas indeks penyimpan kolom dalam Indeks tabel artikel untuk instruksi langkah demi langkah tentang mendeteksi dan meningkatkan kualitas segmen untuk tabel penyimpanan kolom berkluster.

Karena segmen penyimpanan kolom berkualitas tinggi penting, ada baiknya menggunakan ID pengguna yang berada di kelas sumber daya menengah atau besar untuk memuat data. Menggunakan unit gudang data yang lebih rendah berarti Anda ingin menetapkan kelas sumber daya yang lebih besar untuk pengguna pemuatan Anda.

Tabel penyimpan kolom umumnya tidak akan mendorong data ke segmen penyimpan kolom terkompresi hingga ada lebih dari 1 juta baris per tabel. Setiap tabel kumpulan SQL khusus didistribusikan ke dalam 60 distribusi yang berbeda. Dengan demikian, tabel penyimpan kolom tidak akan menguntungkan kueri kecuali tabel memiliki lebih dari 60 juta baris.

Tip

Untuk tabel dengan kurang dari 60 juta baris, memiliki indeks penyimpanan kolom mungkin bukan solusi optimal.

Jika Anda mempartisi data Anda, setiap partisi harus memiliki 1 juta baris untuk mendapatkan manfaat dari indeks penyimpan kolom berkluster. Jika tabel memiliki 100 partisi, maka perlu memiliki setidaknya 6 miliar baris untuk mendapatkan manfaat dari penyimpanan kolom terkluster (60 distribusi 100 partisi 1 juta baris).

Jika tabel Anda tidak memiliki 6 miliar baris, Anda memiliki dua opsi utama. Baik kurangi jumlah partisi atau pertimbangkan untuk menggunakan tabel heap sebagai gantinya. Mungkin juga perlu bereksperimen untuk melihat apakah performa yang lebih baik dapat diperoleh dengan menggunakan tabel tumpukan dengan indeks sekunder daripada tabel penyimpanan kolom.

Saat mengkueri tabel penyimpan kolom, kueri akan berjalan lebih cepat jika Anda hanya memilih kolom yang Anda butuhkan. Informasi lebih lanjut tentang indeks tabel dan penyimpan kolom dan dapat ditemukan pada artikel di bawah ini:

Gunakan kelas sumber daya yang lebih besar untuk meningkatkan performa kueri

Kumpulan SQL menggunakan grup sumber daya untuk mengalokasikan memori ke kueri. Pada awalnya, semua pengguna ditetapkan ke kelas sumber daya kecil yang memberikan memori 100 MB per distribusi. Selalu ada 60 distribusi. Setiap distribusi diberikan minimal 100 MB. Total alokasi memori seluruh sistem adalah 6.000 MB, atau hanya di bawah 6 GB.

Kueri tertentu, seperti gabungan besar atau beban ke tabel penyimpan kolom berkluster, akan mendapat manfaat dari alokasi memori yang lebih besar. Beberapa kueri, seperti pemindaian murni, tidak akan mendapatkan manfaatnya. Memanfaatkan kelas sumber daya yang lebih besar berdampak pada konkurensi. Jadi, Anda harus mengingat ini semua sebelum memindahkan semua pengguna ke kelas sumber daya yang besar.

Untuk informasi tambahan tentang kelas sumber daya, lihat Sumber daya kelas untuk manajemen beban kerja artikel.

Gunakan kelas sumber daya yang lebih kecil untuk meningkatkan konkurensi

Jika Anda melihat penundaan panjang dalam kueri pengguna, pengguna Anda mungkin berjalan di kelas sumber daya yang lebih besar. Skenario ini mempromosikan konsumsi slot konkurensi yang dapat menyebabkan antrean kueri lain. Untuk menentukan apakah kueri pengguna diantrikan, jalankan SELECT * FROM sys.dm_pdw_waits untuk melihat apakah ada baris yang dikembalikan.

Sumber Daya kelas untuk manajemen beban kerja dan sys.dm_pdw_waits artikel akan memberi Anda informasi lebih lanjut.

Gunakan DMV untuk memantau dan mengoptimalkan kueri Anda

Kumpulan SQL khusus memiliki beberapa DMV yang dapat digunakan untuk memantau eksekusi kueri. Artikel pemantauan di bawah ini memandu Anda melalui instruksi langkah demi langkah tentang cara menampilkan detail kueri yang berjalan. Untuk menemukan kueri di DMV ini dengan cepat, menggunakan opsi LABEL dengan kueri Anda bisa membantu. Untuk informasi lebih terperinci, silakan lihat artikel yang disertakan dalam daftar di bawah ini:

Langkah berikutnya

Lihat juga Pemecahan Masalah artikel untuk masalah umum beserta solusinya.

Jika Anda memerlukan informasi yang tidak tersedia di artikel ini, mencari Microsoft Q&A halaman pertanyaan untuk Azure Synapse adalah tempat Anda untuk mengajukan pertanyaan kepada pengguna lain dan ke Grup Produk Azure Synapse Analytics.

Kami secara aktif memantau forum ini untuk memastikan bahwa pertanyaan Anda dijawab baik oleh pengguna lain atau salah satu dari kami. Jika Anda lebih suka mengajukan pertanyaan anda di Stack Overflow, kami juga memiliki Azure Synapse Analytics Stack Overflow Forum internet.