Statistik

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

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 mengubah desain kueri untuk hasil terbaik. Artikel ini membahas konsep statistik dan memberikan panduan untuk menggunakan statistik pengoptimalan kueri secara efektif.

Komponen dan konsep

Statistik

Statistik untuk pengoptimalan kueri adalah objek besar biner (BLOB) yang berisi informasi statistik tentang distribusi nilai dalam 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.

Catatan

Histogram di SQL Server hanya dibuat 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 histogram menyertakan rentang nilai kolom diikuti dengan nilai kolom terikat 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 telah habis, atau ketika 200 nilai telah 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 (ini dimungkinkan karena nilai input diurutkan). Jika rentang baru dibuat, maka satu pasang rentang tetangga yang ada akan diciutkan 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 yang diciutkan tetap di 200 sepanjang langkah ini.
  • Konsolidasi histogram: Pada langkah ketiga, lebih banyak rentang dapat diciutkan jika sejumlah besar informasi tidak hilang. 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 akan memiliki minimal tiga langkah.

Catatan

Jika histogram telah dibangun menggunakan sampel daripada fullscan, maka nilai equal_rows, range_rows, dan distinct_range_rows dan average_range_rows diperkirakan, 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.

Image of how a histogram is calculated from sampled column values.

Untuk setiap langkah histogram di atas:

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

  • Area solid 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 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

Kepadatan 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.

Catatan

Frekuensi adalah informasi tentang kemunculan setiap nilai yang berbeda di kolom kunci pertama objek statistik, dan dihitung sebagai jumlah baris * kepadatan. 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 , ItemId dan Price, kepadatan dihitung pada setiap awalan kolom berikut.

Awalan kolom Kepadatan dihitung pada
(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 MEMBUAT STATISTIK (Transact-SQL). Untuk informasi selengkapnya tentang kapan membuat statistik yang difilter, lihat bagian Kapan Harus Membuat Statistik di artikel ini.

Opsi statistik

Ada opsi yang memengaruhi kapan dan bagaimana statistik dibuat dan diperbarui. Opsi ini hanya dapat dikonfigurasi pada tingkat database.

opsi AUTO_CREATE_STATISTICS

Saat opsi statistik pembuatan otomatis, AUTO_CREATE_STATISTICS AKTIF, 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 statistik dibuat 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 opsi AUTO_CREATE_STATISTICS, nama statistik dimulai dengan _WA. Anda bisa menggunakan kueri berikut untuk menentukan apakah Pengoptimal Kueri telah 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 opsi statistik pembaruan otomatis, AUTO_UPDATE_STATISTICS AKTIF, Pengoptimal Kueri menentukan kapan statistik mungkin sudah kedaluarsa lalu memperbaruinya saat digunakan oleh kueri. 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 menentukan kapan statistik mungkin kedaluarsa dengan menghitung jumlah modifikasi baris sejak pembaruan statistik terakhir dan membandingkan jumlah modifikasi baris dengan ambang batas. Ambang didasarkan pada kardinalitas tabel, yang dapat didefinisikan sebagai jumlah baris dalam tabel atau tampilan terindeks.

Menandai statistik sebagai kedaluarsa berdasarkan modifikasi baris terjadi bahkan ketika opsi AUTO_UPDATE_STATISTICS NONAKTIF. Ketika opsi AUTO_UPDATE STATISTICS NONAKTIF, statistik tidak diperbarui, bahkan ketika ditandai sebagai kedaluarsa. Paket akan terus menggunakan objek statistik yang kedaluarsa. Mengatur AUTO_UPDATE_STATISTICS ke NONAKTIF dapat menyebabkan rencana kueri suboptimal dan performa kueri yang terdegradasi. Mengatur opsi AUTO_UPDATE STATISTICS ke AKTIF disarankan.

  • 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 apakah tabel bersifat sementara atau permanen.

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

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

  • Dimulai dengan SQL Server 2016 (13.x) dan di bawah tingkat kompatibilitas database 130, Mesin Database juga 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 akan lebih sering diperbarui. Namun, jika database memiliki tingkat kompatibilitas di bawah 130, maka ambang batas SQL Server 2014 (12.x) berlaku.

    Jenis tabel Kardinalitas tabel (n) Ambang kompilasi ulang (# modifikasi)
    Sementara n< 6 6
    Sementara 6 <= n<= 500 500
    Permanen 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, maka perhitungannya adalah minimum 500 + (0.20 * 2,000,000) = 400,500 dan SQRT(1,000 * 2,000,000) = 44,721. Ini berarti statistik akan diperbarui setiap 44.721 modifikasi.

Penting

Di SQL Server 2008 R2 (10.50.x) melalui SQL Server 2014 (12.x), atau di SQL Server 2016 (13.x) dan yang lebih baru di bawah tingkat kompatibilitas database 120 dan 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 pekerjaan malam untuk memperbarui statistik karena pembaruan otomatis saat ini tidak cukup sering dipicu, pertimbangkan untuk mengaktifkan bendera pelacakan 2371 untuk menyesuaikan ambang batas ke 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 cache, Mesin Database memverifikasi bahwa rencana kueri mereferensikan statistik terbaru.

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 NONAKTIF 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 NONAKTIF, dan Pengoptimal Kueri memperbarui statistik secara sinkron. Opsi AUTO_UPDATE_STATISTICS_ASYNC berlaku untuk objek statistik yang dibuat untuk indeks, kolom tunggal dalam predikat kueri, dan statistik yang dibuat dengan pernyataan CREATE STATISTICS .

Catatan

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

Pembaruan statistik dapat sinkron (default) atau asinkron.

  • Dengan pembaruan statistik sinkron, kueri selalu mengompilasi dan menjalankan 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 bahkan jika 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 selesai akan mendapat manfaat dari menggunakan 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, menggunakan statistik sinkron akan memastikan statistik sudah diperbarui sebelum kueri dijalankan pada data yang diubah.

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 menghindari penundaan beberapa kueri dan bukan yang lain.

  • Aplikasi Anda telah mengalami waktu permintaan klien habis yang disebabkan oleh satu atau beberapa kueri yang menunggu statistik yang diperbarui. Dalam beberapa kasus, menunggu statistik sinkron dapat menyebabkan aplikasi dengan waktu habis yang agresif gagal.

Catatan

Statistik pada tabel sementara lokal selalu diperbarui secara sinkron terlepas dari opsi AUTO_UPDATE_STATISTICS_ASYNC. Statistik pada tabel sementara global diperbarui secara sinkron atau asinkron sesuai dengan opsi AUTO_UPDATE_STATISTICS_ASYNC yang 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 sering, menggunakan statistik asinkron dapat meningkatkan kemungkinan masalah konkurensi karena pemblokiran kunci.

Di Azure SQL Database, Azure SQL Managed Instance, dan dimulai di SQL Server 2022 (16.x), Anda dapat menghindari potensi masalah konkurensi menggunakan pembaruan statistik asinkron jika Anda mengaktifkan konfigurasi cakupan database ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Dengan konfigurasi ini diaktifkan, permintaan latar belakang akan menunggu untuk memperoleh kunci modifikasi skema (Sch-M) dan mempertahankan 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 akan memperoleh kunci modifikasi skemanya dan memperbarui statistik. Jika permintaan latar belakang tidak dapat memperoleh kunci dalam jangka waktu habis beberapa menit, pembaruan statistik asinkron akan dibatalkan, dan statistik tidak akan diperbarui sampai pembaruan statistik otomatis lainnya dipicu, atau sampai statistik diperbarui secara manual.

Catatan

Opsi konfigurasi cakupan database ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 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)

Di SQL Server sebelum SQL Server 2022 (16.x), jika statistik dibuat secara manual oleh pengguna atau alat pihak ketiga pada database pengguna, objek statistik tersebut dapat memblokir atau mengganggu perubahan skema yang mungkin diinginkan pelanggan.

Dimulai dengan SQL Server 2022 (16.x), opsi penurunan otomatis diaktifkan secara default pada semua database baru dan yang dimigrasikan. Properti AUTO_DROP memungkinkan pembuatan objek statistik dalam mode skema berikutnya tidak akan diblokir oleh objek statistik, tetapi sebaliknya statistik akan dihilangkan seperlunya. Dengan cara ini, statistik yang dibuat secara manual dengan penurunan otomatis diaktifkan berulah seperti statistik yang dibuat secara otomatis.

Catatan

Mencoba mengatur atau membatalkan setel properti penurunan otomatis pada statistik yang dibuat secara otomatis dapat menimbulkan kesalahan. Statistik yang dibuat secara otomatis selalu menggunakan penurunan otomatis. Beberapa cadangan, ketika dipulihkan, mungkin properti ini salah diatur 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 dijalankan 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 penurunan otomatis pada statistik yang ada, gunakan auto_drop kolom di sys.stats:

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

Untuk informasi selengkapnya, lihat MEMBUAT STATISTIK (Transact-SQL)

BERTAMBAH BERTAHAP

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

Ketika opsi INKREMENTAL DARI CREATE STATISTICS AKTIF, statistik yang dibuat adalah statistik per partisi. Saat NONAKTIF, pohon statistik dijatuhkan dan SQL Server mengolah ulang statistik. Defaultnya adalah NONAKTIF. Pengaturan ini mengambil alih properti INCREMENTAL tingkat database. Untuk informasi selengkapnya tentang membuat statistik inkremental, lihat MEMBUAT STATISTIK (Transact-SQL). Untuk informasi selengkapnya tentang membuat statistik per partisi secara otomatis, lihat Properti Database (Halaman Opsi) dan Opsi ALTER DATABASE SET (Transact-SQL).

Ketika partisi baru ditambahkan ke tabel besar, statistik harus diperbarui untuk menyertakan partisi baru. Namun waktu yang diperlukan untuk memindai seluruh tabel (opsi FULLSCAN atau SAMPLE) mungkin cukup panjang. Selain itu, memindai seluruh tabel tidak diperlukan karena hanya statistik pada partisi baru yang mungkin diperlukan. Opsi inkremental membuat dan menyimpan statistik per partisi, dan ketika diperbarui, hanya me-refresh statistik pada partisi yang membutuhkan statistik baru

Jika statistik per partisi tidak didukung, opsi diabaikan dan peringatan dihasilkan. Statistik inkremental tidak didukung untuk jenis statistik berikut:

  • Statistik dibuat dengan indeks yang tidak selaras dengan 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. Pengoptimal Kueri membuat statistik untuk indeks pada tabel atau tampilan saat indeks dibuat. 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 Terfilter dan MEMBUAT INDEKS (Transact-SQL).

    Catatan

    Dimulai dengan SQL Server 2014 (12.x), statistik tidak dibuat dengan memindai semua baris dalam tabel saat indeks yang dipartisi dibuat atau dibangun kembali. 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 pernyataan CREATE STATISTICS . Statistik tambahan ini dapat menangkap korelasi statistik yang tidak memperhitungkan 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 membuat statistik dengan pernyataan CREATE STATISTICS, sebaiknya tetap aktifkan opsi AUTO_CREATE_STATISTICS sehingga Pengoptimal Kueri terus secara rutin membuat statistik kolom tunggal untuk kolom predikat kueri. Untuk informasi selengkapnya tentang predikat kueri, lihat Kondisi Pencarian (Transact-SQL).

Pertimbangkan untuk membuat statistik dengan pernyataan CREATE STATISTICS saat salah satu hal berikut berlaku:

  • Database Engine Tuning Advisor menyarankan pembuatan statistik.
  • Predikat kueri berisi beberapa kolom berkorelasi yang belum ada dalam indeks yang sama.
  • Kueri memilih dari subkumpulan data.
  • Kueri memiliki statistik yang hilang.

Catatan

Untuk informasi khusus untuk tabel dan statistik terkait OLTP Dalam Memori, lihat Statistik untuk Tabel yang Dioptimalkan 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 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 pernyataan CREATE STATISTICS . 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 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 halaman 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 pernyataan CREATE STATISTICS dengan klausa WHERE untuk menentukan ekspresi predikat filter.

Misalnya, menggunakan AdventureWorks2022, setiap produk dalam tabel termasuk dalam Production.Product salah satu dari empat kategori dalam Production.ProductCategory tabel: Sepeda, Komponen, Pakaian, dan Aksesori. 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 nilai NULL, bobot pakaian semuanya NULL, dan bobot aksesori juga NULL.

Menggunakan Sepeda sebagai contoh, statistik yang difilter pada semua bobot sepeda akan memberikan statistik yang lebih akurat kepada Pengoptimal Kueri dan dapat meningkatkan kualitas rencana kueri dibandingkan dengan statistik tabel penuh atau statistik yang tidak ada pada kolom Bobot. Kolom bobot sepeda adalah kandidat yang baik untuk statistik yang difilter tetapi belum tentu menjadi kandidat yang baik untuk indeks yang difilter jika jumlah pencarian berat relatif kecil. 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 BikeWeights statistik yang difilter pada semua subkatoner untuk Sepeda. 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 Sepeda 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
    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 Statistik Kolom yang Hilang dengan menggunakan SQL Server Profiler menunjukkan kapan statistik hilang. Untuk informasi selengkapnya, lihat Kategori Peristiwa Kesalahan dan Peringatan (Mesin Database).

Jika statistik hilang, lakukan langkah-langkah berikut:

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 SQL Server. Skrip untuk statistik sementara dapat dibuat dan diproduksi ulang pada database baca-tulis. Saat diskrip, Management Studio mengubah akhiran nama statistik dari _readonly_database_statistic menjadi _readonly_database_statistic_scripted.

Hanya SQL Server 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 pernyataan DROP STATISTICS .
  • 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 di tempdb, hidupkan ulang layanan SQL Server menyebabkan semua statistik sementara menghilang.

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 yang terjadi saat AUTO_UPDATE_STATISTICS aktif. Anda dapat memperbarui statistik dengan pernyataan UPDATE STATISTICS atau prosedur sp_updatestatstersimpan .

Memperbarui statistik akan memastikan bahwa kueri dikompilasi dengan statistik terbaru. Memperbarui statistik melalui proses apa pun dapat menyebabkan rencana kueri dikombinasikan ulang secara otomatis. Sebaiknya jangan memperbarui statistik secara manual terlalu sering karena ada tradeoff performa antara meningkatkan rencana kueri dan waktu yang diperlukan untuk mengkombinasikan ulang kueri. Tradeoff tertentu tergantung pada aplikasi Anda.

Saat memperbarui statistik dengan UPDATE STATISTICS atau sp_updatestats, sebaiknya tetap AUTO_UPDATE_STATISTICS diatur ke AKTIF sehingga Pengoptimal Kueri secara rutin memperbarui statistik.

  • Untuk informasi selengkapnya tentang cara memperbarui statistik pada kolom, indeks, tabel, atau tampilan terindeks, lihat MEMPERBARUI STATISTIK (Transact-SQL).

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

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

Ketika AUTO_UPDATE_STATISTICS diatur ke NONAKTIF, kompilasi ulang paket masih dapat terjadi karena berbagai alasan lain, tetapi tidak akan terjadi secara otomatis karena pembaruan statistik yang kedaluarsa. Ketika AUTO_UPDATE_STATISTICS diatur ke NONAKTIF, pembaruan statistik hanya akan terjadi melalui proses terjadwal manual lainnya, seperti rencana pemeliharaan. Pengaturan AUTO_UPDATE_STATISTICS ke NONAKTIF karena itu 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 sisipkan terjadi pada kolom kunci naik atau turun.
  • Setelah operasi pemeliharaan.

Untuk contoh memperbarui statistik secara manual, lihat MEMPERBARUI STATISTIK (Transact-SQL).

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 sisipkan terjadi pada kolom kunci naik atau turun

Statistik tentang kolom kunci naik atau menurun, seperti kolom IDENTITAS atau 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 diperbarui dan kueri memilih dari baris yang terakhir ditambahkan, statistik saat ini tidak akan memiliki perkiraan kardinalitas untuk nilai baru ini. Ini dapat mengakibatkan perkiraan kardinalitas yang tidak akurat dan performa kueri yang lambat.

Misalnya, kueri yang memilih dari tanggal pesanan penjualan terbaru akan 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. Ini 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 operasi ALTER INDEX 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 merupakan produk sampingan 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 PERSIST_SAMPLE_PERCENT CREATE STATISTICS (Transact-SQL) atau UPDATE STATISTICS (Transact-SQL), 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

Manfaatkan solusi 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.

Kueri yang menggunakan statistik secara efektif

Implementasi kueri tertentu, seperti variabel lokal dan ekspresi kompleks dalam predikat kueri, dapat menyebabkan rencana kueri suboptimal. Mengikuti panduan desain kueri untuk menggunakan statistik secara efektif dapat membantu menghindari hal ini. Untuk informasi selengkapnya tentang predikat kueri, lihat Kondisi Pencarian (Transact-SQL).

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. Hal ini menyebabkan perkiraan kardinalitas suboptimal dan dapat merusak performa kueri. Untuk informasi selengkapnya tentang histogram, lihat bagian histogram di halaman 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 dengan konstanta di dalamnya. 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. Nilai variabel lokal tidak diketahui saat Pengoptimal Kueri membuat rencana eksekusi kueri. Saat kueri menggunakan parameter, Pengoptimal Kueri menggunakan perkiraan kardinalitas untuk nilai parameter aktual pertama yang diteruskan ke prosedur tersimpan.

  • Pertimbangkan untuk menggunakan tabel standar atau tabel sementara untuk menyimpan hasil fungsi bernilai tabel multi-pernyataan (mstvf). Pengoptimal Kueri tidak membuat statistik untuk fungsi bernilai tabel multi-pernyataan. Dengan 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 pendekatan ini, Pengoptimal Kueri dapat membuat statistik pada kolom tabel dan menggunakannya untuk membuat rencana kueri yang lebih baik. Ada tradeoff 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 saat @date 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 h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
END
GO

    Jika panggilan pertama ke prosedur Sales.GetRecentSales tersimpan melewati NULL untuk @date parameter , Pengoptimal Kueri akan mengkompilasi prosedur tersimpan dengan perkiraan @date = NULL kardinalitas 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 hal 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))
    EXEC 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 h, Sales.SalesOrderDetail 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, Anda dapat menggunakan OPTIMIZE FOR <value> petunjuk atau OPTIMIZE FOR UNKNOWN kueri dengan RECOMPILE. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

Untuk beberapa aplikasi, kompilasi ulang kueri setiap kali dijalankan mungkin memakan waktu terlalu lama. OPTIMIZE FOR Petunjuk kueri 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 h, Sales.SalesOrderDetail 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. Anda dapat menggunakan panduan paket untuk menentukan petunjuk lain, seperti USE PLAN, untuk mengontrol perilaku kueri saat menyelidiki perubahan aplikasi dengan vendor aplikasi. Untuk informasi selengkapnya tentang panduan paket, lihat Panduan Paket.

Di Azure SQL Database, pertimbangkan petunjuk Penyimpanan Kueri untuk memaksa rencana, bukan panduan paket. Untuk informasi selengkapnya, lihat Petunjuk Penyimpanan Kueri.

Baca juga

Langkah berikutnya

  • Defrag Indeks Adaptif dari kotak alat tim Microsoft SQL Server Tiger