Menggunakan Penyimpanan Kueri dengan OLTP Dalam Memori

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Database

Penyimpanan Kueri SQL Server memungkinkan Anda memantau performa kode yang dikompilasi secara asli untuk beban kerja yang menjalankan OLTP dalam memori.

Statistik kompilasi dan runtime dikumpulkan dan diekspos dengan cara yang sama seperti untuk beban kerja berbasis disk. Saat bermigrasi ke OLTP dalam memori, Anda dapat terus menggunakan tampilan Penyimpanan Kueri di SQL Server Management Studio dan skrip kustom yang telah Anda kembangkan untuk beban kerja berbasis disk sebelum migrasi. Ini menghemat investasi Anda dalam mempelajari teknologi Penyimpanan Kueri dan membuatnya dapat digunakan untuk memecahkan masalah semua beban kerja.
Untuk informasi umum tentang menggunakan Penyimpanan Kueri, lihat Memantau Performa Dengan Menggunakan Penyimpanan Kueri.

Menggunakan Penyimpanan Kueri dengan OLTP dalam memori tidak memerlukan konfigurasi fitur tambahan. Saat Anda mengaktifkannya di database Anda, ini berfungsi untuk semua jenis beban kerja.
Namun, ada beberapa aspek spesifik yang harus diperhatikan pengguna saat menggunakan Query Store dengan OLTP dalam memori:

  • Saat Penyimpanan Kueri diaktifkan, kueri, paket, dan statistik waktu kompilasi dikumpulkan secara default. Namun, pengumpulan statistik runtime hanya diaktifkan jika Anda secara eksplisit mengaktifkannya dengan sys.sp_xtp_control_query_exec_stats (Transact-SQL).

  • Saat Anda mengatur @new_collection_value ke 0, Penyimpanan Kueri berhenti mengumpulkan statistik runtime untuk prosedur yang terpengaruh atau seluruh instans SQL Server.

  • Nilai yang dikonfigurasi dengan sys.sp_xtp_control_query_exec_stats (Transact-SQL) tidak bertahan. Pastikan Anda memeriksa dan mengonfigurasi lagi koleksi statistik setelah memulai ulang SQL Server.

  • Seperti halnya pengumpulan statistik kueri reguler, performa dapat menurun saat Anda menggunakan Penyimpanan Kueri untuk melacak eksekusi beban kerja. Pertimbangkan untuk mengaktifkan koleksi statistik hanya untuk subset penting dari prosedur tersimpan yang dikompilasi secara asli.

  • Kueri dan rencana diambil dan disimpan pada kompilasi asli pertama dan diperbarui pada setiap kompilasi ulang.

  • Jika Anda mengaktifkan Penyimpanan Kueri atau menghapus kontennya setelah semua prosedur tersimpan asli dikompilasi, Anda harus mengkompilasi ulang secara manual untuk membuatnya diambil oleh Penyimpanan Kueri. Hal yang sama berlaku jika Anda menghapus kueri secara manual dengan menggunakan sp_query_store_remove_query (Transact-SQL) atau sp_query_store_remove_plan (Transact-SQL). Gunakan sp_recompile (Transact-SQL) untuk memaksa kompilasi ulang prosedur.

  • Penyimpanan Kueri memanfaatkan mekanisme pembuatan rencana dari OLTP dalam memori untuk menangkap rencana eksekusi kueri selama kompilasi. Paket tersimpan secara semantik setara dengan yang akan Anda dapatkan dengan menggunakan SET SHOWPLAN_XML ON dengan satu perbedaan; paket di Penyimpanan Kueri dibagi dan disimpan per pernyataan individual.

  • Saat Anda menjalankan Penyimpanan Kueri dalam database dengan beban kerja campuran, maka Anda bisa menggunakan bidang is_natively_compiled dari sys.query_store_plan (Transact-SQL) untuk menemukan rencana kueri yang dihasilkan dengan cepat oleh kompilasi kode asli.

  • Mode pengambilan Penyimpanan Kueri (parameter QUERY_CAPTURE_MODE dalam pernyataan ALTER TABLE ) tidak memengaruhi kueri dari modul yang dikompilasi secara asli karena selalu diambil terlepas dari nilai yang dikonfigurasi. Ini termasuk pengaturan QUERY_CAPTURE_MODE = NONE.

  • Durasi kompilasi kueri yang diambil oleh Penyimpanan Kueri hanya menyertakan waktu yang dihabiskan dalam pengoptimalan kueri sebelum kode asli dibuat. Lebih tepatnya, ini tidak termasuk waktu untuk kompilasi kode C dan pembuatan struktur internal yang diperlukan untuk pembuatan kode C.

  • Memori memberikan metrik dalam sys.query_store_runtime_stats (Transact-SQL) tidak diisi untuk kueri yang dikompilasi secara asli - nilainya selalu 0. Kolom yang diberikan memori adalah: avg_query_max_used_memory, last_query_max_used_memory, min_query_max_used_memory, max_query_max_used_memory, dan stdev_query_max_used_memory.

Mengaktifkan dan menggunakan Penyimpanan Kueri dengan OLTP Dalam Memori

Contoh sederhana berikut menunjukkan penggunaan Query Store dengan OLTP dalam memori dalam skenario pengguna end-to-end. Dalam contoh ini, kami berasumsi bahwa database (MemoryOLTP) diaktifkan untuk OLTP dalam memori.
Untuk informasi selengkapnya tentang prasyarat untuk tabel yang dioptimalkan memori, lihat Membuat Tabel yang Dioptimalkan Memori dan Prosedur Tersimpan yang Dikompilasi Secara Asli.

USE MemoryOLTP;
GO

-- Create a simple memory-optimized table
CREATE TABLE dbo.Ord
   (OrdNo INTEGER not null PRIMARY KEY NONCLUSTERED,
    OrdDate DATETIME not null,
    CustCode NVARCHAR(5) not null)
WITH (MEMORY_OPTIMIZED=ON);
GO

-- Enable Query Store before native module compilation
ALTER DATABASE MemoryOLTP SET QUERY_STORE = ON;
GO

-- Create natively compiled stored procedure
CREATE PROCEDURE dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
    BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English')

    DECLARE @OrdDate DATETIME = GETDATE();
    INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)
        VALUES (@OrdNo, @CustCode, @OrdDate);
END;
GO

-- Enable runtime stats collection for queries from dbo.OrderInsert stored procedure
DECLARE @db_id INT = DB_ID()
DECLARE @proc_id INT = OBJECT_ID('dbo.OrderInsert');
DECLARE @collection_enabled BIT;

EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,
    @database_id = @db_id, @xtp_object_id = @proc_id;

-- Check the state of the collection flag
EXEC sp_xtp_control_query_exec_stats @database_id = @db_id,
    @xtp_object_id = @proc_id,
    @old_collection_value= @collection_enabled output;
SELECT @collection_enabled AS 'collection status';

-- Execute natively compiled workload
EXEC dbo.OrderInsert 1, 'A';
EXEC dbo.OrderInsert 2, 'B';
EXEC dbo.OrderInsert 3, 'C';
EXEC dbo.OrderInsert 4, 'D';
EXEC dbo.OrderInsert 5, 'E';

-- Check Query Store Data
-- Compile time data
SELECT q.query_id, plan_id, object_id, query_hash, p.query_plan,
    p.initial_compile_start_time, p.last_compile_start_time,
    p.last_execution_time, p.avg_compile_duration,
    p.last_force_failure_reason, p.force_failure_count
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

-- Get runtime stats
-- Check count_executions field to verify that runtime statistics
-- have been collected by the Query Store
SELECT q.query_id, p.plan_id, object_id, rsi.start_time, rsi.end_time,
    p.last_force_failure_reason, p.force_failure_count, rs.*
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
    ON q.query_id = p.plan_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');

Baca juga