Bagikan melalui

Penyimpanan Kueri untuk sekunder yang dapat dibaca

Berlaku untuk: SQL Server 2025 (17.x) Azure SQL Database

Penyimpanan Kueri untuk sekunder terbacakan memungkinkan Query Store memberikan wawasan bagi beban kerja yang berjalan pada replika sekunder. Saat diaktifkan, replika sekunder mengalirkan informasi eksekusi kueri (seperti statistik runtime dan tunggu) ke replika utama, tempat data disimpan di Penyimpanan Kueri dan dibuat terlihat di semua replika.

Dukungan platform

Saat ini, fitur Penyimpanan Kueri untuk sekunder yang dapat dibaca tersedia dan didukung dalam produksi di SQL Server 2025 (17.x), dan di Azure SQL Database. Dimulai dengan SQL Server 2025 (17.x), dan di Azure SQL Database, Penyimpanan Kueri untuk sekunder yang dapat dibaca diaktifkan secara default.

Di SQL Server 2022 (16.x), Query Store untuk sekunder yang bisa dibaca tetap dalam pratinjau, dan karenanya tidak didukung untuk produksi, serta dinonaktifkan secara default. Untuk mengaktifkan Penyimpanan Kueri untuk sekunder yang dapat dibaca di SQL Server 2022 (16.x) saja, bendera pelacakan 12606 diperlukan untuk diaktifkan ke replika sekunder utama dan semua replika sekunder yang dapat dibaca. Penanda pelacakan 12606 tidak ditujukan untuk penyebaran produksi yang didasarkan pada SQL Server 2022 (16.x). Untuk informasi selengkapnya, lihat Catatan rilis SQL Server 2022. Untuk SQL Server 2025 (17.x), Penyimpanan Kueri pada fitur sekunder yang dapat dibaca aktif secara default.

Azure SQL Database, semua database secara otomatis terdaftar dan diaktifkan untuk mendukung Penyimpanan Kueri untuk fitur sekunder yang dapat dibaca, pada tingkat layanan yang didukung dan skenario ketersediaan tinggi. Saat ini, fitur ini tidak didukung di Azure SQL Database Hyperscale.

Saat ini, fitur ini tidak didukung di Azure SQL Managed Instance atau database SQL di Microsoft Fabric.

Skenario ketersediaan tinggi yang didukung

  • Sebelum Anda menggunakan Penyimpanan Kueri untuk sekunder yang dapat dibaca pada instans SQL Server 2025 (17.x), grup ketersediaan Always On harus dikonfigurasi.

  • Untuk Azure SQL Database, Penyimpanan Kueri untuk sekunder yang dapat dibaca mendukung tingkat layanan berikut:

    • Tujuan umum dengan replikasi geografis aktif (tidak ada replika ketersediaan tinggi bawaan; memerlukan konfigurasi replikasi geografis untuk dukungan sekunder)
    • Premium (termasuk replika ketersediaan tinggi bawaan; replikasi geografis aktif juga didukung)
    • Bisnis penting (termasuk replika ketersediaan tinggi bawaan; replikasi geografis aktif juga didukung)

Mengaktifkan Penyimpanan Kueri untuk secondary yang dapat dibaca

Jika Query Store belum diaktifkan dan dalam mode READ_WRITE pada replika utama, Anda harus mengaktifkannya sebelum melanjutkan. Jalankan skrip berikut untuk setiap database yang diinginkan pada replika utama:

ALTER DATABASE [Database_Name]
    SET QUERY_STORE = ON(OPERATION_MODE = READ_WRITE);

Untuk mengaktifkan Penyimpanan Kueri pada semua replika sekunder yang dapat dibaca, sambungkan ke replika utama dan jalankan skrip berikut untuk setiap database yang akan disiapkan untuk menggunakan fitur tersebut.

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE);

Mengaktifkan koreksi rencana otomatis untuk replika sekunder

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database.

Setelah mengaktifkan Penyimpanan Kueri untuk replika sekunder, Anda dapat secara opsional mengaktifkan penyetelan otomatis untuk memungkinkan fitur koreksi rencana otomatis memaksa penerapan rencana pada replika sekunder. Ini memungkinkan pengoptimal kueri untuk secara otomatis mengidentifikasi dan memperbaiki masalah performa kueri yang disebabkan oleh regresi rencana eksekusi pada replika sekunder.

Untuk mengaktifkan koreksi paket otomatis untuk replika sekunder, sambungkan ke replika utama dan jalankan skrip berikut untuk setiap database yang diinginkan:

ALTER DATABASE [Database_Name]
FOR SECONDARY
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

Nonaktifkan Query Store untuk replika sekunder

Untuk menonaktifkan fitur Penyimpanan Kueri untuk replika sekunder pada semua replika sekunder, sambungkan ke master database pada primary replika dan jalankan skrip berikut untuk setiap database yang diinginkan:

ALTER DATABASE [Database_Name]
    FOR SECONDARY
    SET QUERY_STORE = ON
    (OPERATION_MODE = READ_ONLY);

Pastikan Query Store diaktifkan pada replika sekunder

Anda dapat memvalidasi bahwa Penyimpanan Kueri diaktifkan pada secondary replika dengan menyambungkan ke database pada replika sekunder dan menjalankan pernyataan T-SQL berikut:

SELECT desired_state_desc,
       actual_state_desc,
       readonly_reason
FROM sys.database_query_store_options;

Hasil dari kueri tampilan katalog sys.database_query_store_options harus menunjukkan bahwa status aktual Toko Kueri adalah READ_CAPTURE_SECONDARY dengan readonly_reason dari 8.

desired_state_desc actual_state_desc readonly_reason
READ_CAPTURE_SECONDARY READ_CAPTURE_SECONDARY 8

Komentar

Terminologi

Set replika didefinisikan sebagai replika baca/tulis database (utama) dan satu atau beberapa replika baca-saja (sekunder) yang diperlakukan sebagai unit logis. Peran dalam konteks ini mengacu pada peran replika tertentu. Ketika replika berfungsi dalam peran utama, replika baca/tulis adalah yang dapat melakukan modifikasi data dan aktivitas pembacaan. Ketika replika dikonfigurasi untuk hanya melakukan aktivitas baca-saja, replika tersebut berfungsi dalam peran sekunder (sekunder, geo sekunder, geo ha sekunder). Peran dapat berubah melalui peristiwa failover yang direncanakan atau tidak direncanakan, ketika ini terjadi, primer dapat menjadi sekunder atau sebaliknya.

Peran yang saat ini didukung adalah:

  • Primary
  • Secondary
  • Geografi sekunder
  • Geo HA sekunder
  • Replika Teridentifikasi

Cara kerjanya

Data yang disimpan tentang kueri dapat dianalisis sebagai beban kerja berdasarkan peran. Query Store untuk secondary yang dapat dibaca memberi Anda kemampuan untuk memantau performa dari setiap beban kerja unik yang hanya-baca yang mungkin dijalankan pada replika sekunder. Data diintegrasikan pada tingkat peran. Misalnya, konfigurasi grup ketersediaan terdistribusi SQL Server mungkin terdiri dari:

  • Satu replika utama, bagian dari Grup Ketersediaan 1 (AG1)

  • Dua replika sekunder lokal, juga bagian AG1

  • Satu replika utama jarak jauh di lokasi lain yang merupakan bagian dari grup ketersediaan terpisah (AG2). Dalam istilah SQL Server, istilah ini juga biasanya disebut sebagai pengarah global, namun, Query Store untuk fitur sekunder yang dapat dibaca akan mengenali dan menyebutnya sebagai Geo secondary replika, dengan asumsi bahwa itu adalah replika sekunder yang didistribusikan secara geografis.

Jika AG1 dan AG2 dikonfigurasi untuk mengizinkan koneksi baca-saja ketika beban kerja baca-saja dijalankan terhadap salah satu dari replika sekunder AG1, statistik eksekusi Penyimpanan Kueri dikirim ke replika utama AG1 serta diagregasi dan disimpan sebagai data yang dihasilkan dari secondary peran sebelum data tersebut dikirim kembali ke semua replika sekunder termasuk penerus global di AG2. Ketika beban kerja terpisah dijalankan terhadap primer AG2, pengirim global, datanya dikirim kembali ke replika utama AG1 dan disimpan sebagai data yang dihasilkan dari peran Geo secondary.

Dari perspektif pengamatan, tampilan katalog sistem sys.query_store_runtime_stats diperluas untuk membantu mengidentifikasi peran asal statistik eksekusi. Ada hubungan antara tampilan ini dan tampilan katalog sistem sys.query_store_replicas , yang dapat memberikan nama peran yang lebih ramah. Di SQL Server, replica_name kolomnya adalah NULL. Meskipun begitu, kolom replica_name diisi dengan data untuk tingkat layanan Hyperscale jika ada replika bernama yang digunakan untuk beban kerja yang hanya dapat dibaca.

Contoh kueri T-SQL yang dapat digunakan untuk memberikan analisis keseluruhan dari 50 kueri teratas selama 8 jam terakhir, yang menggunakan sumber daya CPU dari semua replika adalah:

-- Top 50 queries by CPU across all replicas in the last 8 hours
DECLARE @hours AS INT = 8;

SELECT TOP 50 qsq.query_id,
              qsp.plan_id,
              CASE qrs.replica_group_id WHEN 1 THEN 'PRIMARY' WHEN 2 THEN 'SECONDARY' WHEN 3 THEN 'GEO SECONDARY' WHEN 4 THEN 'GEO HA SECONDARY' ELSE CONCAT('NAMED REPLICA_', qrs.replica_group_id) END AS replica_type,
              qsq.query_hash,
              qsp.query_plan_hash,
              SUM(qrs.count_executions) AS sum_executions,
              SUM(qrs.count_executions * qrs.avg_logical_io_reads) AS total_logical_reads,
              SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) AS total_cpu_ms,
              AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
              AVG(qrs.avg_cpu_time / 1000.0) AS avg_cpu_ms,
              ROUND(TRY_CAST (SUM(qrs.avg_duration * qrs.count_executions) AS FLOAT) / NULLIF (SUM(qrs.count_executions), 0) * 0.001, 2) AS avg_duration_ms,
              COUNT(DISTINCT qsp.plan_id) AS number_of_distinct_plans,
              qsqt.query_sql_text
FROM sys.query_store_runtime_stats_interval AS qsrsi
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
     INNER JOIN sys.query_store_plan AS qsp
         ON qsp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_query AS qsq
         ON qsq.query_id = qsp.query_id
     INNER JOIN sys.query_store_query_text AS qsqt
         ON qsq.query_text_id = qsqt.query_text_id
WHERE qsrsi.start_time >= DATEADD(HOUR, -@hours, GETUTCDATE())
GROUP BY qsq.query_id, qsq.query_hash, qsp.query_plan_hash, qsp.plan_id, qrs.replica_group_id, qsqt.query_sql_text
ORDER BY SUM(qrs.count_executions * qrs.avg_cpu_time / 1000.0) DESC, AVG(qrs.avg_cpu_time / 1000.0) DESC;

Laporan Penyimpanan Kueri di SQL Server Management Studio (SSMS) 21 dan versi yang lebih baru menyediakan daftar pilihan Replika, yang menyediakan cara untuk menampilkan data Penyimpanan Kueri di berbagai set replika/peran. Selain itu, di dalam tampilan Penjelajah objek, simpul Penyimpanan Kueri mencerminkan status Penyimpanan Kueri saat ini (yaitu, READ_CAPTURE_SECONDARY) jika tersambung ke replika sekunder yang dapat dibaca.

Penyimpanan Kueri untuk telemetri sekunder yang dapat dibaca di Azure SQL Database

Berlaku untuk: Azure SQL Database

Saat streaming statistik runtime Query Store melalui pengaturan diagnostik Azure, dua kolom disertakan untuk membantu mengidentifikasi sumber replika data telemetri:

  • is_primary_b: Nilai Boolean yang menunjukkan apakah data berasal dari replika utama (benar) atau replika sekunder (false)
  • replica_group_id: Bilangan bulat yang sesuai dengan peran replika

Kolom ini sangat penting untuk membedakan metrik dan data performa saat menganalisis beban kerja di seluruh set replika. Saat mengonfigurasi pengaturan diagnostik untuk mengalirkan statistik runtime Query Store ke Log Analytics, Event Hubs, atau Azure Storage, pastikan kueri dan dasbor Anda memperhitungkan kolom ini untuk mengelompokkan data dengan benar berdasarkan peran replika. Untuk informasi selengkapnya tentang mengonfigurasi pengaturan diagnostik dan metrik yang tersedia, lihat Pengaturan diagnostik di Azure Monitor.

Penting

Wawasan Performa Kueri untuk Azure SQL Database (QPI)does not saat ini mendukung konsep tersebutreplica_group_id. Data yang ditampilkan dalam dasbor akan menggabungkan semua data statistik eksekusi dan waktu tunggu dari semua replika.

Pertimbangan performansi untuk Query Store untuk sekunder yang dapat dibaca

Saluran yang digunakan oleh replika sekunder untuk mengirim informasi kueri kembali ke replika utama adalah saluran yang sama yang digunakan untuk menjaga replika sekunder tetap terbarui. Apa artinya channel di sini?

Dalam konfigurasi grup ketersediaan (HADR), replika disinkronkan satu sama lain menggunakan lapisan transportasi khusus yang membawa blok log, pengakuan, dan pesan status antara replika utama dan sekunder. Ini memastikan konsistensi data dan kesiapan failover.

Saat Query Store untuk sekunder yang dapat dibaca diaktifkan, sistem tidak membuat titik akhir jaringan terpisah. Sebaliknya, ini menetapkan jalur komunikasi logis baru di atas lapisan transportasi yang ada:

  • Untuk Azure SQL Database (non-Hyperscale), Azure SQL Managed Instance, dan SQL Server, ini menggunakan lapisan transport Always On untuk ketersediaan tinggi dan pemulihan bencana (HADR).

  • Untuk Azure SQL Database Hyperscale, lapisan transportasi yang berbeda yang disebut lapisan transportasi I/O Blob Jarak Jauh digunakan. Lapisan transport Remote Blob I/O adalah saluran komunikasi antara simpul komputasi dan Server Layanan Log/Halaman. Lapisan transportasi I/O Blob Jarak Jauh menyediakan saluran terenkripsi yang andal untuk memindahkan rekaman log dan halaman data.

Jalur ini memultipleks data eksekusi Query Store (teks kueri, rencana eksekusi, statistik runtime/tunggu) bersama lalu lintas rekaman log seperti biasanya, dengan menggunakan sesi terenkripsi yang sama. Fitur ini memiliki antrean pengambilan dan penerimaan sendiri, yang dapat dilihat dengan menjalankan kueri jendela tampilan dari perspektif berbagai replika:

SELECT pending_message_count,
       messaging_memory_used_mb
FROM sys.database_query_store_internal_state;

Data dari sekunder disimpan dalam tabel Penyimpanan Kueri yang sama di primer, yang dapat meningkatkan persyaratan penyimpanan. Di bawah beban berat, Anda mungkin mengamati latensi atau tekanan balik pada saluran transport. Batasan pengambilan kueri ad hoc yang sama yang berlaku untuk Penyimpanan Kueri pada primer juga berlaku untuk sekunder. Untuk informasi dan panduan selengkapnya tentang mengelola ukuran dan kebijakan pengambilan Penyimpanan Kueri, lihat Simpan data yang paling relevan di Penyimpanan Kueri.

Keterlihatan ID kueri/ID rencana negatif

ID negatif menunjukkan tempat penampung sementara dalam memori untuk kueri dan rencana pada data sekunder sebelum disimpan ke data primer.

Sebelum data Query Store dipertahankan ke server utama dari replika sekunder yang dapat dibaca, kueri dan rencana mungkin ditetapkan pengidentifikasi sementara dalam representasi memori lokal dari Query Store - MEMORYCLERK_QUERYDISKSTORE_HASHMAP. ID kueri dan rencana dapat muncul sebagai angka negatif dan merupakan placeholder sampai replika utama menetapkan pengidentifikasi otoritatif, yang terjadi ketika Query Store menentukan bahwa kueri memenuhi persyaratan mode penangkapan yang telah dikonfigurasi. Jika kebijakan penangkapan kustom diberlakukan, Anda dapat meninjau persyaratan yang harus dipenuhi dengan mengkueri sys.database_query_store_options tampilan katalog sistem.

SELECT query_capture_mode_desc,
       capture_policy_execution_count,
       capture_policy_total_compile_cpu_time_ms,
       capture_policy_total_execution_cpu_time_ms
FROM sys.database_query_store_options;

Setelah kueri ditetapkan sebagai diambil, statistik runtime/tunggu dan rencananya dapat dipertahankan, dan ID sementara lokal diganti dengan ID positif. Ini juga memungkinkan Anda menggunakan kemampuan memaksa atau mengisyaratkan rencana.

Konten terkait

  • Last updated on