آشنایی با ویژگی Batch Mode On Rowstore در SQL Server

مقدمه

در این مقاله قصد داریم به بررسی Bach Mode On Rowstore، موارد کاربرد و تاثیر آن بر میزان CXPACKET Wait بپردازیم. Bach Mode On Rowstore یک ویژگی جدید است که در SQL Server 2019 ارائه شده است. این ویژگی مزیت اجرا به صورت Batch Mode را در کوئری‌‌هایی که Columnstore Index‌ها شرکت ندارند، فراهم می‌سازد. Batch Mode On Rowstore سبب افزایش Performance کوئری‌های آنالیزی می‌شود.
اجرا به صورت Batch Mode بدین معنی است که در هر زمان نزدیک به ۹۰۰ ردیف پردازش می‌گردد، این موضوع سبب افزایش Performance نسبت به پردازش Row by Row می‌شود.ساده ترین توضیحی که برای CXPACET Wait وجود دارد این است که این نوع Wait زمانی اتفاق می‌افتد که Plan اجرایی کوئری به صورت Parallel باشد. معمولا بیشترین نوع Wait بر روی اغلب سیستم ها از همین نوع Wait است.

اجرا به صورت Parallel و CXPACKET wait و Config‌هایی که می‌توان روی سرور جهت تعدیل این نوع از Wait انجام داد بحث مفصلی است و نیاز به مقاله‌ی جداگانه دارد.
در ادامه با بررسی مثال‌هایی مزیت استفاده از Batch Mode On Rowstore و تاثیر آن بر کاهش CXPACKET wait را نشان خواهیم داد. در این مثال‌ها از SQL Server 2019 استفاده می‌گردد.
کد زیر یک Database با نام TestDB را ایجاد می‌نماید:

Use master
GO
If DB_ID (N'TestDB') Is Not Null
Begin
 Alter Database TestDB Set Single_User With Rollback Immediate
 Drop Database TestDB
End
GO
Create Database TestDB On Primary
 (Name = TestDB, FileName = N'D:\DataBases\Data\TestDB.mdf', Size = 1024 mb, FileGrowth = 256 mb, MaxSize = Unlimited)
Log On
 (Name = TestDBLog, FileName = N'D:\DataBases\Data\TestDB.ldf', Size = 1024 mb, FileGrowth = 256 mb, MaxSize = Unlimited)
GO

بعد از ایجاد Database یک جدول با نام Test و یک جدول Temp ایجاد می‌نماییم و آنها را با داده‌های فرضی پر می‌کنیم:

Use TestDB
GO
Drop Table If Exists Test
Create Table Test
(ID Int Not Null, Cl2 Int Not Null, Cl3 Nvarchar(50))
GO
;With Tbl (C) As
(Select 0 Union All Select 0),
Tbl2(C) AS (Select t1.C From Tbl t1 Cross Join Tbl t2),
Tbl3(C) AS (Select t1.C From Tbl2 t1 Cross Join Tbl2 t2),
Tbl4(C) AS (Select t1.C From Tbl3 t1 Cross Join Tbl3 t2),
Tbl5(C) AS (Select t1.C From Tbl4 t1 Cross Join Tbl4 t2),
Tbl6(C) AS (Select t1.C From Tbl5 t1 Cross Join Tbl3 t2),
Tbl7(C) AS (Select ROW_NUMBER() Over (Order By (Select Null)) From Tbl6)
Insert Into Test With (TABLOCK)
Select C, C % 8, CONCAT(N'Cl2', C) From Tbl7
GO
Create Unique Clustered Index IXCID On Test (ID)
GO
DROP TABLE If Exists #Tbl
Create Table #Tbl (ID Int)
Insert into #Tbl values (0)
Insert into #Tbl values (2)
Insert into #Tbl values (4)
Insert into #Tbl values (6)
GO

قبل از اینکه ادامه بدهیم جهت جلوگیری از Disk IO نیاز داریم که داده‌ها را Cache نماییم.
بنابراین کوئری زیر را اجرا می‌نماییم:

Select Cl2, MAX(Test.ID) As MaxID From Test
Inner join #Tbl On Test.Cl2 = #Tbl.ID
Group By Cl2

برای نمایش CPU Time و Elapsed Time گزینه مربوط به Statistics Time را On نموده و آمار مربوط به Wait را Clear می‌نماییم.
کوئری فوق را مجدد اجرا می‌نماییم:

Set Statistics Time On
GO
DBCC SQLPerf('sys.dm_os_wait_stats', Clear)
GO
SQL Server Execution Times:
   CPU time = 172 ms,  elapsed time = 77 ms.

تصویر زیر آمار مربوط به CXPACKET Wait Time را نمایش می‌دهد:

Select * From sys.dm_os_wait_stats Where wait_type = 'CXPACKET'

