خانه SQL Server آشنایی با Buffer Pool Extension در SQL Server SQL Server نوشته شده توسط: علی لواسانی ۳۰ مهر ۱۳۹۹ زمان مطالعه: 6 دقیقه ۳ (۲) مقدمه RAM از بخشهای مختلفی تشکیل شده است که مهم ترین آن بخش Buffer Pool میباشد. SQL Server از این بخش برای Cache کردن Pageها قبل از نوشتن در دیسک و بعد از خواندن از دیسک استفاده میکند. Page در حالت کلی به دو بخش در Buffer قرار دارند. ۱. Clean Page: Page هایی هستند که هیچ دستور DML برروی آنها صورت نگرفته است، این Page ها معمولاً برای Read Operation مانند Select استفاده میشوند. ۲. Dirty Page: Page هایی هستند که دستورات DML بر روی آنها صورت گرفته مانند: Delete,Update,Insert این Pageها با Log Recordها مرتبط هستند، Page ها قبل از این تمیز(Clean) بشوند در دیسک ذخیره میشوند، که این همان مکانیزم WAL میباشد. مفاهیم 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 انجام میشود.اگر 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 Extension# آشنایی با Buffer Pool Extension در sql server# آشنایی با Buffer Pool Extention معرفی نویسنده مقالات 1 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده علی لواسانی علی لواسانی هستم که به صورت تخصصی در حوزه SQL Server، در شرکت نیک آموز در کنار استاد عزیزم جناب آقای مهندس طاهری مشغول به فعالیت و مطالعه می باشم. پروفایل نویسنده معرفی محصول مسعود طاهری دوره آموزشی Performance Tuning در SQL Server (گروه 15) ثبت نام حضوری8.700.000 تومانثبت نام غیرحضوری5.700.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ احمد ۱۳ / ۱۲ / ۰۰ - ۰۲:۵۳ اصلا خوب نبود ، واقعا جای کارداد خواهشا مطالب درست و دارای ارزش علمی گذاشته شود ۱ ۱ پاسخ به دیدگاه علی لواسانی ۱۰ / ۱۰ / ۰۱ - ۰۸:۱۰ با سلام ممنون از اینکه وقت گذاشتید مقاله رو خواندید. جهت بهتر شدن مقاله و رشد عملی ممنون میشم نقاط ضعف و ایرادات مقاله رو بیان کنید. با تشکر ۱ پاسخ به دیدگاه هادی ۰۴ / ۰۸ / ۹۹ - ۱۱:۵۷ عالی بود.بسیار ممنون پاسخ به دیدگاه هادی ۰۴ / ۰۸ / ۹۹ - ۱۱:۵۷ عالی بود.بسیار ممنون پاسخ به دیدگاه امیرصدرا علیدوست ۰۳ / ۰۸ / ۹۹ - ۱۱:۱۷ مهندس علی لواسانی بهترین نویسنده سایت خیلی راحت و قابل فهم موضوع آشنایی با SQL Server درBuffer poolExtention پاسخ به دیدگاه امیرصدرا علیدوست ۰۳ / ۰۸ / ۹۹ - ۱۱:۱۷ مهندس علی لواسانی بهترین نویسنده سایت خیلی راحت و قابل فهم موضوع آشنایی با SQL Server درBuffer poolExtention پاسخ به دیدگاه امیرصدرا ۰۱ / ۰۸ / ۹۹ - ۰۵:۵۵ عالی ترین نویسنه ساین دکتر علی لواسانی آشنایی باtion در SQLServer Buffer pool Exten پاسخ به دیدگاه امیرصدرا ۰۱ / ۰۸ / ۹۹ - ۰۵:۵۵ عالی ترین نویسنه ساین دکتر علی لواسانی آشنایی باtion در SQLServer Buffer pool Exten پاسخ به دیدگاه