Bagikan melalui

Statistik

  • Artikel

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. Topik ini membahas konsep statistik dan memberikan panduan untuk menggunakan statistik pengoptimalan kueri secara efektif.

Komponen dan Konsep

Statistik
Statistik untuk pengoptimalan kueri adalah objek 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, pengoptimal kueri dapat menggunakan perkiraan kardinalitas untuk memilih operator pencarian indeks alih-alih operator pemindaian indeks yang lebih intensif sumber daya, dan dengan demikian 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. Untuk informasi selengkapnya tentang objek statistik, lihat SHOW_STATISTICS DBCC (Transact-SQL).

Statistik Terfilter
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 harus membuat statistik yang difilter, lihat bagian Kapan Harus Membuat Statistik dalam topik ini. Untuk studi kasus, lihat entri blog, Menggunakan Statistik Terfilter dengan Tabel Yang Dipartisi, di situs Web SQLCAT.

Opsi Statistik
Ada tiga opsi yang dapat Anda atur yang memengaruhi kapan dan bagaimana statistik dibuat dan diperbarui. Opsi ini hanya diatur 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 ketat untuk statistik kolom tunggal untuk tabel lengkap.

Saat pengoptimal kueri membuat statistik sebagai akibat dari penggunaan 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 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 kedaluarsa lalu memperbaruinya saat digunakan oleh kueri. Statistik menjadi kedaluarsa setelah 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 data sejak pembaruan statistik terakhir dan membandingkan jumlah modifikasi dengan ambang batas. Ambang didasarkan pada jumlah baris dalam tabel atau tampilan terindeks.

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

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 .

Pembaruan statistik dapat berupa 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. Kueri yang dikompilasi setelah pembaruan asinkron selesai akan mendapat manfaat dari penggunaan 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 setelah menyelesaikan operasi, menggunakan statistik sinkron akan memastikan statistik sudah diperbarui sebelum menjalankan kueri 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.

STATISTIK INKREMENTAL
Saat ON, statistik yang dibuat adalah statistik per partisi. Saat OFF, pohon statistik dijatuhkan dan SQL Server menghitung ulang statistik. Defaultnya adalah NONAKTIF. Pengaturan ini mengambil alih properti INCREMENTAL tingkat database.

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 lama. 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 menyegarkan statistik pada partisi yang membutuhkan statistik baru

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

  • Statistik dibuat dengan indeks yang tidak selaras dengan tabel dasar.

  • Statistik yang dibuat pada database sekunder alwaysOn yang dapat dibaca.

  • 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.
Berlaku untuk: SQL Server 2014 hingga SQL Server 2014.

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 CREATE INDEX (Transact-SQL).

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

Untuk sebagian besar kueri, kedua metode ini untuk membuat statistik 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 akun 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 dapat meningkatkan rencana kueri.

Saat membuat statistik dengan pernyataan CREATE STATISTICS, sebaiknya pertahankan opsi AUTO_CREATE_STATISTICS agar 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 ini berlaku:

  • Konsultan Penyetelan Mesin Database menyarankan pembuatan statistik.

  • Predikat kueri berisi beberapa kolom berkorelasi yang belum ada dalam indeks yang sama.

  • Kueri memilih dari subset data.

  • Kueri memiliki statistik yang hilang.

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 SHOW_STATISTICS DBCC (Transact-SQL).

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

USE AdventureWorks2012;  
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: (), (LastNameLastName, 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 Subset Data

Saat pengoptimal kueri membuat statistik untuk kolom dan indeks tunggal, 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 AdventureWorks2012, setiap produk dalam tabel Production.Product termasuk dalam salah satu dari empat kategori dalam tabel Production.ProductCategory: Sepeda, Komponen, Pakaian, dan Aksesoris. 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, berat 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 merupakan 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 subkataan 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 AdventureWorks2012;
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) saat 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:

  • Verifikasi bahwa AUTO_CREATE_STATISTICS dan AUTO_UPDATE_STATISTICS aktif.

  • Verifikasi bahwa database tidak bersifat baca-saja. Jika database bersifat baca-saja, pengoptimal kueri tidak dapat menyimpan statistik.

  • Buat statistik yang hilang dengan menggunakan pernyataan CREATE STATISTICS.

Saat statistik pada database baca-saja atau rekam jepret baca-saja hilang atau kedaluarsa, 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 disediakan 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 (Transact-SQL ).

  • Pantau statistik menggunakan tampilan katalog sys.stats dan sys.stats_columns . sys_stats menyertakan kolom is_temporary , untuk menunjukkan statistik mana yang permanen dan mana yang bersifat sementara.

Karena statistik sementara disimpan dalam tempdb, mulai 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 karenanya 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 tersimpan sp_updatestats.

Memperbarui statistik akan memastikan bahwa kueri dikompilasi dengan statistik terbaru. Namun, memperbarui statistik menyebabkan kueri dikombinasikan ulang. Sebaiknya jangan memperbarui statistik terlalu sering karena ada tradeoff performa antara meningkatkan rencana kueri dan waktu yang diperlukan untuk mengolah ulang kueri. Tradeoff spesifik tergantung pada aplikasi Anda.

Saat memperbarui statistik dengan STATISTIK PEMBARUAN atau sp_updatestats, sebaiknya tetap AUTO_UPDATE_STATISTICS diatur ke AKTIF sehingga pengoptimal kueri terus memperbarui statistik secara rutin. 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 menentukan kapan statistik terakhir diperbarui, gunakan fungsi 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.

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 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 diperbarui dan kueri memilih dari baris yang terakhir ditambahkan, statistik saat ini tidak akan memiliki perkiraan kardinalitas untuk nilai baru ini. Hal 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 REINDEX, DBCC INDEXDEFRAG, atau ALTER INDEX REORGANIZE. Pengoptimal kueri memperbarui statistik saat Anda membangun kembali indeks pada tabel atau tampilan dengan ALTER INDEX REBUILD atau DBCC DBREINDEX, namun; pembaruan statistik ini adalah produk sampingan dari pembuatan ulang indeks. Pengoptimal kueri tidak memperbarui statistik setelah operasi DBCC INDEXDEFRAG atau ALTER INDEX REORGANIZE.

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

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, menyederhanakan ekspresi ABS(-100) to 100.

  • Jika ekspresi menggunakan beberapa variabel, pertimbangkan untuk membuat kolom komputasi untuk ekspresi lalu membuat statistik atau indeks pada kolom komputasi. Misalnya, predikat WHERE PRICE + Tax > 100 kueri mungkin memiliki perkiraan kardinalitas yang lebih baik jika Anda membuat kolom komputasi 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. 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 is NULL.

    USE AdventureWorks2012;  
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 kardinalitas untuk @date = NULL meskipun predikat kueri tidak dipanggil dengan @date = NULL. Perkiraan kardinalitas ini mungkin jauh berbeda 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 AdventureWorks2012;  
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 petunjuk kueri OPTIMIZE FOR atau OPTIMIZE FOR UNKNOWN dengan RECOMPILE. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

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

USE AdventureWorks2012;  
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 menggunakan petunjuk kueri RECOMPILE mungkin menyebabkan terlalu banyak kompilasi ulang. Anda dapat menggunakan 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 paket, lihat Panduan Rencana.

Lihat juga

BUAT STATISTIK (Transact-SQL)
PERBARUI STATISTIK (Transact-SQL)
sp_updatestats (Transact-SQL)
SHOW_STATISTICS DBCC (Transact-SQL)
UBAH Opsi SET DATABASE (Transact-SQL)
DROP STATISTICS (Transact-SQL)
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
Membuat Indeks Terfilter