MEMBUAT STATISTIK (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticstitik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric

Membuat statistik pengoptimalan kueri pada satu atau beberapa kolom tabel, tampilan terindeks, atau tabel eksternal. Untuk sebagian besar kueri, pengoptimal kueri sudah menghasilkan statistik yang diperlukan untuk rencana kueri berkualitas tinggi; dalam beberapa kasus, Anda perlu membuat statistik tambahan dengan CREATE STATISTICS atau mengubah desain kueri untuk meningkatkan performa kueri.

Untuk mempelajari lebih lanjut, lihat 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
-- Create statistics on an external table

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WITH FULLSCAN ] ;
  
-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ ,...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
    
<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
-- Syntax for Microsoft Fabric
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_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.

Argumen

statistics_name

Adalah nama statistik yang akan dibuat.

table_or_indexed_view_name

Adalah nama tabel, tampilan terindeks, atau tabel eksternal untuk membuat statistik. Untuk membuat statistik pada database lain, tentukan nama tabel yang memenuhi syarat.

kolom [ ,... n]

Satu atau beberapa kolom yang akan disertakan dalam statistik. Kolom harus dalam urutan prioritas dari kiri ke kanan. Hanya kolom pertama yang digunakan untuk membuat histogram. Semua kolom digunakan untuk statistik korelasi lintas kolom yang disebut kepadatan.

Anda dapat menentukan kolom apa pun yang dapat ditentukan sebagai kolom kunci indeks dengan pengecualian berikut:

  • Kolom XML, teks lengkap, dan FILESTREAM tidak dapat ditentukan.

  • Kolom komputasi hanya dapat ditentukan jika pengaturan database ARITHABORT dan QUOTED_IDENTIFIER AKTIF.

  • Kolom jenis yang ditentukan pengguna CLR dapat ditentukan jika jenis mendukung pengurutan biner. Kolom komputasi yang didefinisikan sebagai pemanggilan metode kolom jenis yang ditentukan pengguna dapat ditentukan jika metode ditandai deterministik.

WHERE <filter_predicate>

Menentukan ekspresi untuk memilih subset baris yang akan disertakan saat membuat objek statistik. Statistik yang dibuat dengan predikat filter disebut statistik yang difilter. Predikat filter menggunakan logika perbandingan sederhana dan tidak dapat mereferensikan kolom komputasi, kolom UDT, kolom tipe data spasial, atau kolom tipe data hierarkiID . Perbandingan menggunakan literal NULL tidak diizinkan dengan operator perbandingan. Gunakan operator IS NULL dan IS NOT NULL sebagai gantinya.

Berikut adalah beberapa contoh predikat filter untuk Production.BillOfMaterials tabel:

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
  • WHERE ComponentID IN (533, 324, 753)
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Untuk informasi selengkapnya tentang predikat filter, lihat Membuat Indeks Terfilter.

FULLSCAN

Berlaku untuk: SQL Server 2016 (13.x) (dimulai dengan SQL Server 2016 (13.x) SP1 CU4) dan yang lebih baru (dimulai dengan SQL Server 2017 (14.x) CU1)

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

Saat dihilangkan, SQL Server menggunakan pengambilan sampel untuk membuat statistik, dan menentukan ukuran sampel yang diperlukan untuk membuat rencana kueri berkualitas tinggi.

Di Gudang di Microsoft Fabric, hanya statistik berbasis FULLSCAN kolom tunggal dan SAMPEL kolom tunggal yang didukung. Ketika tidak ada opsi yang disertakan, statistik FULLSCAN dibuat.

Nomor SAMPEL { PERSEN | BARIS }

Menentukan perkiraan persentase atau jumlah baris dalam tabel atau tampilan terindeks untuk digunakan pengoptimal kueri saat membuat 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.

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

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 menentukan 0 PERCENT atau 0 ROWS. Ketika 0 PERCENT atau 0 ROWS ditentukan, objek statistik dibuat, tetapi tidak berisi data statistik.

Di Gudang di Microsoft Fabric, hanya statistik berbasis FULLSCAN kolom tunggal dan SAMPEL kolom tunggal yang didukung. Ketika tidak ada opsi yang disertakan, statistik FULLSCAN dibuat.

PERSIST_SAMPLE_PERCENT = { AKTIF | NONAKTIF }

Ketika ON, statistik mempertahankan persentase pengambilan sampel pembuatan untuk pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel. Saat NONAKTIF, persentase pengambilan sampel statistik akan diatur ulang ke pengambilan sampel default dalam pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel. Defaultnya adalah NONAKTIF.

Catatan

Jika tabel dipotong, semua statistik yang dibangun di HoBT yang dipotong akan kembali menggunakan persentase pengambilan sampel default.

STATS_STREAM = stats_stream

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

NORECOMPUTE

Nonaktifkan opsi pembaruan statistik otomatis, AUTO_STATISTICS_UPDATE, untuk statistics_name. Jika opsi ini ditentukan, pengoptimal kueri akan menyelesaikan pembaruan statistik yang sedang berlangsung untuk statistics_name dan menonaktifkan pembaruan di masa mendatang.

Untuk mengaktifkan kembali pembaruan statistik, hapus statistik dengan DROP STATISTICS lalu jalankan CREATE STATISTICS tanpa opsi NORECOMPUTE.

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 MENGUBAH Opsi SET DATABASE (Transact-SQL). Untuk informasi selengkapnya tentang menonaktifkan dan mengaktifkan kembali pembaruan statistik, lihat Statistik.

INKREMENTAL = { ON | NONAKTIF }

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

Saat AKTIF, statistik yang dibuat adalah statistik per partisi. Saat NONAKTIF, statistik digabungkan untuk semua partisi. 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 jumlah 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: Azure SQL Database, Azure SQL Managed Instance, dan dimulai dengan SQL Server 2022 (16.x)

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 AUTO_DROP 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 dihilangkan seperlunya. Dengan cara ini, statistik yang dibuat secara manual dengan AUTO_DROP diaktifkan 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. 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 AUTO_DROP statistik.

Untuk informasi selengkapnya, lihat opsi AUTO_DROP.

Izin

Memerlukan salah satu izin ini:

  • ALTER TABLE
  • Pengguna adalah pemilik tabel
  • Keanggotaan dalam peran database tetap db_ddladmin

Keterangan

SQL Server dapat menggunakan tempdb untuk mengurutkan baris sampel sebelum membangun statistik.

Statistik untuk tabel eksternal

Saat membuat statistik tabel eksternal, SQL Server mengimpor tabel eksternal ke dalam tabel SQL Server sementara, lalu membuat statistik. Untuk statistik sampel, hanya baris sampel yang diimpor. Jika Anda memiliki tabel eksternal besar, lebih cepat menggunakan pengambilan sampel default alih-alih opsi pemindaian penuh.

Saat tabel eksternal menggunakan DELIMITEDTEXT, , CSV, PARQUETatau DELTA sebagai jenis data, tabel eksternal hanya mendukung statistik untuk satu kolom per CREATE STATISTICS perintah.

Statistik dengan kondisi 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 dalam klausa WHERE untuk memilih subset data yang disertakan dalam statistik.

Kapan menggunakan CREATE STATISTICS

Untuk informasi selengkapnya tentang kapan menggunakan CREATE STATISTICS, lihat Statistik.

Dependensi referensi untuk statistik yang difilter

Tampilan katalog sys.sql_expression_dependencies melacak setiap kolom dalam predikat statistik yang difilter sebagai dependensi referensi. Pertimbangkan operasi yang Anda lakukan pada kolom tabel sebelum membuat statistik yang difilter karena Anda tidak dapat menghilangkan, mengganti nama, atau mengubah definisi kolom tabel yang ditentukan dalam predikat statistik yang difilter.

Batasan dan Pembatasan

  • Memperbarui statistik tidak didukung pada tabel eksternal. Untuk memperbarui statistik pada tabel eksternal, letakkan dan buat ulang statistik.
  • Anda dapat mencantumkan hingga 64 kolom per objek statistik.
  • Opsi MAXDOP tidak kompatibel dengan opsi STATS_STREAM, ROWCOUNT, dan PAGECOUNT.
  • Opsi MAXDOP dibatasi oleh pengaturan MAX_DOP grup beban kerja Resource Governor, jika digunakan.
  • STATISTIK CREATE dan DROP pada tabel eksternal tidak didukung di Azure SQL Database.

Contoh

Contoh menggunakan AdventureWorks database.

J. Gunakan CREATE STATISTICS dengan PERSEN nomor SAMPEL

Contoh berikut membuat ContactMail1 statistik, menggunakan sampel acak 5 persen dari BusinessEntityID kolom Person dan EmailPromotion tabel database AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Gunakan CREATE STATISTICS dengan FULLSCAN dan NORECOMPUTE

Contoh berikut membuat NamePurchase statistik untuk semua baris di BusinessEntityID kolom Person dan EmailPromotion tabel dan menonaktifkan komputasi ulang statistik otomatis.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Gunakan CREATE STATISTICS untuk membuat statistik yang difilter

Contoh berikut membuat statistik ContactPromotion1yang difilter . Mesin Database mengambil sampel 50 persen dari data lalu memilih baris dengan sama dengan EmailPromotion 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Membuat statistik pada tabel eksternal

Satu-satunya keputusan yang perlu Anda buat saat membuat statistik pada tabel eksternal, selain menyediakan daftar kolom, adalah apakah akan membuat statistik dengan mengambil sampel baris atau dengan memindai semua baris. STATISTIK CREATE dan DROP pada tabel eksternal tidak didukung di Azure SQL Database.

Karena SQL Server mengimpor data dari tabel eksternal ke dalam tabel sementara untuk membuat statistik, opsi pemindaian penuh membutuhkan waktu lebih lama. Untuk tabel besar, metode pengambilan sampel default biasanya cukup.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
  
--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Gunakan CREATE STATISTICS dengan FULLSCAN dan PERSIST_SAMPLE_PERCENT

Contoh berikut membuat NamePurchase statistik untuk semua baris di BusinessEntityID kolom Person dan EmailPromotion tabel dan menetapkan persentase pengambilan sampel 100 persen untuk semua pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Contoh menggunakan database AdventureWorksDW

F. Membuat statistik pada dua kolom

Contoh berikut membuat CustomerStats1 statistik, berdasarkan CustomerKey kolom DimCustomer dan EmailAddress tabel. Statistik dibuat berdasarkan pengambilan sampel baris yang signifikan secara statistik dalam Customer tabel.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Membuat statistik dengan menggunakan pemindaian penuh

Contoh berikut membuat CustomerStatsFullScan statistik, berdasarkan pemindaian semua baris dalam DimCustomer tabel.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Membuat statistik dengan menentukan persentase sampel

Contoh berikut membuat CustomerStatsSampleScan statistik, berdasarkan pemindaian 50 persen baris dalam DimCustomer tabel.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Gunakan CREATE STATISTICS dengan AUTO_DROP

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

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) 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;

Langkah berikutnya