Optimasi query: execution plan, SARGable, parameter sniffing, pagination, dan SQL dari EF Core

Sebelum mulai: setup lab sudah dijalankan dan lab 0022 selesai, jadi dbo.Product kembali hanya punya PK_Product (clustered) dan UQ_Product_Name. Lesson ini memakai istilah dari 0022 (Index Seek, Clustered Index Scan, Key Lookup, covering index, logical reads) tanpa mengulangnya. Dua penyebab lambat yang ada di sisi aplikasi, N+1 dan tracking yang tidak perlu (AsNoTracking), sudah dibahas di lesson 0006 (bagian 3 dan 4): di jawaban interview cukup disebut sebagai hal pertama yang dicek.

1. Alur analisis, lalu membaca execution plan

Pewawancara ingin mendengar urutan kerja, bukan daftar trik. Urutan dari dokumentasi EF Core: temukan perintah yang lambat di log, jalankan SQL-nya di tool SQL, lalu baca execution plan-nya. ^[1]

1. Ambil SQL yang sebenarnya: log EF Core atau ToQueryString (bagian 5). Sekalian cek N+1 dan tracking (0006).
2. Jalankan dengan actual plan + SET STATISTICS IO ON di Azure Data Studio, VS Code mssql, atau SSMS.
3. Baca plan: operator mana yang mahal, scan atau seek, Key Lookup berulang, estimated vs actual rows, warning.
4. Perbaiki sesuai temuan: tulis ulang agar SARGable (bagian 2), index (0022), parameter sniffing (bagian 3), keyset pagination (bagian 4).
5. Ukur lagi dengan angka yang sama: logical reads dan plan.

Execution plan menjelaskan "the sequence in which the source tables are accessed" dan "the methods used to extract data from each table". ^[2] Ada dua bentuk:

Yang dibaca di plan untuk interview:

• Operator dan biayanya. "It's particularly important to note the costs associated with each node of the plan, and to identify how indexes are used (or not)." ^[1] Clustered Index Scan pada tabel besar untuk sedikit baris, atau Key Lookup yang dieksekusi ribuan kali, adalah tersangka utama (operator: 0022).
• Estimated vs actual rows. Optimizer memilih plan dari perkiraan. Kalau perkiraannya 16.667 baris dan aktualnya 20, plan yang dipilih untuk 16.667 baris itu salah untuk 20 baris. Penyebab umum: statistik, local variable, dan parameter sniffing (bagian 3).
• Scan tidak selalu buruk. Scan bisa paling efisien bila "the result set of the query is a high percentage of rows from the table." ^[7] Dan analisis plan di database kecil menyesatkan: "always make sure it contains data that is similar to your production system" ^[1], itu alasan lab memakai 100.000 baris.

2. SARGable: jangan bungkus kolom dengan fungsi

"The term SARGable in relational databases refers to a Search ARGumentable predicate that can use an index to speed up the execution of the query." ^[8] Aturannya satu: "Applying any function or computation on the column(s) in the search predicate generally makes the query non-sargable". ^[9] Index menyimpan nilai kolom, bukan nilai YEAR(kolom), jadi engine harus menghitung fungsinya untuk setiap baris: scan.

• Bukan hanya WHERE: "SARGability applies not only to WHERE clauses, but also to JOINs, HAVING, GROUP BY and ORDER BY clauses." Penyebab yang paling sering: CONVERT(), CAST(), ISNULL(), COALESCE() pada kolom. ^[9] Microsoft juga menulis di best practice stored procedure: "Avoid wrapping functions around columns specified in the WHERE and JOIN clauses." ^[10]
• Kalau fungsi tidak bisa dihindari: buat computed column dengan ekspresi yang sama lalu index kolom itu. ^[9]
• Konversi tersembunyi: parameter nvarchar terhadap kolom varchar membuat SQL Server mengonversi kolomnya (CONVERT_IMPLICIT, terlihat sebagai warning di plan), efeknya sama dengan fungsi. Dapper: "On SQL Server it is crucial to use the unicode when querying unicode and ANSI when querying non unicode." ^[11] Kolom lab bertipe nvarchar, jadi lab tidak mendemokannya.
• Rentang tanggal: pakai half-open >= awal AND < awal-berikutnya. BETWEEN '2025-06-01' AND '2025-06-30' melewatkan baris tanggal 30 Juni yang punya jam.

