آشنایی با Buffer Pool Extension در SQL Server

Buffer Pool Extention، رم از بخش‌های مختلفی تشکیل شده است که مهم ترین آن بخش Buffer Pool می‌باشد. SQL Server از این بخش برای Cache کردن Page ها قبل از نوشتن در دیسک و بعد از خواندن از دیسک استفاده می‌کند. Page در حالت کلی به دو بخش در Buffer قرار دارند که در ادامه به آنها می پردازیم.

مفهوم Buffer Pool Extension

۱. Clean Page: Page هایی هستند که هیچ دستور DML برروی آنها صورت نگرفته است، این Page ها معمولاً برای Read Operation مانند دستور Select استفاده می‌شوند.
۲. Dirty Page: Page هایی هستند که دستورات DML بر روی آنها صورت گرفته مانند: Delete ,Update ,Insert این Pageها با Log Recordها مرتبط هستند، Page ها قبل از این تمیز(Clean) بشوند در دیسک ذخیره می‌شوند، که این همان مکانیزم WAL می‌باشد.

مفاهیم Buffer Pool Extention

•  SSD: نسل جدید از Storageها که سرعت بالایی در انتقال و ذخیره سازی فایل و Dataها دارند.
• Buffer: در SQL Server بافر شامل Page های ۸ KB می‌شود، که Data Page یا Index Pageها می‌شود.
•  Page تا زمانی که بافر نیاز به فضا برای ورود Page ها جدید نکند در Buffer نگه‌داری می‌شود. Pageهای تغییر یافته شده (Dirty Page) ها از Buffer به دیسک منتقل می‌شوند.
•  Buffer Pool: که Buffer Cache هم صدا می‌زنند، یک منبع عمومی برای تمامی پایگاه داده به منظور Cache کردن Pageهایشان می‌باشد. بیشترین و کم‌ترین Size آن به مدت زمان اجرا بودن Instance و تنظیمات مربوط به sp_configure می‌باشد. تا زمانی که Instance اجرا می‌باشد Pageها به صورت Cache شده ذخیره می‌گردنند.

مفهوم Buffer Pool Extension (BPE)

Clean Pageها به مدت زیادی در Buffer در دسترس می‌باشد مگر اینکه نیاز به یک Page جدید باشد، تا Pageهای قدیمی حذف و Page های جدید جایگزین آن‌ها شوند. این عمل حذف Page های قدیمی برای SQL Server بسیار هزینه بر خواهد بود، تا بتواند مقدار فضای مورد نیاز را برای Cache کردن Page های جدید ایجاد کند. برای حل مشکل با افزودن RAM به سرور برطرف می‌شود ولی این راه حل بسیار گران قیمت برای سازمان ها خواهد بود .شرکت ماکروسافت در نسخه SQL Server 2014 خود ویژگی Buffer Pool Extension (BPE) را معرفی کرد،این ویژگی باعث افزایش Buffer Pool Cache با کمک SSD می‌شود. SSD را می‌توان مستیماً به سرور فیزیکی و یا SAN متصل کرد. BPE در واقع یک Cache ثانویه می‌باشد فقط برای Clean Page.وقتی که Buffer Pool RAM نیاز به فضا می‌شود Page Clean را به Cache ثانویه منتقل می‌کنند. از مهم ترین ویژگی BPE افزایش سرعت و کارئی SQL Server I/O شده است. برخی از ویژگی‌های موثر BPE در لیست زیر اشاره شده است:

•  افزایشThroughput I/O.
• کاهش تاخیر I/O.
• افزایش Transaction Throughput.
• بهبود عملکرد Read با یک Hybrid Buffer Pool بزرگ‌تر.
• راه اندازی ساده و با قیمت بصرفه به نسبت RAM.
• عدم نیاز به تغییر در معماری Application.
• استفاده بهینه از Buffer Pool در WorkLoad بالا.

در شکل زیر معماری Buffer Pool Extension نمایش داده شده است.


Buffer Pool Extension File اجازه به Buffer Pool Manager از DRAM و NAND-Flash Memory برای ساخت یک Buffer Pool بزرگ‌تر برای نگه‌داری Clean Page به کمک Block های SSD می‌دهد. همان طور که در شکل مشاهده می‌کنید در Relation Engine یک Query بعد از طی نمودن فازهای Query Processing در خواست Data به Storage Engine فرستاده می‌شود. Request ارسال شده به بخش Access Method ارسال می‌شود. Access Method درخواست دریافت شده را به Buffer Manager ارسال می‌کند، Buffer Pool Manage درخواست Get Page را به Buffer Pool (L1) (همان RAM) می‌کند، در صورت موجود بودن Page های مورد نظر Cached Page در Buffer Pool Manager انجام می‌شود. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.
اگر Page مورد نظر در (L1) موجود نباشد در صورت فعال بودن BPE که یک Caching چند سطحی به صورت سلسله مراتبی را ایجاد می‌کند در خواست Read Page از (L2) می‌کند، اگر Page مورد نظر در (L2) هم موجود نباشد (L1) یک درخواست Read I/O به Data File می‌دهد. همان طور که قبلاً هم اشاره شد اگر فضای مناسبی در (L1) موجود نباشد در خواست Write Clean Page ها به (L2) فرستاده می‌شود. برای درک بهتر این موضوع به شکل‌های زیر توجه فرمایید. شما می‌توانید کوئری نویسی را می‌توانید به صورت گام‌به‌گام و به آسانی از نیک آموز یاد بگیرید.

تصویر شماره ۲

تصویر شماره ۳

برای پیاده سازی BPE پیشنهادهایی داده شده است که بتوانیم از این ویژگی به خوبی استفاده کنیم، برای ساخت Buffer Pool Extension File در SSD حتماً Size آن باید از RAM فعلی سیستم بیشتر باشد، توصیه‌ای که شده است Size فایل حتما عددی بین ۴ تا ۸ برابر مقدار RAM فیزیکی سرور باشد و بیشترین عددی که می‌توانیم برای Size فایل در نظر بگیریم عدد ۳۲ می‌باشد که فقط برای Enterprise Edition قابل استفاده می‌باشد. همچنین توصیه دیگر این است که بعد از فعال سازی BPE حتما یک بار سرور را ریست بکنید.

هنگام پیاده سازی BPE حتما به نکات زیر توجه داشته باشید:

• این ویژگی مورد استفاده In Memory OLTP نمی‌باشد.
• این ویژگی تمامی امکانات RAM رو به شما نمی‌دهد و در همه موارد نمی‌تواند جایگزین RAM باشد.
•  امکان کاهش Size فایل در زمان فعال بودن وجود ندارد.
• فقط نسخه‌های ۶۴ بیتی را پشتیبانی می‌کند.
• جهت استفاده از بیشترین توان عملیاتی و همچنین عدم خرابی های ناگهانی در سطح دیسک بهتر است از SSD هایی با RAID 10 استفاده کنیم.

برای پیاده سازی BPE کافی است دستور زیر را اجرا کنید.

ALTER SERVER CONFIGURETION
SET BUFFER POOL EXTENSION ON
(FILENAME= ‘S:SSDCache.BPE’ , SIZE= 128 GB)

در این دستور مثال ما یک درایو SSD که در سیستم ما S تعریف شده و همچنین RAM سرور که ۳۲ GB می‌باشد، Size فایل ۴ برابر RAM در نظر گرفته شده است. برای غیر فعال نمودن BPE هم می‌توان از دستور زیر استفاده نمود.

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

سخن پایانی

Buffer Pool Extention، رم از بخش‌های گوناگونی تشکیل شده است که مهم ترین آن بخش Buffer Pool می‌باشد. SQL Server قبل از نوشتن در دیسک و بعد از خواندن از دیسک از این بخش برای Cache کردن Page ها استفاده می‌کند. در این مقاله به صورت کامل به دو بخش در Buffer پرداختیم. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.