قابلیت Batch Mode in RowStore در SQL Server

از موضوعاتی که توی مطلب Intelligent query processing in SQL databases بهش اشاره کردیم و قرار بود بیشتر در موردش صحبت کنیم مفهوم Batch Mode on Rowstore هست. چون قراره به فیلم آموزشی مربوط به کنفرانس سال 2019 گروه SQLBits  اشاره کنم، به معرفی کوتاهی درباره این گروه و کنفرانس سالانه SQL Bits می پردازیم. در پایان مثال کاربردی از تاثیر عملکرد قابلیت پردازش Batch Mode در بهبود کارایی و کاهش زمان اجرا کوئری میاریم.

گروه SQLBits  به صورت سالانه بزرگترین کنفرانس اروپایی SQL Server رو در لندن برگزار میکنه و هر دوره  افراد برجسته این رشته برای سخنرانی و ارائه آموزش ها شرکت میکنن.

کنفرانس SQLBits 2020 قرار هست 31 March تا 4 April توی لندن برگزار بشه و تا اینجا اسامی تعدادی از سخنران ها و سرفصل های مورد بحث اعلام شده، سعی میکنیم تو مطلب جداگونه ای بهش بپردازیم.

اما چیزی که به این مطلب مربوطه  SQLBits 2019 هست که توی یکی از ارائه ها Niko Neugebauer مفهوم Batch mode رو در نسخه SQL Server 2019 شرح داده.

خوشبختانه تیم SQLBits ویدئو ارائه های سالهای قبل رو تو سایت خودش منتشر میکنه، برای درک کامل مفهوم Batch Mode on Rowstore بهتون پیشنهاد میکنم که حتما حوصله کنین و این ارائه رو ببینید.

Batch_Execution_Mode_on_the_Rowstore

اما اگه نخواستین این فیلم رو ببینین، تعریف ارائه شده در مورد Batch Mode  رو از سایت blog.sqlauthority.com در ادامه میارم. درضمن Pinal Dave، نویسنده sqlauthority جز افراد ارائه دهنده در سالهای گذشته و همچنین در SQLBits 2020 خواهد بود که امسال با عنوان ارائه SQL Server Performance Tuning Real-World Scenarios  در این کنفرانس شرکت میکنه.

لیست  ارائه های Pinal Dave  مربوط به سال های گذشته:

sqlbits.com/speakers/Pinal_Dave

قرار بود توی این مطلب در مورد  Batch Mode on Rowstore بنویسیم اما بیشتر به کنفرانس SQL Bits پرداخته شد، به نظرم لازمه به Brent Ozar هم اشاره کنم، Brent Ozar در کنفرانس SQLBits 2020  حضور داره و قرار هست  Deadlocks: Lets Do One, Understand It, and Fix It رو ارائه بده.

کم کم سعی میکنیم به سال های گذشته این کنفرانس بپردازیم و کنفرانس 2020 رو هم دنبال کنیم .

Simple Example of Batch Mode in RowStore:

(مطلب از زبان نویسنده اصلی، Pinal Dave  بیان شده)

یکی از دلایلی که من عاشق شغلم هستم، اینکه هر روز با آخرین تکنولوژی کار میکنم و باعث افزایش سرعت کامپیوتر مشتری ها میشم.

اخیرا یکی از مشتری ها سیستم خودش رو به آخرین نسخه Sql Server یعنی 2019 ارتقا داد، بعد از Upgrade اونها انتظار بهبود کارایی چشم گیری رو داشتن، اما به طور کلی هیچ تغییر محسوسی ندیدن.

در نهایت با من تماس گرفتن و ما فرایند Comprehensive Database Performance Health Check رو شروع کردیم. خوشبختانه هنگام کار، من تونستم مثال خوبی از کاربرد Batch Mode in RowStore به مشتری نشون بدم و به این طریق بهبود کارایی در نسخه SQL Server 2019  براشون قابل درک شد.

در نسخه Sql server ،2019 امکان Batch mode رو درحالت Row store پشتیبانی میکنه، چیزی که قبلا فقط به ازاء به کارگیری column store index امکان پذیر بود. مهمترین تاثیری که به کارگیری Batch mode در مقایسه با مدل سنتی Row mode داره، بهبود کارایی در عملیات کوئری های دارای aggregation هست.

مدل سنتی Row mode پردازش رو بر پایه row-by-row  انجام میده اما مدل Batch execution mode پردازش رو بر روی دسته های دیتا ( batches ) انجام میده، برای مثال یک batch میتونه شامل چند صد Row  باشه. تعداد Row ها در هر Batch  بین 64  تا 912 هست. این عدد میتونه توی Update ها یا نسخه های بعدی Sql server  تغییر کنه.

مثال ساده :

یه جدول با دیتا آزمایشی ایجاد میکنیم، توجه داشته باشین که برای این تست حتما به دیتا زیادی نیاز هست.

توی این مثال از دیتابیس AdventureWorks استفاده میکنیم.

USE AdventureWorks2017 GO
-- Create New Table
CREATE TABLE [dbo].MySalesOrderDetail]
( [SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL)
ON [PRIMARY]
GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON
[dbo].[MySalesOrderDetail] ( [SalesOrderDetailID])
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount],[LineTotal],[rowguid],[ModifiedDate]
FROM Sales.SalesOrderDetail S1
GO 50

برای انجام تست اولمون Campatibility level  رو به 14 تغییر میدیم که مربوط به sql server 2017  میشه و Select  رو اجرا میکنیم:

-- 2017
ALTER DATABASE [AdventureWorks2017]
SET COMPATIBILITY_LEVEL = 140
GO
-- Comparing Regular Index with ColumnStore Index
USE AdventureWorks2017
GO
SET STATISTICS IO, TIME ON
GO
-- Select Table with regular Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID ORDER BY ProductID
GO

همونطور که در کوئری پلن ایجاد شده می بینیم Actual Execution Mode به صورت Row  هست.

حالا Campatibility level رو به 15 برمیگردونیم تا از قابلیت Batch mode مربوط به SQL server 2019  استفاده کنیم  و مجددا Select  رو اجرا میکنیم:

-- 2019
ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 150
GO
-- Comparing Regular Index with ColumnStore Index
USE AdventureWorks2017
GO
SET STATISTICS IO, TIME ON
GO
-- Select Table with regular Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO

کوئری پلن مربوط به این حالت، Actual Execution Mode رو به صورت Batch انجام داده.

با مقایسه همزمان این دو حالت ،میبینیم که کوئری پلن ها مشابه و با Logical read نزدیک بهم اجرا شدن اما CPU time مربوط به اجرا کوئری در حالت Batch mode کمتر بوده .

در واقع این نشون میده، پردازش داده ها به صورت دسته ای باعث صرف هزینه کمتری در مقایسه با پردازش تک به تک شده  و زمان اجرا کوئری رو بهبود میده .

من این تست رو روی کوئری های مختلفی ازمایش کردم و به طور کلی میشه گفت در صورتی از حالت Batch mode در کوئری استفاده بشه  بین 20 تا 30 درصد بهبود کارایی خواهیم داشت.