درک مفهوم Fill Factor index در SQL Server

هر کاربر پایگاه داده ، کاربر نهایی و نیز  developer ویا adminstrator  به اهمیت indexها برای اجرای بهتر query ها واقف هستند. وجود index  در جداول بزرگ پایگاه داده به SQL Server کمک می کند تا هنگام اجرای queryها عملکرد بهتری داشته باشد. هنگام ایجاد و باز سازی این indexها برای جداول نیاز به تعیین fill factor یا به عبارتی فاکتور پر شدن داریم.

معرفی فاکتور پر شدن (fill factor):

در واقع این فاکتور، درصد پر بودن صفحات سطح برگ این index را مشخص می نماید. هنگامی یک index تعریف و یا بازسازی می شود،حافظه ی مربوط به این صفحات با توجه به مقدار  این فاکتور پر بودن شروع به پر شدن می کند  و نیز مقداری از این  فضای برای استفاده های بعدی خالی می ماند. البته چنین حالتی تنها در صورتی ایجاد می شود که  این مقدار یک مقدار مشخص به غیر از مقدار صفر یا صد باشد. برای مثال در صورتی که این فاکتور برای صفحات  index  برابر با ۷۰ باشد، به این معنی که چیزی در حدود  ۷۰ درصد آن ها نوشته شده و پر است در نظر گرفته می شود، البته ۳۰ درصد ار این فضا برای مصارف آینده مورد استفاده قرار می گیرد.

برای مثال جدولی به نام tblTransactionTable داریم. با کمک دستور sp_spaceused می توان از مقدار فضایی که توسط این جدول پر شده و نیز  index های مربوط به این جدول مطلع شد. خروجی این دستور نشان می دهد که این جدول حاوی ۱۱٫۷ میلیون سطر  می باشد و در حال حاضر هیچ index خاصی برای این جدول تعریف نشده است.

برای تعیین این معیار دو گام داریم:

گام اول : بیشترین مقدار با به عبارتی مقادیر بالای( fill factor)

با کمک SSMS یک index  از نوع nonclustated که تنها حاوی یک ستون با نام Customer_ID است را تعریف می کنیم که البته این ستون به عنوان کلید نیز در نظر گرفته می شود

از منوی option مقدار این فاکتور را ۱۰۰ قرار می دهیم. در صورتی که این مقدار برای index تعریف شده از قبل تعیین نشده باشد ، این مقدار با مقدار فعلی index که در instance مشخص می شود، برابر می شود. باید توجه کنیم که مقادیر صفر و صد برای این فاکتور مساوی در نظر گرفته می شوند و در هر دو حالت صفحات پر می باشند. از آنجا که در این مثال هدف ما تنها بررسی این فاکتور می باشد از سایر گزینه ها در این صفحه چشم پوشی می کنیم. در انتها با انتخاب گزینه ok فرآیند ایجاد index را به اتمام می رسانیم.

پس از ایجاد index دستور sp_spaceused را دوباره اجرا می کنیم تا مقدار فضای گرفته شده توسط این index را که چیزی در حدود ۳۲۴۰۷۰ کیلو بایت می باشد را بیابیم .قبل از ایجاد index،فضا محاسبه شده برای این index تنها برابر با ۷۲ کیلو بایت بود.

هنگامی که داده های جدول اصلی همانند یک سیستم پشتیبان تصمیم که تغییر داده ها در آن مکرر اتفاق نمی افتد ولی به صورت منظم و برنامه ریزی شده وجود دارد، بدون تغییر باقی بمانند و یا دچار تغییر ناچیزی شوند  می توان از مقادیر بالای fill factor استفاده نمود.

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

از طرف دیگر  اگر index داشته باشیم که به طور مداوم تغییر کند ، به مقادیر پایین این فاکتور برای ذخیره ی فضای خالی برای indexهای ورودی جدید نیاز داریم. به عبارت بهتر SQL server باید تقسیم بندی این صفحات را  متناسب با مقادیر جدید index ها انجام دهد.

گام دوم : مقادیر پایین این فاکتور پرشدن(fill factor)

در ادامه index ایجاد شده در مرحله ی قبل را حذف می نماییم و index همانند آن ایجاد می کنیم .تنها تفاوت این index با index مرحله ی قبل در مقدار این فاکتور می باشد. مقدار این فاکتور در  مرحله دوم برابر با  ۵۰ است که به کمک اسکریپت موجود در تصویر زیر آن را تغییر می دهیم.

۵۰ درصد از فضای index در این مرحله پر است .با اجرای دوباره ی دستورsp_spaceused می توانیم اطلاعات جزئی درباره ی جداول حاوی index به دست آوریم. همان طور که در تصویر زیر می بینید مقدار فضای موجود برای این index برابر با ۶۵۱۶۴۰ کیلو بایت ،تقریبا دو برابر فضای index ایجاد شده با مقدار fill factor 50 درصد  در مرحله ی قبل می باشد. دلیل وجود چنین تفاوتی خالی گذاشتن نیمی از صفحات در گام دوم می باشد.

به کار بردن مقادیر کمتر از ۱۰۰ برای این fill factor در شرایطی که داده های زیادی و به صورت مکرر  به indexهای اصلی اضافه می شود ضروری می باشد. با اضافه  شدن این مقادیر جدید باید فضای کافی برای ذخیره ی آنها نیز داشته باشیم، از این رو می توانیم از تقسیم بندی صفحات متناسب با مقادیر indexها برای این منظور استفاده نماییم. لازم به یادآوری است که این تقسیم بندی براساس تعداد دفعات نیاز به تکرار این فرآیند اتفاق می افتد.

صرفا جهت یاد آوری:

  1. هنگامی که مقدار ۰ یا ۱۰۰ مشخص می شد ، صفحه با بیشترین مقدار خود پر می شود، البته این مقدار لزوما ۱۰۰ درصد نیست . برای توجیه این نکته می توان به index های کلیدی و زمان نهایی و صرف نظرکردن از  فضای استفاده نشده، اشاره نمود.
  2. اندازه ی index متناسب با تعداد ستون های  صفحه ی اول مربوط به آن index می باشد . بنابراین هرچه تعداد این ستون ها بیشتر شود نیاز به تعریف فضای ذخیره سازی بزرگتر برای این index ها داریم.
  3. این index ها برای ذخیره سازی به فضا احتیاج دارند، از این رو باید index هایی با مقدار فاکتور پر شدن مناسب ایجاد نماییم. پیش از آنکه این indexها در SQLserver  به شکل واقعی قرار گیرند، مقدار نهایی این فاکتور را باید تعیین و بررسی نماییم.
1 پاسخ

تعقیب

  1. […] درک مفهوم Fill Factor Index در SQL server – تارنمای رسمی … […]

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *