Statistics

Berlaku untuk:SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse AnalyticsSQL database di Microsoft Fabric

Pengoptimal Kueri menggunakan statistik untuk membuat rencana kueri yang meningkatkan performa kueri. Untuk sebagian besar kueri, Pengoptimal Kueri sudah menghasilkan statistik yang diperlukan untuk rencana kueri berkualitas tinggi. Dalam beberapa kasus, Anda perlu membuat statistik tambahan atau memodifikasi desain kueri untuk hasil terbaik. Artikel ini membahas konsep statistik dan memberikan panduan untuk menggunakan statistik pengoptimalan kueri secara efektif.

Komponen dan konsep

Statistics

Statistik untuk pengoptimalan kueri adalah objek besar biner (BLOB) yang memuat informasi statistik mengenai distribusi nilai di satu atau beberapa kolom tabel atau tampilan terindeks. Pengoptimal Kueri menggunakan statistik ini untuk memperkirakan kardinalitas, atau jumlah baris, dalam hasil kueri. Perkiraan kardinalitas ini memungkinkan Pengoptimal Kueri untuk membuat rencana kueri berkualitas tinggi. Misalnya, tergantung pada predikat Anda, Pengoptimal Kueri dapat menggunakan perkiraan kardinalitas untuk memilih operator pencarian indeks alih-alih operator pemindaian indeks yang lebih intensif sumber daya, jika melakukannya meningkatkan performa kueri.

Setiap objek statistik dibuat pada daftar satu atau beberapa kolom tabel dan menyertakan histogram yang menampilkan distribusi nilai di kolom pertama. Objek statistik pada beberapa kolom juga menyimpan informasi statistik tentang korelasi nilai di antara kolom. Statistik korelasi ini, atau kepadatan, berasal dari jumlah baris nilai kolom yang berbeda.

Histogram

Histogram mengukur frekuensi kemunculan untuk setiap nilai yang berbeda dalam himpunan data. Pengoptimal Kueri menghitung histogram pada nilai kolom di kolom kunci pertama objek statistik, memilih nilai kolom dengan mengambil sampel baris secara statistik atau dengan melakukan pemindaian penuh semua baris dalam tabel atau tampilan. Jika histogram dibuat dari sekumpulan baris sampel, total yang disimpan untuk jumlah baris dan jumlah nilai yang berbeda adalah perkiraan dan tidak perlu berupa bilangan bulat utuh.

Note

SQL Server membangun histogram hanya untuk satu kolom - kolom pertama dalam kumpulan kolom kunci objek statistik.

Untuk membuat histogram, Pengoptimal Kueri mengurutkan nilai kolom, menghitung jumlah nilai yang cocok dengan setiap nilai kolom yang berbeda, lalu menggabungkan nilai kolom ke dalam maksimal 200 langkah histogram yang berdekatan. Setiap langkah pada histogram mencakup rentang nilai kolom yang diikuti oleh nilai kolom batas atas. Rentang ini mencakup semua nilai kolom yang mungkin di antara nilai batas, tidak termasuk nilai batas itu sendiri. Nilai kolom terendah yang diurutkan adalah nilai batas atas untuk langkah histogram pertama.

Secara lebih rinci, SQL Server membuat histogram dari kumpulan nilai kolom yang diurutkan dalam tiga langkah:

• Inisialisasi histogram: Pada langkah pertama, urutan nilai yang dimulai pada awal kumpulan yang diurutkan diproses, dan hingga 200 nilai range_high_key, equal_rows, range_rows, dan distinct_range_rows dikumpulkan (range_rows dan distinct_range_rows selalu nol selama langkah ini). Langkah pertama berakhir baik ketika semua input habis, atau ketika 200 nilai ditemukan.
• Pindai dengan penggabungan wadah: Setiap nilai tambahan dari kolom utama kunci statistik diproses di langkah kedua, dalam urutan yang diurutkan. Setiap nilai berturut-turut ditambahkan ke rentang terakhir atau rentang baru di akhir dibuat (pengurutan ini dimungkinkan karena nilai input diurutkan). Jika rentang baru dibuat, proses akan menciutkan satu pasang rentang tetangga yang ada menjadi satu rentang. Pasangan rentang ini dipilih untuk meminimalkan kehilangan informasi. Metode ini menggunakan algoritma perbedaan maksimum untuk meminimalkan jumlah langkah dalam histogram sambil memaksimalkan perbedaan antara nilai batas. Jumlah langkah setelah rentang diciutkan tetap 200 sepanjang langkah ini.
• Konsolidasi Histogram: Pada langkah ketiga, lebih banyak rentang dapat diringkas selama tidak ada kehilangan informasi yang signifikan. Jumlah langkah histogram dapat kurang dari jumlah nilai yang berbeda, bahkan untuk kolom dengan kurang dari 200 titik batas. Oleh karena itu, bahkan jika kolom memiliki lebih dari 200 nilai unik, histogram mungkin memiliki kurang dari 200 langkah. Untuk kolom yang hanya terdiri dari nilai unik, histogram terkonsolidasi memiliki minimal tiga langkah.

Note

Jika histogram dibangun menggunakan sampel daripada fullscan, nilai equal_rows, range_rows, distinct_range_rows, dan average_range_rows adalah perkiraan, dan oleh karena itu mereka tidak perlu menjadi bilangan bulat utuh.

Diagram berikut menunjukkan histogram dengan enam langkah. Area di sebelah kiri nilai batas atas pertama adalah langkah pertama.

Untuk setiap langkah histogram dalam contoh sebelumnya:

• Garis tebal mewakili nilai batas atas (range_high_key) dan berapa kali garis tersebut terjadi (equal_rows).

• Area solid di sebelah kiri range_high_key mewakili rentang nilai kolom dan jumlah rata-rata setiap nilai kolom terjadi (average_range_rows). average_range_rows untuk langkah histogram pertama selalu bernilai 0.

• Garis putus-putus mewakili nilai sampel yang digunakan untuk memperkirakan jumlah total nilai yang berbeda dalam rentang (distinct_range_rows) dan jumlah total nilai dalam rentang (range_rows). Pengoptimal Kueri menggunakan range_rows dan distinct_range_rows untuk menghitung average_range_rows dan tidak menyimpan nilai sampel.

Vektor kepadatan

Density adalah informasi tentang jumlah duplikat dalam kolom atau kombinasi kolom tertentu dan dihitung sebagai 1/(jumlah nilai yang berbeda). Pengoptimal Kueri menggunakan kepadatan untuk meningkatkan perkiraan kardinalitas untuk kueri yang mengembalikan beberapa kolom dari tabel atau tampilan terindeks yang sama. Saat kepadatan menurun, selektivitas nilai meningkat. Misalnya, dalam tabel yang mewakili mobil, banyak mobil memiliki produsen yang sama, tetapi setiap mobil memiliki nomor identifikasi kendaraan (VIN) yang unik. Indeks pada VIN lebih selektif daripada indeks pada produsen, karena VIN memiliki kepadatan yang lebih rendah daripada produsen.

