خانه SQL Server Buffer Pool Extensions در SQL Server 2014 SQL Server افزایش سرعت SQL Server نوشته شده توسط: تورج عزیزی تاریخ انتشار: ۳۰ بهمن ۱۳۹۴ آخرین بروزرسانی: 23 دی 1403 زمان مطالعه: 6 دقیقه ۰ (۰) Buffer Pool Extensions، که در SQL Server 2014 منتشر شد مورد بررسی قرار دهیم. همانطوری که می دانید Buffer Pool یکی از مصرف کننده های اصلی حافظه در SQL Server است. وقتی دیتا را رسانه ذخیره سازی می خوانید، دیتا در Buffer Pool ذخیره می شود. هر چه حافظه اصلی بیشتری داشته باشید، Buffer Pool بزرگتری هم خواهید داشت (که از طریق تنظیمات Max Server Memory ست می شود). برای درک بهتر مفاهیم آموزش جامع SQL Server را مطالعه کنید. بیشترین مشتریان SQL Server با مشکل محدودیت حافظه اصلی در database server ها مواجه هستند: وقتی تمام slot های حافظه اصلی اشغال شده اند، چطور می شود حافظه اضافی را در سرور نصب کرد؟ البته که می توانید به سرور بزرگتری مهاجرت کنید، اما آن هم یک داستان دیگر است… راه حل این مشکل بخصوص انتشار Buffer Pool Extensions در SQL Server 2014 است. با کمک Buffer Pool Extensions، SQL Server لایه دیگری از سلسله مراتب حافظه را بوجود می آورد. بیایید به تصویر زیر نگاهی بیندازیم: مفهوم Buffer Pool Extensions همانطوری که می بینید شما در بالا خود 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 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 محدودیت های Buffer Pool Extensions اولین محدودیتی که شما با آن مواجه هستید این است که 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 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ ha_zarabi_vb6@outlook.com ۰۹ / ۰۶ / ۹۶ - ۰۲:۴۵ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم من یک سئوال داشتم اگر ما BUFFER POOL EXTENSION را غیرفعال کنیم و دوباره از ram های server بخواهیم استفاده بکنیم آن page هایی که در BUFFER POOL EXTENSION قرار دارند آیا در دیسک به صورت فیزیکی نوشته می شوند مثلا ما ۱۰ gb اطلاعات درون BUFFER POOL EXTENSION داریم حالا اگر بخواهیم غیرفعال کنیم این قابلیت رو آیا ۱۰ gb در دیسک نوشته می شود منظورم در فایلهای mdf یا ndf هستند؟ با تشکر از مقاله بسیار عالی شما پاسخ به دیدگاه تورج عزیزی ۱۶ / ۰۳ / ۹۸ - ۰۳:۵۸ سلام از اونجایی که فقط clean page ها یعنی پیج هایی که عاری از تغییر هستند به bpe منتقل میشوند نگرانی از این بابت وجود ندارد چون قبلا این پیج ها توسط checkpoint روی دیسک نوشته شده اند. اطلاعات بیشتر: Buffer Pool Extension Details SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the buffer pool extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager handles the movement of clean pages between the L1 and L2 caches. The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components. SSD Buffer Pool Extension Architecture When enabled, the buffer pool extension specifies the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server. پاسخ به دیدگاه تورج عزیزی ۱۶ / ۰۳ / ۹۸ - ۰۲:۵۱ سلام یک پیج در حافظه اصلی اگر تغییر کرده باشد و اگر به دلیل فشار کمبود حافظه قرار باشد از حافظه خارج شود همزمان یک عمل checkpoint اجرا میشود که باعث می شود هم تغییرات روی دیسک اعمال شود و بعد به bpe منتقل میشود یعنی نسخه ای که روی bpe قرار دارد همیشه روی دیسک هم وجود دارد و نگرانی در مورد غیر فعال کردن bpe وجود ندارد. پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۰۹ / ۰۶ / ۹۶ - ۰۲:۴۵ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم من یک سئوال داشتم اگر ما BUFFER POOL EXTENSION را غیرفعال کنیم و دوباره از ram های server بخواهیم استفاده بکنیم آن page هایی که در BUFFER POOL EXTENSION قرار دارند آیا در دیسک به صورت فیزیکی نوشته می شوند مثلا ما ۱۰ gb اطلاعات درون BUFFER POOL EXTENSION داریم حالا اگر بخواهیم غیرفعال کنیم این قابلیت رو آیا ۱۰ gb در دیسک نوشته می شود منظورم در فایلهای mdf یا ndf هستند؟ با تشکر از مقاله بسیار عالی شما پاسخ به دیدگاه تورج عزیزی ۱۶ / ۰۳ / ۹۸ - ۰۳:۵۸ سلام از اونجایی که فقط clean page ها یعنی پیج هایی که عاری از تغییر هستند به bpe منتقل میشوند نگرانی از این بابت وجود ندارد چون قبلا این پیج ها توسط checkpoint روی دیسک نوشته شده اند. اطلاعات بیشتر: Buffer Pool Extension Details SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the buffer pool extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager handles the movement of clean pages between the L1 and L2 caches. The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components. SSD Buffer Pool Extension Architecture When enabled, the buffer pool extension specifies the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server. پاسخ به دیدگاه تورج عزیزی ۱۶ / ۰۳ / ۹۸ - ۰۲:۵۱ سلام یک پیج در حافظه اصلی اگر تغییر کرده باشد و اگر به دلیل فشار کمبود حافظه قرار باشد از حافظه خارج شود همزمان یک عمل checkpoint اجرا میشود که باعث می شود هم تغییرات روی دیسک اعمال شود و بعد به bpe منتقل میشود یعنی نسخه ای که روی bpe قرار دارد همیشه روی دیسک هم وجود دارد و نگرانی در مورد غیر فعال کردن bpe وجود ندارد. پاسخ به دیدگاه تورج عزیزی ۰۹ / ۱۲ / ۹۴ - ۰۱:۳۴ سلام دوست عزیز این مقاله از سایت sqlpassion.at انتخاب شده میتونید در سایت sqlskills.com و سایت های معروف دیگر هم حساب کنید. پاسخ به دیدگاه غلامحسین عبادی ۰۹ / ۱۲ / ۹۴ - ۱۲:۴۳ با سلام و عرض ادب ممنون از زحمات شما دوست عزیز . اگر راجع به موضوع Buffer Pool Extensions لینک سایتی را میدانید لطفا اطلاع دهید تا مطالعه بیشتری راجع به این موضوع داشته باشم . سپاسگزارم . پاسخ به دیدگاه سید سیاوش گلچوبیان ۰۸ / ۱۲ / ۹۴ - ۰۳:۵۷ البته موضوع، سرعت بالای RAM Drive نسبت به SSD نبود، مسئله نحوه قلبه کردن به محدودیت RAM با حفظ حداکثری Performance بود.قطعا RAM Drive راه حل بسیار پر سرعتی است ولی باید مسئله قیمت آن را نیز مدنظر قرار داد (که البته برای سازمانها اصلا عدد زیاد و مهمی نیست)، در عین حال محدودیت های RAM Drive ها را نیز باید در نظر داشت (البته من در مورد RAM Drive های سخت افزاری صحبت میکنم، زیرا RAM Drive های نرم افزاری برای بحث ما هیچ ارزشی ایجاد نمیکنند) مانند:– محدودیت برخی از آنها در پشتیبانی از تکنولوژی های جدید SATA (عمدتا SATA 1 هستند، مانند i-Ram)– پشتیبانی آنها از RAM های نسل DDR 2 (مانند i-Ram, ACARD,DDRDrive)– محدودیت اسلات های حافظه آنها (حداکثر ۸ اسلات ۸ گیگ ارائه میدهند مانند ACARD)– محدودیت زیاد آنها در مقدار حافظه قابل پشتیبانی (تا جایی که من اطلاع دارم بیشترین ظرفیت را ACARD ارائه میدهد با حجم حداکثر 64GB) البته که اگر هیچ یک از این محدودیت ها برای ما مصداق نداشته باشد و با آنها مشکلی نداشته باشیم، RAM DRIVE راه حل بسیار پر سرعت و مناسبی میباشد (البته RAM Drive داریم تا RAM Drive، همونطور که SSD داریم تا SSD ، مثلا IOPS محصول i-Ram نسبت به خیلی از SSD ها مقدار کمتری است که اصلا جالب نیست، صد البته تنها به واسطه این شاخص نمیتوان قضاوت کرد و گفت i-Ram بد است زیرا همین i-Ram از Response time بسیار بهتری نسبت به همان SSD ها برخوردار است) پاسخ به دیدگاه Hamid J. Fard ۰۸ / ۱۲ / ۹۴ - ۰۵:۵۸ Z-Drive R2 تا ۲ ترابایت فضا قبول می کنه. پاسخ به دیدگاه سید سیاوش گلچوبیان ۰۸ / ۱۲ / ۹۴ - ۱۱:۵۳ Z-Drive R2 جزء RAM Drive ها نیست و از خانواده SSD با تکنولوژی NAND Flash-MLC هست و مانند دیگر SSD ها با محدودیت تعداد Write مواجه است در حالیکه RAM درایورها، از خانواده SDRAM هستند و با همچون محدودیتی مواجه نیستند، علاوه بر این سرعت های وحشتناکی ارائه میکنند (مثلا انواع DDR3 1066 Dual Channelها میتوانند تا 17GB/s خروجی/Transfer rate داشته باشند) که متاسفانه اکثرا بدلیل استفاده از پورت SATA در RAM Drive ها، نرخ انتقال آنها به شدت محدود میشود (SATA 3 خروجی ۱.9GB/s ارائه میدهد و یا Thunderbolt 3 نرخ ۴.8GB/s را و بالاترین سرعت ها را میتوان از PCI Express 3,4 16 lane گرفت که حدود 15GB/s و ۳۱.5GB/s است و ظاهرا در حال حاضر فقط این دو هستند که گلوگاه استفاده از سرعت بالای SDRAM ها نیستند).متاسفانه در بازار، من RAM Drive های مبتنی بر DDR3,4 ندیدم و فقط DDR2 موجود بوده، که اکثرا یا با SATA1 (با نرخ 150MB/s) کار میکرده و یا در برخی برندها نیز با PCI Express (خوشبختانه) ولی در هر صورت حجم آنها به 64GB محدود بود. پاسخ به دیدگاه Hamid J. Fard ۰۹ / ۱۲ / ۹۴ - ۰۸:۳۰ اره یادم نبود. پس در آخر بهترین گزینه حافظه اصلی است. پاسخ به دیدگاه سیدمحمد حسینی ۰۵ / ۱۲ / ۹۴ - ۰۳:۲۳ باز هم ببخشید فقط یه پیشنهاد اگر سایر دستورات مرتبط با این مبحث رو هم کمی ارائه میکردید(فقط یک دستور ارائه شده) بسیار بهتر میشد باز هم ممنون پاسخ به دیدگاه مسعود طاهری ۰۷ / ۱۲ / ۹۴ - ۰۸:۲۴ –buffer_pool_extension غیر فعال کردن ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF GO –B. Returning the number of cached pages in the buffer pool extension file SELECT COUNT(*) AS cached_pages_count FROM sys.dm_os_buffer_descriptors WHERE is_in_bpool_extension = 0 — Clean buffer pool + BPE DBCC DROPCLEANBUFFERS GO — Check pages in BP for the AdventureWorks2014 database SELECT CASE is_in_bpool_extension WHEN 1 THEN ‘SSD’ ELSE ‘RAM’ END AS location, COUNT(*) AS nb_pages, COUNT(*) * 8 / 1024 AS size_in_mb, COUNT(*) * 100. /(SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors) AS percent_ FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID(‘AdventureWorks2014’) GROUP BY is_in_bpool_extension GO پاسخ به دیدگاه سیدمحمد حسینی ۰۵ / ۱۲ / ۹۴ - ۰۳:۵۲ سلام ممنون بابت مقاله خوبتون همچنین از آقا حمید و جناب طاهری عزیز بابت توضیحات تکمیلی پاسخ به دیدگاه مهدی ربانی ذبیحی ۰۵ / ۱۲ / ۹۴ - ۰۸:۱۱ با سلام و خسته نباشید خیلی مقاله عالی و میشه گفت مقاله خاصی بود ممنون از مهندسین که در مورد مقاله بحث کردن با تشکر پاسخ به دیدگاه حمیدقلیپور ۰۲ / ۱۲ / ۹۴ - ۰۶:۵۳ با سلام و احترام بایستی بگم عجب مقاله جالب و چالش برانگیزی بود و اساتید به نکات بسیار خوبی اشاره کردن. با تشکر از تورج عزیز پاسخ به دیدگاه سید سیاوش گلچوبیان ۰۱ / ۱۲ / ۹۴ - ۱۰:۴۳ درضمن فراموش کردم به این موضوع اشاه کنم که خوشبختانه این قابلیت (SSD Trim) در سیستم عامل های Win2008R2, Win7, Win8.1, Win10 ساپورت میشه، البته Win7 یکسری محدودیت در این خصوص دارد. پاسخ به دیدگاه 1 2
