Rencanakan adopsi Fitur OLTP Dalam Memori Anda di SQL Server

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menjelaskan cara adopsi fitur dalam memori di SQL Server memengaruhi aspek lain dari sistem bisnis Anda.

Catatan

J. Adopsi fitur OLTP Dalam Memori

Sub bagian berikut membahas faktor-faktor yang harus Anda pertimbangkan ketika Anda berencana untuk mengadopsi dan menerapkan fitur Dalam Memori.

Prasyarat A.1

Satu prasyarat untuk menggunakan fitur Dalam Memori dapat melibatkan edisi atau tingkat layanan produk SQL. Untuk prasyarat ini dan lainnya, lihat:

A.2 Prakiraan 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 yang didedikasikan 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:

Panduan serupa tersedia untuk Azure SQL Managed Instance:

Database Azure SQL

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 dioptimalkan memori terkadang lebih disukai daripada #TempTable tradisional yang berada di tempdb database. Variabel tabel dapat memberikan perolehan performa 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 Transact-SQL 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 your memory-optimized table with a sub-SELECT from the disk-based table.

  6. HILANGKAN tabel berbasis disk Anda.

  7. Ambil cadangan penuh lainnya.

  8. Lanjutkan aktivitas aplikasi.

Advisor Optimisasi Memori

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

File .dacpac

Anda dapat memperbarui database Anda 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.

  • Aplikasi tingkat data dan file .dacpac

Panduan A.4 untuk apakah fitur OLTP Dalam Memori tepat untuk aplikasi Anda

Untuk panduan tentang apakah fitur OLTP Dalam Memori dapat meningkatkan performa aplikasi tertentu Anda, lihat:

B. Fitur yang tidak didukung

Fitur yang tidak didukung dalam skenario OLTP Dalam Memori 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 spesifiknya adalah bahwa:

  • Item pertama yang dioptimalkan memori membuatnya tidak mungkin untuk pernah menghilangkan 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.

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

B.4 RowVersion, Urutan

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

  • URUTAN 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 mengubah ukuran 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 work-around:

  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 ditahan 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 nonclustered pohon B tradisional sering kali menjadi pilihan yang masuk akal dan sederhana ketika Anda pertama kali menerapkan tabel yang dioptimalkan memori. Nantinya, setelah Melihat performa aplikasi, Anda dapat mempertimbangkan untuk bertukar jenis indeks lain.

Catatan

Dokumentasi SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

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 inexact 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 dari meremehkan berapa banyak wadah yang mungkin dibutuhkan indeks hash Anda, untuk menghindari rantai panjang dalam wadah individual. Untuk detailnya, lihat:

Indeks penyimpan kolom berkluster

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 tahun-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 memori yang dioptimalkan, 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 pada 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(maks). 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 lanjut tentang LOB dan kolom di luar baris tersedia di:

E. Batasan proc asli

Elemen khusus 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 Transact-SQL yang menggunakan fitur yang tidak didukung untuk dikompilasi secara asli, lihat:

Selain batasan pada elemen tertentu dari Transact-SQL, 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 analitik 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 dan gabungan 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 diagregasi.

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 maksimum yang diizinkan, semua transaksi dependen gagal.

Di SQL Server 2016:

  • Maksimum yang diizinkan adalah delapan transaksi dependen. Delapan juga merupakan batas transaksi yang dapat diandalkan oleh transaksi tertentu.
  • Nomor kesalahan adalah 41839. (Di SQL Server 2014, nomor kesalahannya 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:

Konten terkait