Note

Frekuensi adalah informasi tentang kemunculan setiap nilai yang berbeda di kolom kunci pertama objek statistik, dan dihitung sebagai row count * density. Frekuensi maksimum 1 dapat ditemukan di kolom dengan nilai unik.

Vektor kepadatan berisi satu kepadatan untuk setiap awalan kolom dalam objek statistik. Misalnya, jika objek statistik memiliki kolom CustomerIdkunci , , ItemIddan Price, kepadatan dihitung pada setiap awalan kolom berikut.

Awalan kolom	Kepadatan dihitung pada
Aku akan menemui dia. CustomerId	Baris dengan nilai yang cocok untuk CustomerId
(CustomerId, ItemId)	Baris dengan nilai yang cocok untuk CustomerId dan ItemId
(CustomerId, ItemId, Price)	Baris dengan nilai yang cocok untuk CustomerId, ItemId, dan Price

Statistik yang difilter

Statistik yang difilter dapat meningkatkan performa kueri untuk kueri yang memilih dari subset data yang ditentukan dengan baik. Statistik yang difilter menggunakan predikat filter untuk memilih subset data yang disertakan dalam statistik. Statistik terfilter yang dirancang dengan baik dapat meningkatkan rencana eksekusi kueri dibandingkan dengan statistik tabel penuh. Untuk informasi selengkapnya tentang predikat filter, lihat CREATE STATISTICS. Untuk informasi selengkapnya tentang kapan membuat statistik yang difilter, lihat bagian Kapan Harus Membuat Statistik di artikel ini.

Opsi statistik

Anda dapat mengonfigurasi opsi yang memengaruhi kapan dan bagaimana sistem membuat dan memperbarui statistik. Anda hanya dapat mengatur opsi ini di tingkat database.

opsi AUTO_CREATE_STATISTICS

Saat Anda mengaktifkan opsi statistik buat otomatis, AUTO_CREATE_STATISTICS, Pengoptimal Kueri membuat statistik pada kolom individual dalam predikat kueri, seperlunya, untuk meningkatkan perkiraan kardinalitas untuk rencana kueri. Statistik kolom tunggal ini dibuat pada kolom yang belum memiliki histogram dalam objek statistik yang ada. Opsi AUTO_CREATE_STATISTICS tidak menentukan apakah database membuat statistik untuk indeks. Opsi ini juga tidak menghasilkan statistik yang difilter. Ini berlaku secara ketat untuk statistik kolom tunggal untuk tabel lengkap.

Saat Pengoptimal Kueri membuat statistik sebagai hasil dari menggunakan AUTO_CREATE_STATISTICS opsi , nama statistik dimulai dengan _WA. Anda bisa menggunakan kueri berikut untuk menentukan apakah Pengoptimal Kueri membuat statistik untuk kolom predikat kueri.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc
        ON s.stats_id = sc.stats_id
        AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;

opsi AUTO_UPDATE_STATISTICS

Saat Anda mengaktifkan opsi statistik pembaruan otomatis, AUTO_UPDATE_STATISTICS, Pengoptimal Kueri menentukan kapan statistik mungkin kedaluarsa dan memperbaruinya saat kueri menggunakannya. Tindakan ini juga dikenal sebagai kompilasi ulang statistik. Statistik menjadi kedaluarsa setelah modifikasi dari operasi sisipkan, perbarui, hapus, atau gabungkan mengubah distribusi data dalam tabel atau tampilan terindeks. Pengoptimal Kueri menghitung jumlah modifikasi baris sejak pembaruan statistik terakhir dan membandingkan angka tersebut dengan ambang batas untuk menentukan apakah statistik mungkin kedaluarsa. Ambang didasarkan pada kardinalitas tabel, yang merupakan jumlah baris dalam tabel atau tampilan terindeks.

Menandai statistik sebagai kedaluarsa berdasarkan modifikasi baris terjadi bahkan ketika AUTO_UPDATE_STATISTICS opsinya adalah OFF. AUTO_UPDATE_STATISTICS Ketika opsinya adalah OFF, sistem tidak memperbarui statistik, bahkan ketika menandainya sebagai kedaluarsa. Rencana terus berlanjut menggunakan objek statistik yang kedaluwarsa. Pengaturan AUTO_UPDATE_STATISTICS ke OFF dapat menyebabkan rencana kueri suboptimal dan performa kueri yang terdegradasi. Atur opsi AUTO_UPDATE STATISTICS ke ON.

• Hingga SQL Server 2014 (12.x), Mesin Database menggunakan ambang kompilasi ulang berdasarkan jumlah baris dalam tabel atau tampilan terindeks pada statistik waktu dievaluasi. Ambang batas berbeda tergantung pada apakah tabel bersifat sementara atau permanen.

  Jenis tabel	Kardinalitas tabel (n)	Ambang kompilasi ulang (# modifikasi)
  Temporary	n< 6	6
  Temporary	6 <= n<= 500	500
  Permanent	N<= 500	500
  Sementara atau permanen	n> 500	500 + (0,20 * n)

  Misalnya, jika tabel Anda berisi 20.000 baris, perhitungannya adalah 500 + (0.2 * 20,000) = 4,500 dan statistik diperbarui setiap 4.500 modifikasi.

• Dimulai dengan SQL Server 2016 (13.x) dan dengan tingkat kompatibilitas database 130, Database Engine menggunakan ambang kompilasi ulang statistik dinamis yang menurun yang menyesuaikan sesuai dengan kardinalitas tabel pada saat statistik dievaluasi. Dengan perubahan ini, statistik pada tabel besar diperbarui lebih sering. Namun, jika database memiliki tingkat kompatibilitas di bawah 130, ambang batas SQL Server 2014 (12.x) berlaku.

  Jenis tabel	Kardinalitas tabel (n)	Ambang kompilasi ulang (# modifikasi)
  Temporary	n < 6	6
  Temporary	6 <= n <= 500	500
  Permanent	n <= 500	500
  Sementara atau permanen	n > 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  Misalnya, jika tabel Anda berisi 2 juta baris, perhitungannya adalah minimum 500 + (0.20 * 2,000,000) = 400,500 dan SQRT(1,000 * 2,000,000) = 44,721. Ini berarti statistik diperbarui setiap 44.721 modifikasi.

Important

Di SQL Server 2008 R2 (10.50.x) hingga SQL Server 2014 (12.x), atau di SQL Server 2016 (13.x) dan versi yang lebih baru dengan tingkat kompatibilitas database 120 dan versi yang lebih rendah, aktifkan bendera pelacakan 2371 sehingga SQL Server menggunakan ambang pembaruan statistik dinamis yang menurun.

Meskipun direkomendasikan untuk semua skenario, mengaktifkan bendera pelacakan 2371 bersifat opsional. Namun, Anda dapat menggunakan panduan berikut untuk mengaktifkan bendera pelacakan 2371 di lingkungan pra-SQL Server 2016 (13.x):

• Jika Anda menggunakan sistem SAP, aktifkan jejak ini. Untuk informasi selengkapnya, lihat blog ini tentang bendera pelacakan 2371.
• Jika Anda harus mengandalkan tugas terjadwal setiap malam untuk memperbarui statistik karena pembaruan otomatis saat ini tidak terpicu cukup sering, pertimbangkan untuk mengaktifkan trace flag 2371 untuk menyesuaikan ambang batas berdasarkan kardinalitas tabel.

Pengoptimal Kueri memeriksa statistik yang sudah kedaluwarsa sebelum mengkompilasi kueri dan sebelum menjalankan rencana kueri yang di-cache. Sebelum mengkompilasi kueri, Pengoptimal Kueri menggunakan kolom, tabel, dan tampilan terindeks dalam predikat kueri untuk menentukan statistik mana yang mungkin kedaluwarsa. Sebelum menjalankan rencana kueri yang di-cache, Mesin Database memverifikasi bahwa rencana kueri mereferensikan statistik up-to-date.

Opsi AUTO_UPDATE_STATISTICS berlaku untuk objek statistik yang dibuat untuk indeks, kolom tunggal dalam predikat kueri, dan statistik yang dibuat dengan pernyataan CREATE STATISTICS. Opsi ini juga berlaku untuk statistik yang difilter.

Anda dapat menggunakan sys.dm_db_stats_properties untuk melacak jumlah baris yang diubah dalam tabel secara akurat dan memutuskan apakah Anda ingin memperbarui statistik secara manual.

AUTO_UPDATE_STATISTICS selalu OFF untuk tabel yang dioptimalkan memori.

AUTO_UPDATE_STATISTICS_ASYNC

Opsi pembaruan statistik asinkron, AUTO_UPDATE_STATISTICS_ASYNC, menentukan apakah Pengoptimal Kueri menggunakan pembaruan statistik sinkron atau asinkron. Secara default, opsi pembaruan statistik asinkron adalah OFF, dan Pengoptimal Kueri memperbarui statistik secara sinkron. Opsi AUTO_UPDATE_STATISTICS_ASYNC ini berlaku untuk objek statistik yang dibuat untuk indeks, kolom tunggal dalam predikat kueri, dan statistik yang dibuat dengan CREATE STATISTICS pernyataan .

Note

Untuk mengatur opsi pembaruan statistik asinkron di SQL Server Management Studio, di halaman Opsi jendela Properti Database, atur Statistik Pembaruan Otomatis dan Statistik Pembaruan Otomatis Secara Asinkron ke True.

Pembaruan statistik dapat sinkron (default) atau asinkron.

• Dengan pembaruan statistik sinkron, kueri selalu dikompilasi dan dijalankan dengan statistik terbaru. Saat statistik sudah kedaluwarsa, Pengoptimal Kueri menunggu statistik yang diperbarui sebelum mengkompilasi dan menjalankan kueri.

• Dengan pembaruan statistik asinkron, kueri dikompilasi dengan statistik yang ada meskipun statistik yang ada sudah kedaluwarsa. Pengoptimal Kueri dapat memilih rencana kueri suboptimal jika statistik sudah kedaluwarsa saat kueri dikompilasi. Statistik biasanya diperbarui segera setelahnya. Kueri yang dikompilasi setelah pembaruan statistik dapat memanfaatkan statistik yang diperbarui.

Pertimbangkan untuk menggunakan statistik sinkron saat Anda melakukan operasi yang mengubah distribusi data, seperti memotong tabel atau melakukan pembaruan massal dari persentase besar baris. Jika Anda tidak memperbarui statistik secara manual setelah menyelesaikan operasi, penggunaan statistik sinkron memastikan statistik tetap mutakhir sebelum kueri memerlukannya.

Pertimbangkan untuk menggunakan statistik asinkron untuk mencapai waktu respons kueri yang lebih dapat diprediksi untuk skenario berikut:

• Aplikasi Anda sering menjalankan kueri yang sama, kueri serupa, atau rencana kueri cache serupa. Waktu respons kueri Anda mungkin lebih dapat diprediksi dengan pembaruan statistik asinkron daripada dengan pembaruan statistik sinkron karena Pengoptimal Kueri dapat menjalankan kueri masuk tanpa menunggu statistik terbaru. Ini mencegah beberapa kueri tertunda, sementara kueri lain tidak.

• Aplikasi Anda mengalami timeout pada permintaan klien akibat satu atau beberapa kueri yang menunggu statistik yang telah diperbarui. Dalam beberapa kasus, menunggu statistik sinkron dapat menyebabkan aplikasi dengan batas waktu agresif gagal.

Note

Statistik pada tabel sementara lokal selalu diperbarui secara sinkron terlepas dari opsinya AUTO_UPDATE_STATISTICS_ASYNC . Statistik pada tabel sementara global diperbarui secara sinkron atau asinkron sesuai dengan opsi yang AUTO_UPDATE_STATISTICS_ASYNC ditetapkan untuk database pengguna.

Pembaruan statistik asinkron dilakukan oleh permintaan latar belakang. Ketika permintaan siap untuk menulis statistik yang diperbarui ke database, permintaan mencoba untuk memperoleh kunci modifikasi skema pada objek metadata statistik. Jika sesi yang berbeda sudah memegang kunci pada objek yang sama, pembaruan statistik asinkron diblokir hingga kunci modifikasi skema dapat diperoleh. Demikian pula, sesi yang perlu memperoleh kunci stabilitas skema (Sch-S) pada objek metadata statistik untuk mengompilasi kueri dapat diblokir oleh sesi latar belakang pembaruan statistik asinkron, yang sudah menahan atau menunggu untuk memperoleh kunci modifikasi skema. Oleh karena itu, untuk beban kerja dengan kompilasi kueri yang sangat sering dan pembaruan statistik yang juga sering, penggunaan statistik asinkron dapat meningkatkan kemungkinan terjadinya masalah konkurensi akibat pemblokiran kunci.

Di Azure SQL Database, Azure SQL Managed Instance, dan dimulai pada SQL Server 2022 (16.x), Anda dapat menghindari potensi masalah konkurensi menggunakan pembaruan statistik asinkron jika Anda mengaktifkan ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYkonfigurasi cakupan database. Dengan konfigurasi ini diaktifkan, permintaan di latar belakang menunggu untuk memperoleh kunci modifikasi skema (Sch-M) dan menyimpan statistik yang diperbarui pada antrean berprioritas rendah terpisah, memungkinkan permintaan lain untuk terus mengkompilasi kueri dengan statistik yang ada. Setelah tidak ada sesi lain yang memegang kunci pada objek metadata statistik, permintaan latar belakang memperoleh kunci modifikasi skemanya dan memperbarui statistik. Jika, meskipun kecil kemungkinannya, permintaan latar belakang tidak dapat mendapatkan kunci dalam jangka waktu tunggu beberapa menit, pembaruan statistik asinkron dibatalkan, dan statistik tidak diperbarui hingga pembaruan statistik otomatis lain dipicu atau hingga statistik diperbarui secara manual.

Note

ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY Opsi konfigurasi terlingkup database tersedia di Azure SQL Database, Azure SQL Managed Instance, dan di SQL Server dimulai dengan SQL Server 2022 (16.x).

opsi AUTO_DROP

Berlaku untuk: Azure SQL Database, Azure SQL Managed Instance, dan dimulai dengan SQL Server 2022 (16.x)

Dalam SQL Server sebelum SQL Server 2022 (16.x), jika Anda membuat statistik secara manual atau menggunakan alat pihak ketiga pada database pengguna, objek statistik tersebut dapat memblokir atau mengganggu perubahan skema.

Dimulai dengan SQL Server 2022 (16.x), opsi penurunan otomatis diaktifkan secara default pada semua database baru dan yang dimigrasikan. Jika Anda mengaktifkan properti AUTO_DROP, database membuat objek statistik dalam mode sedemikian rupa sehingga perubahan skema berikutnya tidak diblokir oleh objek statistik; sebaliknya, statistik tersebut akan dihapus bila diperlukan. Dengan cara ini, statistik yang dibuat secara manual dengan penghapusan otomatis yang diaktifkan berperilaku seperti statistik yang dibuat secara otomatis.

Dalam Azure SQL Database, Azure SQL Managed Instance, dan SQL Server 2022 (16.x) dan versi yang lebih baru, statistik yang dibuat secara otomatis selalu bereaksi seolah-olah AUTO_DROP diaktifkan.

Note

Upaya untuk menetapkan atau menghapus setelan properti hapus otomatis pada statistik yang dibuat otomatis dapat menimbulkan kesalahan. Statistik yang dibuat secara otomatis selalu menggunakan penurunan otomatis. Beberapa cadangan, ketika dipulihkan, dapat mengatur properti ini dengan tidak benar sampai lain kali objek statistik diperbarui (secara manual atau otomatis). Namun, statistik yang dibuat secara otomatis selalu berulah seperti statistik penurunan otomatis. Saat memulihkan database ke SQL Server 2022 (16.x) dari versi sebelumnya, disarankan untuk menjalankan sp_updatestats pada database, mengatur metadata yang tepat untuk fitur penurunan otomatis statistik.

Misalnya, untuk membuat objek statistik secara manual pada dbo.DatabaseLog tabel:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Misalnya, untuk memperbarui pengaturan penurunan otomatis objek statistik pada dbo.DatabaseLog tabel:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Untuk mengevaluasi pengaturan hapus otomatis pada statistik yang ada, gunakan kolom auto_drop di sys.stats:

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Untuk informasi selengkapnya, lihat AUTO_DROP.

INCREMENTAL

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru.

Saat Anda mengatur opsi INCREMENTAL dari CREATE STATISTICS ke ON, Anda membuat statistik untuk setiap partisi. Saat Anda mengaturnya ke OFF, database menghilangkan pohon statistik dan mengolah ulang statistik. Defaultnya adalah OFF. Pengaturan ini mengesampingkan properti INCREMENTAL tingkat database.

• Untuk informasi selengkapnya tentang membuat statistik inkremental, lihat CREATE STATISTICS.
• Untuk informasi selengkapnya tentang membuat statistik per partisi secara otomatis, lihat Properti Database (Halaman Opsi) dan ALTER DATABASE SET opsi.

Saat Anda menambahkan partisi baru ke tabel besar, Anda harus memperbarui statistik untuk menyertakan partisi baru. Namun, waktu yang diperlukan untuk memindai seluruh tabel (FULLSCAN atau SAMPLE opsi) bisa panjang. Selain itu, memindai seluruh tabel tidak diperlukan karena hanya statistik pada partisi baru yang mungkin diperlukan. Opsi inkremental membuat dan menyimpan statistik berdasarkan per partisi, dan ketika diperbarui, hanya merefresh statistik pada partisi yang membutuhkan statistik baru.

Jika statistik per partisi tidak didukung, database mengabaikan opsi dan menghasilkan peringatan. Statistik inkremental tidak didukung untuk jenis statistik berikut:

• Statistik dibuat dengan indeks yang tidak selaras dengan partisi pada tabel dasar.
• Statistik yang dibuat pada database sekunder yang dapat dibaca AlwaysOn.
• Statistik yang dibuat pada database baca-saja.
• Statistik dibuat pada indeks yang difilter.
• Statistik dibuat pada tampilan.
• Statistik dibuat pada tabel internal.
• Statistik dibuat dengan indeks spasial atau indeks XML.

Kapan harus membuat statistik

Pengoptimal Kueri sudah membuat statistik dengan cara berikut:

1. Saat Anda membuat indeks pada tabel atau tampilan, Pengoptimal Kueri membuat statistik untuk indeks. Statistik ini dibuat pada kolom kunci indeks. Jika indeks adalah indeks yang difilter, Pengoptimal Kueri membuat statistik yang difilter pada subset baris yang sama yang ditentukan untuk indeks yang difilter. Untuk informasi selengkapnya tentang indeks yang difilter, lihat Membuat indeks yang difilter dan CREATE INDEX.

   Note

   Pada SQL Server 2014 (12.x) dan versi yang lebih baru, database tidak membuat statistik dengan memindai semua baris dalam tabel saat Anda membuat atau membangun kembali indeks yang dipartisi. Sebagai gantinya, Pengoptimal Kueri menggunakan algoritma pengambilan sampel default untuk menghasilkan statistik. Setelah meningkatkan database dengan indeks yang dipartisi, Anda mungkin melihat perbedaan dalam data histogram untuk indeks ini. Perubahan perilaku ini mungkin tidak memengaruhi performa kueri. Untuk mendapatkan statistik pada indeks yang dipartisi dengan memindai semua baris dalam tabel, gunakan CREATE STATISTICS atau UPDATE STATISTICS dengan FULLSCAN klausul .

2. Pengoptimal Kueri membuat statistik untuk kolom tunggal dalam predikat kueri saat AUTO_CREATE_STATISTICS aktif.

Untuk sebagian besar kueri, kedua metode untuk membuat statistik ini memastikan rencana kueri berkualitas tinggi. Dalam beberapa kasus, Anda dapat meningkatkan rencana kueri dengan membuat statistik tambahan dengan menggunakan pernyataan .CREATE STATISTICS Statistik tambahan ini dapat menangkap korelasi statistik yang tidak diperhitungkan oleh Pengoptimal Kueri saat membuat statistik untuk indeks atau kolom tunggal. Aplikasi Anda mungkin memiliki korelasi statistik tambahan dalam data tabel yang, jika dihitung ke dalam objek statistik, dapat memungkinkan Pengoptimal Kueri untuk meningkatkan rencana kueri. Misalnya, statistik yang difilter pada subset baris data atau statistik multikolom pada kolom predikat kueri mungkin meningkatkan rencana kueri.

Saat Anda membuat statistik menggunakan pernyataan CREATE STATISTICS, biarkan opsi AUTO_CREATE_STATISTICSON agar Pengoptimal Kueri terus membuat statistik kolom tunggal secara berkala untuk kolom predikat kueri. Untuk informasi selengkapnya tentang predikat kueri, lihat kondisi pencarian .

Pertimbangkan untuk membuat statistik dengan menggunakan CREATE STATISTICS pernyataan ketika salah satu kondisi berikut berlaku:

• Konsultan Penyetelan Mesin Database menyarankan pembuatan statistik.
• Predikat kueri berisi beberapa kolom berkorelasi yang belum ada kuncinya dalam indeks yang sama.
• Kueri memilih dari subkumpulan data.
• Kueri tidak memiliki statistik.

Note

Untuk informasi spesifik tentang tabel dan statistik yang terkait dengan In-Memory OLTP, lihat Statistik untuk Tabel yang Dioptimalkan untuk Memori.

Predikat kueri berisi beberapa kolom berkorelasi

Saat predikat kueri berisi beberapa kolom yang memiliki hubungan dan dependensi lintas kolom, statistik pada beberapa kolom mungkin meningkatkan rencana kueri. Statistik pada beberapa kolom berisi statistik korelasi lintas kolom, yang disebut kepadatan , yang tidak tersedia dalam statistik kolom tunggal. Kepadatan dapat meningkatkan perkiraan kardinalitas saat hasil kueri bergantung pada hubungan data di antara beberapa kolom.

Jika kolom sudah berada dalam indeks yang sama, objek statistik multikolom sudah ada dan Anda tidak perlu membuatnya secara manual. Jika kolom belum berada dalam indeks yang sama, Anda dapat membuat statistik multikolom dengan membuat indeks pada kolom atau dengan menggunakan CREATE STATISTICS pernyataan . Ini membutuhkan lebih banyak sumber daya sistem untuk mempertahankan indeks daripada objek statistik. Jika aplikasi tidak memerlukan indeks multikolom, Anda dapat melakukan ekonomis pada sumber daya sistem dengan membuat objek statistik tanpa membuat indeks.

Saat Anda membuat statistik multikolom, urutan kolom dalam definisi objek statistik memengaruhi efektivitas kepadatan untuk membuat perkiraan kardinalitas. Objek statistik menyimpan kepadatan untuk setiap awalan kolom kunci dalam definisi objek statistik. Untuk informasi selengkapnya tentang kepadatan, lihat bagian Kepadatan di artikel ini.

Untuk membuat kepadatan yang berguna untuk perkiraan kardinalitas, kolom dalam predikat kueri harus cocok dengan salah satu awalan kolom dalam definisi objek statistik. Misalnya, contoh berikut membuat objek statistik multikolom pada kolom LastName, , MiddleNamedan FirstName.

USE AdventureWorks2022;
GO

IF EXISTS (SELECT name
           FROM sys.stats
           WHERE name = 'LastFirst'
                 AND object_ID = OBJECT_ID('Person.Person'))
    DROP STATISTICS Person.Person.LastFirst;
GO

CREATE STATISTICS LastFirst
    ON Person.Person(LastName, MiddleName, FirstName);
GO

Dalam contoh ini, objek LastFirst statistik memiliki kepadatan untuk awalan kolom berikut: (LastName), , (LastName, MiddleName)dan (LastName, MiddleName, FirstName). Kepadatan tidak tersedia untuk (LastName, FirstName). Jika kueri menggunakan LastName dan FirstName tanpa menggunakan MiddleName, kepadatan tidak tersedia untuk perkiraan kardinalitas.

Kueri memilih dari subkumpulan data

Saat Pengoptimal Kueri membuat statistik untuk satu kolom dan indeks, pengoptimal kueri membuat statistik untuk nilai di semua baris. Saat kueri memilih dari subset baris, dan subset baris tersebut memiliki distribusi data yang unik, statistik yang difilter dapat meningkatkan rencana kueri. Anda dapat membuat statistik yang difilter dengan menggunakan CREATE STATISTICS pernyataan dengan klausa WHERE untuk menentukan ekspresi predikat filter.

Misalnya, menggunakan AdventureWorks2025, setiap produk dalam Production.Product tabel termasuk dalam salah satu dari empat kategori dalam Production.ProductCategory tabel: Bikes, , ComponentsClothing, dan Accessories. Masing-masing kategori memiliki distribusi data yang berbeda untuk berat: bobot sepeda berkisar antara 13,77 hingga 30,0, bobot komponen berkisar antara 2,12 hingga 1050,00 dengan beberapa NULL nilai, bobot pakaian semuanya NULL, dan bobot aksesori juga NULL.

Menggunakan Bikes sebagai contoh, statistik yang difilter pada semua bobot sepeda memberikan statistik yang lebih akurat ke Pengoptimal Kueri dan dapat meningkatkan kualitas rencana kueri dibandingkan dengan statistik tabel penuh atau statistik yang tidak ada pada kolom Bobot. Kolom bobot sepeda cocok untuk statistik terfilter, tetapi belum tentu cocok untuk indeks terfilter jika jumlah pencarian berdasarkan bobot relatif sedikit. Perolehan performa untuk pencarian yang disediakan indeks yang difilter mungkin tidak melebihi biaya pemeliharaan dan penyimpanan tambahan untuk menambahkan indeks yang difilter ke database.

Pernyataan berikut membuat statistik berdasarkan filter BikeWeights pada semua subkategori untuk Bikes. Ekspresi predikat yang difilter mendefinisikan sepeda dengan menghitung semua subkategori sepeda dengan perbandingan Production.ProductSubcategoryID IN (1,2,3). Predikat tidak dapat menggunakan nama kategori Bikes karena disimpan dalam tabel Production.ProductCategory, dan semua kolom dalam ekspresi filter harus berada dalam tabel yang sama.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

Pengoptimal Kueri dapat menggunakan BikeWeights statistik yang difilter untuk meningkatkan rencana kueri untuk kueri berikut yang memilih semua sepeda yang memiliki berat lebih dari 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER JOIN Production.ProductSubcategory AS S
         ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
      AND P.Weight > 25
ORDER BY P.Weight;
GO

Kueri mengidentifikasi statistik yang hilang

Jika kesalahan atau peristiwa lain mencegah Pengoptimal Kueri membuat statistik, Pengoptimal Kueri membuat rencana kueri tanpa menggunakan statistik. Pengoptimal Kueri menandai statistik sebagai hilang dan mencoba meregenerasi statistik saat kueri dijalankan berikutnya.

Statistik yang hilang ditunjukkan sebagai peringatan (nama tabel dalam teks merah) ketika rencana eksekusi kueri ditampilkan secara grafis menggunakan SQL Server Management Studio. Selain itu, memantau kelas peristiwa Missing Column Statistics dengan menggunakan SQL Server Profiler menunjukkan kapan statistik tidak ada. Untuk informasi selengkapnya, lihat Kategori Peristiwa Kesalahan dan Peringatan (Mesin Database).

Jika statistik hilang, lakukan langkah-langkah berikut:

• Verifikasi bahwa AUTO_CREATE_STATISTICS dan AUTO_UPDATE_STATISTICS AKTIF.
• Verifikasi bahwa database tidak bersifat baca-saja. Jika database bersifat baca-saja, objek statistik baru tidak dapat disimpan.
• Buat statistik yang hilang dengan menggunakan pernyataan CREATE STATISTICS.

Statistik sementara

Saat statistik pada database baca-saja atau rekam jepret baca-saja hilang atau basi, Mesin Database membuat dan mempertahankan statistik sementara di tempdb. Ketika Mesin Database membuat statistik sementara, nama statistik ditambahkan dengan akhiran _readonly_database_statistic untuk membedakan statistik sementara dari statistik permanen. Akhiran _readonly_database_statistic dicadangkan untuk statistik yang dihasilkan oleh Mesin Basis Data. Skrip untuk statistik sementara dapat dibuat dan dijalankan pada database baca-tulis. Saat diskrip, Management Studio mengubah akhiran nama statistik dari _readonly_database_statistic menjadi _readonly_database_statistic_scripted.

Hanya Mesin Database yang dapat membuat dan memperbarui statistik sementara. Namun, Anda dapat menghapus statistik sementara dan memantau properti statistik menggunakan alat yang sama dengan yang Anda gunakan untuk statistik permanen:

• Hapus statistik sementara menggunakan DROP STATISTICS pernyataan .
• Pantau statistik menggunakan tampilan katalog sys.stats dan sys.stats_columns . Tampilan sys.stats katalog sistem menyertakan is_temporary kolom , untuk menunjukkan statistik mana yang permanen dan mana yang bersifat sementara.

Karena statistik sementara disimpan dalam tempdb, hidupkan ulang Mesin Database menghapus semua statistik sementara.

Sama seperti untuk semua statistik, membuat dan memperbarui statistik sementara memerlukan penguncian modifikasi skema (Sch-M) pada objek. Kunci ini mungkin memblokir kueri dan proses lain, termasuk proses pengulangan sistem pada replika sekunder yang menerapkan transaksi dari replika utama. Jika pemblokiran ini memengaruhi beban kerja kueri atau penyebaran data, Anda dapat menonaktifkan pembuatan otomatis dan pembaruan statistik sementara menggunakan READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATEREADABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE dan masing-masing.

Kapan harus memperbarui statistik

Pengoptimal Kueri menentukan kapan statistik mungkin kedaluarsa lalu memperbaruinya saat diperlukan untuk rencana kueri. Dalam beberapa kasus, Anda dapat meningkatkan rencana kueri dan oleh karena itu meningkatkan performa kueri dengan memperbarui statistik lebih sering daripada saat AUTO_UPDATE_STATISTICS adalah ON. Anda dapat memperbarui statistik dengan menggunakan UPDATE STATISTICS pernyataan atau prosedur sp_updatestatstersimpan .

Memperbarui statistik akan memastikan bahwa kueri dikompilasi dengan statistik terbaru. Memperbarui statistik melalui proses apa pun dapat menyebabkan rencana kueri kompilasi ulang secara otomatis. Jangan memperbarui statistik secara manual terlalu sering karena ada tradeoff performa antara meningkatkan rencana kueri dan waktu yang diperlukan untuk mengkombinasikan ulang kueri. Kompromi spesifik bergantung pada aplikasi Anda.

Saat Anda memperbarui statistik dengan menggunakan UPDATE STATISTICS atau sp_updatestats, tetap AUTO_UPDATE_STATISTICS atur ke ON sehingga Pengoptimal Kueri secara rutin memperbarui statistik.

• Untuk informasi selengkapnya tentang cara memperbarui statistik pada kolom, indeks, tabel, atau tampilan terindeks, lihat UPDATE STATISTICS.

• Untuk informasi tentang cara memperbarui statistik untuk semua tabel yang ditentukan pengguna dan internal dalam database, lihat prosedur tersimpan sp_updatestats.

• Untuk informasi selengkapnya tentang ambang batas untuk pembaruan statistik otomatis, lihat Opsi AUTO_UPDATE_STATISTICS.

Ketika Anda mengatur AUTO_UPDATE_STATISTICS ke OFF, kompilasi ulang paket masih dapat terjadi karena berbagai alasan lain, tetapi tidak terjadi secara otomatis karena pembaruan statistik yang kedaluarsa. Ketika Anda mengatur AUTO_UPDATE_STATISTICS ke OFF, pembaruan statistik hanya terjadi melalui proses terjadwal manual lainnya, seperti rencana pemeliharaan. Oleh karena itu, pengaturan AUTO_UPDATE_STATISTICS ke OFF dapat menyebabkan rencana kueri suboptimal dan performa kueri yang terdegradasi.

Mendeteksi statistik yang kedaluarsa

Untuk menentukan kapan statistik terakhir diperbarui, gunakan fungsi sys.dm_db_stats_properties atau STATS_DATE .

Pertimbangkan untuk memperbarui statistik untuk kondisi berikut:

• Waktu eksekusi kueri lambat.
• Operasi penyisipan dilakukan pada kolom kunci berurutan menaik atau menurun.
• Setelah operasi pemeliharaan.

Untuk contoh memperbarui statistik secara manual, lihat UPDATE STATISTICS.

Waktu eksekusi kueri lambat

Jika waktu respons kueri lambat atau tidak dapat diprediksi, pastikan kueri memiliki statistik terbaru sebelum melakukan langkah-langkah pemecahan masalah tambahan.

Operasi penyisipan dilakukan pada kolom kunci urutan menaik atau menurun

Statistik tentang kolom kunci naik atau menurun, seperti IDENTITY atau kolom tanda waktu nyata, mungkin memerlukan pembaruan statistik yang lebih sering daripada yang dilakukan Pengoptimal Kueri. Sisipkan operasi menambahkan nilai baru ke kolom naik atau turun. Jumlah baris yang ditambahkan mungkin terlalu kecil untuk memicu pembaruan statistik. Jika statistik tidak mutakhir dan kueri memilih dari baris yang paling baru ditambahkan, statistik saat ini tidak memiliki perkiraan kardinalitas untuk nilai-nilai baru ini. Kondisi ini dapat mengakibatkan perkiraan kardinalitas yang tidak akurat dan performa kueri yang lambat.

Misalnya, kueri yang memilih dari tanggal pesanan penjualan terbaru memiliki perkiraan kardinalitas yang tidak akurat jika statistik tidak diperbarui untuk menyertakan perkiraan kardinalitas untuk tanggal pesanan penjualan terbaru.

Setelah operasi pemeliharaan

Pertimbangkan untuk memperbarui statistik setelah melakukan prosedur pemeliharaan yang mengubah distribusi data, seperti memotong tabel atau melakukan penyisipan massal dari persentase besar baris. Memperbarui statistik secara proaktif dapat menghindari keterlambatan pemrosesan kueri di masa mendatang saat kueri menunggu pembaruan statistik otomatis.

Operasi seperti membangun kembali, mendefragmentasi, atau mengatur ulang indeks tidak mengubah distribusi data. Oleh karena itu, Anda tidak perlu memperbarui statistik setelah melakukan ALTER INDEX operasi REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, atau ALTER INDEX REORGANIZE . Pengoptimal Kueri memperbarui statistik saat Anda membangun ulang indeks pada tabel atau tampilan dengan ALTER INDEX REBUILD atau DBCC DBREINDEX, namun pembaruan statistik ini adalah produk byproduksi dari pembuatan ulang indeks. Pengoptimal Kueri tidak memperbarui statistik setelah operasi DBCC INDEXDEFRAG atau ALTER INDEX REORGANIZE.

Tip

Dimulai dengan SQL Server 2016 (13.x) SP1 CU4, gunakan opsi CREATE STATISTICS PERSIST_SAMPLE_PERCENT dari atau UPDATE STATISTICS untuk mengatur dan mempertahankan persentase pengambilan sampel tertentu untuk pembaruan statistik berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel.

Manajemen indeks dan statistik otomatis

Gunakan solusi cerdas seperti Defrag Indeks Adaptif untuk mengelola defragmentasi indeks dan pembaruan statistik secara otomatis untuk satu atau beberapa database. Prosedur ini secara otomatis memilih apakah akan membangun kembali atau mengatur ulang indeks sesuai dengan tingkat fragmentasinya, di antara parameter lainnya, dan memperbarui statistik dengan ambang batas linier.

Menentukan statistik mana yang digunakan Pengoptimal Kueri

Anda dapat menemukan objek statistik yang digunakan Pengoptimal Kueri saat mengkompilasi kueri dengan memeriksa perkiraan atau rencana eksekusi aktual. Saat Anda memeriksa rencana eksekusi, elemen OptimizerStatsUage berisi elemen StatisticsInfo, yang memuat informasi tentang objek statistik yang dimuat oleh Pengoptimal Kueri selama kompilasi. Elemen StatisticsInfo berisi nama objek statistik, database, skema, dan tabel miliknya, jumlah modifikasi pada waktu kompilasi, persentase pengambilan sampel, dan kapan terakhir diperbarui.

Gunakan salah satu teknik berikut untuk memeriksa rencana eksekusi:

• Di SQL Server Management Studio, pilih Sertakan Rencana Eksekusi Aktual (Ctrl+M) sebelum menjalankan kueri. Di tab Rencana eksekusi yang muncul dengan hasilnya, Anda dapat:
  • Klik kanan di dalam paket grafis dan pilih Tampilkan XML Rencana Eksekusi. Cari OptimizerStatsUsage elemen dan setiap elemen turunan StatisticsInfo .
  • Pilih operator akhir (paling kiri). (Dalam kasus SELECT kueri, operator ini adalah simpul SELECT .) Di jendela Properti , perluas node OptimizerStatsUsage , dan lihat informasi tentang objek statistik yang digunakan dalam kueri.
• Atur SETSET STATISTICS XML ON sebelum kueri. Pilih hyperlink yang muncul dengan hasil untuk menampilkan XML rencana eksekusi.
• Kueri sys.dm_exec_query_plan atau sys.dm_exec_query_statistics_xml untuk kueri terbaru.
• Baca rencana yang sebelumnya ditangkap dari Query Store menggunakan sys.query_store_plan.

Setiap StatisticsInfo elemen terlihat seperti fragmen XML berikut dari kueri pada AdventureWorks2022 database sampel:

<StatisticsInfo 
    Database="[AdventureWorks2022]" 
    Schema="[Sales]" 
    Table="[SalesOrderDetail]" 
    Statistics="[IX_SalesOrderDetail_ProductID]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2025-09-07T15:32:16.89" />

Attribute	Meaning
Database, Schema, Table	Objek tempat statistik berada.
Statistics	Nama objek statistik dalam database. Gunakan nama ini dengan DBCC SHOW_STATISTICS atau sys.stats untuk memeriksa histogram dan vektor kepadatan.
ModificationCount	Jumlah perubahan data sejak statistik terakhir diperbarui, pada saat rencana disusun. Nilai besar relatif terhadap ukuran tabel menunjukkan bahwa statistik kedaluarsa selama kompilasi.
SamplingPercent	Persentase baris yang diambil sampelnya untuk membangun statistik. Nilai yang lebih rendah dapat menghasilkan histogram yang kurang akurat untuk data condong.
LastUpdate	Tanda waktu pembaruan statistik terakhir. Jika opsi AUTO_UPDATE_STATISTICS diaktifkan pada database, database secara otomatis memperbarui statistik saat diperlukan.

Note

StatisticsInfo mencerminkan statistik yang tersedia dan dipertimbangkan selama kompilasi rencana. StatisticsInfo Jika entri hilang untuk kolom tempat kueri Anda difilter, pengoptimal kueri tidak mengidentifikasi statistik yang relevan, yang merupakan sumber potensial performa buruk.

Untuk memeriksa jumlah kesegaran dan modifikasi saat ini untuk objek statistik, gunakan sys.dm_db_stats_properties. Misalnya, kueri berikut menyediakan metrik saat ini untuk objek statistik bernama IX_SalesOrderDetail_ProductID pada tabel Sales.SalesOrderDetail:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND s.name = N'IX_SalesOrderDetail_ProductID';

Untuk menentukan apakah opsi pembuatan dan pembaruan statistik otomatis database saat ini diaktifkan, gunakan:

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE [name] = DB_NAME();

Kueri yang menggunakan statistik secara efektif

Implementasi kueri tertentu, seperti variabel lokal dan ekspresi kompleks dalam predikat kueri, dapat menyebabkan rencana kueri suboptimal. Untuk menghindari masalah ini, ikuti panduan desain kueri untuk menggunakan statistik secara efektif. Untuk informasi selengkapnya tentang predikat kueri, lihat kondisi pencarian .

Anda dapat meningkatkan rencana kueri dengan menerapkan panduan desain kueri yang menggunakan statistik secara efektif untuk meningkatkan perkiraan kardinalitas untuk ekspresi, variabel, dan fungsi yang digunakan dalam predikat kueri. Ketika Pengoptimal Kueri tidak mengetahui nilai ekspresi, variabel, atau fungsi, pengoptimal kueri tidak tahu nilai mana yang akan dicari dalam histogram dan oleh karena itu tidak dapat mengambil perkiraan kardinalitas terbaik dari histogram. Sebaliknya, Pengoptimal Kueri mendasarkan perkiraan kardinalitas pada jumlah rata-rata baris per nilai yang berbeda untuk semua baris sampel dalam histogram. Situasi ini menyebabkan perkiraan kardinalitas suboptimal dan dapat merusak performa kueri. Untuk informasi selengkapnya tentang histogram, lihat bagian histogram dalam artikel ini atau sys.dm_db_stats_histogram.

Panduan berikut menjelaskan cara menulis kueri untuk meningkatkan rencana kueri dengan meningkatkan perkiraan kardinalitas.

Meningkatkan perkiraan kardinalitas untuk ekspresi

Untuk meningkatkan perkiraan kardinalitas untuk ekspresi, ikuti panduan berikut:

• Jika memungkinkan, sederhanakan ekspresi yang berisi konstanta. Pengoptimal Kueri tidak mengevaluasi semua fungsi dan ekspresi yang berisi konstanta sebelum menentukan perkiraan kardinalitas. Misalnya, sederhanakan ekspresi ABS(-100) ke 100.
• Jika ekspresi menggunakan beberapa variabel, pertimbangkan untuk membuat kolom komputasi untuk ekspresi, lalu buat statistik atau indeks pada kolom komputasi. Misalnya, predikat kueri mungkin memiliki perkiraan kardinalitas yang lebih baik jika Anda membuat kolom komputasi WHERE PRICE + Tax > 100 untuk ekspresi Price + Tax.

Meningkatkan perkiraan kardinalitas untuk variabel dan fungsi

Untuk meningkatkan perkiraan kardinalitas untuk variabel dan fungsi, ikuti panduan berikut:

• Jika predikat kueri menggunakan variabel lokal, pertimbangkan untuk menulis ulang kueri untuk menggunakan parameter alih-alih variabel lokal. Pengoptimal Kueri tidak mengetahui nilai variabel lokal saat membuat rencana eksekusi kueri. Saat kueri menggunakan parameter, Pengoptimal Kueri menggunakan perkiraan kardinalitas untuk nilai parameter aktual pertama yang diterima prosedur tersimpan.

• Pertimbangkan untuk menggunakan tabel biasa atau tabel sementara untuk menampung hasil dari fungsi bernilai tabel dengan banyak pernyataan. Pengoptimal Kueri tidak membuat statistik untuk fungsi bernilai tabel multistatement. Dengan menggunakan pendekatan ini, Pengoptimal Kueri dapat membuat statistik pada kolom tabel dan menggunakannya untuk membuat rencana kueri yang lebih baik.

• Pertimbangkan untuk menggunakan tabel standar atau tabel sementara sebagai pengganti variabel tabel. Pengoptimal Kueri tidak membuat statistik untuk variabel tabel. Dengan menggunakan pendekatan ini, Pengoptimal Kueri dapat membuat statistik pada kolom tabel dan menggunakannya untuk membuat rencana kueri yang lebih baik. Ada kompromi dalam menentukan apakah akan menggunakan tabel sementara atau variabel tabel. Variabel tabel yang digunakan dalam prosedur tersimpan menyebabkan lebih sedikit kompilasi ulang prosedur tersimpan daripada tabel sementara. Bergantung pada aplikasi, menggunakan tabel sementara alih-alih variabel tabel mungkin tidak meningkatkan performa.

• Jika prosedur tersimpan berisi kueri yang menggunakan parameter passed-in, hindari mengubah nilai parameter dalam prosedur tersimpan sebelum menggunakannya dalam kueri. Perkiraan kardinalitas untuk kueri didasarkan pada nilai parameter yang diteruskan dan bukan nilai yang diperbarui. Untuk menghindari perubahan nilai parameter, Anda dapat menulis ulang kueri untuk menggunakan dua prosedur tersimpan.

  Misalnya, prosedur Sales.GetRecentSales tersimpan berikut mengubah nilai parameter @date ketika @date adalah NULL.

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

  Jika panggilan pertama ke prosedur tersimpan Sales.GetRecentSales meneruskan NULL untuk parameter @date, Pengoptimal Kueri mengkompilasi prosedur tersimpan dengan perkiraan kardinalitas untuk @date = NULL meskipun predikat kueri tidak dipanggil dengan @date = NULL. Perkiraan kardinalitas ini mungkin berbeda secara signifikan dari jumlah baris dalam hasil kueri aktual. Akibatnya, Pengoptimal Kueri mungkin memilih rencana kueri suboptimal. Untuk membantu menghindari masalah ini, Anda dapat menulis ulang prosedur tersimpan menjadi dua prosedur sebagai berikut:

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNullRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      EXECUTE Sales.GetNonNullRecentSales @date;
  END
  GO
  
  IF OBJECT_ID('Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNonNullRecentSales
  @date DATETIME
  AS
  BEGIN
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

Meningkatkan perkiraan kardinalitas dengan petunjuk kueri

Untuk meningkatkan perkiraan kardinalitas untuk variabel lokal, gunakan hint kueri OPTIMIZE FOR <value> atau OPTIMIZE FOR UNKNOWN dengan RECOMPILE. Untuk informasi selengkapnya, lihat petunjuk Kueri .

Untuk beberapa aplikasi, kompilasi ulang kueri setiap kali dijalankan mungkin memakan waktu terlalu lama. Petunjuk kueri OPTIMIZE FOR dapat membantu meskipun Anda tidak menggunakan opsi RECOMPILE. Misalnya, Anda dapat menambahkan OPTIMIZE FOR opsi ke prosedur Sales.GetRecentSales tersimpan untuk menentukan tanggal tertentu. Contoh berikut menambahkan OPTIMIZE FOR opsi ke Sales.GetRecentSales prosedur.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Meningkatkan perkiraan kardinalitas dengan panduan rencana

Untuk beberapa aplikasi, panduan desain kueri mungkin tidak berlaku karena Anda tidak dapat mengubah kueri atau petunjuk kueri RECOMPILE dapat menyebabkan terlalu banyak kompilasi ulang. Gunakan panduan rencana untuk menentukan petunjuk lain, seperti USE PLAN, untuk mengontrol perilaku kueri saat menyelidiki perubahan aplikasi dengan vendor aplikasi. Untuk informasi selengkapnya tentang panduan rencana, lihat Panduan Rencana.

Di Azure SQL Database, pertimbangkan penggunaan hint Query Store untuk memaksakan rencana eksekusi, bukan panduan rencana. Untuk informasi selengkapnya, lihat Petunjuk Query Store.

Konten terkait

• Statistik untuk Tabel yang Dioptimalkan Memori
• CREATE STATISTICS (Transact-SQL)
• UPDATE UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (T-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• ALTER DATABASE SET options (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• Membuat indeks yang difilter
• STATS_DATE (T-SQL)
• sys.dm_db_stats_properties (T-SQL)
• sys.dm_db_stats_histogram (T-SQL)
• sys.stats
• sys.stats_columns (T-SQL)
• Indeks Adaptif Defrag