همان طور که مشاهده می‌نمایید CXPACKET wait time برابر با یک میلی ثانیه است. (این زمان ارتباط مستقیم به سخت افزار دارد.) در ادامه Compatibility Level مربوط به Database را برابر ۱۴۰ قرار می‌دهیم.

ALter Database TestDB Set Compatibility_Level = 140
GO
DBCC sqlperf('sys.dm_os_wait_stats', clear)
GO
Select Cl2, MAX(Test.ID) As MaxID From Test
Inner join #Tbl On Test.Cl2 = #Tbl.ID
Group By Cl2
SQL Server Execution Times:
   CPU time = 859 ms,  elapsed time = 290 ms.

بررسی Paln اجرایی کوئری

مجدد Compatibility Level مربوط به Database را به ۱۵۰ تغییر می‌دهیم و کوئری را اجرا می‌نماییم:

ALter Database TestDB Set Compatibility_Level = 150
Select Cl2, MAX(Test.ID) As MaxID From Test
Inner join #Tbl On Test.Cl2 = #Tbl.ID
Group By Cl2

تصویر زیر Properties مربوط به Clustered Index Scan را نمایش می‌دهد:همان طور که مشاهده می نمایید Mode اجرای کوئری به حالت Batch می‌باشد. رکوردها به صورت مساوی بین CPU Core ها تقسیم نشده‌اند. از تقسیم Actual Number of Rows for All Execution بر Actual Number of Batch تقریبا به عدد ۹۰۰ می‌رسیم.
نکته: اجرا به حالت Batch مخصوص اجرای Parallel نیست، در حالت اجرا به شکل سریال نیز می‌تواند وجود داشته باشد. فقط به معنی پردازش تقریبا ۹۰۰ ردیف در هر واحد زمان است.

بررسی Batch Mode On Rowstore  از دیدگاهی دیگر

کوئری‌های آنالیزی بر روی جداول سیستم‌هایی OLTP که به شکل Row-Based ذخیره شده‌اند اجرا نمی‌شوند. درنتیجه ممکن است به نظر بیاید که این ویژگی اصولا کمکی به بهبود Performance نمی‌کند و شاید استفاده‌ایی نداشته باشد.
نخست اینکه همه سازمان‌ها دارای Data WareHouse نیستند و گزارش‌های آنالیزی روی جداولی که به شکل Row-Based ذخیره شده اند اجرا می‌شوند. واقعا نباید کوئری‌های آنالیزی را بر روی سیستم‌های OLTP اجرا نمود و این گونه از سیستم‌ها تا زمانی که اصلاح شوند می‌توانند از این ویژگی استفاده مطلوب نمایند. این سیستم‌ها ممکن است از تکنیک Data Partitioning استفاده نمایند.
می‌توان از Filtered Columnstore Index نیز استفاده نمود اما ایجاد و نگهداری ایندکس‌ها هزینه دارد.
حالت دیگر این است که فرض نمایید سازمان شما یک سرور را فقط جهت ذخیره سازی داده‌ها و عملیات DML در نظر گرفته است و هیچ گونه گزارشی بر روی این سرور اجرا نمی‌شود. این گونه سیستم ها معمولا از تکنولوژی Memory Optimized Table استفاده می‌نمایند. داده‌ها با یک Delay بسیار کم به سرور دیگری منتقل می‌شوند و وب سرویس‌های بسیار زیادی از این سرور دوم ریز رکوردها را بر اساس بازه تاریخ دریافت می‌نمایند و وظیفه اصلی سیستم پاسخ گویی به این وب سرویس‌ها است. در این گونه سیستم‌ها ممکن است ذخیره سازی رکوردها به شکل Row-Based و استفاده از Data_Compression از نوع Page Compression بهتر از ذخیره سازی به صورت Columnar جواب دهد. قطعا علاوه بر وب سرویس‌هایی که ریز رکورد دریافت می‌نمایند، یک سری از کوئری های آنالیزی هم بر روی این جداول اجرا خواهد شد.
این کوئری‌ها می‌توانند از ویژگی Batch Mode On Rowstore به خوبی بهره‌مند گردند.
در اینجا نیز می‌توان از NonClustered Columnstore Index استفاده نمود اما همان طور که گفته شده ایجاد و نگهداری ایندکس‌ها بخصوص بر روی جداولی با حجم بالا بسیار هزینه بر است. البته اجرا به حالت Batch فقط یکی از ویژگی های Columnstore Index  می‌‌باشد.
Columnstore Index‌ها فشرده سازی بیشتری دارند (بستگی به میزان تکراری بودن دارد) و فقط ستون‌های مورد نیاز را پردازش می‌‌نمایند. تا قبل از SQL Server 2019 با ایجاد یک Columnstore Index جعلی از ویژگی اجرا به حالت Batch بر روی جداول Row Store استفاده می‌شد که نوعی Hack هم محسوب می شد.