Azure Synapse Analytics'te ayrılmış SQL havuzundaki işlemleri iyileştirme
Uzun geri alma risklerini en aza indirirken ayrılmış SQL havuzundaki işlem kodunuzun performansını iyileştirmeyi öğrenin.
İşlemler ve günlüğe kaydetme
İşlemler, ilişkisel SQL havuzu altyapısının önemli bir bileşenidir. İşlemler veri değişikliği sırasında kullanılır. Bu işlemler açık veya örtük olabilir. Tek INSERT, UPDATE ve DELETE deyimlerinin tümü örtük işlemlere örnektir. Açık işlemler BEGIN TRAN, COMMIT TRAN veya ROLLBACK TRAN kullanır. Açık işlemler genellikle birden çok değişiklik deyiminin tek bir atomik ünitede birbirine bağlanması gerektiğinde kullanılır.
SQL havuzundaki değişiklikler işlem günlükleri kullanılarak izlenir. Her dağıtımın kendi işlem günlüğü vardır. İşlem günlüğü yazma işlemleri otomatiktir. Yapılandırma gerekmez. Ancak, bu işlem yazma işlemini garanti ederken sistemde bir ek yük getirir. İşlemsel olarak verimli kod yazarak bu etkiyi en aza indirebilirsiniz. İşlemsel olarak verimli kod geniş anlamda iki kategoriye ayrılır.
- Mümkün olduğunda en düşük günlük yapılarını kullanın
- Tekil uzun süre çalışan işlemlerden kaçınmak için kapsamlı toplu işlemleri kullanarak verileri işleme
- Belirli bir bölümde yapılan büyük değişiklikler için bölüm değiştirme deseni benimseme
En az ve tam günlük
Her satır değişikliğini izlemek için işlem günlüğünü kullanan tam olarak günlüğe kaydedilen işlemlerden farklı olarak, en az günlüğe kaydedilen işlemler yalnızca kapsam ayırmalarını ve meta veri değişikliklerini izler. Bu nedenle, minimum günlük yalnızca bir hatadan sonra işlemi geri almak veya açık bir istek (ROLLBACK TRAN) için gereken bilgileri günlüğe kaydetmeyi içerir. İşlem günlüğünde çok daha az bilgi izlense de, en düşük düzeyde günlüğe kaydedilen bir işlem benzer boyuttaki tam günlüğe kaydedilen işlemden daha iyi performans gösterir. Ayrıca işlem günlüğüne daha az yazma işlemi gittiği için çok daha az miktarda günlük verisi oluşturulur ve G/Ç daha verimlidir.
İşlem güvenliği sınırları yalnızca tam olarak günlüğe kaydedilen işlemler için geçerlidir.
Not
En az günlüğe kaydedilen işlemler açık işlemlere katılabilir. Ayırma yapılarındaki tüm değişiklikler izlendiğinden, en az günlüğe kaydedilen işlemleri geri almak mümkündür.
En az günlüğe kaydedilen işlemler
Aşağıdaki işlemler minimum düzeyde günlüğe kaydedilebilir:
- SELECT OLARAK TABLO OLUŞTURMA (CTAS)
- EKLE.. SEÇİN
- CREATE INDEX
- ALTER INDEX REBUILD
- DROP INDEX
- TRUNCATE TABLE
- DROP TABLE
- ALTER TABLE SWITCH PARTITION
Not
İç veri taşıma işlemleri (BROADCAST ve SHUFFLE gibi) işlem güvenlik sınırından etkilenmez.
Toplu yükleme ile minimum günlük kaydı
CTAS ve INSERT... SELECT her ikisi de toplu yükleme işlemleridir. Ancak her ikisi de hedef tablo tanımından etkilenir ve yükleme senaryosuna bağlıdır. Aşağıdaki tabloda toplu işlemlerin ne zaman tam veya en az günlüğe kaydediltiği açıklanmaktadır:
Birincil Dizin | Yükleme Senaryosu | Günlük Modu |
---|---|---|
Yığın | Herhangi biri | En az |
Kümelenmiş | Boş hedef tablo | En az |
Kümelenmiş | Yüklenen satırlar hedefteki mevcut sayfalarla çakışmaz | En az |
Kümelenmiş | Yüklenen satırlar hedefteki mevcut sayfalarla çakışıyor | Tam |
Kümelenmiş Columnstore Dizini | Toplu iş boyutu >= bölüme hizalı dağıtım başına 102.400 | En az |
Kümelenmiş Columnstore Dizini | Bölüme hizalı dağıtım başına toplu iş boyutu < 102.400 | Tam |
İkincil veya kümelenmemiş dizinleri güncelleştirmeye yönelik yazma işlemlerinin her zaman tam olarak günlüğe kaydedilen işlemler olacağını belirtmek önemlidir.
Önemli
Ayrılmış SQL havuzu 60 dağıtıma sahiptir. Bu nedenle, tüm satırların eşit olarak dağıtıldığını ve tek bir bölüme eklendiğini varsayarsak, toplu işleminizin Kümelenmiş Columnstore Dizinine yazarken en az günlüğe kaydedilecek 6.144.000 veya daha büyük satır içermesi gerekir. Tablo bölümlenmişse ve eklenen satırlar yayılmış bölüm sınırlarına yayılıyorsa, veri dağıtımının eşit olduğu varsayılarak bölüm sınırı başına 6.144.000 satır gerekir. Her dağıtımdaki her bölümün, eklemenin dağıtımda en az düzeyde oturum açması için 102.400 satır eşiğini bağımsız olarak aşması gerekir.
Kümelenmiş dizine sahip boş olmayan bir tabloya veri yüklemek genellikle tam olarak günlüğe kaydedilmiş ve en az günlüğe kaydedilen satırların bir karışımını içerebilir. Kümelenmiş dizin, sayfaların dengeli bir ağacıdır (b-ağaç). Yazılmakta olan sayfa zaten başka bir işlemden satırlar içeriyorsa, bu yazma işlemleri tamamen günlüğe kaydedilir. Ancak, sayfa boşsa o sayfaya yazma işlemi en az günlüğe kaydedilir.
Silmeleri iyileştirme
DELETE, tam olarak günlüğe kaydedilen bir işlemdir. Bir tablo veya bölümdeki büyük miktarda veriyi silmeniz gerekiyorsa, tutmak istediğiniz veriler genellikle daha anlamlıdır SELECT
ve bu işlem en az günlüğe kaydedilen işlem olarak çalıştırılabilir. Verileri seçmek için CTAS ile yeni bir tablo oluşturun. Oluşturulduktan sonra, eski tablonuzu yeni oluşturulan tabloyla değiştirmek için RENAME kullanın.
-- Delete all sales transactions for Promotions except PromotionKey 2.
--Step 01. Create a new table select only the records we want to kep (PromotionKey 2)
CREATE TABLE [dbo].[FactInternetSales_d]
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT
FOR VALUES ( 20000101, 20010101, 20020101, 20030101, 20040101, 20050101
, 20060101, 20070101, 20080101, 20090101, 20100101, 20110101
, 20120101, 20130101, 20140101, 20150101, 20160101, 20170101
, 20180101, 20190101, 20200101, 20210101, 20220101, 20230101
, 20240101, 20250101, 20260101, 20270101, 20280101, 20290101
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [PromotionKey] = 2
OPTION (LABEL = 'CTAS : Delete')
;
--Step 02. Rename the Tables to replace the
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_d] TO [FactInternetSales];
Güncelleştirmeleri iyileştirme
UPDATE, tam olarak günlüğe kaydedilen bir işlemdir. Bir tablo veya bölümde çok sayıda satırı güncelleştirmeniz gerekiyorsa, bunu yapmak için CTAS gibi en az günlüğe kaydedilmiş bir işlemi kullanmak genellikle çok daha verimli olabilir.
Aşağıdaki örnekte tam bir tablo güncelleştirmesi CTAS'ye dönüştürüldü, böylece minimum günlük kaydı mümkündür.
Bu durumda, tablodaki satışlara geçmişe dönük olarak bir indirim tutarı ekliyoruz:
--Step 01. Create a new table containing the "Update".
CREATE TABLE [dbo].[FactInternetSales_u]
WITH
( CLUSTERED INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT
FOR VALUES ( 20000101, 20010101, 20020101, 20030101, 20040101, 20050101
, 20060101, 20070101, 20080101, 20090101, 20100101, 20110101
, 20120101, 20130101, 20140101, 20150101, 20160101, 20170101
, 20180101, 20190101, 20200101, 20210101, 20220101, 20230101
, 20240101, 20250101, 20260101, 20270101, 20280101, 20290101
)
)
)
AS
SELECT
[ProductKey]
, [OrderDateKey]
, [DueDateKey]
, [ShipDateKey]
, [CustomerKey]
, [PromotionKey]
, [CurrencyKey]
, [SalesTerritoryKey]
, [SalesOrderNumber]
, [SalesOrderLineNumber]
, [RevisionNumber]
, [OrderQuantity]
, [UnitPrice]
, [ExtendedAmount]
, [UnitPriceDiscountPct]
, ISNULL(CAST(5 as float),0) AS [DiscountAmount]
, [ProductStandardCost]
, [TotalProductCost]
, ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
ELSE [SalesAmount] - 5
END AS MONEY),0) AS [SalesAmount]
, [TaxAmt]
, [Freight]
, [CarrierTrackingNumber]
, [CustomerPONumber]
FROM [dbo].[FactInternetSales]
OPTION (LABEL = 'CTAS : Update')
;
--Step 02. Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_u] TO [FactInternetSales];
--Step 03. Drop the old table
DROP TABLE [dbo].[FactInternetSales_old]
Not
Büyük tabloları yeniden oluşturmak, ayrılmış SQL havuzu iş yükü yönetimi özelliklerini kullanmaktan yararlanabilir. Daha fazla bilgi için bkz. İş yükü yönetimi için kaynak sınıfları.
Bölüm değiştirme ile iyileştirme
Tablo bölümü içinde büyük ölçekli değişikliklerle karşılaşırsanız bölüm değiştirme deseni mantıklıdır. Veri değişikliği önemliyse ve birden çok bölüme yayılıyorsa, bölümler üzerinde yineleme aynı sonucu elde eder.
Bölüm anahtarı gerçekleştirme adımları şunlardır:
- Boş bir dışarı bölüm oluşturma
- CTAS olarak 'güncelleştirmeyi' gerçekleştirme
- Mevcut verileri out tablosuna değiştirme
- Yeni verileri değiştirme
- Verileri temizleme
Ancak, geçiş için bölümleri tanımlamaya yardımcı olmak için aşağıdaki yardımcı yordamı oluşturun.
CREATE PROCEDURE dbo.partition_data_get
@schema_name NVARCHAR(128)
, @table_name NVARCHAR(128)
, @boundary_value INT
AS
IF OBJECT_ID('tempdb..#ptn_data') IS NOT NULL
BEGIN
DROP TABLE #ptn_data
END
CREATE TABLE #ptn_data
WITH ( DISTRIBUTION = ROUND_ROBIN
, HEAP
)
AS
WITH CTE
AS
(
SELECT s.name AS [schema_name]
, t.name AS [table_name]
, p.partition_number AS [ptn_nmbr]
, p.[rows] AS [ptn_rows]
, CAST(r.[value] AS INT) AS [boundary_value]
FROM sys.schemas AS s
JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
JOIN sys.indexes AS i ON t.[object_id] = i.[object_id]
JOIN sys.partitions AS p ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.partition_schemes AS h ON i.[data_space_id] = h.[data_space_id]
JOIN sys.partition_functions AS f ON h.[function_id] = f.[function_id]
LEFT JOIN sys.partition_range_values AS r ON f.[function_id] = r.[function_id]
AND r.[boundary_id] = p.[partition_number]
WHERE i.[index_id] <= 1
)
SELECT *
FROM CTE
WHERE [schema_name] = @schema_name
AND [table_name] = @table_name
AND [boundary_value] = @boundary_value
OPTION (LABEL = 'dbo.partition_data_get : CTAS : #ptn_data')
;
GO
Bu yordam, kodun yeniden kullanılmasını en üst düzeye çıkarır ve bölüm değiştirme örneğini daha kompakt tutar.
Aşağıdaki kod, tam bölüm değiştirme yordamı elde etmek için daha önce bahsedilen adımları gösterir.
--Create a partitioned aligned empty table to switch out the data
IF OBJECT_ID('[dbo].[FactInternetSales_out]') IS NOT NULL
BEGIN
DROP TABLE [dbo].[FactInternetSales_out]
END
CREATE TABLE [dbo].[FactInternetSales_out]
WITH
( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT
FOR VALUES ( 20020101, 20030101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE 1=2
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;
--Create a partitioned aligned table and update the data in the select portion of the CTAS
IF OBJECT_ID('[dbo].[FactInternetSales_in]') IS NOT NULL
BEGIN
DROP TABLE [dbo].[FactInternetSales_in]
END
CREATE TABLE [dbo].[FactInternetSales_in]
WITH
( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT
FOR VALUES ( 20020101, 20030101
)
)
)
AS
SELECT
[ProductKey]
, [OrderDateKey]
, [DueDateKey]
, [ShipDateKey]
, [CustomerKey]
, [PromotionKey]
, [CurrencyKey]
, [SalesTerritoryKey]
, [SalesOrderNumber]
, [SalesOrderLineNumber]
, [RevisionNumber]
, [OrderQuantity]
, [UnitPrice]
, [ExtendedAmount]
, [UnitPriceDiscountPct]
, ISNULL(CAST(5 as float),0) AS [DiscountAmount]
, [ProductStandardCost]
, [TotalProductCost]
, ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
ELSE [SalesAmount] - 5
END AS MONEY),0) AS [SalesAmount]
, [TaxAmt]
, [Freight]
, [CarrierTrackingNumber]
, [CustomerPONumber]
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey BETWEEN 20020101 AND 20021231
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;
--Use the helper procedure to identify the partitions
--The source table
EXEC dbo.partition_data_get 'dbo','FactInternetSales',20030101
DECLARE @ptn_nmbr_src INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_src
--The "in" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_in',20030101
DECLARE @ptn_nmbr_in INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_in
--The "out" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_out',20030101
DECLARE @ptn_nmbr_out INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_out
--Switch the partitions over
DECLARE @SQL NVARCHAR(4000) = '
ALTER TABLE [dbo].[FactInternetSales] SWITCH PARTITION '+CAST(@ptn_nmbr_src AS VARCHAR(20)) +' TO [dbo].[FactInternetSales_out] PARTITION ' +CAST(@ptn_nmbr_out AS VARCHAR(20))+';
ALTER TABLE [dbo].[FactInternetSales_in] SWITCH PARTITION '+CAST(@ptn_nmbr_in AS VARCHAR(20)) +' TO [dbo].[FactInternetSales] PARTITION ' +CAST(@ptn_nmbr_src AS VARCHAR(20))+';'
EXEC sp_executesql @SQL
--Perform the clean-up
TRUNCATE TABLE dbo.FactInternetSales_out;
TRUNCATE TABLE dbo.FactInternetSales_in;
DROP TABLE dbo.FactInternetSales_out
DROP TABLE dbo.FactInternetSales_in
DROP TABLE #ptn_data
Küçük toplu işlerle günlüğe kaydetmeyi en aza indirme
Büyük veri değiştirme işlemleri için, iş birimini kapsamak için işlemi öbeklere veya toplu işlemlere bölmek mantıklı olabilir.
Aşağıdaki kod, çalışan bir örnektir. Toplu iş boyutu, tekniği vurgulamak için önemsiz bir sayıya ayarlanmıştır. Gerçekte toplu iş boyutu önemli ölçüde daha büyük olacaktır.
SET NO_COUNT ON;
IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
DROP TABLE #t;
PRINT '#t dropped';
END
CREATE TABLE #t
WITH ( DISTRIBUTION = ROUND_ROBIN
, HEAP
)
AS
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq_nmbr
, SalesOrderNumber
, SalesOrderLineNumber
FROM dbo.FactInternetSales
WHERE [OrderDateKey] BETWEEN 20010101 and 20011231
;
DECLARE @seq_start INT = 1
, @batch_iterator INT = 1
, @batch_size INT = 50
, @max_seq_nmbr INT = (SELECT MAX(seq_nmbr) FROM dbo.#t)
;
DECLARE @batch_count INT = (SELECT CEILING((@max_seq_nmbr*1.0)/@batch_size))
, @seq_end INT = @batch_size
;
SELECT COUNT(*)
FROM dbo.FactInternetSales f
PRINT 'MAX_seq_nmbr '+CAST(@max_seq_nmbr AS VARCHAR(20))
PRINT 'MAX_Batch_count '+CAST(@batch_count AS VARCHAR(20))
WHILE @batch_iterator <= @batch_count
BEGIN
DELETE
FROM dbo.FactInternetSales
WHERE EXISTS
(
SELECT 1
FROM #t t
WHERE seq_nmbr BETWEEN @seq_start AND @seq_end
AND FactInternetSales.SalesOrderNumber = t.SalesOrderNumber
AND FactInternetSales.SalesOrderLineNumber = t.SalesOrderLineNumber
)
;
SET @seq_start = @seq_end
SET @seq_end = (@seq_start+@batch_size);
SET @batch_iterator +=1;
END
Duraklatma ve ölçeklendirme kılavuzu
Ayrılmış SQL havuzu, ayrılmış SQL havuzunuzu isteğe bağlı olarak duraklatmanızı, sürdürmenizi ve ölçeklendirmenizi sağlar. Ayrılmış SQL havuzunuzu duraklattığınızda veya ölçeklendirdiğinizde, tüm uçuş içi işlemlerin hemen sonlandırıldığını anlamanız önemlidir; açık işlemlerin geri alınmasına neden olur. İş yükünüz duraklatma veya ölçeklendirme işleminden önce uzun süre çalışan ve tamamlanmamış bir veri değişikliği yapmışsa, bu çalışmanın geri alınması gerekir. Bu geri alma işlemi ayrılmış SQL havuzunuzu duraklatma veya ölçeklendirme süresini etkileyebilir.
Önemli
Hem hem DELETE
de UPDATE
tam olarak günlüğe kaydedilen işlemlerdir ve bu nedenle bu geri alma/yineleme işlemleri, eşdeğer en az günlüğe kaydedilen işlemlerden önemli ölçüde daha uzun sürebilir.
En iyi senaryo, ayrılmış bir SQL havuzunu duraklatmadan veya ölçeklendirmeden önce veri değişikliği işlemlerinin tamamlanmasına izin vermektir. Ancak bu senaryo her zaman pratik olmayabilir. Uzun bir geri alma riskini azaltmak için aşağıdaki seçeneklerden birini göz önünde bulundurun:
- CTAS kullanarak uzun süre çalışan işlemleri yeniden yazma
- İşlemi öbeklere bölün; satırların bir alt kümesinde çalışma
Sonraki adımlar
Yalıtım düzeyleri ve işlem sınırları hakkında daha fazla bilgi edinmek için bkz. Ayrılmış SQL havuzundaki işlemler. Diğer En İyi Yöntemlere genel bakış için bkz . Ayrılmış SQL havuzu en iyi yöntemleri.