Padanan Node: aturan ini sama di PostgreSQL/MySQL. Prisma where: { createdAt: { gte, lt } } menghasilkan rentang yang SARGable; masalahnya muncul saat Anda menulis raw SQL dengan fungsi pada kolom.

3. Parameter sniffing (versi ringkas)

"Parameter sensitivity, also known as parameter sniffing, refers to a process where the Database Engine sniffs the current parameter values during compilation or recompilation, and passes them to the Query Optimizer". Berlaku untuk stored procedure, query lewat sp_executesql, dan prepared query. ^[4] Query berparameter dari EF Core dan Dapper dikirim lewat sp_executesql, jadi ikut kena.

Biasanya ini bagus: plan dibuat sekali lalu dipakai ulang. Masalahnya muncul pada data tidak merata: "a single cached plan for a parameterized query isn't optimal for all possible incoming parameter values. This is the case with non-uniform data distributions." ^[12] Lab sengaja timpang: Category 1 punya 90.000 Product, Category 6 hanya 20.

Gejala khas di produksi: "kemarin cepat, hari ini lambat, padahal kodenya tidak berubah", atau lambat hanya untuk nilai tertentu. Di actual plan, properti operator SELECT memperlihatkan nilai parameter saat kompilasi dan saat eksekusi; kalau berbeda jauh, curigai sniffing.

Penanganan, masing-masing punya harga ("Each method has associated tradeoffs and drawbacks"): ^[9]

SQL Server 2022: Parameter Sensitive Plan (PSP) optimization "automatically enables multiple, active cached plans for a single parameterized statement", aktif di compatibility level 160 (default database baru), saat ini hanya untuk predikat equality. Fitur ini bisa menyembunyikan demo klasik, jadi skrip lab mematikannya sementara dengan ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF lalu menyalakannya lagi. ^[12] Untuk interview, cukup sebut bahwa fitur ini ada; jawaban utamanya tetap mekanisme dan cara penanganan di atas.

4. Pagination: OFFSET/FETCH vs keyset

-- OFFSET/FETCH (EF Core: .Skip(90000).Take(20))
SELECT Id, Name, Price FROM dbo.Product
ORDER BY Id
OFFSET 90000 ROWS FETCH NEXT 20 ROWS ONLY;

-- Keyset / seek (EF Core: .Where(p => p.Id > lastId).Take(20))
SELECT TOP (20) Id, Name, Price FROM dbo.Product
WHERE Id > @lastId
ORDER BY Id;

• OFFSET "Specifies the number of rows to skip before the query starts to return rows" ^[14], tapi melewati bukan berarti gratis: "The database must still process the first 20 entries, even if they aren't returned to the application; this creates possibly significant computation load that increases with the number of rows being skipped." Kalau data berubah di antara dua halaman, baris bisa terlewat atau tampil dua kali. ^[15]
• Keyset: "simply use a WHERE clause to skip rows, instead of an offset." Dengan index pada kolom urutannya, query ini "very efficient" dan tidak terpengaruh perubahan di baris sebelumnya. Batasannya: tidak bisa lompat ke halaman tertentu ("does not support random access"), hanya maju/mundur. ^[15]
• Urutan harus unik, apa pun metodenya: "relational databases do not apply any ordering by default, even on the primary key." Urut menurut kolom yang bisa kembar (Price, CreatedAt)? Tambahkan Id sebagai pemecah seri, dan buat composite index yang cocok. ^[15]
• Keyset dua kolom ditulis dengan OR, karena perbandingan (A, B) > (x, y) tidak didukung EF Core LINQ (dan tidak ada di T-SQL): WHERE A > @x OR (A = @x AND B > @y). ^[15]

// EF Core, sampel resmi: urut Date lalu PostId, keyset dua kunci
var nextPage = await context.Posts
    .OrderBy(b => b.Date).ThenBy(b => b.PostId)
    .Where(b => b.Date > lastDate || (b.Date == lastDate && b.PostId > lastId))
    .Take(10)
    .ToListAsync();

Kapan OFFSET tetap wajar: tabel kecil, admin panel dengan nomor halaman, halaman awal saja. Kapan keyset: infinite scroll, API "next cursor", export besar. Padanan Node: Prisma skip/take (offset) vs cursor + take (keyset).

5. Melihat SQL yang dihasilkan EF Core

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)   // setiap perintah SQL + durasinya
        .EnableSensitiveDataLogging();                    // nilai parameter ikut tampil: HANYA development

var query = db.Products
    .Where(p => p.CategoryId == categoryId)
    .OrderBy(p => p.Price)
    .TagWith("Products by category");                    // komentar di SQL, mudah dicari di log

Console.WriteLine(query.ToQueryString());               // SQL-nya, TANPA mengeksekusi query
var products = await query.ToListAsync();

• Simple logging (LogTo) "requires minimal configuration and no additional NuGet packages"; di level Information log berisi akses database. ^[16] Setiap perintah dicatat dengan durasinya, mis. Executed DbCommand (4ms) [Parameters=[...]] lalu SQL-nya, jadi query lambat langsung terlihat. Tapi: "Leaving command execution logging enabled in your production environment is usually a bad idea." ^[1] (Di ASP.NET Core, log yang sama keluar lewat ILogger dengan kategori Microsoft.EntityFrameworkCore.Database.Command.)
• Nilai parameter tidak ikut tampil di log maupun pesan exception secara default (di log muncul sebagai @p0='?'). EnableSensitiveDataLogging "Enables application data to be included in exception messages, logging, etc. This can include ... parameter values for commands being sent to the database"; aktifkan "only ... if you have the appropriate security measures in place". ^[17]
• ToQueryString() (EF Core 5+): "Generates a string representation of the query used. This string may not be suitable for direct execution and is intended only for use in debugging." ^[18] Hasilnya berupa DECLARE parameter diikuti SELECT ... WHERE [p].[CategoryId] = @__categoryId_0 (nama parameter tergantung versi). Salin ke Azure Data Studio, jalankan dengan actual plan: kembali ke langkah 2 di bagian 1.
• TagWith "allows you to inject a small, identifying comment into the SQL query", supaya SQL di log atau di monitoring server bisa dikaitkan ke kode LINQ-nya. ^[1]

Hubungkan ke bagian sebelumnya: Skip/Take diterjemahkan menjadi OFFSET/FETCH; Where(p => p.CreatedAt.Year == 2025) menjadi DATEPART(year, ...) pada kolom (non-SARGable, bandingkan dengan rentang tanggal); query berparameter EF Core kena parameter sniffing. N+1 dan AsNoTracking: lesson 0006. Padanan Node: LogTo ≈ Prisma log: ['query'] atau knex debug; ToQueryString ≈ knex .toString() / .toSQL().

6. Hands-on (±15 menit): lihat sendiri setiap masalah

Skrip ini membuat index dan stored procedure sementara, memperlihatkan setiap masalah dan perbaikannya, lalu menghapus semuanya. Bandingkan tiga hal: operator di plan, Estimated vs Actual Number of Rows (arahkan kursor ke operator), dan logical reads di tab Messages.

1. Container jalan (docker start sql2022), setup lab sudah dijalankan, lab 0022 sudah selesai.
2. Buka dotnet/lab/0023-optimasi-query.sql di Azure Data Studio, VS Code mssql, atau SSMS. Nyalakan actual execution plan.
3. Jalankan per bagian (blok sampai GO). Sebelum setiap query, tebak operatornya dan apakah reads naik atau turun. Catat:

Bagian 3 mematikan PSP optimization di level database dan menyalakannya lagi di 3d; bagian 0 dan 5 juga menyalakannya, jadi aman walau skrip berhenti di tengah lalu dijalankan ulang. Lewat sqlcmd: docker cp dotnet/lab/0023-optimasi-query.sql sql2022:/tmp/0023.sql lalu docker exec sql2022 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Dev_Passw0rd!' -C -i /tmp/0023.sql (logical reads tampil, plan grafis tidak).

-- Lesson 0023 · Optimasi query: execution plan, SARGable, parameter sniffing, pagination OFFSET vs keyset.
-- Jalankan setelah dotnet/lab/00-setup-product-catalog-lab.sql (dan sesudah lab 0022 selesai, yang menghapus index-nya sendiri).
-- Boleh dijalankan ulang. Jalankan PER BAGIAN (blok sampai GO), dengan actual execution plan menyala
-- (tombol "Explain"/"Actual plan" di Azure Data Studio / VS Code mssql, Ctrl+M di SSMS).
-- Untuk setiap query, bandingkan: operator di plan, Estimated vs Actual Number of Rows, dan logical reads (tab Messages).
-- Angka di komentar adalah perkiraan; yang penting arah perubahannya.

USE ProductCatalogLab;
SET NOCOUNT ON;
GO

-- 0. Kondisi awal ---------------------------------------------------------------------------
-- Bersihkan sisa skrip ini kalau pernah berhenti di tengah jalan.
DROP PROCEDURE IF EXISTS dbo.usp_ProductsByCategory;
DROP PROCEDURE IF EXISTS dbo.usp_ProductsByCategory_Recompile;
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CreatedAt ON dbo.Product;
-- Bagian 3 mematikan PSP optimization; kembalikan ke default (ON) kalau skrip pernah terhenti sebelum bagian 5.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
GO

-- Database baru di SQL Server 2022 memakai compatibility level 160: syarat PSP optimization (bagian 3).
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();
SELECT name, value FROM sys.database_scoped_configurations
WHERE name = N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';
GO

-- Index untuk bagian 1 dan 3: hanya CategoryId (+ Id otomatis). Sengaja TIDAK covering.
CREATE INDEX IX_Product_CategoryId ON dbo.Product (CategoryId);
GO

-- 1. Execution plan: estimated vs actual ---------------------------------------------------
-- 1a. Estimated plan: query TIDAK dijalankan, hanya plan dari optimizer (tanpa angka runtime).
--     SET SHOWPLAN_XML harus sendirian di batch-nya.
SET SHOWPLAN_XML ON;
GO
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;
GO
SET SHOWPLAN_XML OFF;
GO

-- 1b. Actual plan: query dijalankan, plan ikut membawa ActualRows. Klik XML-nya untuk membuka plan grafis.
--     Literal 6: optimizer memakai histogram, perkiraan ±20 baris = aktual 20 -> Index Seek + Key Lookup.
SET STATISTICS IO ON;
SET STATISTICS XML ON;
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = 6;
SET STATISTICS XML OFF;
GO

-- 1c. Perkiraan meleset. Local variable tidak bisa "diintip" saat kompilasi, jadi optimizer memakai
--     rata-rata (density): 100.000 / 6 Category = perkiraan ±16.667 baris, padahal aktualnya 20.
--     Dengan perkiraan sebesar itu, kemungkinan besar plan-nya Clustered Index Scan: reads sebanyak seluruh tabel
--     untuk 20 baris. Selisih besar Estimated vs Actual = tanda pertama saat membaca plan query lambat.
DECLARE @c int = 6;
SET STATISTICS XML ON;
SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = @c;
SET STATISTICS XML OFF;
GO

-- 2. SARGable vs non-SARGable ---------------------------------------------------------------
-- 2a. UQ_Product_Name (dari setup) berisi Name + Id, jadi kedua query ini dijawab dari index itu saja.
-- Fungsi pada kolom: engine harus menghitung LEFT() untuk setiap baris -> Index Scan seluruh index.
SELECT Id, Name FROM dbo.Product WHERE LEFT(Name, 12) = N'Product 0001';

-- Hasil sama (100 baris: Product 000100..000199), tapi LIKE dengan prefix tetap bisa dicari di index -> Index Seek, reads jauh lebih kecil.
SELECT Id, Name FROM dbo.Product WHERE Name LIKE N'Product 0001%';

-- Wildcard di depan tidak punya titik awal di index -> kembali scan (10 baris, tetap membaca seluruh index).
SELECT Id, Name FROM dbo.Product WHERE Name LIKE N'%0001';
GO

-- 2b. Tanggal. Index pada CreatedAt; Id ikut otomatis, jadi SELECT Id, CreatedAt tetap covering.
CREATE INDEX IX_Product_CreatedAt ON dbo.Product (CreatedAt);
GO

-- Non-SARGable: YEAR() dan MONTH() membungkus kolom -> Index Scan seluruh IX_Product_CreatedAt.
SELECT Id, CreatedAt FROM dbo.Product WHERE YEAR(CreatedAt) = 2025 AND MONTH(CreatedAt) = 6;

-- SARGable: kolom dibiarkan polos, rentang half-open [1 Juni, 1 Juli) -> Index Seek, hasil sama (3.323 baris menurut rumus seed).
SELECT Id, CreatedAt FROM dbo.Product WHERE CreatedAt >= '2025-06-01' AND CreatedAt < '2025-07-01';
GO

-- 3. Parameter sniffing dengan data tidak merata -------------------------------------------
-- Category 1 = 90.000 Product, Category 6 = 20 Product (setup). Satu plan tidak cocok untuk keduanya.
CREATE OR ALTER PROCEDURE dbo.usp_ProductsByCategory @CategoryId int
AS
    SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = @CategoryId;
GO

-- SQL Server 2022 (compat 160) punya PSP optimization yang bisa menyimpan beberapa plan per query dan
-- MENYEMBUNYIKAN efek klasik ini. Matikan dulu di level database supaya masalahnya terlihat (dinyalakan lagi di 3d).
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
-- Kosongkan plan cache database INI saja (jangan DBCC FREEPROCCACHE tanpa parameter di server bersama).
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

-- 3a. Nilai kecil lebih dulu: plan untuk 20 baris (Index Seek + Key Lookup) dikompilasi dan DISIMPAN.
EXEC dbo.usp_ProductsByCategory @CategoryId = 6;   -- logical reads: puluhan
-- Nilai besar memakai plan yang sama: 90.000 Key Lookup -> logical reads ratusan ribu, jauh di atas satu scan.
-- Di actual plan, lihat properti operator SELECT: Parameter Compiled Value = 6, Parameter Runtime Value = 1.
EXEC dbo.usp_ProductsByCategory @CategoryId = 1;
GO

-- 3b. Urutan dibalik: plan untuk 90.000 baris (Clustered Index Scan) disimpan, lalu dipakai untuk 20 baris.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC dbo.usp_ProductsByCategory @CategoryId = 1;   -- scan: wajar untuk 90% tabel
EXEC dbo.usp_ProductsByCategory @CategoryId = 6;   -- tetap scan seluruh tabel untuk 20 baris
GO

-- 3c. Dua perbaikan.
-- Perbaikan 1: OPTION (RECOMPILE) -> plan baru setiap eksekusi, dibuang setelahnya. Bayarannya CPU kompilasi tiap panggilan.
CREATE OR ALTER PROCEDURE dbo.usp_ProductsByCategory_Recompile @CategoryId int
AS
    SELECT Id, Name, Price FROM dbo.Product WHERE CategoryId = @CategoryId
    OPTION (RECOMPILE);
GO
EXEC dbo.usp_ProductsByCategory_Recompile @CategoryId = 6;   -- Index Seek + Key Lookup
EXEC dbo.usp_ProductsByCategory_Recompile @CategoryId = 1;   -- Clustered Index Scan
GO
-- Alternatif lain (tidak dijalankan): OPTION (OPTIMIZE FOR (@CategoryId = 1)) memakai nilai "khas" yang Anda pilih;
-- OPTION (OPTIMIZE FOR UNKNOWN) memakai rata-rata, sama seperti local variable di 1c.

-- Perbaikan 2 (akar masalah): covering index. Tidak ada Key Lookup, jadi Index Seek bagus untuk 20 MAUPUN 90.000 baris
-- dan plan yang disimpan aman untuk semua nilai.
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
CREATE INDEX IX_Product_CategoryId_Covering ON dbo.Product (CategoryId) INCLUDE (Name, Price);
GO
EXEC sp_recompile N'dbo.usp_ProductsByCategory';
GO
EXEC dbo.usp_ProductsByCategory @CategoryId = 6;   -- Index Seek, beberapa reads
EXEC dbo.usp_ProductsByCategory @CategoryId = 1;   -- Index Seek yang sama, reads sebanding scan, tanpa lookup
GO

-- 3d. Nyalakan lagi PSP optimization dan ulangi skenario 3a (index non-covering).
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
CREATE INDEX IX_Product_CategoryId ON dbo.Product (CategoryId);
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
-- Kalau PSP memilih predikat ini, nilai 6 dan 1 mendapat plan (query variant) berbeda dan reads untuk 1 kembali wajar.
-- Tidak dijamin terjadi untuk setiap query; bandingkan reads-nya dengan 3a.
EXEC dbo.usp_ProductsByCategory @CategoryId = 6;
EXEC dbo.usp_ProductsByCategory @CategoryId = 1;
GO
-- Query Store aktif default di database baru SQL Server 2022; variant PSP tercatat di sini (kosong = PSP tidak dipakai).
SELECT query_variant_query_id, parent_query_id, dispatcher_plan_id
FROM sys.query_store_query_variant;
GO

-- 4. Pagination: OFFSET/FETCH vs keyset -----------------------------------------------------
-- 4a. Urut Id. Halaman ke-4.501 (20 per halaman): OFFSET harus membaca dan membuang 90.000 baris dulu.
SELECT Id, Name, Price FROM dbo.Product
ORDER BY Id
OFFSET 90000 ROWS FETCH NEXT 20 ROWS ONLY;     -- logical reads: ratusan (hampir seluruh tabel)

-- Keyset: aplikasi mengingat Id terakhir halaman sebelumnya, lalu Index Seek langsung ke sana.
DECLARE @lastId int = 90000;
SELECT TOP (20) Id, Name, Price FROM dbo.Product
WHERE Id > @lastId
ORDER BY Id;                                   -- logical reads: hitungan jari, hasil sama persis
GO

-- 4b. Urutan yang lebih realistis: Product terbaru dulu (CreatedAt DESC), Id sebagai pemecah seri supaya urutan unik.
-- IX_Product_CreatedAt (dari bagian 2) secara efektif berisi (CreatedAt, Id), cocok dengan urutan ini.
SELECT Id, Name, CreatedAt FROM dbo.Product
ORDER BY CreatedAt DESC, Id DESC
OFFSET 90000 ROWS FETCH NEXT 20 ROWS ONLY;     -- scan + sort, atau 90.000 lookup: mahal
GO

-- Nilai kunci baris terakhir halaman sebelumnya. Di aplikasi nilainya datang dari halaman yang baru ditampilkan;
-- di sini diambil dengan OFFSET supaya hasilnya bisa dibandingkan, jadi STATISTICS IO dimatikan sebentar.
SET STATISTICS IO OFF;
DECLARE @lastCreatedAt datetime2(0), @lastId int;
SELECT @lastCreatedAt = CreatedAt, @lastId = Id FROM dbo.Product
ORDER BY CreatedAt DESC, Id DESC
OFFSET 89999 ROWS FETCH NEXT 1 ROWS ONLY;
SET STATISTICS IO ON;

-- Keyset dua kunci. T-SQL tidak punya (CreatedAt, Id) < (@a, @b), jadi ditulis dengan OR.
-- Syarat pertama (CreatedAt <= ...) memberi rentang seek; sisanya membuang baris seri yang sudah tampil.
-- Hasilnya harus sama dengan query OFFSET di atas, dengan Index Seek + 20 Key Lookup saja.
SELECT TOP (20) Id, Name, CreatedAt FROM dbo.Product
WHERE CreatedAt <= @lastCreatedAt
  AND (CreatedAt < @lastCreatedAt OR Id < @lastId)
ORDER BY CreatedAt DESC, Id DESC;
GO

SET STATISTICS IO OFF;
GO

