Rencanakan adopsi Fitur OLTP In-Memory Anda di SQL Server

  • Artikel
  • 8 menit untuk membaca

Berlaku untuk:yes SQL Server (semua versi yang didukung) YesAzure SQL Database YesAzure SQL Managed Instance

Artikel ini menjelaskan cara adopsi fitur In-Memory memengaruhi aspek lain dari sistem bisnis Anda.

J. Adopsi fitur OLTP In-Memory

Sub-bagian berikut membahas faktor-faktor yang harus Anda pertimbangkan ketika Anda berencana untuk mengadopsi dan menerapkan fitur In-Memory. Banyak informasi penjelasan tersedia di:

Prasyarat A.1

Salah satu prasyarat untuk menggunakan fitur In-Memory dapat melibatkan edisi atau tingkat layanan produk SQL. Untuk prasyarat ini dan prasyarat lainnya, lihat:

A.2 Memperkirakan jumlah memori aktif

Apakah sistem Anda memiliki memori aktif yang cukup untuk mendukung tabel baru yang dioptimalkan memori?

Microsoft SQL Server

Tabel yang dioptimalkan memori yang berisi data 200 GB memerlukan lebih dari 200 GB memori aktif untuk dukungannya. Sebelum menerapkan tabel memori yang dioptimalkan yang berisi sejumlah besar data, Anda harus memperkirakan jumlah memori aktif tambahan yang mungkin perlu Anda tambahkan ke komputer server Anda. Untuk panduan estimasi, lihat:

Azure SQL Database

Untuk database yang dihosting di layanan cloud Azure SQL Database, tingkat layanan yang Anda pilih memengaruhi jumlah memori aktif yang diizinkan untuk digunakan database Anda. Anda harus berencana untuk memantau penggunaan memori database Anda dengan menggunakan pemberitahuan. Untuk detailnya, lihat:

Variabel tabel yang dioptimalkan memori

Variabel tabel yang dinyatakan optimal memori terkadang lebih disukai daripada #TempTable tradisional yang berada di database tempdb . Variabel tabel tersebut dapat memberikan perolehan performa yang signifikan tanpa menggunakan memori aktif dalam jumlah yang signifikan.

Tabel A.3 harus offline untuk dikonversi ke memori yang dioptimalkan

Beberapa fungsionalitas ALTER TABLE tersedia untuk tabel yang dioptimalkan memori. Tetapi Anda tidak dapat mengeluarkan pernyataan ALTER TABLE untuk mengonversi tabel berbasis disk menjadi tabel yang dioptimalkan memori. Sebagai gantinya, Anda harus menggunakan serangkaian langkah yang lebih manual. Berikut ini adalah berbagai cara anda dapat mengonversi tabel berbasis disk anda menjadi memori-dioptimalkan.

Pembuatan skrip manual

Salah satu cara untuk mengonversi tabel berbasis disk Anda ke tabel yang dioptimalkan memori adalah dengan mengkodekan langkah-langkah SQL Transact yang diperlukan sendiri.

  1. Menangguhkan aktivitas aplikasi.

  2. Ambil cadangan penuh.

  3. Ganti nama tabel berbasis disk Anda.

  4. Terbitkan pernyataan CREATE TABLE untuk membuat tabel baru yang dioptimalkan memori Anda.

  5. INSERT INTO tabel yang dioptimalkan memori Anda dengan sub-SELECT dari tabel berbasis disk.

  6. DROP tabel berbasis disk Anda.

  7. Ambil cadangan penuh lainnya.

  8. Lanjutkan aktivitas aplikasi.

Advisor Pengoptimalan Memori

Alat Advisor Pengoptimalan Memori dapat menghasilkan skrip untuk membantu mengimplementasikan konversi tabel berbasis disk ke tabel yang dioptimalkan memori. Alat ini dipasang sebagai bagian dari SQL Server Data Tools (SSDT).

File .dacpac

Anda dapat memperbarui database di tempat dengan menggunakan file .dacpac, yang dikelola oleh SSDT. Di SSDT Anda dapat menentukan perubahan pada skema yang dikodekan dalam file .dacpac.

Anda bekerja dengan file .dacpac dalam konteks proyek Visual Studio jenis Database.

Panduan A.4 untuk apakah fitur OLTP In-Memory tepat untuk aplikasi Anda

Untuk panduan tentang apakah fitur OLTP In-Memory dapat meningkatkan performa aplikasi tertentu Anda, lihat:

B. Fitur tidak didukung

Fitur yang tidak didukung dalam skenario OLTP In-Memory tertentu dijelaskan di:

Subbagian berikut menyoroti beberapa fitur yang lebih penting yang tidak didukung.

REKAM JEPRET B.1 database

Setelah pertama kali tabel atau modul yang dioptimalkan memori dibuat dalam database tertentu, tidak ada SNAPSHOT database yang dapat diambil. Alasan khususnya adalah bahwa:

  • Item pertama yang dioptimalkan memori membuatnya tidak mungkin untuk pernah menjatuhkan file terakhir dari FILEGROUP yang dioptimalkan memori; Dan
  • Tidak ada database yang memiliki file dalam FILEGROUP yang dioptimalkan memori yang dapat mendukung SNAPSHOT.

Biasanya SNAPSHOT dapat berguna untuk iterasi pengujian cepat.

Kueri lintas database B.2

Tabel yang dioptimalkan memori tidak mendukung transaksi lintas database . Anda tidak dapat mengakses database lain dari transaksi yang sama atau kueri yang sama yang juga mengakses tabel yang dioptimalkan memori.

Variabel tabel tidak transaksi. Oleh karena itu, variabel tabel yang dioptimalkan memori dapat digunakan dalam kueri lintas database.

Petunjuk tabel B.3 READPAST

Tidak ada kueri yang dapat menerapkan petunjuk tabel READPAST ke tabel yang dioptimalkan memori apa pun.

Petunjuk READPAST berguna dalam skenario di mana beberapa sesi masing-masing mengakses dan memodifikasi sekumpulan baris kecil yang sama, seperti dalam memproses antrean.

B.4 RowVersion, Sequence

  • Tidak ada kolom yang dapat ditandai untuk RowVersion pada tabel yang dioptimalkan memori.

  • SEQUENCE tidak dapat digunakan dengan batasan dalam tabel yang dioptimalkan memori. Misalnya, Anda tidak dapat membuat batasan DEFAULT dengan klausa NEXT VALUE FOR. SEQUENCEs dapat digunakan dengan pernyataan INSERT dan UPDATE.

C. Pemeliharaan administratif

Bagian ini menjelaskan perbedaan dalam administrasi database tempat tabel yang dioptimalkan memori digunakan.

Reset seed identitas C.1, kenaikan > 1

DBCC CHECKIDENT, untuk mem-reseed kolom IDENTITY, tidak dapat digunakan pada tabel yang dioptimalkan memori.

Nilai kenaikan dibatasi tepat 1 untuk kolom IDENTITY pada tabel yang dioptimalkan memori.

C.2 DBCC CHECKDB tidak dapat memvalidasi tabel yang dioptimalkan memori

Perintah DBCC CHECKDB tidak melakukan apa pun ketika targetnya adalah tabel yang dioptimalkan memori. Langkah-langkah berikut adalah solusi:

  1. Cadangkan log transaksi.

  2. Cadangkan file dalam FILEGROUP yang dioptimalkan memori ke perangkat null. Proses pencadangan memanggil validasi checksum.

    Jika kerusakan ditemukan, lanjutkan dengan langkah berikutnya.

  3. Salin data dari tabel yang dioptimalkan memori Anda ke dalam tabel berbasis disk, untuk penyimpanan sementara.

  4. Pulihkan file FILEGROUP yang dioptimalkan memori.

  5. INSERT INTO tabel yang dioptimalkan memori data yang Anda simpan sementara dalam tabel berbasis disk.

  6. HILANGKAN tabel berbasis disk yang menahan data untuk sementara waktu.

D. Performa

Bagian ini menjelaskan situasi di mana performa tabel yang dioptimalkan memori dapat disimpan di bawah potensi penuh.

Pertimbangan Indeks D.1

Semua indeks pada tabel yang dioptimalkan memori dibuat dan dikelola oleh pernyataan terkait tabel CREATE TABLE dan ALTER TABLE. Anda tidak dapat menargetkan tabel yang dioptimalkan memori dengan pernyataan CREATE INDEX.

Indeks nonkluster pohon B tradisional sering kali menjadi pilihan yang masuk akal dan sederhana ketika Anda pertama kali menerapkan tabel yang dioptimalkan memori. Kemudian, setelah Anda melihat performa aplikasi Anda, Anda dapat mempertimbangkan untuk menukar jenis indeks lain.

Catatan

SQL Server dokumentasi menggunakan istilah pohon B umumnya mengacu pada indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Tinjau Panduan Arsitektur dan Desain Indeks SQL Server untuk detailnya.

Dua jenis indeks khusus memerlukan diskusi dalam konteks tabel yang dioptimalkan memori: Indeks hash, dan indeks Penyimpan Kolom.

Untuk gambaran umum indeks pada tabel yang dioptimalkan memori, lihat:

Indeks hash

Indeks hash dapat menjadi format tercepat untuk mengakses satu baris tertentu dengan nilai kunci primer yang tepat dengan menggunakan operator ''=.

  • Operator yang tidak tepat seperti '!=', '>', atau 'BETWEEN' akan membahayakan performa jika digunakan dengan indeks hash.

  • Indeks hash mungkin bukan pilihan terbaik jika tingkat duplikasi nilai kunci menjadi terlalu tinggi.

  • Jaga agar tidak meremehkan berapa banyak wadah yang mungkin dibutuhkan indeks hash Anda, untuk menghindari rantai panjang dalam wadah individual. Untuk detailnya, lihat:

Indeks penyimpan kolom nonkluster

Tabel yang dioptimalkan memori memberikan throughput tinggi dari data transaksional bisnis umum, dalam paradigma yang kami sebut pemrosesan transaksi online atau OLTP. Indeks penyimpan kolom memberikan throughput agregasi yang tinggi dan pemrosesan serupa yang kami sebut Analytics. Dalam beberapa tahun terakhir, pendekatan terbaik yang tersedia untuk memenuhi kebutuhan OLTP dan Analytics adalah memiliki tabel terpisah dengan pergerakan data yang berat, dan dengan beberapa tingkat duplikasi data. Saat ini tersedia solusi hibrid yang lebih sederhana: memiliki indeks penyimpan kolom pada tabel yang dioptimalkan memori.

  • Indeks penyimpan kolom dapat dibangun pada tabel berbasis disk, bahkan sebagai indeks berkluster. Tetapi pada tabel yang dioptimalkan memori, indeks penyimpan kolom tidak dapat diklusterkan.

  • Kolom LOB atau di luar baris untuk tabel yang dioptimalkan memori mencegah pembuatan indeks penyimpan kolom pada tabel.

  • Tidak ada pernyataan ALTER TABLE yang dapat dijalankan terhadap tabel yang dioptimalkan memori sementara indeks penyimpan kolom ada di tabel.

    • Pada Agustus 2016, Microsoft memiliki rencana jangka pendek untuk meningkatkan performa pembuatan ulang indeks penyimpan kolom.

Kolom LOB D.2 dan di luar baris

Objek besar (LOB) adalah kolom dari jenis seperti varchar(max). Memiliki beberapa kolom LOB pada tabel yang dioptimalkan memori mungkin tidak cukup membahayakan performa. Tetapi hindari memiliki lebih banyak kolom LOB daripada kebutuhan data Anda. Saran yang sama berlaku untuk kolom di luar baris. Jangan tentukan kolom sebagai nvarchar(3072) jika varchar(512) sudah cukup.

Sedikit lebih banyak tentang kolom LOB dan di luar baris tersedia di:

E. Batasan proc asli

Elemen tertentu dari Transact-SQL tidak didukung dalam modul T-SQL yang dikompilasi secara asli, termasuk prosedur tersimpan. Untuk detail tentang fitur mana yang didukung, lihat:

Untuk pertimbangan saat memigrasikan modul SQL Transact yang menggunakan fitur yang tidak didukung untuk dikompilasi secara asli, lihat:

Selain batasan pada elemen SQL Transact tertentu, ada juga batasan pada operator kueri yang didukung dalam modul T-SQL yang dikompilasi secara asli. Karena keterbatasan ini, prosedur tersimpan yang dikompilasi secara asli tidak cocok untuk kueri analitis yang memproses himpunan data besar.

Tidak ada pemrosesan paralel dalam proc asli

Pemrosesan paralel tidak dapat menjadi bagian dari rencana kueri apa pun untuk proc asli. Proc asli selalu berutas tunggal.

Jenis gabungan

Gabungan hash maupun gabungan tidak dapat menjadi bagian dari rencana kueri apa pun untuk proc asli. Gabungan perulangan berlapis digunakan.

Tidak ada agregasi hash

Saat rencana kueri untuk proc asli memerlukan fase agregasi, hanya agregasi aliran yang tersedia. Agregasi hash tidak didukung dalam rencana kueri untuk proc asli.

  • Agregasi hash lebih baik ketika data dari sejumlah besar baris harus dikumpulkan.

F. Desain aplikasi: Transaksi dan logika coba lagi

Transaksi yang melibatkan tabel yang dioptimalkan memori dapat menjadi tergantung pada transaksi lain yang melibatkan tabel yang sama. Jika jumlah transaksi dependen mencapai melebihi maksimum yang diizinkan, semua transaksi dependen gagal.

Pada SQL Server 2016:

  • Maksimum yang diizinkan adalah 8 transaksi dependen. 8 juga merupakan batas transaksi yang dapat diandalkan oleh setiap transaksi tertentu.
  • Nomor kesalahan adalah 41839. (Pada SQL Server 2014 nomor kesalahan adalah 41301.)

Anda dapat membuat skrip Transact-SQL Anda lebih kuat terhadap kemungkinan kesalahan transaksi dengan menambahkan logika coba lagi ke skrip Anda. Logika coba lagi lebih mungkin membantu ketika panggilan UPDATE dan DELETE sering terjadi, atau jika tabel yang dioptimalkan memori direferensikan oleh kunci asing di tabel lain. Untuk detailnya, lihat: