Pola Aplikasi untuk Mempartisi Tabel yang Dioptimalkan Memori

Artikel
08/21/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

OLTP Dalam Memori mendukung pola desain aplikasi yang membahayakan sumber daya performa pada data yang relatif saat ini. Pola ini dapat berlaku ketika data saat ini dibaca atau diperbarui jauh lebih sering daripada data yang lebih lama. Dalam hal ini, kami mengatakan data saat ini aktif atau panas, dan data yang lebih lama dingin.

Ide utamanya adalah menyimpan data panas dalam tabel yang dioptimalkan memori. Setiap minggu atau bulanan, data lama yang menjadi dingin dipindahkan ke tabel yang dipartisi. Tabel yang dipartisi memiliki datanya yang disimpan pada disk atau hard drive lainnya, bukan dalam memori.

Biasanya, desain ini menggunakan kunci tanggalwaktu untuk memungkinkan proses pemindahan membedakan antara data panas versus dingin secara efisien.

Pemartisian tingkat lanjut

Desain ini berniat untuk meniluki memiliki tabel yang dipartisi yang juga memiliki satu partisi yang dioptimalkan memori. Agar desain ini berfungsi, Anda harus memastikan bahwa semua tabel berbagi skema umum. Sampel kode nanti dalam artikel ini menunjukkan teknik .

Data baru diduga panas menurut definisi. Data panas disisipkan dan diperbarui dalam tabel yang dioptimalkan memori. Data dingin dipertahankan dalam tabel tradisional yang dipartisi. Secara berkala, prosedur tersimpan menambahkan partisi baru. Partisi berisi data dingin terbaru yang telah dipindahkan dari tabel yang dioptimalkan memori.

Jika operasi hanya membutuhkan data panas, operasi dapat menggunakan prosedur tersimpan yang dikompilasi secara asli untuk mengakses data. Operasi yang mungkin mengakses data panas atau dingin harus menggunakan Transact-SQL yang ditafsirkan, untuk menggabungkan tabel yang dioptimalkan memori dengan tabel yang dipartisi.

Menambahkan partisi

Data yang baru saja menjadi dingin harus dipindahkan ke tabel yang dipartisi. Langkah-langkah untuk pertukaran partisi berkala ini adalah sebagai berikut:

Untuk data dalam tabel yang dioptimalkan memori, tentukan tanggalwaktu yang merupakan batas atau cutoff antara panas versus data yang baru dingin.
Sisipkan data yang baru dingin, dari tabel OLTP Dalam Memori, ke dalam tabel cold_staging .
Hapus data dingin yang sama dari tabel yang dioptimalkan memori.
Tukar tabel cold_staging ke dalam partisi.
Tambahkan partisi.

Jendela pemeliharaan

Salah satu langkah sebelumnya adalah menghapus data yang baru dingin dari tabel yang dioptimalkan memori. Ada interval waktu antara penghapusan ini dan langkah terakhir yang menambahkan partisi baru. Selama interval ini, aplikasi apa pun yang mencoba membaca data yang baru dingin akan gagal.

Untuk sampel terkait, lihat Partisi Tingkat Aplikasi.

Sampel Kode

Sampel Transact-SQL berikut ditampilkan dalam serangkaian blok kode yang lebih kecil, hanya untuk kemudahan presentasi. Anda dapat menambahkan semuanya ke dalam satu blok kode besar untuk pengujian Anda.

Secara keseluruhan, sampel T-SQL menunjukkan cara menggunakan tabel yang dioptimalkan memori dengan tabel berbasis disk yang dipartisi.

Fase pertama sampel T-SQL membuat database, lalu membuat objek seperti tabel dalam database. Fase selanjutnya menunjukkan cara memindahkan data dari tabel yang dioptimalkan memori ke dalam tabel yang dipartisi.

Membuat database

Bagian sampel T-SQL ini membuat database pengujian. Database dikonfigurasi untuk mendukung tabel yang dioptimalkan memori dan tabel yang dipartisi.

CREATE DATABASE PartitionSample;
GO

-- Add a FileGroup, enabled for In-Memory OLTP.
-- Change file path as needed.

ALTER DATABASE PartitionSample
    ADD FILEGROUP PartitionSample_mod
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE PartitionSample
    ADD FILE(
        NAME = 'PartitionSample_mod',
        FILENAME = 'c:\data\PartitionSample_mod')
    TO FILEGROUP PartitionSample_mod;
GO

Membuat tabel memori yang dioptimalkan untuk data panas

Bagian ini membuat tabel yang dioptimalkan memori yang menyimpan data terbaru, yang sebagian besar masih panas data.

USE PartitionSample;
GO

-- Create a memory-optimized table for the HOT Sales Order data.
-- Notice the index that uses datetime2.

CREATE TABLE dbo.SalesOrders_hot (
   so_id INT IDENTITY PRIMARY KEY NONCLUSTERED,
   cust_id INT NOT NULL,
   so_date DATETIME2 NOT NULL INDEX ix_date NONCLUSTERED,
   so_total MONEY NOT NULL,
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc)
) WITH (MEMORY_OPTIMIZED=ON);
GO

Membuat tabel yang dipartisi untuk data dingin

Bagian ini membuat tabel yang dipartisi yang menyimpan data dingin.

-- Create a partition and table for the COLD Sales Order data.
-- Notice the index that uses datetime2.

CREATE PARTITION FUNCTION [ByDatePF](datetime2) AS RANGE RIGHT
   FOR VALUES();
GO

CREATE PARTITION SCHEME [ByDateRange]
   AS PARTITION [ByDatePF]
   ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SalesOrders_cold (
   so_id INT NOT NULL,
   cust_id INT NOT NULL,
   so_date DATETIME2 NOT NULL,
   so_total MONEY NOT NULL,
   CONSTRAINT PK_SalesOrders_cold PRIMARY KEY (so_id, so_date),
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc)
) ON [ByDateRange](so_date);
GO

Membuat tabel untuk menyimpan data dingin selama pemindahan

Bagian ini membuat tabel cold_staging. Tampilan yang menyatukan data panas dan dingin dari dua tabel juga dibuat.

-- A table used to briefly stage the newly cold data, during moves to a partition.

CREATE TABLE dbo.SalesOrders_cold_staging (
   so_id INT NOT NULL,
   cust_id INT NOT NULL,
   so_date datetime2 NOT NULL,
   so_total MONEY NOT NULL,
   CONSTRAINT PK_SalesOrders_cold_staging PRIMARY KEY (so_id, so_date),
   INDEX ix_date_total NONCLUSTERED (so_date desc, so_total desc),
   CONSTRAINT CHK_SalesOrders_cold_staging CHECK (so_date >= '1900-01-01')
);
GO

-- A view, for retrieving the aggregation of hot plus cold data.

CREATE VIEW dbo.SalesOrders
AS SELECT so_id,
          cust_id,
          so_date,
          so_total,
          1 AS 'is_hot'
       FROM dbo.SalesOrders_hot
   UNION ALL
   SELECT so_id,
          cust_id,
          so_date,
          so_total,
          0 AS 'is_cold'
       FROM dbo.SalesOrders_cold;
GO

Membuat prosedur tersimpan

Bagian ini membuat prosedur tersimpan yang Anda jalankan secara berkala. Prosedur ini memindahkan data yang baru dingin dari tabel yang dioptimalkan memori ke dalam tabel yang dipartisi.

-- A stored procedure to move all newly cold sales orders data
-- to its staging location.

CREATE PROCEDURE dbo.usp_SalesOrdersOffloadToCold @splitdate datetime2
   AS
   BEGIN
      BEGIN TRANSACTION;

      -- Insert the cold data as a temporary heap.
      INSERT INTO dbo.SalesOrders_cold_staging WITH (TABLOCKX)
      SELECT so_id , cust_id , so_date , so_total
         FROM dbo.SalesOrders_hot WITH (serializable)
         WHERE so_date <= @splitdate;

      -- Delete the moved data from the hot table.
      DELETE FROM dbo.SalesOrders_hot WITH (SERIALIZABLE)
         WHERE so_date <= @splitdate;

      -- Update the partition function, and switch in the new partition.
      ALTER PARTITION SCHEME [ByDateRange] NEXT USED [PRIMARY];

      DECLARE @p INT = (
        SELECT MAX(partition_number)
            FROM sys.partitions
            WHERE object_id = OBJECT_ID('dbo.SalesOrders_cold'));

      EXEC sp_executesql
        N'ALTER TABLE dbo.SalesOrders_cold_staging
            SWITCH TO dbo.SalesOrders_cold partition @i',
        N'@i int',
        @i = @p;

      ALTER PARTITION FUNCTION [ByDatePF]()
      SPLIT RANGE( @splitdate);

      -- Modify a constraint on the cold_staging table, to align with new partition.
      ALTER TABLE dbo.SalesOrders_cold_staging
         DROP CONSTRAINT CHK_SalesOrders_cold_staging;

      DECLARE @s nvarchar( 100) = CONVERT( nvarchar( 100) , @splitdate , 121);
      DECLARE @sql nvarchar( 1000) = N'alter table dbo.SalesOrders_cold_staging 
         add constraint CHK_SalesOrders_cold_staging check (so_date > ''' + @s + ''')';
      PRINT @sql;
      EXEC sp_executesql @sql;

      COMMIT;
END;
GO

Siapkan data sampel, dan demo prosedur tersimpan

Bagian ini menghasilkan dan menyisipkan data sampel, lalu menjalankan prosedur tersimpan sebagai demonstrasi.

-- Insert sample values into the hot table.
INSERT INTO dbo.SalesOrders_hot VALUES(1,SYSDATETIME(), 1);
GO
INSERT INTO dbo.SalesOrders_hot VALUES(1, SYSDATETIME(), 1);
GO
INSERT INTO dbo.SalesOrders_hot VALUES(1, SYSDATETIME(), 1);
GO

-- Verify that the hot data is in the table, by selecting from the view.
SELECT * FROM dbo.SalesOrders;
GO

-- Treat all data in the hot table as cold data:
-- Run the stored procedure, to move (offload) all sales orders to date to cold storage.
DECLARE @t datetime2 = SYSDATETIME();
EXEC dbo.usp_SalesOrdersOffloadToCold @t;

-- Again, read hot plus cold data from the view.
SELECT * FROM dbo.SalesOrders;
GO

-- Retrieve the name of every partition.
SELECT OBJECT_NAME( object_id) , * FROM sys.dm_db_partition_stats ps
   WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold');

-- Insert more data into the hot table.
INSERT INTO dbo.SalesOrders_hot VALUES(2, SYSDATETIME(), 1);
GO
INSERT INTO dbo.SalesOrders_hot VALUES(2, SYSDATETIME(), 1);
GO
INSERT INTO dbo.SalesOrders_hot VALUES(2, SYSDATETIME(), 1);
GO

-- Read hot plus cold data from the view.
SELECT * FROM dbo.SalesOrders;
GO

-- Again, run the stored procedure, to move all sales orders to date to cold storage.
DECLARE @t datetime2 = SYSDATETIME();
EXEC dbo.usp_SalesOrdersOffloadToCold @t;

-- Read hot plus cold data from the view.
SELECT * FROM dbo.SalesOrders;
GO

-- Again, retrieve the name of every partition.
-- The stored procedure can modify the partitions.
SELECT OBJECT_NAME( object_id) , partition_number , row_count
  FROM sys.dm_db_partition_stats ps
  WHERE object_id = OBJECT_ID( 'dbo.SalesOrders_cold')
    AND index_id = 1;

Hilangkan semua objek demo

Ingatlah untuk membersihkan database uji demo dari sistem pengujian Anda.

-- You must first leave the context of the PartitionSample database.

-- USE <A-Database-Name-Here>;
GO

DROP DATABASE PartitionSample;
GO

Lihat Juga

Tabel yang Dioptimalkan Memori

Bagikan melalui