-- 5. Kembalikan lab ke kondisi setup --------------------------------------------------------
DROP PROCEDURE IF EXISTS dbo.usp_ProductsByCategory;
DROP PROCEDURE IF EXISTS dbo.usp_ProductsByCategory_Recompile;
DROP INDEX IF EXISTS IX_Product_CategoryId ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CategoryId_Covering ON dbo.Product;
DROP INDEX IF EXISTS IX_Product_CreatedAt ON dbo.Product;
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
GO

-- Harus kembali hanya PK_Product dan UQ_Product_Name, dan PSP optimization = 1 (ON).
SELECT i.name, i.type_desc
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID(N'dbo.Product') AND i.index_id > 0
ORDER BY i.index_id;
SELECT name, value FROM sys.database_scoped_configurations
WHERE name = N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';
GO

7. Latihan tulis query

Tulis sendiri di editor SQL, jalankan di lab, baru buka jawaban. Tidak dinilai otomatis; bandingkan hasil, plan, dan logical reads.

CREATE INDEX IX_Product_CreatedAt ON dbo.Product (CreatedAt);
SET STATISTICS IO ON;

-- Sebelum: CONVERT membungkus kolom -> Index Scan seluruh IX_Product_CreatedAt
SELECT Id, CreatedAt FROM dbo.Product WHERE CONVERT(char(7), CreatedAt, 120) = '2025-03';

-- Sesudah: kolom polos, rentang half-open -> Index Seek
SELECT Id, CreatedAt FROM dbo.Product
WHERE CreatedAt >= '2025-03-01' AND CreatedAt < '2025-04-01';

-- Bukti jumlah sama
SELECT
    (SELECT COUNT(*) FROM dbo.Product WHERE CONVERT(char(7), CreatedAt, 120) = '2025-03') AS Lama,
    (SELECT COUNT(*) FROM dbo.Product WHERE CreatedAt >= '2025-03-01' AND CreatedAt < '2025-04-01') AS Baru;

SET STATISTICS IO OFF;
DROP INDEX IF EXISTS IX_Product_CreatedAt ON dbo.Product;

-- (1) Price di key; Id otomatis ikut sebagai key terakhir (clustered key), Name di INCLUDE -> covering
CREATE INDEX IX_Product_Price ON dbo.Product (Price) INCLUDE (Name);
GO

-- Nilai baris terakhir halaman pertama (di aplikasi: dari halaman yang baru ditampilkan)
DECLARE @lastPrice decimal(18, 2), @lastId int;
SELECT @lastPrice = Price, @lastId = Id FROM dbo.Product
ORDER BY Price, Id
OFFSET 19 ROWS FETCH NEXT 1 ROWS ONLY;

-- (2) Keyset: halaman berikutnya
SELECT TOP (20) Id, Name, Price FROM dbo.Product
WHERE Price > @lastPrice OR (Price = @lastPrice AND Id > @lastId)
ORDER BY Price, Id;

-- Pembanding: harus 20 baris yang sama
SELECT Id, Name, Price FROM dbo.Product
ORDER BY Price, Id
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;
GO

DROP INDEX IF EXISTS IX_Product_Price ON dbo.Product;

Ringkasan untuk dicetak: Reference: Optimasi query · Index: Reference: Index · Istilah: GLOSSARY.md (bagian "Database (SQL Server)")

Ada yang belum jelas? Tanyakan ke saya (agent) di terminal, misalnya: "apa itu Query Store dan bagaimana dipakai untuk menemukan query yang tiba-tiba lambat?", "kapan computed column + index lebih baik daripada menulis ulang query?", atau "bagaimana keyset pagination dipakai untuk tombol halaman sebelumnya?"

Sumber: [1] EF Core: Performance Diagnosis · [2] Execution plans · [3] Display the estimated execution plan · [4] Query processing architecture guide · [5] SET SHOWPLAN_XML · [6] SET STATISTICS XML · [7] Clustered and nonclustered indexes described · [8] Index architecture and design guide · [9] Troubleshoot high-CPU-usage issues · [10] CREATE PROCEDURE (best practices) · [11] Dapper README · [12] Parameter Sensitive Plan optimization · [13] Query hints · [14] ORDER BY clause (OFFSET/FETCH) · [15] EF Core: Pagination · [16] EF Core: Simple logging · [17] EnableSensitiveDataLogging · [18] ToQueryString