MEMBUAT STATISTIK (Transact-SQL)

Artikel
05/21/2024

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL di Microsoft Fabric Warehouse 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 memodifikasi desain kueri untuk meningkatkan performa kueri.

Untuk mempelajari lebih lanjut, lihat Statistik.

Catatan

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

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, dan Azure SQL Managed Instance.

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

Sintaks untuk Azure Synapse Analytics dan Analytics Platform System (PDW).

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 | = | <> | != | > | >= | !> | < | <= | !<

Sintaks untuk 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
      }
    ]
[ ; ]

Argumen

statistics_name

Nama statistik yang akan dibuat.

table_or_indexed_view_name

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 ARITHABORT pengaturan database dan QUOTED_IDENTIFIER adalah ON.
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 jenis data spasial, atau kolom tipe data hierarkiID . Perbandingan menggunakan NULL literal tidak diizinkan dengan operator perbandingan. IS NULL Gunakan operator 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 yang difilter.

FULLSCAN

Berlaku untuk: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1, dan versi yang lebih baru

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

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 kolom FULLSCAN tunggal dan kolom SAMPLEtunggal yang didukung. Ketika tidak ada opsi yang disertakan, SAMPLE statistik 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 ROWS, angka bisa dari 0 hingga jumlah total baris. 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 ditentukan 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. Saat SAMPLE atau 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 kolom FULLSCAN tunggal dan kolom SAMPLEtunggal yang didukung. Ketika tidak ada opsi yang disertakan, FULLSCAN statistik 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. Ketika OFF, persentase pengambilan sampel statistik diatur ulang ke pengambilan sampel default dalam pembaruan berikutnya yang tidak secara eksplisit menentukan persentase pengambilan sampel. Default adalah OFF.

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_UPDATEuntuk 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 NORECOMPUTE tanpa opsi .

Peringatan

Jika Anda menonaktifkan pembaruan statistik otomatis, mungkin mencegah Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri yang melibatkan tabel. Anda harus menggunakan opsi ini dengan hemat, dan hanya oleh administrator database yang memenuhi syarat.

Untuk informasi selengkapnya tentang opsi ini AUTO_STATISTICS_UPDATE , lihat OPSI UBAH KUMPULAN DATABASE. Untuk informasi selengkapnya tentang menonaktifkan dan mengaktifkan kembali pembaruan statistik, lihat Statistik.

INKREMENTAL = { ON | NONAKTIF }

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

Ketika ON, statistik yang dibuat adalah statistik per partisi. Ketika OFF, statistik digabungkan untuk semua partisi. Default adalah OFF.

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 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3, dan versi yang lebih baru

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama operasi statistik. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat paralelisme maksimum (opsi konfigurasi server). 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 indeks paralel ke angka yang ditentukan.
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 versi yang lebih baru, dan Azure SQL Database, Azure SQL Managed Instance

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), AUTO_DROP opsi diaktifkan secara default pada semua database baru dan yang dimigrasikan. Properti AUTO_DROP memungkinkan pembuatan objek statistik dalam mode sehingga perubahan skema berikutnya tidak diblokir oleh objek statistik, tetapi sebaliknya statistik dihilangkan seperlunya. Dengan cara ini, statistik yang dibuat secara manual dengan AUTO_DROP perilaku yang diaktifkan berpura-pura seperti statistik yang dibuat secara otomatis.

Catatan

Mencoba mengatur atau membatalkan pengaturan properti Auto_Drop pada statistik yang dibuat secara otomatis mungkin menimbulkan kesalahan. Statistik yang dibuat secara otomatis selalu menggunakan penurunan otomatis. Beberapa cadangan, saat 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 AUTO_DROP untuk dijalankan sp_updatestats pada database, mengatur metadata yang tepat untuk fitur 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 harus 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. Anda tidak dapat menghilangkan, mengganti nama, atau mengubah definisi kolom tabel yang ditentukan dalam predikat statistik yang difilter.

Batasan

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 STATS_STREAMopsi , , ROWCOUNTdan PAGECOUNT .
Opsi MAXDOP dibatasi oleh pengaturan grup MAX_DOP beban kerja Resource Governor, jika digunakan.
CREATE dan DROP STATISTICS pada tabel eksternal tidak didukung di Azure SQL Database.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

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. CREATE dan DROP STATISTICS 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;

Bagikan melalui

MEMBUAT STATISTIK (Transact-SQL)

Sintaks

Argumen

statistics_name

table_or_indexed_view_name

kolom [ ,... n ]

WHERE <filter_predicate>

FULLSCAN

Nomor SAMPEL { PERSEN | BARIS }

PERSIST_SAMPLE_PERCENT = { AKTIF | NONAKTIF }

STATS_STREAM = stats_stream

NORECOMPUTE

INKREMENTAL = { ON | NONAKTIF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { AKTIF | NONAKTIF }

Izin

Keterangan

Statistik untuk tabel eksternal

Statistik dengan kondisi yang difilter

Kapan menggunakan CREATE STATISTICS

Dependensi referensi untuk statistik yang difilter

Batasan

Contoh

J. Gunakan CREATE STATISTICS dengan PERSEN nomor SAMPEL

B. Gunakan CREATE STATISTICS dengan FULLSCAN dan NORECOMPUTE

C. Gunakan CREATE STATISTICS untuk membuat statistik yang difilter

D. Membuat statistik pada tabel eksternal

E. Gunakan CREATE STATISTICS dengan FULLSCAN dan PERSIST_SAMPLE_PERCENT

Contoh menggunakan database AdventureWorksDW

F. Membuat statistik pada dua kolom

G. Membuat statistik dengan menggunakan pemindaian penuh

H. Membuat statistik dengan menentukan persentase sampel

I. Gunakan CREATE STATISTICS dengan AUTO_DROP

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

MEMBUAT STATISTIK (Transact-SQL)

Sintaks

Argumen

statistics_name

table_or_indexed_view_name

kolom [ ,... n ]

WHERE <filter_predicate>

FULLSCAN

Nomor SAMPEL { PERSEN | BARIS }

PERSIST_SAMPLE_PERCENT = { AKTIF | NONAKTIF }

STATS_STREAM = stats_stream

NORECOMPUTE

INKREMENTAL = { ON | NONAKTIF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { AKTIF | NONAKTIF }

Izin

Keterangan

Statistik untuk tabel eksternal

Statistik dengan kondisi yang difilter

Kapan menggunakan CREATE STATISTICS

Dependensi referensi untuk statistik yang difilter

Batasan

Contoh

J. Gunakan CREATE STATISTICS dengan PERSEN nomor SAMPEL

B. Gunakan CREATE STATISTICS dengan FULLSCAN dan NORECOMPUTE

C. Gunakan CREATE STATISTICS untuk membuat statistik yang difilter

D. Membuat statistik pada tabel eksternal

E. Gunakan CREATE STATISTICS dengan FULLSCAN dan PERSIST_SAMPLE_PERCENT

Contoh menggunakan database AdventureWorksDW

F. Membuat statistik pada dua kolom

G. Membuat statistik dengan menggunakan pemindaian penuh

H. Membuat statistik dengan menentukan persentase sampel

I. Gunakan CREATE STATISTICS dengan AUTO_DROP

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: