Bagikan melalui

Praktik terbaik untuk kumpulan SQL khusus di Azure Synapse Analytics

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 Anda pada sebuah konsep dan mengarahkan Anda ke artikel yang lebih rinci yang membahas konsep tersebut lebih dalam.

Pemuatan kumpulan SQL khusus

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

Mengurangi biaya dengan menjeda dan menyesuaikan skala

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

Menjaga statistik

Kumpulan SQL khusus dapat dikonfigurasi untuk secara otomatis mendeteksi dan membuat statistik 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.

Mengoptimalkan 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 memasukkan ribuan atau jutaan baris sepanjang hari, kemungkinan bahwa metode INSERT tunggal 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.

Proses pemuatan 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 mengalihkan pekerjaan ini dan harus membaca seluruh file dengan memuatnya ke tempdb agar dapat mengakses data tersebut.

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 berfungsi memadai untuk beberapa jenis beban kerja. Tetapi, dalam kebanyakan kasus, kolom distribusi memiliki performa yang lebih baik.

Contoh paling umum dari tabel yang didistribusikan berdasarkan kolom yang lebih unggul dibandingkan 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. Lebih sedikit langkah berarti kueri yang lebih cepat. Pergerakan data yang lebih sedikit juga menghasilkan kueri yang lebih cepat.

Petunjuk

Saat memuat tabel terdistribusi, data masuk Anda tidak boleh diurutkan pada kunci distribusi. Melakukan hal ini akan memperlambat pemuatan.

Tautan artikel yang disediakan di bawah ini akan memberi Anda detail selengkapnya tentang meningkatkan performa melalui 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 6.000 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 dikembalikan. Untuk mengurangi potensi pembatalan yang panjang, kecilkan ukuran transaksi jika memungkinkan. Meminimalkan ukuran transaksi 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. Pelaksanaan tiap sesi kemudian akan dipersingkat menjadi 15 menit.

Tip

Gunakan kasus Pengelogan Minimal khusus, seperti CTAS, TRUNCATE, DROP TABLE, atau INSERT ke tabel kosong untuk mengurangi risiko putar kembali.

Cara lain untuk menghilangkan pembatalan adalah dengan menggunakan operasi Metadata Only seperti pengalihan partisi untuk manajemen data. Misalnya, daripada mengoperasikan perintah DELETE untuk menghilangkan semua baris dalam tabel di mana order_date berada pada bulan Oktober 2001, Anda dapat memisahkan data Anda menjadi bulanan. Kemudian, Anda dapat menukar partisi berisi data dengan 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 mendefinisikan DDL Anda, gunakan jenis data terkecil yang akan mendukung data Anda, karena melakukan hal tersebut 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 jika itu sudah mencukupi, daripada menggunakan NVARCHAR.

Lihat artikel Gambaran umum tabel, Jenis data tabel, dan CREATE TABLE untuk tinjauan yang lebih rinci tentang konsep penting yang relevan dengan informasi di atas.

Gunakan tabel heap sementara untuk data transien

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 perintah 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.

Petunjuk

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. Kurangi jumlah partisi atau, sebagai alternatif, pertimbangkan untuk menggunakan tabel heap. 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 dapat ditemukan dalam 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. Terdapat selalu 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 pemuatan ke tabel 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 selengkapnya tentang kelas sumber daya, lihat artikel Kelas sumber daya untuk manajemen beban kerja.

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 mendorong penggunaan slot konkuren yang dapat mengantrikan kueri lainnya. Untuk menentukan apakah kueri pengguna diantrikan, jalankan SELECT * FROM sys.dm_pdw_waits untuk melihat apakah ada baris yang dikembalikan.

Kelas sumber daya untuk manajemen beban kerja dan artikel sys.dm_pdw_waits akan memberikan informasi lebih lanjut kepada Anda.

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 dengan cepat di DMV ini, menggunakan opsi LABEL dalam kueri Anda dapat membantu. Untuk informasi terperinci tambahan, lihat artikel yang disertakan dalam daftar di bawah ini:

Konten terkait

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.