Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Logika estimasi kardinalitas, yang disebut estimator kardinalitas, dirancang ulang di SQL Server 2014 untuk meningkatkan kualitas rencana kueri, dan oleh karena itu untuk meningkatkan performa kueri. Estimator kardinalitas baru menggabungkan asumsi dan algoritma yang bekerja dengan baik pada beban kerja OLTP modern dan pergudangan data. Hal ini didasarkan pada penelitian estimasi kardinalitas mendalam tentang beban kerja modern, dan pembelajaran kami selama 15 tahun terakhir dalam meningkatkan estimator kardinalitas SQL Server. Umpan balik dari pelanggan menunjukkan bahwa meskipun sebagian besar kueri akan mendapat manfaat dari perubahan atau tetap tidak berubah, sejumlah kecil mungkin menunjukkan regresi dibandingkan dengan estimator kardinalitas sebelumnya.
Nota
Perkiraan kardinalitas adalah prediksi jumlah baris dalam hasil kueri. Pengoptimal kueri menggunakan perkiraan ini untuk memilih rencana untuk menjalankan kueri. Kualitas rencana kueri berdampak langsung pada peningkatan performa kueri.
Rekomendasi Pengujian dan Penyetelan Performa
Estimator kardinalitas baru diaktifkan untuk semua database baru yang dibuat di SQL Server 2014. Namun, peningkatan ke SQL Server 2014 tidak mengaktifkan estimator kardinalitas baru pada database yang ada.
Untuk memastikan performa kueri terbaik, gunakan rekomendasi ini untuk menguji beban kerja Anda dengan estimator kardinalitas baru sebelum mengaktifkannya pada sistem produksi Anda.
Tingkatkan semua database yang ada untuk menggunakan estimator kardinalitas baru. Untuk melakukan ini, gunakan ALTER DATABASE Compatibility Level (Transact-SQL) untuk mengatur tingkat kompatibilitas database ke 120.
Jalankan beban kerja pengujian Anda dengan estimator kardinalitas yang baru, lalu atasi masalah performa baru dengan cara yang sama seperti saat ini Anda mengatasi masalah performa.
Setelah beban kerja Anda berjalan dengan estimator kardinalitas baru (tingkat kompatibilitas database 120 (SQL Server 2014)), dan kueri tertentu telah regresi, Anda dapat menjalankan kueri dengan bendera pelacakan 9481 untuk menggunakan versi estimator kardinalitas yang digunakan di SQL Server 2012 dan yang lebih lama. Untuk menjalankan kueri dengan trace flag, lihat artikel KB Mengaktifkan perilaku pengoptimal kueri SQL Server yang memengaruhi rencana dan dapat dikontrol oleh trace flag yang berbeda pada tingkat kueri tertentu.
Jika Anda tidak dapat mengubah semua database sekaligus untuk menggunakan estimator kardinalitas baru, Anda dapat menggunakan estimator kardinalitas sebelumnya untuk semua database dengan menggunakan AlTER DATABASE Compatibility Level (Transact-SQL) untuk mengatur tingkat kompatibilitas database ke 110.
Jika beban kerja Anda berjalan dengan tingkat kompatibilitas database 110 dan Anda ingin menguji atau menjalankan kueri tertentu dengan estimator kardinalitas baru, Anda dapat menjalankan kueri dengan bendera pelacakan 2312 untuk menggunakan estimator kardinalitas versi SQL Server 2014. Untuk menjalankan kueri dengan bendera pelacakan, lihat artikel KB Mengaktifkan perilaku pengoptimal kueri SQL Server yang memengaruhi rencana yang dapat dikontrol oleh bendera pelacakan yang berbeda pada tingkat kueri tertentu.
XEvents baru
Ada dua query_optimizer_estimate_cardinality XEvent baru untuk mendukung rencana kueri baru.
query_optimizer_estimate_cardinality terjadi ketika pengoptimal kueri memperkirakan kardinalitas pada ekspresi relasional.
query_optimizer_force_both_cardinality_estimation_behaviors terjadi ketika dua traceflag, yaitu 2312 dan 9481, diaktifkan, mencoba memaksa perilaku estimasi kardinalitas lama dan baru secara bersamaan.
Contoh
Contoh berikut menunjukkan beberapa perubahan dalam perkiraan kardinalitas baru. Kode untuk memperkirakan kardinalitas telah ditulis ulang. Logikanya kompleks dan tidak dimungkinkan untuk memberikan daftar lengkap semua perubahan.
Nota
Contoh-contoh ini disediakan sebagai informasi konseptual. Tidak ada tindakan yang perlu Anda lakukan untuk mengubah cara Anda merancang database dan kueri.
Contoh A. Perkiraan kardinalitas baru menggunakan kardinalitas rata-rata untuk data naik yang baru ditambahkan
Contoh ini menunjukkan bagaimana estimator kardinalitas baru dapat meningkatkan perkiraan kardinalitas untuk data naik yang melebihi nilai maksimum dalam tabel selama pembaruan statistik terbaru.
SELECT item, category, amount FROM dbo.Sales AS s WHERE Date = '2013-12-19';
Dalam contoh ini, baris baru ditambahkan ke tabel Penjualan setiap hari, kueri meminta penjualan yang terjadi pada 19/12/2013, dan statistik terakhir diperbarui pada 18/12/2013. Estimator kardinalitas sebelumnya mengasumsikan nilai 19/12/2013 tidak ada karena tanggal melebihi tanggal maksimum dan statistik belum diperbarui untuk menyertakan nilai 19/12/2013. Situasi ini, yang dikenal sebagai masalah kunci naik, akan terjadi jika Anda memuat data pada siang hari, lalu menjalankan kueri terhadap data sebelum statistik diperbarui.
Perilaku ini telah berubah. Sekarang, bahkan jika statistik belum diperbarui untuk data naik terbaru yang ditambahkan sejak pembaruan statistik terakhir, estimator kardinalitas baru mengasumsikan nilai ada dan menggunakan kardinalitas rata-rata untuk setiap nilai di kolom sebagai perkiraan kardinalitas.
Contoh B. Perkiraan kardinalitas baru mengasumsikan predikat yang difilter pada tabel yang sama memiliki beberapa korelasi
Untuk contoh ini, asumsikan tabel Mobil sebagai 1000 baris, Make memiliki 200 kecocokan untuk 'Honda', Model memiliki 50 kecocokan untuk 'Civic', dan bahwa semua Civic adalah Honda. Oleh karena itu, 20% nilai di kolom Merk adalah 'Honda', 5% nilai di kolom Model adalah 'Civic', dan jumlah aktual Honda Civic adalah 50. Perkiraan kardinalitas sebelumnya mengasumsikan nilai dalam kolom Buat dan Model independen satu sama lain. Pengoptimal kueri sebelumnya memperkirakan ada 10 Honda Civics (.05 * .20 * 1000 baris = 10 baris).
SELECT year, purchase_price FROM dbo.Cars WHERE Make = 'Honda' AND Model = 'Civic';
Perilaku ini telah berubah. Sekarang, perkiraan kardinalitas baru mengasumsikan kolom Merk dan Tipe memiliki suatu korelasi. Pengoptimal kueri memperkirakan kardinalitas yang lebih tinggi dengan menambahkan komponen eksponensial ke persamaan estimasi. Pengoptimal kueri sekarang memperkirakan bahwa 22,36 baris ( .05 * SQRT(.20) * 1000 baris = 22,36 baris ) cocok dengan predikat. Untuk skenario ini dan distribusi data tertentu, 22,36 baris lebih mendekati 50 baris sebenarnya yang akan dikembalikan oleh kueri.
Perhatikan, logika estimator kardinalitas yang baru mengurutkan selektivitas predikat dan meningkatkan nilai eksponen. Misalnya, jika selektivitas predikat adalah .05, .20, dan .25, perkiraan kardinalitas adalah (.05 * SQRT(.20) * SQRT(SQRT(.25)) ).
Contoh C. Perkiraan kardinalitas baru mengasumsikan predikat yang difilter pada tabel yang berbeda bersifat independen
Untuk contoh ini, estimator kardinalitas sebelumnya mengasumsikan bahwa filter predikat s.type dan r.date berkorelasi. Namun, hasil pengujian pada beban kerja modern menunjukkan bahwa filter predikat pada kolom dalam tabel yang berbeda biasanya tidak berkorelasi satu sama lain.
SELECT s.ticket, s.customer, r.store FROM dbo.Sales AS s CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND s.type = 'toy' AND r.date = '2013-12-19';
Perilaku ini telah berubah. Sekarang, logika estimator kardinalitas baru mengasumsikan bahwa s.type tidak berkorelasi dengan r.date. Dalam istilah praktis, asumsinya adalah bahwa mainan dikembalikan setiap hari dan tidak hanya pada hari tertentu. Dalam hal ini, perkiraan kardinalitas baru akan menjadi angka yang lebih kecil dari perkiraan kardinalitas sebelumnya.