Foto oleh panumas nikhomkhai via Pexels

Statistics adalah informasi ringkas yang disimpan SQL Server tentang distribusi nilai pada kolom-kolom tertentu, dipakai oleh query optimizer untuk memperkirakan jumlah baris yang akan dihasilkan sebuah operasi sebelum memilih execution plan yang paling efisien.

Parameter sniffing adalah perilaku SQL Server yang membuat plan eksekusi stored procedure berdasarkan nilai parameter yang dipakai pada eksekusi pertama kali, lalu plan tersebut di-cache dan dipakai ulang untuk eksekusi berikutnya meski parameter yang dikirim berbeda jauh karakteristiknya.

Kombinasi statistics yang usang dan parameter sniffing yang tidak tepat adalah salah satu penyebab paling umum query yang 'kadang cepat, kadang lambat' tanpa perubahan kode apa pun, dan memahami keduanya penting untuk mendiagnosis masalah performa yang tidak konsisten.

Melihat Statistics dengan DBCC SHOW_STATISTICS

Melihat histogram statistics sebuah index

DBCC SHOW_STATISTICS('Orders', 'IX_Orders_CustomerID'); -- Expected output: histogram yang menunjukkan -- perkiraan distribusi nilai CustomerID pada tabel Orders
DBCC SHOW_STATISTICS menampilkan histogram yang berisi perkiraan jumlah baris untuk rentang nilai tertentu pada kolom yang di-index, serta kapan terakhir kali statistics tersebut diperbarui. Informasi ini menjadi dasar bagi query optimizer untuk memilih antara Index Seek, Index Scan, atau Table Scan.

Parameter Sniffing dan Dampaknya

Plan yang di-cache dari parameter pertama bisa tidak cocok untuk parameter lain

CREATE PROCEDURE sp_GetOrdersByStatus @Status VARCHAR(20) AS BEGIN SELECT * FROM Orders WHERE Status = @Status; END; -- Eksekusi pertama dengan @Status = 'Pending' (hanya 10 baris) -- menghasilkan plan Index Seek, lalu di-cache -- Eksekusi berikutnya dengan @Status = 'Completed' (1 juta baris) -- TETAP memakai plan Index Seek yang sama, padahal Table Scan lebih cocok
Masalah muncul ketika distribusi data untuk nilai parameter yang berbeda sangat timpang: plan yang optimal untuk status 'Pending' (sedikit baris, Index Seek cocok) bisa menjadi sangat tidak efisien ketika dipakai ulang untuk status 'Completed' (banyak baris, Table Scan sebenarnya lebih cepat), namun SQL Server tetap memakai plan yang sudah di-cache tersebut.

Memaksa Recompile untuk Mengatasi Parameter Sniffing

WITH RECOMPILE membuat plan baru setiap eksekusi

CREATE PROCEDURE sp_GetOrdersByStatus @Status VARCHAR(20) WITH RECOMPILE AS BEGIN SELECT * FROM Orders WHERE Status = @Status; END; -- Expected: SQL Server membuat plan baru -- setiap kali procedure dipanggil, sesuai parameter saat itu
WITH RECOMPILE memaksa SQL Server membuang plan yang di-cache dan membuat plan baru setiap kali stored procedure dipanggil, memastikan plan tersebut selalu sesuai dengan parameter aktual yang dikirim. Konsekuensinya, ada overhead tambahan untuk kompilasi ulang setiap eksekusi, sehingga opsi ini sebaiknya dipakai selektif hanya pada procedure yang benar-benar terdampak parameter sniffing.

Memperbarui Statistics Secara Manual

UPDATE STATISTICS memperbarui perkiraan distribusi data

UPDATE STATISTICS Orders; -- atau untuk index tertentu saja: UPDATE STATISTICS Orders IX_Orders_CustomerID WITH FULLSCAN; -- Expected: query optimizer kini memakai -- gambaran data terbaru, bukan data lama yang sudah usang
Statistics yang usang, misalnya setelah proses bulk insert besar-besaran, bisa membuat query optimizer salah memperkirakan jumlah baris dan akhirnya memilih plan yang tidak optimal. UPDATE STATISTICS dengan opsi WITH FULLSCAN memberikan hasil paling akurat karena memeriksa seluruh baris tabel, meski membutuhkan waktu lebih lama dibanding sampling default.

