خانه SQL Server Buffer Pool Extensions در SQL Server 2014 SQL Server افزایش سرعت SQL Server نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۳۰ بهمن ۱۳۹۴ آخرین بروزرسانی: ۱۰ اردیبهشت ۱۴۰۱ زمان مطالعه: 5 دقیقه ۰ (۰) مقدمه در پست امروز قصد دارم Buffer Pool Extensions که در SQL Server 2014 منتشر شد صحبت کنم. همانطوری که می دانید Buffer Pool یکی از مصرف کننده های اصلی حافظه در SQL Server است. وقتی دیتا را رسانه ذخیره سازی می خوانید، دیتا در Buffer Pool ذخیره می شود. هر چه حافظه اصلی بیشتری داشته باشید، Buffer Pool بزرگتری هم خواهید داشت (که از طریق تنظیمات Max Server Memory ست می شود). بیشترین مشتریان SQL Server با مشکل محدودیت حافظه اصلی در database server ها مواجه هستند: وقتی تمام slot های حافظه اصلی اشغال شده اند، چطور می شود حافظه اضافی را در سرور نصب کرد؟ البته که می توانید به سرور بزرگتری مهاجرت کنید، اما آن هم یک داستان دیگر است… راه حل این مشکل بخصوص انتشار Buffer Pool Extensions در SQL Server 2014 است. با کمک Buffer Pool Extensions، SQL Server لایه دیگری از سلسله مراتب حافظه را بوجود می آورد. بیایید به تصویر زیر نگاهی بیندازیم:همانطوری که می بینید شما در بالا خود Buffer Pool را دارید (که خیلی سریع است) و در پایین رسانه ذخیره سازی سنتی را می بینید، که خیلی کند است. و Buffer Pool Extensions هم بین این دو قرار گرفته است. Buffer Pool Extensions خودش شامل یک فایل ساده است (که اصطلاحاً Extension File نامیده می شود) که باید روی رسانه خیلی سریع قرار بگیرد – مثل یک درایو SSD. یک Extension File درست مثل page file در سیستم عامل Windows است. به جای اینکه حافظه فیزیکی اضافی به Database server اضافه کنیم، یک Extension File روی یک درایو SSD پیکربندی کنید – فقط همین! قبل از اینکه بخواهم در مورد پیکربندی و روش استفاده از Buffer Pool Extensions صحبت کنم، می خواهم کمی در مورد معماری و طراحی پشت صحنه Buffer Pool Extensions صحبت کنم. Buffer Pool سنتی در SQL Server همیشه بین Page های تمیز و کثیف (تمیز به معنی عاری از تغییر از زمان قرار گرفتن در حافظه اصلی) تفاوت قائل می شود. یک page کثیف در حافظه تغییر کرده اما هنوز در رسانه ذخیره سازی ثبت نشده است. فرآیندی به نام CHECKPOINT ظرف حدود یک دقیقه صفحات کثیف را روی رسانه ذخیره سازی ثبت می کند، و این به این معنی است که صفحات کثیف ما تمیز می شوند! Buffer Pool Extensions خودش فقط وقتی استفاده می شود که Buffer Pool در SQL Server تحت فشار کمبود حافظه باشد. فشار حافظه به این معنی است که SQL Server نیاز به حافظه بیشتری نسبت به چیزی که در اختیار دارد است. در این شرایط Buffer برخی Page ها را که نسبت به سایرین کمتر به آنها رجوع شده از Buffer Pool خارج می کند. SQL Server در اینجا از یک سیاستی به نام Least Recently Used Policy (LRU) استفاده می کند. حالا اگر شما یک Extension File پیکربندی کرده باشید، SQL Server این page های اخراجی را در آنها می نویسد، به جای اینکه این کار را در رسانه ذخیره سازی که در پایین ترین سطح هرم بالا قرار دارد انجام دهد. اگر page یک page کثیف باشد، این page همزمان در رسانه فیزیکی هم نوشته می شود (از طریق یک عمل asynchronous I/O) . بنابراین شما هیچ دیتایی را مادامی که با Buffer Pool Extensions کار می کنید از دست نمی دهید. در نقطه ای از زمان Extension File شما کاملاً پر می شود. در چنین شرایطی SQL Server باید صفحات قدیمی را از Extension File خارج کند (باز هم از طریق سیاست LRU) و در نهایت آنها را در رسانه فیزیکی بنویسد. Extension File به عنوان یک لایه اضافی بین Buffer Pool و رسانه ذخیره سازی عمل می کند. حالا اجازه دهید ببینیم چطور Buffer Pool Extensions در SQL Server 2014 تنظیم می شود. SQL Server در اینجا دستور ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION را ارائه می کند. ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON ( FILENAME = 'd:ExtensionFile.BPE', SIZE = 10 GB ) GO اولین محدودیتی که شما با آن مواجه هستید این است که Extension File حداقل باید هم اندازه خود Buffer Pool باشد. اگر شما اندازه فایل کوچکتری را تعیین می کنید، یک پیام خطای دوست داشتنی از SQL Server دریافت می کنید: Msg 868, Level 16, State 1, Line 1 Buffer pool extension size must be larger than the current memory allocation threshold 1596 MB. Buffer pool extension is not enabled. و محدودیت بعدی که شما قطعاً با آن مواجهید این است که شما نمی توانید اندازه Extension File را در طول زمان اجرای SQL Server تغییر دهید. مثلاً وقتی می خواهید Extension File را به اندازه ای بزرگتر تغییر دهید، باید Buffer Pool Extensions را غیر فعال کنید و دوباره آن را فعال کنید. و شما در طول زمان انجام این کار با افت Performance مواجه خواهید شد، چون شما لایه مهمی از Caching را در SQL Server غیر فعال می کنید! Be aware of this fact, when you are planning a deployment of the Buffer Pool Extensions for your production environment!!! و علاوه بر این شما نخواهید توانست اندازه Extension File را کاهش دهید، اندازه فعلی همیشه باید از اندازه قبلی بزرگتر باشد. در غیر این صورت با خطای زیر مواجه می شوید: Msg 868, Level 16, State 1, Line 3 Buffer pool extension size must be larger than the current memory allocation threshold 4096 MB. Buffer pool extension is not enabled. Config فعلی Buffer Pool Extensions می تواند از طریق یک dmv به نام sys.dm_os_buffer_pool_extension_configuration مشاهده شود. شما چه زمانی باید از Buffer Pool Extensions استفاده کنید؟ توصیه مایکروسافت بر این است که workload شما باید write-heavy باشد، مانند OLTP workload. و وقتی شما در مورد خود Extension File صحبت می کنید باید یک SSD خیلی سریع خرجش کنید و نه یک هارد دیسک! چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ Hamid J. Fard ۰۱ / ۱۲ / ۹۴ - ۱۱:۰۷ مقاله جالبی بود اما یک نکته: در استفاده از Buffer Pool Extension حتی درایو SSD هم زیاد کمکی نمی کند به این دلیل که درایو SSD برای نوشتن اول عملیات حذف را انجام می دهد و بعد عملیات نوشتن. اگر می خواهید از این قابلیت استفاده کنید باید یک درایو بر اساس RAM داشته باشید یا اینکه حجم حافظه را زیاد کنید. در نظر داشته باشید که حتی اگر SSD هم استفاده کنید در آخر نیاز به I/O دارد. پاسخ به دیدگاه سید سیاوش گلچوبیان ۰۱ / ۱۲ / ۹۴ - ۰۴:۴۷ سلامالبته موضوع افت Performance درخصوص عملیات Delete بر روی SSD ها رو میشه با استفاده از قابلیت SSD TRIM به شدت بهبود داد، تا افت Performance ای که در حالت overwrite برای SSD ها پیش میاد (به همون دلیلی که ذکر کردید) جبران شه. البته پیش نیازش اینه که : سیستم عامل شما از قابلیت SSD TRIM پشتیبانی کند firmware اس اس دی شما هم از قابلیت SSD TRIM پشتیبانی کند با استفاده از دستور fsutil behavior set DisableDeleteNotify 0 قابلیت TRIM رو فعال کنیم پاسخ به دیدگاه Hamid J. Fard ۰۲ / ۱۲ / ۹۴ - ۰۷:۰۴ خب در آخر بازهم IO اضافی هستش که به سیستم تحمیل می شود و اینکه کلا اگر هم بهترین SSD استفاده شود باز هم سرعت بالایی نسبت به RAM درایو ها ندارد. و البته SSD طول عمر محدودی دارد و اگر احیانا Block خراب باشد هیچ موقع اخطار IO را به Sql server منتقل نمی کند. پاسخ به دیدگاه مسعود طاهری ۰۲ / ۱۲ / ۹۴ - ۰۷:۵۹ سلام قبول دارم. اما بار IO هر چقدر هم باشه باز هم در SSD خیلی نسبت به دیسک مکانیکی بهتر است. و وقتی RAM هم نشه به این سادگی (یا RAM هم متناسب با Workload بانک اطلاعاتی نباشد) تهیه کرد و سایز DB بالا باشه میشه روی این گزینه حساب باز کرد. در خصوص خراب شدن SSD هم باید این نکته را در نظر داشت که SSD تنها نوع دیسک هایی است که می توان زمان خراب شدن آن را محاسبه کرد که برای اینکار APP وجود دارد. در ضمن تکنولوژی هایی مانند Trim که سیاوش عزیز هم اشاره کرد راه کار مناسبی است. پاسخ به دیدگاه Hamid J. Fard ۰۸ / ۱۲ / ۹۴ - ۰۵:۴۷ بار IO منظورم Pending IO , Queue IO در SQL Server و سیستم عامل برای هر پردازش است که زمان زیادی را تلف می کند. مسعود طاهری ۰۱ / ۱۲ / ۹۴ - ۱۱:۰۴ سلام حمید جان من این حالت را تست کردم از این قابلیت از هیچی بهتر است. مخصوصا زمانی که بنا به دلایلی واقعا امکان تهیه RAM وجود ندارد و سخت افزار متناسب با Workload سیستم نمی باشد. طرف اومده یه VM فسقلی را به ازای N نفر آدم تخصیص داده. امکان بازنویسی کوئری و… هم به این سادگی وجود ندارد و … توی این حالت ایندکس و Tune کردن و… تا حدی جواب میده اگر به سورس کوئری دسترسی نداشته باشی و اجازه تغییر نداشته باشی مجبوری دست به دامن این نوع ویژگی ها بشی هنوز که هنوز است توی ایران اکثر کوئری ها و … برای SQL Server نوشته میشه رنگ و بوی SQL Server 2000 را می دهد. بنابراین در برخی از مواقع روی این نوع قابلیت ها باید حساب باز کرد پاسخ به دیدگاه 1 2