PERBARUI STATISTIK (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric

Memperbarui statistik pengoptimalan kueri pada tabel atau tampilan terindeks. Secara default, pengoptimal kueri sudah memperbarui statistik seperlunya untuk meningkatkan rencana kueri; dalam beberapa kasus, Anda dapat meningkatkan performa kueri dengan menggunakan UPDATE STATISTICS atau prosedur tersimpan sp_updatestats untuk memperbarui statistik lebih sering daripada pembaruan default.

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 terlalu sering karena ada tradeoff performa antara meningkatkan rencana kueri dan waktu yang diperlukan untuk mengkombinasikan ulang kueri. Tradeoff tertentu tergantung pada aplikasi Anda. UPDATE STATISTICS dapat menggunakan tempdb untuk mengurutkan sampel baris untuk membangun statistik.

Catatan

Untuk informasi selengkapnya tentang statistik di Microsoft Fabric, lihat Statistik di Microsoft Fabric.

Konvensi sintaks transact-SQL

Sintaksis

-- Syntax for SQL Server and Azure SQL Database  
  
UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( { <partition_number> | <range> } [, ...n] ) ]  
            | <update_stats_stream_option> [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;  
  
<update_stats_stream_option> ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]  

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name | index_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
            | RESAMPLE   
        }  
    ]  
[;]  

-- Syntax for Microsoft Fabric

UPDATE STATISTICS [ schema_name . ] table_name   
    [ ( { statistics_name } ) ]  
    [ WITH   
       {  
              FULLSCAN   
            | SAMPLE number PERCENT   
        }  
    ]  
[;]

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Catatan

Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.

Argumen

table_or_indexed_view_name

Adalah nama tabel atau tampilan terindeks yang berisi objek statistik.

index_or_statistics_name atau statistics_name | index_name atau statistics_name

Adalah nama indeks untuk memperbarui statistik pada atau nama statistik yang akan diperbarui. Jika index_or_statistics_name atau statistics_name tidak ditentukan, pengoptimal kueri memperbarui semua statistik untuk tabel atau tampilan terindeks. Ini termasuk statistik yang dibuat menggunakan pernyataan CREATE STATISTICS, statistik kolom tunggal yang dibuat saat AUTO_CREATE_STATISTICS aktif, dan statistik yang dibuat untuk indeks.

Untuk informasi selengkapnya tentang AUTO_CREATE_STATISTICS, lihat Opsi UBAH KUMPULAN DATABASE. Untuk menampilkan semua indeks untuk tabel atau tampilan, Anda bisa menggunakan sp_helpindex.

FULLSCAN

Komputasi statistik dengan memindai semua baris dalam tabel atau tampilan terindeks. FULLSCAN dan SAMPLE 100 PERCENT memiliki hasil yang sama. FULLSCAN tidak dapat digunakan dengan opsi SAMPLE.

Nomor SAMPEL { PERSEN | BARIS }

Menentukan perkiraan persentase atau jumlah baris dalam tabel atau tampilan terindeks untuk digunakan pengoptimal kueri saat memperbarui statistik. Untuk PERCENT, angka bisa dari 0 hingga 100 dan untuk BARIS, angka bisa dari 0 hingga jumlah baris total. Persentase aktual atau jumlah baris sampel pengoptimal kueri mungkin tidak cocok dengan persentase atau angka yang ditentukan. Misalnya, pengoptimal kueri memindai semua baris di halaman data.

SAMPLE berguna untuk kasus khusus di mana rencana kueri, berdasarkan pengambilan sampel default, tidak optimal. Dalam kebanyakan situasi, tidak perlu menentukan SAMPLE karena pengoptimal kueri menggunakan pengambilan sampel dan menentukan ukuran sampel yang signifikan secara statistik secara default, sebagaimana diperlukan untuk membuat rencana kueri berkualitas tinggi.

Catatan

Di SQL Server 2016 (13.x) saat menggunakan kompatibilitas database tingkat 130, pengambilan sampel data untuk membangun statistik dilakukan secara paralel untuk meningkatkan performa pengumpulan statistik. Pengoptimal kueri akan menggunakan statistik sampel paralel setiap kali ukuran tabel melebihi ambang batas tertentu. Dimulai dengan SQL Server 2017 (14.x), terlepas dari tingkat kompatibilitas database, perilaku diubah kembali menggunakan pemindaian serial untuk menghindari potensi masalah performa dengan penantian LATCH yang berlebihan. Sisa rencana kueri saat memperbarui statistik akan mempertahankan eksekusi paralel jika memenuhi syarat.

SAMPLE tidak dapat digunakan dengan opsi FULLSCAN. Ketika SAMPEL maupun FULLSCAN tidak ditentukan, pengoptimal kueri menggunakan data sampel dan menghitung ukuran sampel secara default.

Sebaiknya jangan tentukan 0 PERSEN atau 0 BARIS. Ketika 0 PERSEN atau ROWS ditentukan, objek statistik diperbarui tetapi tidak berisi data statistik.

Untuk sebagian besar beban kerja, pemindaian penuh tidak diperlukan, dan pengambilan sampel default memadai. Namun, beban kerja tertentu yang sensitif terhadap distribusi data yang bervariasi secara luas mungkin memerlukan peningkatan ukuran sampel, atau bahkan pemindaian penuh. Meskipun perkiraan mungkin menjadi lebih akurat dengan pemindaian penuh daripada pemindaian sampel, rencana kompleks mungkin tidak mendapat manfaat secara substansial.

Untuk informasi selengkapnya, lihat Komponen dan konsep statistik.

PENGAMBILAN SAMPEL ULANG

Perbarui setiap statistik menggunakan laju sampel terbarunya.

Menggunakan RESAMPLE dapat menghasilkan pemindaian tabel penuh. Misalnya, statistik untuk indeks menggunakan pemindaian tabel penuh untuk laju sampelnya. Ketika tidak ada opsi sampel (SAMPLE, FULLSCAN, RESAMPLE) yang ditentukan, pengoptimal kueri mengambil sampel data dan menghitung ukuran sampel secara default.

Di Gudang di Microsoft Fabric, RESAMPLE tidak didukung.

PERSIST_SAMPLE_PERCENT = { AKTIF | NONAKTIF }

Berlaku untuk: Paket Layanan SQL Server 2016 (13.x) 1 CU4, SQL Server 2017 (14.x) Paket Layanan 1, atau SQL Server 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Managed Instance

Ketika AKTIF, statistik akan mempertahankan persentase pengambilan sampel yang ditetapkan untuk pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel. Saat NONAKTIF, persentase pengambilan sampel statistik akan direset ke pengambilan sampel default dalam pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel. Defaultnya adalah NONAKTIF.

DBCC SHOW_STATISTICS dan sys.dm_db_stats_properties mengekspos nilai persentase sampel yang bertahan untuk statistik yang dipilih.

Jika AUTO_UPDATE_STATISTICS dijalankan, ia menggunakan persentase pengambilan sampel yang bertahan jika tersedia, atau menggunakan persentase pengambilan sampel default jika tidak. Perilaku RESAMPLE tidak terpengaruh oleh opsi ini.

Jika tabel dipotong, semua statistik yang dibangun di atas tumpukan terpotong atau pohon B (HoBT) akan kembali menggunakan persentase pengambilan sampel default.

Catatan

Di SQL Server, saat membangun ulang indeks yang sebelumnya memiliki statistik yang diperbarui dengan PERSIST_SAMPLE_PERCENT, persentase sampel yang bertahan diatur ulang kembali ke default. Dimulai dengan SQL Server 2016 (13.x) SP2 CU17, SQL Server 2017 (14.x) CU26, dan SQL Server 2019 (15.x) CU10, persentase sampel yang bertahan disimpan bahkan saat membangun kembali indeks.

ON PARTITIONS ( { <partition_number> | <range> } [, ... n] ) ]

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

Memaksa statistik tingkat daun yang mencakup partisi yang ditentukan dalam klausa ON PARTITIONS untuk dikomputasi ulang, dan kemudian digabungkan untuk membangun statistik global. WITH RESAMPLE diperlukan karena statistik partisi yang dibangun dengan laju sampel yang berbeda tidak dapat digabungkan bersama-sama.

SEMUA | KOLOM | INDEKS

Perbarui semua statistik, statistik yang ada yang dibuat pada satu atau beberapa kolom, atau statistik yang dibuat untuk indeks. Jika tidak ada opsi yang ditentukan, pernyataan UPDATE STATISTICS memperbarui semua statistik pada tabel atau tampilan terindeks.

NORECOMPUTE

Nonaktifkan opsi pembaruan statistik otomatis, AUTO_UPDATE_STATISTICS, untuk statistik yang ditentukan. Jika opsi ini ditentukan, pengoptimal kueri menyelesaikan pembaruan statistik ini dan menonaktifkan pembaruan di masa mendatang.

Untuk mengaktifkan kembali perilaku opsi AUTO_UPDATE_STATISTICS, jalankan PERBARUI STATISTIK lagi tanpa opsi NORECOMPUTE atau jalankan sp_autostats.

Peringatan

Menggunakan opsi ini dapat menghasilkan rencana kueri suboptimal. Sebaiknya gunakan opsi ini dengan hemat, lalu hanya oleh administrator sistem yang memenuhi syarat.

Untuk informasi selengkapnya tentang opsi AUTO_STATISTICS_UPDATE, lihat Opsi UBAH SET DATABASE.

INKREMENTAL = { ON | NONAKTIF }

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

Ketika AKTIF, statistik dibuat ulang sesuai statistik partisi. Ketika NONAKTIF, pohon statistik dihilangkan dan SQL Server menghitung ulang statistik. Defaultnya adalah NONAKTIF.

Jika statistik per partisi tidak didukung, kesalahan akan 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.

MAXDOP = max_degree_of_parallelism

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x) SP2 dan SQL Server 2017 (14.x) CU3).

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama durasi operasi statistik. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme. Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

max_degree_of_parallelism dapat berupa:

1
Menekan pembuatan rencana paralel.

>1 Membatasi jumlah maksimum prosesor yang digunakan dalam operasi statistik paralel ke angka yang ditentukan atau lebih sedikit berdasarkan beban kerja sistem saat ini.

0 (default)
Menggunakan jumlah prosesor aktual atau lebih sedikit berdasarkan beban kerja sistem saat ini.

update_stats_stream_option

Diidentifikasi hanya untuk tujuan informasi. Tidak didukung. Kompatibilitas di masa mendatang tidak dijamin.

AUTO_DROP = { AKTIF | NONAKTIF }

Berlaku untuk: SQL Server 2022 (16.x) dan yang lebih baru.

Saat ini, jika statistik dibuat oleh alat pihak ketiga pada database pelanggan, objek statistik tersebut dapat memblokir atau mengganggu perubahan skema yang mungkin diinginkan pelanggan.

(Dimulai dengan SQL Server 2022 (16.x))| Fitur ini memungkinkan pembuatan objek statistik dalam mode sehingga perubahan skema tidak akan diblokir oleh statistik, tetapi sebaliknya statistik akan dihilangkan. Dengan cara ini, statistik penurunan otomatis berulah seperti statistik yang dibuat secara otomatis.

Catatan

Mencoba mengatur atau membatalkan pengaturan properti Auto_Drop 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.

Keterangan

Kapan HARUS MEMPERBARUI STATISTIK

Untuk informasi selengkapnya tentang kapan harus menggunakan UPDATE STATISTICS, lihat Kapan harus memperbarui statistik.

Pembatasan

  • Memperbarui statistik tidak didukung pada tabel eksternal. Untuk memperbarui statistik pada tabel eksternal, letakkan dan buat ulang statistik.
  • Opsi MAXDOP tidak kompatibel dengan STATS_STREAMopsi , ROWCOUNT dan PAGECOUNT .
  • Opsi MAXDOP dibatasi oleh pengaturan grup MAX_DOP beban kerja Resource Governor, jika digunakan.

Memperbarui semua statistik dengan sp_updatestats

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

EXEC sp_updatestats;

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.

Menentukan Pembaruan Statistik Terakhir

Untuk menentukan kapan statistik terakhir diperbarui, gunakan fungsi STATS_DATE .

PDW / Azure Synapse Analytics

Sintaks berikut tidak didukung oleh Analytics Platform System (PDW) / Azure Synapse Analytics:

UPDATE STATISTICS t1 (a,b);   

UPDATE STATISTICS t1 (a) WITH SAMPLE 10 ROWS;  

UPDATE STATISTICS t1 (a) WITH NORECOMPUTE;  

UPDATE STATISTICS t1 (a) WITH INCREMENTAL = ON;  

UPDATE STATISTICS t1 (a) WITH stats_stream = 0x01;

Izin

ALTER Memerlukan izin pada tabel atau tampilan.

Contoh

J. Memperbarui semua statistik pada tabel

Contoh berikut memperbarui semua statistik pada SalesOrderDetail tabel.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail;  
GO

B. Memperbarui statistik untuk indeks

Contoh berikut memperbarui statistik untuk AK_SalesOrderDetail_rowguid indeks SalesOrderDetail tabel.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;  
GO

C. Memperbarui statistik dengan menggunakan pengambilan sampel 50 persen

Contoh berikut membuat lalu memperbarui statistik untuk Name kolom dan ProductNumber dalam Product tabel.

USE AdventureWorks2022;
GO  
CREATE STATISTICS Products
    ON Production.Product ([Name], ProductNumber)
    WITH SAMPLE 50 PERCENT
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS Production.Product(Products)
    WITH SAMPLE 50 PERCENT;

D. Memperbarui statistik dengan menggunakan FULLSCAN dan NORECOMPUTE

Contoh berikut memperbarui Products statistik dalam Product tabel, memaksa pemindaian penuh semua baris dalam Product tabel, dan menonaktifkan statistik otomatis untuk Products statistik.

USE AdventureWorks2022;  
GO  
UPDATE STATISTICS Production.Product(Products)  
    WITH FULLSCAN, NORECOMPUTE;  
GO

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Memperbarui statistik pada tabel

Contoh berikut memperbarui CustomerStats1 statistik pada Customer tabel.

UPDATE STATISTICS Customer (CustomerStats1);

F. Memperbarui statistik dengan menggunakan pemindaian penuh

Contoh berikut memperbarui CustomerStats1 statistik, berdasarkan pemindaian semua baris dalam Customer tabel.

UPDATE STATISTICS Customer (CustomerStats1) WITH FULLSCAN;

G. Memperbarui semua statistik pada tabel

Contoh berikut memperbarui semua statistik pada Customer tabel.

UPDATE STATISTICS Customer;

H. Menggunakan CREATE STATISTICS dengan AUTO_DROP

Untuk menggunakan statistik penurunan otomatis, cukup tambahkan yang berikut ini ke klausa "WITH" dari statistik buat atau perbarui.

UPDATE STATISTICS Customer (CustomerStats1) WITH AUTO_DROP = ON

Konten terkait