Mengatasi Parameter Sniffing dengan OPTIMIZE FOR dan RECOMPILE

OPTIMIZE FOR memakai asumsi nilai tetap, RECOMPILE membuat plan baru tiap eksekusi

CREATE PROCEDURE dbo.usp_GetOrdersByStatus @Status VARCHAR(20) AS BEGIN SELECT OrderID, CustomerID, TotalAmount FROM Orders WHERE Status = @Status OPTION (OPTIMIZE FOR (@Status = 'Pending')); -- asumsikan nilai paling umum END; -- Plan selalu dioptimalkan untuk distribusi 'Pending', bukan nilai eksekusi pertama -- Alternatif: paksa compile ulang plan setiap eksekusi untuk kolom dengan skew tinggi ALTER PROCEDURE dbo.usp_GetOrdersByStatus @Status VARCHAR(20) AS BEGIN SELECT OrderID, CustomerID, TotalAmount FROM Orders WHERE Status = @Status OPTION (RECOMPILE); END;

Parameter sniffing terjadi karena SQL Server membuat execution plan untuk stored procedure berdasarkan nilai parameter yang dipakai pada eksekusi pertama kali (atau setelah plan di-cache ulang), lalu menyimpan plan tersebut di cache untuk dipakai ulang pada eksekusi berikutnya dengan parameter yang berbeda. Jika distribusi data pada kolom tersebut sangat tidak merata (data skew), plan yang optimal untuk satu nilai bisa menjadi sangat buruk untuk nilai lain.

OPTION (OPTIMIZE FOR (@Status = 'Pending')) memaksa optimizer selalu membuat estimasi cardinality seolah-olah parameter tersebut bernilai 'Pending', terlepas dari nilai aktual yang dikirim saat runtime, sehingga cocok dipakai ketika satu nilai tertentu jauh lebih sering dipanggil dibanding nilai lain dan plan untuk nilai tersebut diketahui stabil dan baik.

OPTION (RECOMPILE) mengambil pendekatan berbeda dengan membuang cache plan dan membuat rencana eksekusi baru setiap kali procedure dipanggil, berdasarkan nilai parameter aktual pada eksekusi tersebut; pendekatan ini menjamin plan selalu sesuai dengan parameter yang dikirim, namun menambah overhead compile pada setiap eksekusi, sehingga lebih cocok untuk procedure yang jarang dipanggil namun sangat sensitif terhadap data skew.

Kapan Statistik Perlu Diperbarui Secara Manual

modification_counter yang tinggi dibanding jumlah baris menandakan statistik usang

-- Memeriksa kapan statistik terakhir diperbarui dan berapa banyak perubahan terjadi SELECT s.name AS StatName, sp.last_updated, sp.rows, sp.modification_counter FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE s.object_id = OBJECT_ID('Orders'); -- modification_counter tinggi dibanding rows berarti statistik sudah usang -- Update manual dengan FULLSCAN untuk akurasi maksimum (dipakai setelah bulk load) UPDATE STATISTICS Orders WITH FULLSCAN; -- Update seluruh statistik di database sekaligus, dengan sampling default EXEC sp_updatestats;

SQL Server secara otomatis memperbarui statistik ketika ambang batas perubahan data tertentu terlampaui, namun pembaruan otomatis ini memakai sampling, bukan pemindaian penuh, dan baru terpicu setelah sejumlah baris berubah signifikan sejak statistik terakhir diperbarui. Pada tabel yang sangat besar, ambang batas ini bisa berarti jutaan baris berubah sebelum SQL Server menyadari statistik sudah usang, sehingga query di antara periode tersebut bisa memakai estimasi cardinality yang keliru.

Update statistik manual sebaiknya dilakukan setelah operasi yang mengubah data secara masif dalam waktu singkat, seperti bulk insert jutaan baris, migrasi data, atau penghapusan besar-besaran, karena menunggu mekanisme otomatis bereaksi bisa membuat query berikutnya memakai plan yang buruk untuk sementara waktu sebelum ambang batas otomatis terpenuhi. UPDATE STATISTICS dengan opsi WITH FULLSCAN memberi akurasi maksimum karena memindai seluruh baris, berbeda dari sampling default yang lebih cepat namun kurang presisi pada data dengan distribusi tidak merata.

Kolom modification_counter pada sys.dm_db_stats_properties menunjukkan berapa banyak baris berubah sejak statistik terakhir diperbarui, dan membandingkannya dengan jumlah total baris (rows) membantu menentukan apakah statistik sudah cukup usang untuk diperbarui manual. Pembaruan statistik manual yang dijadwalkan rutin pada jam sepi trafik juga umum dilakukan pada tabel besar yang mengalami perubahan data tinggi namun tidak cukup drastis untuk memicu threshold otomatis dengan cepat.

Ambang Auto-Update Statistics dan Trace Flag 2371 pada Tabel Besar

Trace flag 2371 menurunkan ambang auto-update statistics untuk tabel besar

-- Perilaku lama: statistik auto-update setelah ~20% baris berubah (tetap, tidak dinamis) -- Pada tabel 50 juta baris, berarti 10 juta baris harus berubah dulu sebelum statistik diperbarui -- Mengaktifkan ambang batas dinamis yang lebih kecil untuk tabel besar DBCC TRACEON(2371, -1); -- Dengan trace flag ini, ambang batas menurun secara dinamis seiring bertambahnya -- jumlah baris tabel, sehingga tabel besar tidak perlu menunggu 20% berubah -- Alternatif modern sejak compatibility level 130: otomatis aktif tanpa trace flag ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = 150;

Secara default, SQL Server memicu auto-update statistics setelah sekitar 20 persen baris pada tabel berubah sejak statistik terakhir diperbarui, sebuah ambang batas tetap yang pada tabel kecil berarti hanya butuh sedikit perubahan, namun pada tabel puluhan juta baris berarti jutaan baris harus berubah dulu sebelum statistik dianggap usang dan diperbarui otomatis.

Trace flag 2371 mengubah ambang batas tersebut menjadi dinamis, di mana persentase perubahan yang dibutuhkan semakin kecil seiring membesarnya jumlah baris tabel, sehingga tabel besar bisa mendapat pembaruan statistik lebih sering dan lebih relevan dengan kondisi data terkini. Sejak database memakai compatibility level 130 ke atas, perilaku ambang batas dinamis ini sudah aktif secara default tanpa perlu mengaktifkan trace flag secara manual, sehingga trace flag 2371 kini terutama relevan untuk database yang masih dipertahankan pada compatibility level lama karena alasan kompatibilitas aplikasi.

Kesalahan Umum yang Sering Terjadi

Mengabaikan Statistics yang Sudah Lama Tidak Diperbarui

Tabel yang sering menerima bulk insert atau delete dalam jumlah besar tanpa auto-update statistics yang memadai bisa membuat query optimizer bekerja dengan gambaran data yang sudah jauh berbeda dari kondisi aktual.

Memakai WITH RECOMPILE di Semua Stored Procedure

Menambahkan WITH RECOMPILE secara membabi buta pada semua stored procedure akan menambah overhead kompilasi yang signifikan pada procedure yang sebenarnya tidak terdampak parameter sniffing sama sekali.

Tidak Menyadari Query Lambat Disebabkan Plan Lama

Developer sering mengira query yang tiba-tiba melambat disebabkan oleh pertumbuhan data semata, padahal akar masalahnya adalah plan lama yang sudah tidak optimal untuk distribusi data saat ini namun tetap dipakai karena masih tersimpan di cache.

Kapan Pendekatan Ini Tidak Cocok Dipakai

Parameter sniffing sebenarnya adalah fitur optimasi yang bermanfaat untuk sebagian besar kasus karena menghindari kompilasi ulang yang mahal pada setiap eksekusi, dan hanya menjadi masalah ketika distribusi data antar parameter sangat timpang.

Solusi seperti WITH RECOMPILE atau OPTIMIZE FOR tertentu memberikan plan yang lebih sesuai pada kasus timpang, namun menukarnya dengan overhead kompilasi tambahan, sehingga penerapannya perlu diuji dan dibatasi hanya pada procedure yang benar-benar menunjukkan gejala parameter sniffing.

Ringkasan

Statistics memberi gambaran distribusi data yang dipakai query optimizer untuk membuat keputusan, dan parameter sniffing bisa menyebabkan plan yang dibuat untuk satu nilai parameter dipakai paksa untuk nilai lain yang karakteristik datanya sangat berbeda. Kenali gejala query yang performanya tidak konsisten, lalu pertimbangkan UPDATE STATISTICS atau WITH RECOMPILE secara selektif sebagai solusinya.