سه نکته کلیدی در کارایی WINDOW FUNCTION - مقدمه

  یک عبارت و دستور Sql ممکن است در کتاب آموزشی و بر روی کاغذ بسیار سریع و بهینه معرفی شود، اما آنچه ارزشمند است، کارایی آن دستور در زمان اجرا است. همانطور که می دانید در جهت اجرای بهینه هر دستوری، توجه به یک سری نکات الزامی است به عنوان مثال نوشتن دستورات Sargable در کوئری، طراحی مناسب ایندکس و یا ملاحظات پیکربندی TempDB و ... . اگرچه Optimizer در اجرای توابع WINDOW FUNCTION یکسری بهینه سازی مانند Fast Track و ... را به صورت خودکار اعمال می کند اما در این آموزش سه نکته کلیدی در کارایی WINDOW FUNCTION را که کنترل و مدیریت آن به عنوان طراح کوئری برعهده ما است را بررسی می کنیم. شاید هر کدام از این سه نکته کلیدی نیازمند ارائه یک مقاله آموزشی جداگانه باشد اما سعی شده است ضمن یک معرفی اجمالی از هر کدام، بیشتر به بررسی نحوه استفاده و تأثیرگذاری آن ها در کارایی توابع WINDOW FUNCTION بپردازیم. پیشنهاد می شود قبل از شروع مطالعه این آموزش، آموزش های نحوه خواندن EXECUTION PLAN و Rows و Range در WINDOW FUNCTION را مطالعه کنید.

دانلود اسکریپ های لازم برای آموزش 

---

 سه نکته کلیدی در کارایی WINDOW FUNCTION - معرفی

در مجموعه آموزش های کوئری نویسی، به معرفی و بررسی انواع توابع WINDOW FUNCTION پرداختیم. جهت ارزیابی کارایی این توابع، در ابتدا لازم است با عوامل تأثیر گذار بر کارایی آن ها آشنا شویم و سپس در یک آموزش جداگانه به مقایسه کارایی WINDOW FUNCTION با روش های قدیمی مورد استفاده، قبل از معرفی این توابع می پردازیم.

 سه نکته کلیدی در کارایی WINDOW FUNCTION - ایندکس گذاری

در مباحث performance tuning همواره ایندکس به عنوان قلب و روح بهینه سازی معرفی می شود. همانطور که می دانید مبحث ایندکس گذاری در sql server بسیار گسترده است و در این  قسمت از آموزش، تنها نحوه ایجاد ایندکس مناسب در جهت افزایش کارایی WINDOW FUNCTION را بررسی می کنیم. ایندکس ایجاد شده جهت افزایش کارایی WINDOW FUNCTION  باید یک POC ایندکس باشد. به منظور آشنایی بهتر با این مورد، از کوئری زیر که به منظور محاسبه Runing Total در جدول Transactions ایجاد شده بوسیله اجرای اسکریپت ابتدای آموزش، طراحی شده است استفاده می کنیم:(خروجی کوئری را در آموزش WINDOW FUNCTION و پایان محدودیت های GROUP BY مشاهده کنید)

SELECT actid, tranid, val,
  SUM(val) OVER(PARTITION BY actid ORDER BY tranid  ROWS UNBOUNDED PRECEDING) AS RuningTotal
FROM dbo.Transactions
where actid=1

مشاهده Execution Plan کوئری:

اگر مراحل اجرای کوئری بالا را دنبال کنیم، پس از بازیابی رکوردهای جدول از ایندکس کلاستر(علامت warning به دلیل عدم وجود statistics است)، رکوردهای خروجی بر اساس فیلد actid گروهبندی می شوند(عملگر seqment با شماره 2) در ادامه به منظور انجام محاسبه بر روی یک پارتیشن در هر لحظه، از عملگرهای sequence project و seqment استفاده شده است(شماره 3 و 4 در تصویر بالا)، با توجه به فریم تعریف شده rows Unbounded Preceding نیاز به نگهداری موقت یک کپی از رکوردهای هر پارتیشن است که برای این منظور از window spool استفاده می شود(شماره 5) جهت محاسبه تابع SUM از stream aggregate استفاده شده و نهایتاً عملگر computed scalar نیز در صورت وجود نتیجه 0 در تابع SUM آن را به NULL تبدیل می کند.

اگر اسکریپت ایجاد جدول Transactions را از ابتدای آموزش دانلود و مشاهده کنید، خواهید دید که این جدول شامل کلید اصلی بصورت زیر است:

CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)

ایندکس کلاستر ایجاد شده به ازای کلید اصلی بالا دارای سه ویژگی زیر است:

     1. بر اساس فیلد actid مرتب شده است - فیلد Partition BY در کوئری بالا

     2. بر اساس فیلد tranid نیز مرتب است - فیلد Order BY در کوئری بالا

     3. حاوی همه فیلدهای مورد نیاز در خروجی کوئری می باشد - Covering Index

در واقع می توانیم بگوییم که ایندکس کلاستر ایجاد شده به ازای کلید اصلی روی جدول Transactions یک POC ایندکس است:

     P: مرتب بودن بر اساس فیلد گروهبندی(partition by) در عبارت ()OVER

     O: مرتب بودن بر اساس فیلد Order By در عبارت ()OVER

     C: حاوی همه فیلدهای موجود در دستور Select

با تغییر مرتب سازی از tranid به val در هر پارتیشن، دیگر ایندکس کلاستر موجود در جدول Transactions ویژگی POC را از دست می دهد. مقایسه هزینه اجرای دو کوئری:

افزایش هزینه 4 برابری در اجرای کوئری دوم، به دلیل انجام عملیات مرتب سازی(sort) در TempDB است(به علت وجود علامت warning در این عملگر). با توجه به وجود ایندکس با ویژگی POC در کوئری اول، این هزینه مرتب سازی حذف شده است.

 سه نکته کلیدی در کارایی WINDOW FUNCTION - عملیات SPOOLING

 به طور خلاصه از عملگر spool برای تهیه یک کپی از رکوردها استفاده می شود. هدف از spool می تواند به منظور حفظ consistency در اجرای تراکنش باشد مانند جلوگیری از بروز halloween problem در بروزرسانی رکوردها و یا به منظور افزایش کارایی باشد مثلاً نگهداری رکوردها و یا نتایج میانی پراستفاده، در یک محل مانند TempDB، برای جلوگیری از مراجعه مجدد به جدول و یا انجام محاسبات تکراری. جهت ذخیره سازی رکوردها و یا نتایج میانی از جداول داخلی با نام worktable استفاده می شود(ساختاری شبیه جدول موقت). چند نوع عملگر spooling در sql server وجود دارد مانند Table Spool ،Index Spool و ... اما عملگر spool مورد استفاده در WINDOW FUNCTION با نام Window Spool شناخته می شود.

نکته کلیدی در کارایی WINDOW FUNCTION و مرتبط با spooling این است که آیا عملیات spool بر روی دیسک انجام می شود و یا در حافظه اصلی. به عبارت دیگر محل نگهداری یک کپی از رکوردها، در TempDB و بر روی دیسک است و یا در ram سرور و بر روی حافظه اصلی؟

نکته: Window Spool به دو صورت On-Disk Spool و یا In-Memory spool ممکن است انجام شود.

با توجه به اینکه در execution plan برای هر دو حالت On-Disk Spool و In-Memory spool آیکن مشابهی نمایش داده می شود، برای تشخیص نوع spooling در Window Spool می توان از روش های زیر استفاده کرد:

     1. فعال کردن STATISTICS IO قبل از اجرای کوئری و بررسی میزان Read از worktable

     2. ایجاد یک session جدید در Extended Events برای کنترل رویداد window_spool_ondisk_warning

    3. استفاده از profiler و بررسی میزان (write(write to disk

در این آموزش، از روش اول استفاده می کنیم و در صورتیکه در خروجی statistics io تعداد عملیات خواندن از worktable زیاد باشد به معنای رخ دادن  On-Disk Spool است و در حالتیکه تعداد عملیات خواندن از worktable برابر با صفر باشد به معنای رخ دادن In-Memory spool است(به منظور آشنایی شما با روش های دوم و سوم، توضیح مختصری ارائه خواهد شد).
 نکته: در صورت تعیین نکردن فریم در window function به صورت پیش فرض از فریم RANGE UNBOUNDED PRECEDING استفاده می شود.

  در کوئری زیر از Aggregate window function بدون فریم بندی استفاده شده است:

با توجه به اینکه تعداد عملیات خواندن زیادی از worktable انجام شده است، در نتیجه عملگر window spool موجود در execution plan، عملیات spool رو بر روی دیسک انجام داده است و در واقع spooling از نوع On-Disk Spool است.

این مورد را در profiler نیز می توان مشاهده کرد. در کوئری زیر از فریم پیش فرض RANGE UNBOUNDED PRECEDING استفاده شده است. جهت تشخیص نوع spooling، در profiler بایستی میزان write را بررسی کنیم. در صورتیکه تعداد write برابر با صفر باشد از  In-Memory spool استفاده شده و در غیر این صورت از On-Disk Spool استفاده شده است(در profiler مقدار write به معنای write to disk است):

به عنوان روش سوم جهت تشخیص On-Disk Spool از In-Memory spool می توان از Extended Events استفاده کرد:

 

توجه داشته باشید که در صورت استفاده از RANGE برای تعیین فریم، همواره spooling بر روی دیسک و در Tempdb انجام شده(On-Disk Spool) و منجر به کاهش کارایی خواهد شد. بنابراین:

     1. در صورت استفاده از توابعی که دارای فریم بندی هستند(مانند aggregate window function و یا Window offset functions) تا حد امکان از ROWS برای تعیین فریم استفاده کنید.

     2. در صورت عدم نیاز به فریم بندی، جهت جلوگیری از اعمال فریم بندی پیش فرض با RANGE، به طور صریح فریم ROWS UNBOUNDED PRECEDING را در عبارت ()OVER بنویسید.

در کوئری زیر با تغییر فریم از RANGE به ROWS، بدون تغییر در نتیجه خروجی، On-Disk Spool به In-Memory spool تبدیل می شود:

بررسی میزان write(برابر با صفر) در profiler پس از اجرای کوئری نیز بیانگر استفاده از memory جهت عملیات spooling است:

نکته:  در زمان استفاده از ROWS اگر تعداد رکوردهای هر فریم بیش از 10 هزار عدد باشد از On-Disk Spool استفاده می شود.

لازم به ذکر است که در نکته بالا حد آستانه اشاره شده در تعداد رکوردهای هر فریم، تا نسخه sql server 2017 می باشد و ممکن است این تعداد در نسخه های بعدی تغییر کند.

به عنوان یک مثال عملی در مورد نکته فوق، دو کوئری زیر را طراحی کرده ایم:

--*********************************************************ROWS
--استفاده می شود in-memory spool اگر تعداد رکوردهای هر فریم کمتر یا مساوی با 10هزار باشد از  ROWS در زمان استفاده از 
--10,000 - 1 + 1 =10000    تعداد رکورد هر پارتیشن
SET STATISTICS IO ON
SELECT actid, tranid, val,
  MAX(val) OVER(PARTITION BY actid  ORDER BY tranid 
        ROWS BETWEEN 9999 PRECEDING  AND 9999 PRECEDING) AS maxval
FROM dbo.Transactions;
SET STATISTICS IO OFF 

--استفاده می شود on-disk spool اگر تعداد رکوردهای هر فریم بیشتر از 10هزار باشد از  ROWS در زمان استفاده از 
--10,001 - 1 + 1 =10001     تعداد رکورد هر پارتیشن
SET STATISTICS IO ON
SELECT actid, tranid, val,
  MAX(val) OVER(PARTITION BY actid   ORDER BY tranid
        ROWS BETWEEN 10000 PRECEDING AND 10000 PRECEDING) AS maxval
FROM dbo.Transactions;
SET STATISTICS IO OFF 

خروجی STATISTICS IO به صورت زیر است:

 همانطور که ملاحظه کردید، جهت بررسی spooling در window function علاوه بر توجه به میزان read و write(با توجه به روش مورد استفاده) در worktable، بایستی حد آستانه(threshold) تعداد رکورد موجود در هر فریم در صورت استفاده از ROWS را نیز کنترل نمایید.

سه نکته کلیدی در کارایی WINDOW FUNCTION - قابلیت Batch mode processing

امکان Batch Mode Processing در sql server2012 برای بعضی از عملگرها(مانند filter , compute scalar, hash match) معرفی شد و ایده اصلی در پردازش دسته ای این است که به جای تکرار عملیات یکسان به ازای تک تک رکوردها بصورت جداگانه، در یک مرحله، عملیات یکسان را برای همه رکوردها انجام دهیم. همانطور که در آموزش WINDOW FUNCTION چیست اشاره شد در sql server 2016 با معرفی یک عملگر جدید به نام Window Aggregate Operator و ایجاد قابلیت Batch Processing در اجرای این عملگر، افزایش چشمگیری در اجرای اکثر توابع window function ایجاد شد. 

در sql server جهت اجرای یک Window functions در ابتدا بایستی window(مجموعه ای از رکوردها) ایجاد شده و سپس تابع مربوطه به طور جداگانه بر روی هر گروه اعمال شود. برای این کار از مجموعه عملگرهایی مانند Window Spool, Segment, Sequence Project و Compute Scalar استفاده می شود اما با قابلیت Batch processing عملیات انجام شده توسط کلیه این عملگرها بوسیله یک عملگر تحت عنوان Window Aggregate Operator هندل می شود. به منظور بهره مندی از این قابلیت الزاماً جدول مورد نظر بایستی دارای columnstore index باشد. به منظور مقایسه کارایی اجرای Window functions  در حالات مختلف، سه جدول با شرایط زیر ایجاد می کنیم:

     1. جدول Transactions دارای تنها یک ایندکس کلاستر(به ازای کلید اصلی) 

     2. جدول TransactionsCS دارای تنها یک ایندکس columnstore 

     3. جدول TransactionsDCS با یک ایندکس کلاستر(به ازای کلید اصلی) و یک nonclustered columnstore index

اگر اسکریپت موجود در ابتدای آموزش را اجرا کرده باشید، تا اینجا جدول Transactions را در دیتابیس testwindow دارید. در ادامه جهت ایجاد جدول TransactionsCS کافیست اسکریپت زیر را اجرا کنید:

-- TransactionsCS ایجاد جدول
SELECT * INTO dbo.TransactionsCS FROM dbo.Transactions;

--COLUMNSTORE INDEX ایجاد 
CREATE CLUSTERED COLUMNSTORE INDEX idx_cs ON dbo.TransactionsCS;

با توجه به معرفی filtered nonclustered columnstore indexe در sql server 2016، به منظور بهره مندی از قابلیت Batch Mode Processing کافیست تا یک ایندکس columnstore خالی در جدول سوم به صورت زیر ایجاد کنیم:

-- TransactionsDCS  ایجاد جدول
SELECT * INTO dbo.TransactionsDCS FROM dbo.Transactions;

-- ایجاد کلید اصلی در جدول به همراه ایندکس کلاستر
ALTER TABLE dbo.TransactionsDCS ADD CONSTRAINT PK_TransactionsDCS PRIMARY KEY(actid, tranid);

--خالی COLUMNSTORE INDEX ایجاد یک 
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_cs_dummy ON dbo.TransactionsDCS(actid)
  WHERE actid = -1 AND actid = -2;

با توجه به شرط متناقض موجود در ایجاد ایندکس، sql server بدون مراجعه به جدول و به سرعت ایندکس idx_cs_dummy را ایجاد می کند. پس از ایجاد جداول مورد نظر که هر کدام حاوی 10 میلیون رکورد هستند، کوئری زیر را به منظور محاسبه مجموع مبلغ تراکنش های هر اکانت در هر یک از سه جدول اجرا و نتایج را مقایسه می کنیم(تعداد رکورد جداول با تغییر مقدار متغییرهای @num_partitions و @rows_per_partition در زمان اجرای اسکریپت قابل تغییر است):

SELECT actid, tranid, val,
  SUM(val) OVER(PARTITION BY actid) AS acttotal
 From -- هر کدام از جداول

اجرای کوئری را با جدول Transactions شروع می کنیم. با وجود ایندکس کلاستر در این جدول، در واقع رکوردها در حالت row mode در ساختار B-Tree ذخیره شده اند:

مراحل انجام کوئری در شکل بالا توضیح داده شده است. همانطور که مشاهده می کنید در اجرای کوئری از on-disc spool استفاده شده و تعداد عملگر زیادی نیز جهت انجام محاسبات استفاده شده است. در شکل زیر، مدت زمان اجرا، تعداد read و write و هزینه cpu قابل مشاهده است:

در ادامه کوئری را بر روی جدول TransactionsCS دارای Clustered columnstore index اجرا می کنیم:

با توجه به اینکه رکوردهای استخراج شده از columnstore index مرتب(sort) شده نیستند در sql server 2016 جهت اجرای هر چه سریعتر عملیات مرتب سازی، عملگر sort نیز در حالت Batch processing اجرا می شود.

در پلن اجرایی(execution plan) فوق، چند عملگر و عبارت جدید نسبت به پلن هایی که معمولاً بصورت روزمره مشاهده می کنیم وجود دارد. اول اینکه یک حالت اجرایی جدید با نام Batch mode در tooltip عملگرها و در قسمت Actual Execution Mode وجود دارد. دومین مورد حذف شدن spooling در اجرای کوئری بدون اعمال تغییر در فریم بندی پیش فرض است. نکته جالب دیگر در پلن بالا، کاهش تعداد عملگرهاست. همانطور که اشاره شد عملگر window aggregate کلیه عملیاتی که عملگرهای قبلی انجام می دادند را با سرعت بیشتر و در یک مرحله انجام می دهد که این مورد علاوه بر افزایش سرعت اجرا، منجر به کاهش میزان I/O نیز می شود. این مورد با مشاهده هزینه اجرایی کوئری در تصویر زیر مشهود است:

مقدار صفر در write بیان کننده عدم استفاده از دیسک جهت انجام محاسبات میانی و یا spooling است. زمان اجرایی، میزان I/O و هزینه اجرایی cpu به میزان چشم گیری بهبود پیدا کرده است.

در آخرین مرحله، نوبت به استفاده از جدول TransactionsDCS که دارای یک ایندکس کلاستر rowstore B-tree به همراه filtered nonclustered columnstore index بدون رکورد است می رسد:

در این حالت با توجه به وجود ایندکس کلاستر و مرتب بودن رکوردها بر اساس فیلد گروهبندی(partition by) دیگر نیازی به استفاده از عملگر sort(که اغلب هزینه بر می باشد) نیست. دقت کنید که عملیات scan در ایندکس کلاستر در حالت row mode انجام شده است. مجدداً در این حالت نیز به دلیل وجود columnstore index از عملگر window aggregate استفاده شده است و اما هزینه اجرایی کوئری بالا در جدول TransactionsDCS در شکل زیر قابل مشاهده است:

 نمودار زیر مقایسه ای از زمان اجرای کوئری در سه جدول TransactionsCS ،Transactions و TransactionsDCS را نمایش می دهد:

در کوئری طراحی شده جهت مقایسه کارایی، تنها نحوه اجرای تابع Aggregate window function را بدون وجود فریم در سه جدول بررسی کردیم. شما به عنوان تمرین می توانید کارایی سایر توابع را به همراه حالات مختلف فریم بندی نیز بررسی کنید در اینصورت خواهید دید که تابع Ntile در حالت rowstore/batch mode کارایی بهتری نسبت به حالت columnstore/batch mode دارد. همینطور در توابع LEAD و LAG تنها زمانی از batch mode Window Aggregate operator استفاده می شود که offset برابر با یک باشد(حالت پیش فرض). موارد دیگری در مورد سایر توابع و انواع فریم های مورد استفاده در توابع نیز وجود دارد که در آموزشی جداگانه بیشتر در مورد آن ها و مبحث Batch Mode Processing مطلب خواهم نوشت.

---

 سه نکته کلیدی در کارایی WINDOW FUNCTION - جمع بندی

در این آموزش با سه اصطلاح POC Index، Spooling و Batch Mode Processing آشنا شدیم و نحوه تأثیرگذاری آن ها در کارایی window functions را دیدیم. روش هایی جهت تشخیص انجام Spooling بر روی دیسک و یا حافظه اصلی معرفی شد و در پایان نیز امکان فوق العاده Batch Mode Processing و عملگر Window Aggregate در اجرای توابع window function بررسی شد. پیشنهاد می شود جهت آشنایی بیشتر با موارد مطرح شده در این آموزش، با استفاده از اسکریپت های تهیه شده، انواع حالات را که فرصت بررسی آن ها در طول آموزش سه نکته کلیدی در کارایی WINDOW FUNCTION میسر نبود به عنوان تمرین انجام دهید.

• قبلی
• بعدی