SQL Server 2019 ile gelen yenilikleri anlatmaya devam ediyoruz. Bu yazıda Batch Mode on rowstore özelliğinden bahsedip demo yapacağız. Bu özellik SQL Server 2019’un Intelligent Query Processing (Akıllı Sorgu İşleme) adı altındaki özelliklerden biridir.
SQL Server 2012 versiyonu ile hayatımıza giren columnstore index kavramı, özellikle warehouse ve analitik raporlama veritabanlarındaki tablolarda kullanılmaya başlanmıştı. Columnstore index kullanımı bu yapılarda performansı açısından çok önemlidir. SQL Server 2019 ile beraber akıllı sorgu işleme adı altında topladığı özelliklerden biri olan Batch Mode on rowstore, mevcuttaki yapıyı kullanarak columnstore index yapısı gibi sorgularımızı çağırmamızı sağlar. Bu sayede columnstore index oluşturmadan aynı performansı elde etmiş ve ek bir maliyet oluşturmamaktadır. Tabi bu ortamı sağlarken sistem üzerindeki IO kaynaklarını bir miktar tüketmektedir. Özellikle analitik sorgulamalar da kullanıldığını varsaydığımızda CPU kaynak kullanımı olması yüksek ihtimaldir. Bu sebeple performans açısından olumlu bir etki yaratsa da kaynak kullanımlarını izlemek ve kontrollü bir şekilde yönetmek gerekmektedir. Bu özelliği kullanmak için de veritabanımızın Compatibility Level 150 (SQL Server 2019) olması yeterlidir.
Şimdi bu özellikle ilgili bir demo yapalım.
Öncelikle bu uygulamada veritabanı olarak Microsoft’un WideWorldImportersDW isimli test veritabanını kullanacağım. İlgili veritabanına buradan ulaşabilirsiniz.
Veritabanını restore ettikten sonra aşağıdaki script ile çalışma yapacağım tabloyu büyütüyorum.
USE WideWorldImportersDW;
GO
IF OBJECT_ID('Fact.OrderHistory') IS NULL
BEGIN
SELECT [Order Key], [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
INTO Fact.OrderHistory
FROM Fact.[Order];
END;
ALTER TABLE Fact.OrderHistory
ADD CONSTRAINT PK_Fact_OrderHistory PRIMARY KEY NONCLUSTERED ([Order Key] ASC, [Order Date Key] ASC) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX IX_Stock_Item_Key
ON Fact.OrderHistory ([Stock Item Key])
INCLUDE(Quantity)
WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX IX_OrderHistory_Quantity
ON Fact.OrderHistory ([Quantity])
INCLUDE([Order Key])
WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX IX_OrderHistory_CustomerKey
ON Fact.OrderHistory([Customer Key])
INCLUDE ([Total Including Tax])
WITH (DATA_COMPRESSION = PAGE);
GO
IF (SELECT COUNT(*) FROM [Fact].[OrderHistory]) < 3702592
BEGIN
DECLARE @i smallint
SET @i = 0
WHILE @i < 4
BEGIN
INSERT INTO [Fact].[OrderHistory] ([City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key])
SELECT [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
FROM [Fact].[OrderHistory];
SET @i = @i +1
END;
END
GO
IF OBJECT_ID('Fact.OrderHistoryExtended') IS NULL
BEGIN
SELECT [Order Key], [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
INTO Fact.OrderHistoryExtended
FROM Fact.[OrderHistory];
END;
ALTER TABLE Fact.OrderHistoryExtended
ADD CONSTRAINT PK_Fact_OrderHistoryExtended PRIMARY KEY NONCLUSTERED ([Order Key] ASC, [Order Date Key] ASC)
WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX IX_Stock_Item_Key
ON Fact.OrderHistoryExtended ([Stock Item Key])
INCLUDE (Quantity);
GO
IF (SELECT COUNT(*) FROM [Fact].[OrderHistory]) < 29620736
BEGIN
DECLARE @i smallint
SET @i = 0
WHILE @i < 3
BEGIN
INSERT Fact.OrderHistoryExtended([City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key])
SELECT [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]
FROM Fact.OrderHistoryExtended;
SET @i = @i +1
END;
END
GO
UPDATE Fact.OrderHistoryExtended
SET [WWI Order ID] = [Order Key];
GO
İlgili işlem sonucunda log file büyüdüğü için shrink ediyorum.
CHECKPOINT GO DBCC SHRINKFILE (N'WWI_Log' , 0, TRUNCATEONLY) GO SELECT * FROM sys.dm_db_log_space_usage GO
İlk olarak veritabanımın compatibility level ayarını 150 (SQL Server 2019) yapıyorum. Ardından sorgumu ilgili özellik olmadan çalıştırıyorum. Bunun için DISALLOW_BATCH_MODE hinti kullanılmıştır.
Resim – 1
Görüldüğü üzere sorgu 5 sn. sürmüş, Stream Aggregate operatöründe vakit kaybetmiş durumdadır. Table Scan operatörünün Properties alanına baktığımızda Actual Execution Mode alanında Row yazdığını görüyoruz.
Şimdi aynı sorguyu hint kullanmadan yani Batch Mode on rowstore özelliği aktif haliyle çalıştıracağım.
Resim – 2
Resim – 2’de görüldüğü üzere sorgu 1 sn. sürdü. Actual Execution Mode alanında Batch ifadesi yazmakta ve Stream Aggregate operatörünün kaybolduğunu görmekteyiz. Ayrıca tüm operatörlerde sürelerin azaldığına da dikkat etmenizi istiyorum.
Eğer sorguda kullandığımız hintler yerine veritabanı bazında bu özelliği aktif/pasif edebilmek için aşağıdaki sorguları kullanabilirsiniz.
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; -- pasif ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON; -- aktif
Bu yazımızda SQL Server 2019 ile gelen yeniliklerden Batch Mode on rowstore özelliğini anlatıp, performans olan etkisini demo yaparak göstermiş olduk. SQL Server 2019 ile gelen yenilikleri anlatmaya devam edeceğiz. Bir sonraki yazıda görüşmek üzere.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
https://www.mshowto.org
https://techcommunity.microsoft.com/t5/azure-sql/introducing-batch-mode-on-rowstore/ba-p/386256
TAGs: SQL Server 2019, SQL Server 2019 yenilikleri, SQL 2019, Batch Mode, rowstore, columnstore, batch mode on rowstore, row, batch, intelligent query processing
