نیک آموز > وبلاگ > SQL Server > دیگر نگران Fragmentation نباشید!!!

دیگر نگران Fragmentation نباشید!!!

نوشته شده توسط: میلاد فیروزی

تاریخ انتشار: ۰۳ بهمن ۱۳۹۴

آخرین بروزرسانی: 30 بهمن 1403

زمان مطالعه: 5 دقیقه

خوب خیلی ها حتما مفهوم Fragmentation را شنیده اید و نگران این مساله هستید که شاید Index های شما هم دچار این مساله شده باشند! فکر می کنم بهتر است ابتدا کمی عقب تر رفته و روی مفهوم Fragmentation تمرکز کنیم تا ببینیم دقیقا مشکل کجاست و از چه چیزی ناشی می شود.

فرض کنید Index شما عملکردی مشابه دفترچه تلفن داشته باشد ، اگر صفحه ای پر شده باشد ناچارید یا فشرده بنویسید و یا اگر این کار ممکن نباشد مجبور می شوید برگه ای سفید در انتهای دفترچه تلفن به نام های جدید اختصاص بدهید که این کار باعث به هم خوردن ترتیب اطلاعاتتان می شود و با دو مشکل بزرگ روبرو خواهید شد،

یک صفحه ی جدید اضافه شده که اطلاعات کمی روی آن قرار دارد (Internal Fragmentation)
ترتیب صفحات دفترچه تلفن شما به هم می خورد (External Fragmentation)

مفهوم Fragmentation

یک راه حل پیش پا افتاده می تواند این باشد که صفحات دفترچه تلفن خود را به نحوی پر کنید که در هر صفحه کمی فضای خالی وجود داشته باشد ، به این ترتیب اگر نام جدیدی اضافه شد می تواند در این فضای خالی جای بگیرد ولی خوب این راه حل خراب شدن دفترچه تلفن را به تعویق می اندازد و نمی تواند کامل جلوی این اتفاق را بگیرد ، پایگاه داده SQL Server هم عملی مشابه را با مفهوم Fill Factor انجام می دهد ، با معین کردن درصد Fill Factor به SQL می گوییم که صفحاتمان تا چند درصد پر باشند تا کمی فضای خالی برای درج رکوردهای جدید وجود داشته باشد.

آپدیت کردن رکوردها هم می تواند مشکل ساز شود ، فرض کنید خانم Pat Down که تا دیروز مجرد بود و نامش در ردیف D قرار داشت امروز با آقای Phil McCann ازدواج می کند و نام خانوادگی اش به McCann تغییر می کند ، حال باید نام وی از ردیف D پاک شود و در ردیف M جا داده شود ، DELETEها هم به دلیل جا گذاشتن فضای خالی مشکل ساز می شوند.(Internal Fragmentation)

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز

تاثیرات مفهوم Fragmentation

Internal Fragmentation بد (داشتن فضاهای خالی زیاد در صفحات) بدان معناست که Index شما از آن اندازه ای که می بایست بزرگتر شده است. به جای اینکه دفترچه تلفن ما ۱۰۰۰ صفحه ای که صد در صد پر هستند ممکن است ۱۱۰۰ صفحه داشته باشیم که ۹۰درصد پر باشند و این بدان معناست که هر زمان نیاز باشد که Index را Scan کنیم ۱۰% بیشتر طول خواهد کشید و چون SQL صفحات خالی را نیز Cache می کند این باز هم بدان معناست که به ۱۰% حافظه RAM بیشتر برای Cache کردن اطلاعات نیازمندیم.پایین ترین سطح Caching در SQL Server یک صفحه می باشد نه یک رکورد. External Fragmentation بد نیز دردسرهای خودش را دارد ، عملکرد ذخیره سازی کندتر خواهد شد ، SQL به جای اینکه صفحات را به ترتیب پیمایش کند مجبور است تا صفحات را به خاطر رکوردهای متفاوت عقب و جلو پیمایش کند. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

رفع مشکل Fragmentation به صورت موقت

مشکل Fragmentation را می توان توسط Rebuild و یا Reorganize کردن ایندکس ها برطرف نمود ، خیلی ها این کار را با Maintenance Plan انجام می دهند ، مشکلی که این برنامه دارد این است که تمامی ایندکس های شما را بدون این که ضرورت یا عدم ضرورت این کار را در نظر بگیرد Rebuild یا Reorganize می کند. ممکن است از آخرین باری که این کار انجام شده روی برخی از جداول شما حتی یک رکورد هم Insert نشده باشد Maintenance Plan این نکته را هم نادیده می گیرد. شما می‌توانید کوئری نویسی را به صورت گام‌به‌گام از نیک آموز فرا بگیرید.

خوب این مساله مشکلاتی را به همراه دارد ، Rebuild و یا Reorganize کردن ایندکس ها باعث می شود تا SQL Server در Transaction Log بنویسد و حجم Log شما افزایش یابد ، هرچقدر این Log بزرگتر شود Log Backupها بیشتر طول خواهد کشید ، اطلاعات بیشتری را برای بحث Mirroring می بایستی از طریق Network انتقال دهیم و Restoreها بیشتر طول خواهد کشید. اتفاق های ناگوارتری هم ممکن است رخ دهد ، برخی از DBAها تصمیم می گیرند تا Fill Factor را مقدار کمتری در نظر بگیرند مثلا ۵۰% با این کا رنصف هر صفحه خالی خواهد ماند و Insertها سریع تر خواهد شد ولی اتفاقی که می افتد این است که Readها دوبرابر کند تر خواهد شد.فرض کنید دفترچه تلفنتان بجای ۱۰۰۰ صفحه پر ۲۰۰۰ صفحه نیمه پرداشته باشد. برای آشنایی بهتر با DBA مقاله راهنمای مسیر شغلی DBA را مطالعه کنید.

رفع مشکل Fragmentation به طور دائم

خوب می توانیم با Cache کردن دیتابیس و یا حداقل اطلاعاتی که بیشتر در دسترس هستند شروع کنیم ، External Fragmentation (ترتیب نادرست صفحات در دیسک) تا زمانی که اطلاعات را از رم می خوانیم اهمیتی ندارند ، اطلاعات رو Cache کرده و خیال خودتان را راحت کنید ، ۳۴۸ گیگ رم چیزی حدود ۱۵ میلیون تومان آب خواهد خورد.

سخن پایانی

مفهوم Fragmentation، کوئری های خود را بررسی کنید و ببینید آیا Query که واقعا تعداد فراخوانی آن زیاد بوده و گیر ایندکس های شما می باشد دارید یا خیر؟ اگر داشتید به فکر اصلاح ایندکس ها باشید ، همیشه Rebuild و یا Reorganize کردن ایندکس ها اولین راه حل نیست. می توانید توسط Query زیر درصد Fragmentation ایندکس های خود را بفهمید و روی آن ها تصمیم بگیرید ، معمولا برای عددی بین ۵ تا ۳۰ Reorganize کردن و برای عددی بالای ۳۰ Rebuild کردن پیشنهاد می شود ، ولی یادتان باشد این فرمول همیشه درست نیست و بهتر است همیشه خودتان ایندکس ها را با توجه به Queryهایتان بررسی کنید و به عنوان یک DBA بهترین تصمیم را بگیرید. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc