Not
Bu sayfaya erişim yetkilendirme gerektiriyor. Oturum açmayı veya dizinleri değiştirmeyi deneyebilirsiniz.
Bu sayfaya erişim yetkilendirme gerektiriyor. Dizinleri değiştirmeyi deneyebilirsiniz.
Şunlar için geçerlidir: Azure Synapse Analytics
Bu makalede kümelenmiş columnstore dizini (CCI) durumunu değerlendirmeye yönelik biraz farklı bir yaklaşım tanıtılır. Aşağıdaki bölümlerde yer alan adımları izleyin veya Azure Data Studio aracılığıyla not defterindeki adımları yürütebilirsiniz.
Not
Bu not defterini açmaya çalışmadan önce yerel makinenizde Azure Data Studio'yu yüklediğinizden emin olun. Yüklemek için Azure Data Studio'yu yüklemeyi öğrenin bölümüne gidin.
Genel olarak, bir CCI'nin kalitesini etkileyen iki önemli faktör vardır:
Satır gruplarını ve meta verileri sıkıştır - Gerçek satır grubu sayısı, satır grubundaki satır sayısı için ideal sayıya yakındır.
Sıkıştırılmış satır grupları - Satır grupları columnstore sıkıştırması kullanıyor.
Küçük tablolar, fazla bölümlenmiş tablolar veya az bölümlenmiş tablolar gibi diğer koşullar, tartışmasız olarak düşük kalite veya sistem durumu koşullarıdır. Ancak bu koşullar, 4. Adımda değerlendirilebilecek tasarım geliştirme fırsatları olarak daha iyi sınıflandırılır.
1\. Adım: CCI sistem durumunuzun özetini analiz etme
Tek bir ölçüm satırı almak için aşağıdaki sorguyu kullanın.
WITH cci_detail AS (
SELECT t.object_id,
rg.partition_number,
COUNT(*) AS total_rowgroup_count,
SUM(CASE WHEN rg.state = 1 THEN 1 END) AS open_rowgroup_count,
CEILING((SUM(rg.[total_rows]) - SUM(rg.deleted_rows))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
SUM(rg.size_in_bytes/1024/1024.) AS size_in_mb,
SUM(CASE WHEN rg.state = 1 THEN rg.size_in_bytes END /1024/1024.) AS open_size_in_mb
FROM sys.pdw_nodes_column_store_row_groups rg
JOIN sys.pdw_nodes_tables nt ON rg.object_id = nt.object_id
AND rg.pdw_node_id = nt.pdw_node_id
AND rg.distribution_id = nt.distribution_id
JOIN sys.pdw_table_mappings mp ON nt.name = mp.physical_name
JOIN sys.tables t ON mp.object_id = t.object_id
GROUP BY t.object_id,
rg.partition_number
)
SELECT COUNT(DISTINCT object_id) AS tables_assessed_count,
COUNT(*) AS partitions_assessed_count,
SUM(total_rowgroup_count) AS actual_rowgroup_count,
SUM(ideal_rowgroup_count) AS ideal_rowgroup_count,
SUM(open_rowgroup_count) AS uncompressed_rowgroup_count,
CAST(SUM(size_in_mb) AS DECIMAL(19, 4)) AS actual_size_in_mb,
CAST(SUM(open_size_in_mb) AS DECIMAL(19, 4)) AS uncompressed_size_in_mb,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 100. AS DECIMAL(9, 4)) AS excess_pct,
CAST(((SUM(total_rowgroup_count) - SUM(ideal_rowgroup_count)) / SUM(total_rowgroup_count)) * 1. AS DECIMAL(9, 4)) * SUM(size_in_mb) AS excess_size_in_mb
FROM cci_detail
Sonuçtan, ayrılmış SQL havuzunuzun CCI sistem durumuna genel bir bakış elde edebilirsiniz. Bu bilgiler doğrudan eyleme dönüştürülemez, ancak bakım yordamlarının ideal bir duruma ulaşmak için önemini anlamanıza yardımcı olur.
| Sütun adı | Description |
|---|---|
tables_assessed_count |
CCI tablolarının sayısı |
partitions_assessed_count |
Bölümlerin sayısı Not: Bölümlenmemiş tablolar 1 olarak sayılır. |
actual_rowgroup_count |
Satır gruplarının fiziksel sayısı |
ideal_rowgroup_count |
Satır sayısı için ideal olabilecek, hesaplanan satır grubu sayısı |
uncompressed_rowgroup_count |
Sıkıştırılmamış veriler içeren satır gruplarının sayısı. (AÇIK satırlar olarak da bilinir) |
actual_size_in_mb |
MB cinsinden CCI verilerinin fiziksel boyutu |
uncompressed_size_in_mb |
MB cinsinden sıkıştırılmamış verilerin fiziksel boyutu |
excess_pct |
Daha fazla iyileştirilebilecek satır gruplarının yüzdesi |
excess_size_in_mb |
İyileştirilmemiş satır gruplarından tahmini MB |
2\. Adım: Ayrıntılı CCI bilgilerini analiz etme
Aşağıdaki sorgu hangi tablo bölümlerinin yeniden derleme adayı olduğuna ilişkin ayrıntılı bir rapor sağlar. CCI ayrıntıları, bakımdan en fazla yararlanabilecek tabloları/bölümleri belirlemeye ve önceliklendirmeye yardımcı olan üç ölçümde sağlanır. WHERE yan tümcesinde bu ölçümler için uygun eşik değerlerini ayarlayın ve ardından ORDER BY yan tümcesinde en çok ilgilendiğiniz ölçümleri kullanın. Ayrıntılı bilgiler, ayrılmış SQL havuzunuzun derlemede gecikmelere yol açabilecek çok fazla sayıda küçük, parçalanmış tablodan etkilenip etkilenmediğini saptamak için de yararlı olabilir.
Not
Açıklamalı fnMs_GenerateIndexMaintenanceScript işlev, dizinleri korumak için ortak betikler oluşturabilen tablo değerli bir işlevdir (TVF). Sonuçtaki bakım betiklerini almak istiyorsanız, 37 ve 39 satırlarını açın. Sorguyu çalıştırmadan önce, işlevi oluşturmak için Dizin bakım betikleri oluşturma bölümündeki betiği kullanın. Sonuçtan elde ettiğiniz bakım betiğini çalıştırırken, largerc veya xlargerc gibi uygun boyutta bir kaynak sınıfı kullandığınızdan emin olun.
| Sütun adı | Kalite özelliği | Description |
|---|---|---|
excess_pct |
Sıkıştırılma | Daha fazla sıkıştırılabilir satır gruplarının yüzdesi |
excess_size_in_mb |
Sıkıştırılma | İyileştirilmemiş satır gruplarından tahmini MB |
OPEN_rowgroup_size_in_mb |
Sıkıştırma | Dizindeki sıkıştırılmamış verilerin gerçek MB'ı |
WITH cci_info AS(
SELECT t.object_id AS [object_id],
MAX(schema_name(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
COUNT(DISTINCT rg.distribution_id) AS [distribution_count],
SUM(rg.size_in_bytes/1024/1024) AS [size_in_mb],
SUM(rg.[total_rows]) AS [row_count_total],
COUNT(*) AS [total_rowgroup_count],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count],
SUM(CASE WHEN rg.[State] = 1 THEN 1 ELSE 0 END) AS [OPEN_rowgroup_count],
SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows] ELSE 0 END) AS [OPEN_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 1 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [OPEN_rowgroup_size_in_mb],
SUM(CASE WHEN rg.[State] = 2 THEN 1 ELSE 0 END) AS [CLOSED_rowgroup_count],
SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows] ELSE 0 END) AS [CLOSED_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 2 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [CLOSED_size_in_mb],
SUM(CASE WHEN rg.[State] = 3 THEN 1 ELSE 0 END) AS [COMPRESSED_rowgroup_count],
SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows],
CAST(SUM(CASE WHEN rg.[State] = 3 THEN rg.[size_in_bytes]/1024./1024. ELSE 0 END) AS DECIMAL(19, 4)) AS [COMPRESSED_size_in_mb],
SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows] ELSE 0 END) AS [COMPRESSED_rowgroup_rows_DELETED]
FROM sys.[pdw_nodes_column_store_row_groups] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
GROUP BY t.object_id,
rg.partition_number
)
, calc_excess AS(
SELECT *,
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 100. AS DECIMAL(9, 4)) AS [excess_pct],
CAST(((total_rowgroup_count - ideal_rowgroup_count) / total_rowgroup_count) * 1. AS DECIMAL(9, 4)) * size_in_mb AS [excess_size_in_mb]
FROM cci_info
)
SELECT calc_excess.*
-- , script.*
FROM calc_excess
-- CROSS APPLY dbo.fnMs_GenerateIndexMaintenanceScript(object_id, partition_number) AS script
WHERE -- set your own threshold(s) for the following; 0 is the ideal, but usually not practical
calc_excess.[excess_size_in_mb] > 300
OR calc_excess.excess_pct > 0.1
OR calc_excess.OPEN_rowgroup_size_in_mb > 100
ORDER BY calc_excess.[excess_size_in_mb] DESC;
3\. Adım: Bakım CCI sistem durumunu iyileştirmediğinde ne yapmalı?
Bir tabloda/bölümde bakım gerçekleştirmek aşağıdaki senaryolardan biriyle sonuçlanabilir:
excess_pctveexcess_size_in_mb, bakım öncesine göre daha büyüktür.- Bakım deyimi yetersiz bellek nedeniyle başarısız olur.
Tipik nedenler
- Yetersiz kaynak.
- Yetersiz hizmet düzeyi (DWU).
- Tablo büyük ve bölümlenmemiş.
Önerilen düzeltmeler
- Yürüten kullanıcının kaynak sınıfını veya iş yükü grubunu değiştirerek bakım deyimleri için kaynakları artırın.
- Bakımı gerçekleştirmek için DWU düzeyini geçici olarak artırın.
- Sorunlu tabloda bir bölümleme stratejisi uygulayın ve ardından bölümlerde bakım gerçekleştirin.
4\. Adım: Tasarım geliştirme fırsatlarını denetleme
Kapsamlı olmasa da, aşağıdaki sorgu CCI'lerle ilgili performans veya bakım sorunlarına neden olduğu bulunan olası fırsatları belirlemenize yardımcı olabilir.
| Fırsat başlığı | Description | Öneriler |
|---|---|---|
| Küçük tablo | Tablo 15 milyondan az satır içeriyor | Dizini CCI'den şu şekilde değiştirmeyi göz önünde bulundurun:
|
| Bölümleme fırsatı veya bölümlenmemiş tablo | Hesaplanan ideal satır grubu sayısı 180 M'den büyük (veya yaklaşık 188 M satır) | Bölümleme stratejisi uygulayın veya bölüm başına satır sayısını 188M'den az olacak şekilde (dağıtım başına bölüm başına yaklaşık üç satır grubu) azaltmak için mevcut bölümleme stratejisini değiştirin |
| Fazla bölümlenmiş tablo | Tablo en büyük bölüm için 15 milyondan az satır içeriyor | Saymak:
|
WITH cci_info AS (
SELECT t.object_id AS [object_id],
MAX(SCHEMA_NAME(t.schema_id)) AS [schema_name],
MAX(t.name) AS [table_name],
rg.partition_number AS [partition_number],
SUM(rg.[total_rows]) AS [row_count_total],
CEILING((SUM(rg.[total_rows]) - SUM(rg.[deleted_rows]))/COUNT(DISTINCT rg.distribution_id)/1048576.) * COUNT(DISTINCT rg.distribution_id) AS [ideal_rowgroup_count]
FROM sys.[pdw_nodes_column_store_row_groups] rg
JOIN sys.[pdw_nodes_tables] nt ON rg.[object_id] = nt.[object_id]
AND rg.[pdw_node_id] = nt.[pdw_node_id]
AND rg.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_table_mappings] mp ON nt.[name] = mp.[physical_name]
JOIN sys.[tables] t ON mp.[object_id] = t.[object_id]
GROUP BY t.object_id,
rg.partition_number
)
SELECT object_id,
MAX(SCHEMA_NAME),
MAX(TABLE_NAME),
COUNT(*) AS number_of_partitions,
MAX(row_count_total) AS max_partition_row_count,
MAX(ideal_rowgroup_count) partition_ideal_row_count,
CASE
-- non-partitioned tables
WHEN COUNT(*) = 1 AND MAX(row_count_total) < 15000000 THEN 'Small table'
WHEN COUNT(*) = 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Partitioning opportunity'
-- partitioned tables
WHEN COUNT(*) > 1 AND MAX(row_count_total) < 15000000 THEN 'Over-partitioned table'
WHEN COUNT(*) > 1 AND MAX(ideal_rowgroup_count) > 180 THEN 'Under-partitioned table'
END AS warning_category
FROM cci_info
GROUP BY object_id
Dizin bakım betikleri oluşturma
Ayrılmış SQL havuzunuzda işlev oluşturmak dbo.fnMs_GenerateIndexMaintenanceScript için aşağıdaki sorguyu çalıştırın. Bu işlev, CCI'nizi üç yolla iyileştirmek için betikler oluşturur. Bu işlevi yalnızca URI'leri değil kümelenmiş (rowstore) dizinlerini de korumak için kullanabilirsiniz.
Parametreler
| Parametre adı | Zorunlu | Açıklama |
|---|---|---|
@object_id |
Y | object_id hedef tablonun |
@partition_number |
Y | partition_number hedeften sys.partitions hedefe. Tablo bölümlenmemişse 1 belirtin. |
Çıkış tablosu
| Sütun adı | Açıklama |
|---|---|
rebuild_script |
Verilen tablo/bölüm için oluşturulan ALTER INDEX ALL ... REBUILD deyim. Bölümlenmemiş yığınlar döndürür NULL. |
reorganize_script |
Verilen tablo/bölüm için oluşturulan ALTER INDEX ALL ... REORGANIZE deyim. Bölümlenmemiş yığınlar döndürür NULL. |
partition_switch_script |
Yalnızca bölümlenmiş tablolar için geçerlidir; , tablo bölümlenmemişse veya geçersiz bir bölüm numarası belirtilmişse olacaktır NULL . CCI bir ORDER yan tümcesi ile oluşturulduysa işlenir. |
CREATE FUNCTION dbo.fnMs_GenerateIndexMaintenanceScript (@object_id INT, @partition_number INT = 1)
RETURNS TABLE
AS
RETURN(
WITH base_info AS (
SELECT
t.object_id
, SCHEMA_NAME(t.schema_id) AS [schema_name]
, t.name AS table_name
, i.index_type
, i.index_cols
, i.index_type_desc
, tdp.distribution_policy_desc
, c.name hash_distribution_column_name
FROM sys.tables t
JOIN (
SELECT
i.object_id
, i.index_id
, MAX(i.type) AS index_type
, MAX(CASE WHEN i.type = 5 AND ic.column_store_order_ordinal != 0 THEN ' ORDER ' ELSE '' END)
+ '(' + STRING_AGG(
CASE
WHEN i.type IN (1, 5)
AND (ic.key_ordinal != 0 OR ic.column_store_order_ordinal != 0)
THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
END
, ',') WITHIN GROUP(ORDER BY ic.column_store_order_ordinal, ic.key_ordinal) + ')' AS index_cols
, MAX(i.type_desc)
+ CASE
WHEN MAX(i.type) IN (1, 5) THEN ' INDEX'
ELSE ''
END COLLATE SQL_Latin1_General_CP1_CI_AS AS index_type_desc
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.index_id <= 1
GROUP BY i.object_id, i.index_id
) AS i
ON t.object_id = i.object_id
JOIN sys.pdw_table_distribution_properties tdp ON t.object_id = tdp.object_id
LEFT JOIN sys.pdw_column_distribution_properties cdp ON t.object_id = cdp.object_id AND cdp.distribution_ordinal = 1
LEFT JOIN sys.columns c ON cdp.object_id = c.object_id AND cdp.column_id = c.column_id
WHERE t.object_id = @object_id
)
, param_data_type AS (
SELECT
pp.function_id
, typ.name AS data_type_name
, CAST(CASE
WHEN typ.collation_name IS NOT NULL THEN 1
WHEN typ.name LIKE '%date%' THEN 1
WHEN typ.name = 'uniqueidentifier' THEN 1
ELSE 0
END AS BIT) AS use_quotes_on_values_flag
FROM sys.partition_parameters pp
JOIN sys.types typ ON pp.user_type_id = typ.user_type_id
)
, boundary AS (
SELECT
t.object_id
, c.name AS partition_column_name
, pf.boundary_value_on_right
, prv.boundary_id
, prv.boundary_id + CASE WHEN pf.boundary_value_on_right = 1 THEN 1 ELSE 0 END AS [partition_number]
, CASE
WHEN pdt.use_quotes_on_values_flag = 1 THEN '''' + CAST(
CASE pdt.data_type_name
WHEN 'date' THEN CONVERT(char(10), prv.value, 120)
WHEN 'smalldatetime' THEN CONVERT(VARCHAR, prv.value, 120)
WHEN 'datetime' THEN CONVERT(VARCHAR, prv.value, 121)
WHEN 'datetime2' THEN CONVERT(VARCHAR, prv.value, 121)
ELSE prv.value
END
AS VARCHAR(32)) + ''''
ELSE CAST(prv.value AS VARCHAR(32))
END AS boundary_value
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id <= 1
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal = 1
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN param_data_type pdt ON pf.function_id = pdt.function_id
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE t.object_id = @object_id
)
, partition_clause AS (
SELECT
object_id
, COUNT(*) - 1 -- should always be the 2nd to last partition in stage table
+ CASE WHEN MAX([partition_number]) = @partition_number THEN 1 ELSE 0 END -- except when last partition
AS [source_partition_number]
, 'WHERE ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' >= ' + MIN(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
ELSE
' <= ' + MAX(CASE WHEN [partition_number] = @partition_number THEN boundary_value END)
END
+ ' AND ' + MAX(partition_column_name)
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN
' < ' + MAX(boundary_value)
ELSE
' > ' + MIN(boundary_value)
END AS filter_clause
, ', PARTITION (' + MAX(partition_column_name) + ' RANGE '
+ CASE WHEN MAX(CAST(boundary_value_on_right AS TINYINT)) = 1 THEN 'RIGHT' ELSE 'LEFT' END
+ ' FOR VALUES(' + STRING_AGG(boundary_value, ',') + '))' AS [partition_clause]
FROM boundary
WHERE [partition_number] BETWEEN @partition_number - 1 AND @partition_number + 1
GROUP BY object_id
)
SELECT
CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REBUILD'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END + ';' END AS [rebuild_script]
, CASE WHEN index_type IN (1, 5) THEN 'ALTER INDEX ALL ON [' + [schema_name] + '].[' + [table_name] + '] REORGANIZE'
+ CASE WHEN partition_clause.[object_id] IS NOT NULL THEN ' PARTITION = ' + CAST(@partition_number AS VARCHAR(16)) ELSE '' END
+ CASE WHEN index_type = 5 THEN ' WITH (COMPRESS_ALL_ROW_GROUPS = ON)' ELSE '' END + ';' END AS [reorganize_script]
, 'CREATE TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] WITH(' + index_type_desc + ISNULL(index_cols, '')
+ ', DISTRIBUTION = ' + distribution_policy_desc + CASE WHEN distribution_policy_desc = 'HASH' THEN '(' + hash_distribution_column_name + ')' ELSE '' END
+ partition_clause.partition_clause + ') AS SELECT * FROM [' + [schema_name] + '].[' + [table_name] + '] ' + filter_clause + CASE WHEN index_type = 5 AND index_cols IS NOT NULL THEN ' OPTION(MAXDOP 1)' ELSE '' END + ';'
+ ' ALTER TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp] SWITCH PARTITION ' + CAST(source_partition_number AS VARCHAR(16))
+ ' TO [' + [schema_name] + '].[' + [table_name] + '] PARTITION ' + CAST(@partition_number AS VARCHAR(16))
+ ' WITH (TRUNCATE_TARGET = ON);'
+ ' DROP TABLE [' + schema_name + '].[' + table_name + '_p' + CAST(@partition_number AS VARCHAR(16)) + '_tmp];' AS [partition_switch_script]
FROM base_info
LEFT JOIN partition_clause
ON base_info.object_id = partition_clause.object_id
);
GO
Daha Fazla Bilgi
Ayrılmış SQL havuzunda CCI için daha ayrıntılı bir anlayış elde etmek ve ek değerlendirme araçları edinmek için bkz: