Buffer Pool Extensions در SQL Server 2014

درباره نویسنده

تورج عزیزی

تورج عزیزی

مشاوره، تحقیقات و انجام پروژه در زمینه SQL Server Performance Tuning

20 دیدگاه

  1. Hamid J. Fard

    Hamid J. Fard

       مقاله جالبی بود اما یک نکته:
    در استفاده از Buffer Pool Extension  حتی درایو SSD هم زیاد کمکی نمی کند به این دلیل که درایو SSD برای نوشتن اول عملیات حذف را انجام می دهد و بعد عملیات نوشتن. اگر می خواهید از این قابلیت استفاده کنید باید یک درایو بر اساس RAM داشته باشید یا اینکه حجم حافظه را زیاد کنید. در نظر داشته باشید که حتی اگر SSD هم استفاده کنید در آخر نیاز به I/O دارد.
    پاسخ
    1. مسعود طاهری

      مسعود طاهری

      سلام حمید جان 

      من این حالت را تست کردم از این قابلیت از هیچی بهتر است. مخصوصا زمانی که بنا به دلایلی واقعا امکان تهیه RAM وجود ندارد و سخت افزار متناسب با Workload سیستم نمی باشد.
      طرف اومده یه VM فسقلی را به ازای N نفر آدم تخصیص داده. امکان بازنویسی کوئری و… هم به این سادگی وجود ندارد و …
      توی این حالت ایندکس و Tune کردن و… تا حدی جواب میده اگر به سورس کوئری دسترسی نداشته باشی و  اجازه تغییر نداشته باشی مجبوری دست به دامن این نوع ویژگی ها بشی
      هنوز که هنوز است توی ایران اکثر کوئری ها و … برای SQL Server نوشته میشه رنگ و بوی SQL Server 2000 را می دهد. بنابراین در برخی از مواقع روی این نوع قابلیت ها باید حساب باز کرد
      پاسخ
    2. سید سیاوش گلچوبیان

      سید سیاوش گلچوبیان

         سلام
      البته موضوع افت Performance درخصوص عملیات Delete بر روی SSD ها رو میشه با استفاده از قابلیت SSD TRIM به شدت بهبود داد، تا افت Performance ای که در حالت overwrite برای SSD ها پیش میاد (به همون دلیلی که ذکر کردید) جبران شه. البته پیش نیازش اینه که :

      1. سیستم عامل شما از قابلیت SSD TRIM پشتیبانی کند
      2. firmware اس اس دی شما هم از قابلیت SSD TRIM پشتیبانی کند
      3. با استفاده از دستور fsutil behavior set DisableDeleteNotify 0 قابلیت TRIM رو فعال کنیم
      پاسخ
      1. Hamid J. Fard

        Hamid J. Fard

           خب در آخر بازهم IO اضافی هستش که به سیستم تحمیل می شود و اینکه کلا اگر هم بهترین SSD استفاده شود باز هم سرعت بالایی نسبت به RAM درایو ها ندارد. و البته SSD طول عمر محدودی دارد و اگر احیانا Block خراب باشد هیچ موقع اخطار IO را به Sql server منتقل نمی کند.

      2. مسعود طاهری

        مسعود طاهری

            سلام 

        قبول دارم. اما بار IO هر چقدر هم باشه باز هم در SSD خیلی نسبت به دیسک مکانیکی بهتر است.
        و وقتی RAM هم نشه به این سادگی (یا RAM هم متناسب با Workload بانک اطلاعاتی نباشد) تهیه کرد و سایز DB  بالا باشه میشه روی این گزینه حساب باز کرد.
        در خصوص خراب شدن SSD هم باید این نکته را در نظر داشت که SSD تنها نوع دیسک هایی است که می توان زمان خراب شدن آن را محاسبه کرد که برای اینکار APP وجود دارد. در ضمن تکنولوژی هایی مانند Trim که سیاوش عزیز هم اشاره کرد راه کار مناسبی است. 
      3. Hamid J. Fard

        Hamid J. Fard

            بار IO منظورم Pending IO , Queue IO در SQL Server و سیستم عامل برای هر پردازش است که زمان زیادی را تلف می کند.

  2. سید سیاوش گلچوبیان

    سید سیاوش گلچوبیان

       درضمن فراموش کردم به این موضوع اشاه کنم که خوشبختانه این قابلیت (SSD Trim) در سیستم عامل های Win2008R2, Win7, Win8.1, Win10 ساپورت میشه، البته Win7 یکسری محدودیت در این خصوص دارد.

    پاسخ
  3. حمیدقلیپور

    حمیدقلیپور

        با سلام و احترام

    بایستی بگم عجب مقاله جالب و چالش برانگیزی بود و اساتید به نکات بسیار خوبی اشاره کردن.
    با تشکر از تورج عزیز
    پاسخ
  4. مهدی ربانی ذبیحی

    مهدی ربانی ذبیحی

        با سلام و خسته نباشید 

    خیلی مقاله عالی و میشه گفت مقاله خاصی بود ممنون از مهندسین که در مورد مقاله بحث کردن با تشکر
    پاسخ
  5. سیدمحمد حسینی

    سیدمحمد حسینی

        سلام

    ممنون بابت مقاله خوبتون
    همچنین از آقا حمید و جناب طاهری عزیز بابت توضیحات تکمیلی
    پاسخ
  6. سیدمحمد حسینی

    سیدمحمد حسینی

    باز هم ببخشید

    فقط یه پیشنهاد
    اگر سایر دستورات مرتبط با این مبحث رو هم کمی ارائه میکردید(فقط یک دستور ارائه شده) 
    بسیار بهتر میشد
    باز هم ممنون
    پاسخ
    1. مسعود طاهری

      مسعود طاهری

          –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
      پاسخ
  7. سید سیاوش گلچوبیان

    سید سیاوش گلچوبیان

       البته موضوع، سرعت بالای 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)
    – محدودیت اسلات های حافظه آنها (حداکثر 8 اسلات 8 گیگ ارائه میدهند مانند ACARD)
    – محدودیت زیاد آنها در مقدار حافظه قابل پشتیبانی (تا جایی که من اطلاع دارم بیشترین ظرفیت را ACARD ارائه میدهد با حجم حداکثر 64GB)

    البته که اگر هیچ یک از این محدودیت ها برای ما مصداق نداشته باشد و با آنها مشکلی نداشته باشیم، RAM DRIVE راه حل بسیار پر سرعت و مناسبی میباشد (البته RAM Drive داریم تا RAM Drive، همونطور که SSD داریم تا SSD ، مثلا IOPS محصول i-Ram نسبت به خیلی از SSD ها مقدار کمتری است که اصلا جالب نیست، صد البته تنها به واسطه این شاخص نمیتوان قضاوت کرد و گفت i-Ram بد است زیرا همین i-Ram از Response time بسیار بهتری نسبت به همان SSD ها برخوردار است)

    پاسخ
    1. Hamid J. Fard

      Hamid J. Fard

      Z-Drive R2 تا ۲ ترابایت فضا قبول می کنه.

      پاسخ
      1. سید سیاوش گلچوبیان

        سید سیاوش گلچوبیان

           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 خروجی 1.9GB/s ارائه میدهد و یا Thunderbolt 3 نرخ 4.8GB/s را و بالاترین سرعت ها را میتوان از PCI Express 3,4 16 lane گرفت که حدود 15GB/s و 31.5GB/s است و ظاهرا در حال حاضر فقط این دو هستند که گلوگاه استفاده از سرعت بالای SDRAM ها نیستند).
        متاسفانه در بازار، من RAM Drive های مبتنی بر DDR3,4 ندیدم و فقط DDR2 موجود بوده، که اکثرا یا با SATA1 (با نرخ 150MB/s) کار میکرده و یا در برخی برندها نیز با PCI Express (خوشبختانه) ولی در هر صورت حجم آنها به 64GB محدود بود.

      2. Hamid J. Fard

        Hamid J. Fard

            اره یادم نبود.

        پس در آخر بهترین گزینه حافظه اصلی است.
  8. غلامحسین عبادی

    غلامحسین عبادی

       با سلام و عرض ادب

    ممنون از زحمات شما دوست عزیز . اگر راجع به موضوع  Buffer Pool Extensions لینک سایتی را میدانید لطفا اطلاع دهید تا مطالعه بیشتری راجع به این موضوع داشته باشم . سپاسگزارم .

    پاسخ
  9. تورج عزیزی

    تورج عزیزی

    سلام دوست عزیز
    این مقاله از سایت sqlpassion.at انتخاب شده میتونید در سایت sqlskills.com و سایت های معروف دیگر هم حساب کنید.  

    پاسخ
  10. حسن ضرابی

    حسن ضرابی

    با سلام و خسته نباشید خدمت شما
    از مقاله بسیار عالیتون واقعا ممنونم
    من یک سئوال داشتم اگر ما BUFFER POOL EXTENSION را غیرفعال کنیم و دوباره از ram های server بخواهیم استفاده بکنیم آن page هایی که در BUFFER POOL EXTENSION قرار دارند آیا در دیسک به صورت فیزیکی نوشته می شوند مثلا ما 10 gb اطلاعات درون BUFFER POOL EXTENSION داریم حالا اگر بخواهیم غیرفعال کنیم این قابلیت رو آیا 10 gb در دیسک نوشته می شود منظورم در فایلهای mdf یا ndf هستند؟
    با تشکر از مقاله بسیار عالی شما

    پاسخ

    1. تورج عزیزی

      سلام
      یک پیج در حافظه اصلی اگر تغییر کرده باشد و اگر به دلیل فشار کمبود حافظه قرار باشد از حافظه خارج شود همزمان یک عمل checkpoint اجرا میشود که باعث می شود هم تغییرات روی دیسک اعمال شود و بعد به bpe منتقل میشود یعنی نسخه ای که روی bpe قرار دارد همیشه روی دیسک هم وجود دارد و نگرانی در مورد غیر فعال کردن bpe وجود ندارد.

      پاسخ
    2. تورج عزیزی

      تورج عزیزی

      سلام
      از اونجایی که فقط 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.

      پاسخ

ارسال یک نظر

نشانی ایمیل شما منتشر نخواهد شد.

تمامی حقوق مادی و معنوی این وب سایت متعلق به نیک آموز می باشد.
این سایت توسط تیم آموزش برنامه نویسی نیک آموز مدیریت می شود.

ثبت نام دوره آموزشی SQL Server ویژه برنامه نویسان به صورت اقساطی
ثبت نام در دوره
close-image