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.
Disediakan dalam artikel ini adalah rekomendasi dan contoh untuk membuat dan memperbarui statistik pengoptimalan kueri menggunakan sumber daya Synapse SQL: kumpulan SQL khusus dan kumpulan SQL tanpa server.
Statistik dalam kumpulan SQL khusus
Mengapa menggunakan statistik
Semakin banyak kumpulan SQL khusus yang tahu tentang data Anda, semakin cepat ia dapat menjalankan kueri. Setelah memuat data ke dalam kumpulan SQL khusus, mengumpulkan statistik pada data Anda adalah salah satu hal terpenting yang dapat Anda lakukan untuk pengoptimalan kueri.
Pengoptimal kueri kumpulan SQL khusus adalah pengoptimal berbasis biaya. Pengoptimal membandingkan biaya berbagai rencana kueri, dan kemudian memilih rencana dengan biaya terendah. Dalam kebanyakan kasus, ia memilih rencana yang akan dieksekusi paling cepat.
Misalnya, jika pengoptimal memperkirakan bahwa tanggal yang akan digunakan untuk memfilter kueri Anda akan mengembalikan satu baris, pengoptimal akan memilih satu rencana. Jika memperkirakan bahwa tanggal yang dipilih akan mengembalikan 1 juta baris, maka akan mengembalikan rencana yang berbeda.
Pembuatan statistik otomatis
Mesin kumpulan SQL khusus akan menganalisis kueri pengguna masuk untuk statistik yang hilang ketika opsi AUTO_CREATE_STATISTICS database diatur ke ON. Jika statistik tidak ada, pengoptimal kueri membuat statistik pada kolom individual dalam predikat kueri atau kondisi gabungan.
Fungsi ini digunakan untuk meningkatkan perkiraan kardinalitas untuk rencana kueri.
Penting
Pembuatan statistik otomatis saat ini diaktifkan secara default.
Anda dapat memeriksa apakah gudang data Anda telah terkonfigurasi AUTO_CREATE_STATISTICS dengan menjalankan perintah berikut:
SELECT name, is_auto_create_stats_on
FROM sys.databases
Jika gudang data Anda tidak mengaktifkan AUTO_CREATE_STATISTICS, kami sarankan Anda mengaktifkan properti ini dengan menjalankan perintah berikut:
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Pernyataan ini akan memicu pembuatan statistik otomatis:
- PILIH
- INSERT-SELECT
- CTAS
- Pembaruan
- MENGHAPUS
- EXPLAIN ketika berisi gabungan atau ketika keberadaan predikat terdeteksi
Nota
Pembuatan statistik otomatis tidak dihasilkan pada tabel sementara atau eksternal.
Pembuatan statistik otomatis dilakukan secara sinkron. Jadi, Anda mungkin mengalami sedikit penurunan performa kueri jika kolom Anda kehilangan statistik. Waktu untuk membuat statistik untuk satu kolom tergantung pada ukuran tabel.
Untuk menghindari penurunan kinerja yang terukur, Anda harus memastikan statistik telah dibuat terlebih dahulu dengan menjalankan beban kerja tolok ukur sebelum memprofilkan sistem.
Nota
Pembuatan statistik akan dicatat dalam sys.dm_pdw_exec_requests di bawah konteks pengguna yang berbeda.
Saat statistik otomatis dibuat, mereka akan berbentuk: WA_Sys<ID kolom 8 digit dalam format Hex>_<ID tabel 8 digit dalam format Hex>. Anda dapat melihat statistik yang sudah dibuat dengan menjalankan perintah SHOW_STATISTICS DBCC :
DBCC SHOW_STATISTICS (<table_name>, <target>)
table_name adalah nama tabel yang berisi statistik yang akan ditampilkan, yang tidak dapat menjadi tabel eksternal. Targetnya adalah nama indeks target, statistik, atau kolom yang akan menampilkan informasi statistik.
Perbarui statistik
Salah satu praktik terbaik adalah memperbarui statistik pada kolom tanggal setiap hari saat tanggal baru ditambahkan. Setiap kali baris baru dimuat ke dalam gudang data, tanggal pemuatan baru, atau tanggal transaksi ditambahkan. Penambahan ini mengubah distribusi data dan menjadikan statistik kedaluwarsa.
Statistik pada kolom negara atau wilayah dalam tabel pelanggan mungkin tidak perlu diperbarui karena distribusi nilai biasanya tidak berubah. Dengan asumsi distribusi konstan antara pelanggan, menambahkan baris baru ke variasi tabel tidak akan mengubah distribusi data.
Namun, ketika gudang data Anda hanya berisi satu negara atau wilayah dan Anda membawa data dari negara atau wilayah baru, maka Anda perlu memperbarui statistik di kolom negara atau wilayah.
Berikut ini adalah rekomendasi untuk memperbarui statistik:
| Tipe | Rekomendasi |
|---|---|
| Frekuensi pembaruan statistik | Konservatif: Setiap hari Setelah memuat atau mengubah data Anda |
| Pengambilan sampel | Kurang dari 1 miliar baris, gunakan pengambilan sampel default (20 persen).
Dengan lebih dari 1 miliar baris, gunakan pengambilan sampel dua persen. |
Menentukan pembaruan statistik terakhir
Salah satu pertanyaan pertama yang diajukan saat Anda memecahkan masalah kueri adalah, "Apakah statistik sudah diperbarui?"
Pertanyaan ini bukan pertanyaan yang dapat dijawab berdasarkan usia data. Objek statistik terbaru mungkin sudah tua jika tidak ada perubahan material pada data yang mendasarinya. Ketika jumlah baris telah berubah secara substansial, atau perubahan material dalam distribusi nilai untuk kolom terjadi, maka saatnya untuk memperbarui statistik.
Tidak ada tampilan manajemen dinamis yang tersedia untuk menentukan apakah data dalam tabel telah berubah sejak terakhir kali statistik diperbarui. Mengetahui usia statistik Anda dapat memberi Anda sebagian gambaran.
Anda bisa menggunakan kueri berikut untuk menentukan terakhir kali statistik Anda diperbarui pada setiap tabel.
Nota
Jika ada perubahan material dalam distribusi nilai untuk kolom, Anda harus memperbarui statistik terlepas dari terakhir kali nilai tersebut diperbarui.
SELECT
sm.[name] AS [schema_name],
tb.[name] AS [table_name],
co.[name] AS [stats_column_name],
st.[name] AS [stats_name],
STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
sys.objects ob
JOIN sys.stats st
ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns sc
ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns co
ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types ty
ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables tb
ON co.[object_id] = tb.[object_id]
JOIN sys.schemas sm
ON tb.[schema_id] = sm.[schema_id]
WHERE
st.[user_created] = 1;
Kolom tanggal di gudang data, misalnya, biasanya memerlukan pembaruan statistik yang sering. Setiap kali baris baru dimuat ke dalam gudang data, tanggal pemuatan baru, atau tanggal transaksi ditambahkan. Penambahan ini mengubah distribusi data dan menjadikan statistik kedaluwarsa.
Statistik pada kolom jenis kelamin dalam tabel pelanggan mungkin tidak perlu diperbarui. Dengan asumsi distribusi konstan antara pelanggan, menambahkan baris baru ke variasi tabel tidak akan mengubah distribusi data.
Tetapi, jika gudang data Anda hanya berisi satu jenis kelamin dan persyaratan baru menghasilkan beberapa jenis kelamin, maka Anda perlu memperbarui statistik pada kolom jenis kelamin.
Untuk informasi lebih lanjut, tinjau artikel Statistik .
Mengimplementasikan manajemen statistik
Sebaiknya Anda memperluas proses pemuatan data untuk memastikan bahwa statistik diperbarui pada akhir proses pemuatan. Pemuatan data adalah saat tabel paling sering mengubah ukuran, distribusi nilai, atau keduanya. Dengan demikian, proses pemuatan adalah tempat yang logis untuk mengimplementasikan beberapa proses manajemen.
Prinsip panduan berikut disediakan untuk memperbarui statistik Anda selama proses pemuatan:
- Pastikan bahwa setiap tabel yang dimuat memiliki setidaknya satu objek statistik yang diperbarui. Proses ini memperbarui informasi ukuran tabel (jumlah baris dan jumlah halaman) sebagai bagian dari pembaruan statistik.
- Fokus pada kolom yang berpartisipasi dalam klausul JOIN, GROUP BY, ORDER BY, dan DISTINCT.
- Pertimbangkan untuk lebih sering memperbarui kolom "kunci urutan naik" seperti tanggal transaksi, karena nilai-nilai penting ini tidak akan disertakan dalam histogram statistik.
- Pertimbangkan untuk memperbarui kolom distribusi statis dengan lebih jarang.
- Ingat, setiap objek statistik diperbarui secara berurutan. Menerapkan
UPDATE STATISTICS <TABLE_NAME>tidak selalu ideal, terutama untuk tabel yang lebar dengan banyak objek statistik.
Untuk informasi selengkapnya, lihat Estimasi Kardinalitas .
Contoh: Membuat statistik
Contoh-contoh ini menunjukkan cara menggunakan bermacam opsi untuk membuat statistik. Opsi yang Anda gunakan untuk setiap kolom bergantung pada karakteristik data Anda dan bagaimana kolom akan digunakan dalam kueri.
Membuat statistik kolom tunggal dengan opsi default
Untuk membuat statistik pada kolom, berikan nama untuk objek statistik dan nama kolom. Sintaks ini menggunakan semua opsi default. Secara default, kumpulan SQL khusus mengambil sampel 20 persen dari tabel saat membuat statistik.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Contohnya:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1);
Membuat statistik kolom tunggal dengan memeriksa setiap baris
Tingkat pengambilan sampel default sebesar 20 persen cukup untuk sebagian besar situasi. Namun, Anda dapat menyesuaikan laju pengambilan sampel. Untuk mengambil sampel tabel lengkap, gunakan sintaks ini:
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Contohnya:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Membuat statistik kolom tunggal dengan menentukan ukuran sampel
Opsi lain yang Anda miliki adalah menentukan ukuran sampel sebagai persen:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH SAMPLE 50 PERCENT;
Membuat statistik kolom tunggal hanya pada beberapa baris
Anda juga dapat membuat statistik pada sebagian baris dalam tabel Anda, yang disebut statistik yang difilter.
Misalnya, Anda dapat menggunakan statistik yang difilter saat berencana untuk mengkueri partisi tertentu dari tabel besar yang dipartisi. Dengan membuat statistik hanya pada nilai partisi, akurasi statistik akan meningkat. Anda juga akan mengalami peningkatan performa kueri.
Contoh ini membuat statistik pada rentang nilai. Nilai dapat dengan mudah ditentukan agar sesuai dengan rentang nilai dalam partisi.
CREATE STATISTICS stats_col1
ON table1(col1)
WHERE col1 > '2000101' AND col1 < '20001231';
Nota
Agar pengoptimal kueri mempertimbangkan menggunakan statistik yang difilter saat memilih paket kueri terdistribusi, kueri harus pas di dalam definisi objek statistik. Menggunakan contoh sebelumnya, klausa WHERE kueri perlu menentukan nilai col1 antara 2000101 dan 20001231.
Membuat statistik kolom tunggal dengan semua opsi
Anda juga dapat menggabungkan opsi bersama-sama. Contoh berikut membuat objek statistik yang difilter dengan ukuran sampel kustom:
CREATE STATISTICS stats_col1
ON table1 (col1)
WHERE col1 > '2000101' AND col1 < '20001231'
WITH SAMPLE 50 PERCENT;
Untuk referensi lengkapnya, lihat MEMBUAT STATISTIK.
Membuat statistik multi kolom
Untuk membuat objek statistik multi kolom, gunakan contoh sebelumnya, tapi tentukan kolom lainnya.
Nota
Histogram, yang digunakan untuk memperkirakan jumlah baris dalam hasil kueri, hanya tersedia untuk kolom pertama yang tercantum dalam definisi objek statistik.
Dalam contoh ini, histogram ada di product_category. Statistik lintas kolom dihitung pada product_category dan product_sub_category:
CREATE STATISTICS stats_2cols
ON table1 (product_category, product_sub_category)
WHERE product_category > '2000101' AND product_category < '20001231'
WITH SAMPLE 50 PERCENT;
Karena korelasi ada antara product_category dan product_sub_category, objek statistik multikolom dapat berguna jika kolom ini diakses secara bersamaan. Saat melakukan kueri pada tabel ini, statistik multikolom akan meningkatkan estimasi kardinalitas untuk gabungan, agregasi GROUP BY, jumlah berbeda, dan filter WHERE (selama kolom statistik utama termasuk dalam bagian dari filter).
Membuat statistik pada semua kolom dalam tabel
Salah satu cara untuk membuat statistik adalah dengan mengeluarkan perintah CREATE STATISTICS setelah membuat tabel:
CREATE TABLE dbo.table1
(
col1 int
, col2 int
, col3 int
)
WITH
(
CLUSTERED COLUMNSTORE INDEX
)
;
CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);
Menggunakan prosedur tersimpan untuk membuat statistik pada semua kolom dalam database
Kumpulan SQL tidak memiliki prosedur tersimpan sistem yang setara dengan sp_create_stats di SQL Server. Prosedur tersimpan ini membuat objek statistik kolom tunggal pada setiap kolom database yang belum memiliki statistik.
Contoh berikut akan membantu Anda memulai desain database Anda. Jangan ragu untuk menyesuaikannya dengan kebutuhan Anda:
CREATE PROCEDURE [dbo].[prc_sqldw_create_stats]
( @create_type tinyint -- 1 default, 2 Fullscan, 3 Sample
, @sample_pct tinyint
)
AS
IF @create_type IS NULL
BEGIN
SET @create_type = 1;
END;
IF @create_type NOT IN (1,2,3)
BEGIN
THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
DROP TABLE #stats_ddl;
END;
CREATE TABLE #stats_ddl
WITH ( DISTRIBUTION = HASH([seq_nmbr])
, LOCATION = USER_DB
)
AS
WITH T
AS
(
SELECT t.[name] AS [table_name]
, s.[name] AS [table_schema_name]
, c.[name] AS [column_name]
, c.[column_id] AS [column_id]
, t.[object_id] AS [object_id]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
FROM sys.[tables] t
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[columns] c ON t.[object_id] = c.[object_id]
LEFT JOIN sys.[stats_columns] l ON l.[object_id] = c.[object_id]
AND l.[column_id] = c.[column_id]
AND l.[stats_column_id] = 1
LEFT JOIN sys.[external_tables] e ON e.[object_id] = t.[object_id]
WHERE l.[object_id] IS NULL
AND e.[object_id] IS NULL -- not an external table
)
SELECT [table_schema_name]
, [table_name]
, [column_name]
, [column_id]
, [object_id]
, [seq_nmbr]
, CASE @create_type
WHEN 1
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
WHEN 2
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
WHEN 3
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
END AS create_stat_ddl
FROM T
;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
;
WHILE @i <= @t
BEGIN
SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Untuk membuat statistik pada semua kolom dalam tabel menggunakan opsi default, jalankan prosedur tersimpan.
EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;
Untuk membuat statistik pada semua kolom dalam tabel menggunakan fullscan, panggil prosedur ini:
EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;
Untuk membuat statistik sampel pada semua kolom dalam tabel, masukkan 3, dan persen sampel. Prosedur di bawah ini menggunakan laju sampel 20 persen.
EXEC [dbo].[prc_sqldw_create_stats] 3, 20;
Contoh: Memperbarui statistik
Untuk memperbarui statistik, Anda dapat:
- Memperbarui satu objek statistik. Menentukan nama objek statistik yang ingin Anda perbarui.
- Memperbarui semua objek statistik pada tabel. Menentukan nama tabel dan bukan satu objek statistik tertentu.
Memperbarui satu objek statistik tertentu
Gunakan sintaks berikut untuk memperbarui objek statistik tertentu:
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Contohnya:
UPDATE STATISTICS [dbo].[table1] ([stats_col1]);
Dengan memperbarui objek statistik tertentu, Anda dapat meminimalkan waktu dan sumber daya yang diperlukan untuk mengelola statistik. Tindakan ini memerlukan beberapa pemikiran untuk memilih objek statistik terbaik untuk diperbarui.
Memperbarui semua statistik pada tabel
Metode sederhana untuk memperbarui semua objek statistik pada tabel adalah:
UPDATE STATISTICS [schema_name].[table_name];
Contohnya:
UPDATE STATISTICS dbo.table1;
Pernyataan UPDATE STATISTICS mudah digunakan. Ingatlah bahwa itu memperbarui semua statistik pada tabel, meminta lebih banyak pekerjaan daripada yang diperlukan.
Jika performa bukan masalah, metode ini adalah cara term mudah dan paling lengkap untuk menjamin bahwa statistik sudah diperbarui.
Nota
Saat memperbarui semua statistik pada tabel, kumpulan SQL khusus melakukan pemindaian untuk mengambil sampel tabel untuk setiap objek statistik. Jika tabel besar dan memiliki banyak kolom dan banyak statistik, akan lebih efisien memperbarui statistik individual berdasarkan kebutuhan.
Untuk implementasi UPDATE STATISTICS prosedur, lihat Tabel sementara. Metode implementasinya sedikit berbeda dari prosedur CREATE STATISTICS sebelumnya, tetapi hasilnya sama.
Untuk sintaks lengkapnya, lihat Memperbarui statistik.
Metadata statistik
Ada beberapa tampilan dan fungsi sistem yang dapat Anda gunakan untuk menemukan informasi tentang statistik. Misalnya, Anda dapat melihat apakah objek statistik mungkin kedaluarsa dengan menggunakan fungsi STATS_DATE(). STATS_DATE() memungkinkan Anda untuk melihat kapan statistik terakhir dibuat atau diperbarui.
Tampilan katalog untuk statistik
Tampilan sistem ini menyediakan informasi tentang statistik:
| Tampilan katalog | Deskripsi |
|---|---|
| sys.columns | Satu baris untuk setiap kolom. |
| sys.objects | Satu baris untuk setiap objek dalam database. |
| sys.schemas | Satu baris untuk setiap skema dalam database. |
| sys.stats | Satu baris untuk setiap objek statistik. |
| sys.stats_columns | Satu baris untuk setiap kolom dalam objek statistik. Tautan kembali ke sys.columns. |
| sys.tables | Satu baris untuk setiap tabel (menyertakan tabel eksternal). |
| sys.table_types | Satu baris untuk setiap jenis data. |
Fungsi sistem untuk statistik
Fungsi sistem ini berguna untuk bekerja dengan statistik:
| Fungsi sistem | Deskripsi |
|---|---|
| STATS_DATE | Tanggal objek statistik terakhir diperbarui. |
| DBCC SHOW_STATISTICS | Tingkat ringkasan dan informasi terperinci tentang distribusi nilai seperti yang dipahami oleh objek statistik. |
Menggabungkan kolom dan fungsi statistik ke dalam satu tampilan
Tampilan ini menyautkan kolom yang terkait dengan statistik dan hasil dari fungsi STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
;
Contoh DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() menunjukkan data yang disimpan dalam objek statistik. Data ini muncul dalam tiga bagian:
- Kepala Halaman
- Vektor kepadatan
- Histogram
Header adalah metadata tentang statistik. Histogram menampilkan distribusi nilai di kolom kunci pertama dari objek statistik.
Vektor kepadatan mengukur korelasi lintas kolom. Kumpulan SQL khusus menghitung perkiraan kardinalitas dengan salah satu data dalam objek statistik.
Menampilkan header, kepadatan, dan histogram
Contoh sederhana ini menampilkan semua tiga bagian objek statistik:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Contohnya:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Memperlihatkan satu atau beberapa bagian dari DBCC SHOW_STATISTICS()
Jika Anda hanya tertarik untuk menampilkan bagian tertentu, gunakan klausa WITH dan tentukan bagian mana yang ingin Anda lihat:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
WITH stat_header, histogram, density_vector
Contohnya:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
Perbedaan dalam DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() diimplementasikan lebih ketat dalam kumpulan SQL khusus dibandingkan dengan SQL Server:
- Fitur yang tidak terdokumentasi tidak didukung.
- Tidak dapat menggunakan Stats_stream.
- Tidak dapat menggabungkan hasil untuk subset data statistik tertentu. Misalnya, STAT_HEADER JOIN DENSITY_VECTOR.
- NO_INFOMSGS tidak dapat diatur untuk penekanan pesan.
- Kurung siku di sekitar nama statistik tidak dapat digunakan.
- Tidak dapat menggunakan nama kolom untuk mengidentifikasi objek statistik.
- Pesan galat khusus 2767 tidak didukung.
Statistik dalam kumpulan SQL tanpa server
Statistik dibuat per kolom tertentu untuk himpunan data tertentu (jalur penyimpanan).
Nota
Statistik tidak dapat dibuat untuk kolom LOB.
Mengapa menggunakan statistik
Semakin banyak kumpulan SQL tanpa server yang tahu tentang data Anda, semakin cepat ia dapat menjalankan kueri terhadapnya. Mengumpulkan statistik pada data Anda adalah salah satu hal terpenting yang dapat Anda lakukan untuk mengoptimalkan kueri Anda.
Pengoptimal kueri kumpulan SQL tanpa server adalah pengoptimal berbasis biaya. Pengoptimal membandingkan biaya berbagai rencana kueri, dan kemudian memilih rencana dengan biaya terendah. Dalam kebanyakan kasus, ia memilih rencana yang akan dieksekusi paling cepat.
Misalnya, jika pengoptimal memperkirakan bahwa tanggal yang difilter oleh kueri Anda akan mengembalikan satu baris, maka pengoptimal akan memilih satu rencana. Jika diperkirakan bahwa tanggal yang dipilih akan mengembalikan 1 juta baris, maka akan memilih rencana yang berbeda.
Pembuatan statistik otomatis
Kumpulan SQL tanpa server menganalisis kueri pengguna masuk untuk statistik yang hilang. Jika statistik tidak tersedia, pengoptimal kueri membuat statistik pada kolom individual dalam predikat atau kondisi gabungan dalam kueri untuk meningkatkan perkiraan kardinalitas untuk rencana eksekusi kueri.
Pernyataan SELECT akan memicu pembuatan statistik secara otomatis.
Nota
Untuk pembuatan otomatis pengambilan sampel statistik digunakan dan dalam banyak kasus persentase pengambilan sampel akan kurang dari 100%. Alur ini sama untuk setiap format file. Perlu diingat bahwa ketika membaca CSV dengan versi parser 1.0, pengambilan sampel tidak didukung dan pembuatan statistik otomatis tidak dilakukan jika persentase pengambilan sampel kurang dari 100%. Untuk tabel kecil dengan perkiraan kardinalitas rendah (jumlah baris) pembuatan statistik otomatis akan dipicu dengan persentase pengambilan sampel 100%. Itu pada dasarnya berarti bahwa fullscan dipicu dan statistik otomatis dibuat bahkan untuk CSV dengan parser versi 1.0.
Pembuatan statistik otomatis dilakukan secara sinkron sehingga Anda mungkin mengalami sedikit penurunan performa kueri jika kolom Anda kehilangan statistik. Waktu untuk membuat statistik untuk satu kolom tergantung pada ukuran file yang ditargetkan.
Pembuatan statistik secara manual
Kumpulan SQL tanpa server memungkinkan Anda membuat statistik secara manual. Jika Anda menggunakan parser versi 1.0 dengan CSV, Anda mungkin harus membuat statistik secara manual, karena versi pengurai ini tidak mendukung pengambilan sampel. Pembuatan statistik otomatis dengan pengurai versi 1.0 tidak akan terjadi, kecuali persentase pengambilan sampel adalah 100%.
Lihat contoh berikut untuk instruksi tentang cara membuat statistik secara manual.
Perbarui statistik
Perubahan pada data dalam file, menghapus, dan menambahkan file mengakibatkan perubahan distribusi data dan membuat statistik kedaluarsa. Dalam hal ini, statistik perlu diperbarui.
Kumpulan SQL tanpa server secara otomatis membuat ulang statistik untuk kolom OPENROWSET jika data diubah secara signifikan. Setiap kali statistik dibuat secara otomatis, kondisi himpunan data pada saat itu juga disimpan: jalur file, ukuran, tanggal modifikasi terakhir.
Ketika statistik basi, statistik baru akan dibuat. Algoritma melewati data dan membandingkannya dengan status himpunan data saat ini. Jika ukuran perubahan lebih besar dari ambang batas tertentu, statistik lama akan dihapus dan akan dibuat ulang melalui himpunan data baru.
Statistik manual tidak pernah dinyatakan basi.
Nota
Untuk pembuatan ulang otomatis statistik digunakan pengambilan sampel, dan dalam banyak kasus persentase pengambilan sampel akan kurang dari 100%. Alur ini sama untuk setiap format file. Perlu diingat bahwa ketika membaca CSV, pengambilan sampel dengan parser versi 1.0 tidak didukung dan pembentukan ulang statistik otomatis tidak akan terjadi dengan persentase pengambilan sampel kurang dari 100%. Dalam hal ini Anda perlu menghilangkan dan membuat ulang statistik secara manual. Periksa contoh di bawah ini tentang cara menghilangkan dan membuat statistik. Untuk tabel kecil dengan perkiraan kardinalitas rendah (jumlah baris), rekreasi statistik otomatis akan dipicu dengan persentase pengambilan sampel 100%. Itu pada dasarnya berarti bahwa fullscan dipicu dan statistik otomatis dibuat bahkan untuk CSV dengan parser versi 1.0.
Salah satu pertanyaan pertama yang diajukan saat Anda memecahkan masalah kueri adalah, "Apakah statistik sudah diperbarui?"
Ketika jumlah baris telah berubah secara substansial atau ada perubahan material dalam distribusi nilai untuk kolom, maka sudah waktunya untuk memperbarui statistik.
Nota
Jika ada perubahan material dalam distribusi nilai untuk kolom, Anda harus memperbarui statistik terlepas dari terakhir kali nilai tersebut diperbarui.
Mengimplementasikan manajemen statistik
Anda mungkin ingin memperluas alur data Anda untuk memastikan bahwa statistik diperbarui ketika data diubah secara signifikan melalui penambahan, penghapusan, atau perubahan file.
Prinsip panduan berikut disediakan untuk memperbarui statistik Anda:
- Pastikan bahwa himpunan data memiliki setidaknya satu objek statistik yang diperbarui. Ini memperbarui informasi ukuran (jumlah baris dan jumlah halaman) sebagai bagian dari pembaruan statistik.
- Fokus pada kolom yang digunakan dalam klausa WHERE, JOIN, GROUP BY, ORDER BY, dan DISTINCT.
- Perbarui kolom "kunci naik" seperti tanggal transaksi lebih sering karena nilai-nilai ini tidak akan disertakan dalam histogram statistik.
- Perbarui kolom distribusi statis lebih jarang.
Untuk informasi selengkapnya, lihat Estimasi Kardinalitas .
Contoh: Membuat statistik untuk kolom di jalur OPENROWSET
Contoh berikut menunjukkan kepada Anda cara menggunakan berbagai opsi untuk membuat statistik di kumpulan SQL tanpa server Azure Synapse. Opsi yang Anda gunakan untuk setiap kolom bergantung pada karakteristik data Anda dan bagaimana kolom akan digunakan dalam kueri. Untuk informasi selengkapnya tentang prosedur tersimpan yang digunakan dalam contoh ini, tinjau sys.sp_create_openrowset_statistics dan sys.sp_drop_openrowset_statistics, yang hanya berlaku untuk kumpulan SQL tanpa server.
Nota
Anda hanya dapat membuat statistik kolom tunggal saat ini.
Izin berikut diperlukan untuk menjalankan sp_create_openrowset_statistics dan sp_drop_openrowset_statistics: MENGELOLA OPERASI MASSAL atau MENGELOLA OPERASI MASSAL DATABASE.
Prosedur tersimpan berikut digunakan untuk membuat statistik:
sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'
Argumen: [ @stmt = ] N'statement_text' - Menentukan pernyataan Transact-SQL yang akan mengembalikan nilai kolom yang akan digunakan untuk statistik. Anda dapat menggunakan TABLESAMPLE untuk menentukan sampel data yang akan digunakan. Jika TABLESAMPLE tidak ditentukan, FULLSCAN akan digunakan.
<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )
Nota
Pengambilan sampel CSV tidak berfungsi jika Anda menggunakan parser versi 1.0, hanya FULLSCAN yang didukung untuk CSV dengan parser versi 1.0.
Membuat statistik kolom tunggal dengan memeriksa setiap baris
Untuk membuat statistik pada kolom, berikan kueri yang mengembalikan kolom yang statistiknya Anda butuhkan.
Secara default, jika Anda tidak menentukan sebaliknya saat membuat statistik secara manual, kumpulan SQL tanpa server menggunakan 100% data yang disediakan dalam himpunan data saat membuat statistik.
Misalnya, untuk membuat statistik dengan opsi default (FULLSCAN) untuk kolom populasi himpunan data berdasarkan file us_population.csv:
EXEC sys.sp_create_openrowset_statistics N'SELECT
population
FROM OPENROWSET(
BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
FORMAT = ''CSV'',
PARSER_VERSION = ''2.0'',
HEADER_ROW = TRUE)
AS [r]'
Membuat statistik kolom tunggal dengan menentukan ukuran sampel
Anda dapat menentukan ukuran sampel sebagai persen:
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Contoh: Memperbarui statistik
Untuk memperbarui statistik, Anda perlu menghapus dan membuat kembali statistik. Untuk informasi selengkapnya, tinjau sys.sp_create_openrowset_statistics dan sys.sp_drop_openrowset_statistics.
Prosedur tersimpan sys.sp_drop_openrowset_statistics digunakan untuk menghapus statistik:
sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'
Nota
Izin berikut diperlukan untuk menjalankan sp_create_openrowset_statistics dan sp_drop_openrowset_statistics: MENGELOLA OPERASI MASSAL atau MENGELOLA OPERASI MASSAL DATABASE.
Argumen: [ @stmt = ] N'statement_text' - Menentukan pernyataan Transact-SQL yang sama yang digunakan saat statistik dibuat.
Untuk memperbarui statistik untuk kolom tahun dalam himpunan data, yang didasarkan pada population.csv file, Anda perlu menghilangkan dan membuat statistik:
EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
GO
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Contoh: Membuat statistik untuk kolom tabel eksternal
Contoh berikut menunjukkan kepada Anda cara menggunakan berbagai opsi untuk membuat statistik. Opsi yang Anda gunakan untuk setiap kolom bergantung pada karakteristik data Anda dan bagaimana kolom akan digunakan dalam kueri.
Nota
Anda hanya dapat membuat statistik kolom tunggal saat ini.
Untuk membuat statistik pada kolom, berikan nama untuk objek statistik dan nama kolom.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Argumen: external_table Menentukan tabel eksternal yang harus digunakan untuk membuat statistik.
Hitung statistik dengan FULLSCAN dengan memindai semua baris. FULLSCAN dan SAMPLE 100 PERCENT memiliki hasil yang sama. FULLSCAN tidak dapat digunakan dengan opsi SAMPLE.
NOMOR SAMPEL PERCENT Menentukan perkiraan persentase atau jumlah baris dalam tabel atau tampilan terindeks untuk digunakan pengoptimal kueri saat membuat statistik. Angka bisa dari 0 hingga 100.
SAMPLE tidak dapat digunakan dengan opsi FULLSCAN.
Nota
Pengambilan sampel CSV tidak berfungsi jika Anda menggunakan parser versi 1.0, hanya FULLSCAN yang didukung untuk CSV dengan parser versi 1.0.
Membuat statistik kolom tunggal dengan memeriksa setiap baris
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Membuat statistik kolom tunggal dengan menentukan ukuran sampel
-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH SAMPLE 5 percent, NORECOMPUTE
Contoh: Memperbarui statistik
Untuk memperbarui statistik, Anda perlu menghapus dan membuat kembali statistik. Hilangkan statistik terlebih dahulu:
DROP STATISTICS census_external_table.sState
Dan buat statistik:
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Metadata statistik
Ada beberapa tampilan dan fungsi sistem yang dapat Anda gunakan untuk menemukan informasi tentang statistik. Misalnya, Anda dapat melihat apakah objek statistik mungkin kedaluarsa dengan menggunakan fungsi STATS_DATE(). STATS_DATE() memungkinkan Anda untuk melihat kapan statistik terakhir dibuat atau diperbarui.
Nota
Metadata statistik hanya tersedia untuk kolom tabel eksternal. Metadata statistik tidak tersedia untuk kolom OPENROWSET.
Tampilan katalog untuk statistik
Tampilan sistem ini menyediakan informasi tentang statistik:
| Tampilan katalog | Deskripsi |
|---|---|
| sys.columns | Satu baris untuk setiap kolom. |
| sys.objects | Satu baris untuk setiap objek dalam database. |
| sys.schemas | Satu baris untuk setiap skema dalam database. |
| sys.stats | Satu baris untuk setiap objek statistik. |
| sys.stats_columns | Satu baris untuk setiap kolom dalam objek statistik. Tautan kembali ke sys.columns. |
| sys.tables | Satu baris untuk setiap tabel (menyertakan tabel eksternal). |
| sys.table_types | Satu baris untuk setiap jenis data. |
Fungsi sistem untuk statistik
Fungsi sistem ini berguna untuk bekerja dengan statistik:
| Fungsi sistem | Deskripsi |
|---|---|
| STATS_DATE | Tanggal objek statistik terakhir diperbarui. |
Menggabungkan kolom dan fungsi statistik ke dalam satu tampilan
Tampilan ini menyautkan kolom yang terkait dengan statistik dan hasil dari fungsi STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[user_created] = 1
;
Langkah berikutnya
Untuk lebih meningkatkan performa kueri untuk kumpulan SQL khusus, lihat Memantau beban kerja anda dan Praktik terbaik untuk kumpulan SQL khusus.
Untuk lebih meningkatkan performa kueri untuk kumpulan SQL tanpa server, lihat Praktik terbaik untuk kumpulan SQL tanpa server.