خوب خیلی ها حتما اصطلاح Fragmentation را شنیده اید و نگران این مساله هستید که شاید Index های شما هم دچار این مساله شده باشند!
فکر می کنم بهتر است ابتدا کمی عقب تر رفته و روی مفهوم Fragmentation تمرکز کنیم تا ببینیم دقیقا مشکل کجاست و از چه چیزی ناشی می شود.
فرض کنید Index های شما عملکردی مشابه دفترچه تلفن داشته باشد ، اگر صفحه ای پر شده باشد ناچارید یا فشرده بنویسید و یا اگر این کار ممکن نباشد مجبور می شوید برگه ای سفید در انتهای دفترچه تلفن به نام های جدید اختصاص بدهید که این کار باعث به هم خوردن ترتیب اطلاعاتتان می شود و با دو مشکل بزرگ روبرو خواهید شد،
- یک صفحه ی جدید اضافه شده که اطلاعات کمی روی آن قرار دارد (Internal Fragmentation)
- ترتیب صفحات دفترچه تلفن شما به هم می خورد (External Fragmentation)
خوب شاید در این حالت دقترچه تلفن شما چیزی شبیه شکل زیر بشود ،
یک راه حل پیش پا افتاده می تواند این باشد که صفحات دفترچه تلفن خود را به نحوی پر کنید که در هر صفحه کمی فضای خالی وجود داشته باشد ، به این ترتیب اگر نام جدیدی اضافه شد می تواند در این فضای خالی جای بگیرد ولی خوب این راه حل خراب شدن دفترچه تلفن را به تعویق می اندازد و نمی تواند کامل جلوی این اتفاق را بگیرد ، SQL Server هم عملی مشابه را با مفهوم Fill Factor انجام می دهد ، با معین کردن درصد Fill Factor به SQL می گوییم که صفحاتمان تا چند درصد پر باشند تا کمی فضای خالی برای درج رکوردهای جدید وجود داشته باشد.
آپدیت کردن رکوردها هم می تواند مشکل ساز شود ، فرض کنید خانم Pat Down که تا دیروز مجرد بود و نامش در ردیف D قرار داشت امروز با آقای Phil McCann ازدواج می کند و نام خانوادگی اش به McCann تغییر می کند ، حال باید نام وی از ردیف D پاک شود و در ردیف M جا داده شود ، DELETEها هم به دلیل جا گذاشتن فضای خالی مشکل ساز می شوند.(Internal Fragmentation)
Fragmentation چگونه می تواند به عملکرد SQL Server آسیب بزند؟؟؟!
Internal Fragmentation بد (داشتن فضاهای خالی زیاد در صفحات) بدان معناست که Index شما از آن اندازه ای که می بایست بزرگتر شده است. به جای اینکه دفترچه تلفن ما 1000 صفحه ای که صد در صد پر هستند ممکن است 1100 صفحه داشته باشیم که 90درصد پر باشند و این بدان معناست که هر زمان نیاز باشد که Index را Scan کنیم 10% بیشتر طول خواهد کشید و چون SQL صفحات خالی را نیز Cache می کند این باز هم بدان معناست که به 10% حافظه RAM بیشتر برای Cache کردن اطلاعات نیازمندیم.پایین ترین سطح Caching در SQL Server یک صفحه می باشد نه یک رکورد.
External Fragmentation بد نیز دردسرهای خودش را دارد ، عملکرد ذخیره سازی کندتر خواهد شد ، SQL به جای اینکه صفحات را به ترتیب پیمایش کند مجبور است تا صفحات را به خاطر رکوردهای متفاوت عقب و جلو پیمایش کند.
رفع مشکل 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 را مقدار کمتری در نظر بگیرند مثلا 50% . با این کا رنصف هر صفحه خالی خواهد ماند و Insertها سریع تر خواهد شد ولی اتفاقی که می افتد این است که Readها دوبرابر کند تر خواهد شد.فرض کنید دفترچه تلفنتان بجای 1000 صفحه پر 2000 صفحه نیمه پرداشته باشد.
رفع مشکل Fragmentation به طور دایم
خوب می توانیم با Cache کردن دیتابیس و یا حداقل اطلاعاتی که بیشتر در دسترس هستند شروع کنیم ، External Fragmentation (ترتیب نادرست صفحات در دیسک) تا زمانی که اطلاعات را از رم می خوانیم اهمیتی ندارند ، اطلاعات رو Cache کرده و خیال خودتان را راحت کنید ، 348 گیگ رم چیزی حدود 15 میلیون تومان آب خواهد خورد.
Queryهای خود را بررسی کنید و ببینید آیا Query که واقعا تعداد فراخوانی آن زیاد بوده و گیر ایندکس های شما می باشد دارید یا خیر؟ اگر داشتید به فکر اصلاح ایندکس ها باشید ، همیشه Rebuild و یا Reorganize کردن ایندکس ها اولین راه حل نیست. می توانید توسط Query زیر درصد Fragmentation ایندکس های خود را بفهمید و روی آن ها تصمیم بگیرید ، معمولا برای عددی بین 5 تا 30 Reorganize کردن و برای عددی بالای 30 Rebuild کردن پیشنهاد می شود ، ولی یادتان باشد این فرمول همیشه درست نیست و بهتر است همیشه خودتان ایندکس ها را با توجه به Queryهایتان بررسی کنید و به عنوان یک DBA بهترین تصمیم را بگیرید.
[sql] 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 [/sql]
|
منبع: آموزش برنامه نویسی نیک آموز
38 دیدگاه
سپهر صفایی
با سلام
میلاد فیروزی
متشکر از نظر بسیار کاربردی شما دوست عزیز
Hamid J. Fard
نکته این جا است که اگر شما Recover Model را به Bulked-Logged تغییر دهید تمامی Data Page هایی که تغییر داده شده اند در Log Backup خواهند بود. این عملیات بازهم تاثیر در عملیات Mirroring و Backup گیری فایل تراکنش دارد.
مسعود طاهری
سلام حمید جان باید به این نکته توجه داشته باشیم کهپروسه Mirroring به علت مکانیزم داخلی خود و… اجازه نمی دهد که شما Recovery Model بانک اطلاعاتی را تغییر دهید (نیاز به Recovery Model= Full دارد)
مسعود طاهری
البته فک کنم منظور شما از این
قاسم گل میری
اگر plan bakup به این صورت باشد که در طول روز log bakup بگیرید تغییر Recovery Model توصیه نمیشود چون باعث شکستن chain های log bakup میشود.
Hamid J. Fard
البته اگر به Bulk-Logged تغییر پیدا کند زنجیره لاگ از بین نخواهد رفت
مسعود طاهری
سلام
Step 1. Take a T-log backup, before switching to Bulk-logged recovery
Step 2. Set Database to Bulk-logged recovery
Step 3. Perform Bulk-logged operations
Step 4. Set Database to FULL
Step 5. Again, take a T-log backup
سپهر صفایی
سلام
Hamid J. Fard
تغییر Recovery Model به هیچ عنوان از حجم Log جلوگیری نمی کند فقط نوع ذخیره سازی برای هر تراکنش را تغییر می دهد
مسعود طاهری
سلام دوستان لینک های زیر مثال های خوبی در خصوص بحث تغییر Recovery Model و انجام Bulk Operation دارد.
Hamid J. Fard
عذر می خواهم . Log Backup
سپهر صفایی
با تشکر از لینک هایی که معرفی کردید. آیا راه حلی برای جلوگیری از رشد log file هنگام reorganize کردن ایندکس ها دارید؟!
مسعود طاهری
تهیه منظم Log Backup
سپهر صفایی
بله در حالت bulk-logged تاثیری در حجم فایل logBackup ندارد ولی هنگام عملیات REINDEXING حجم فایل log زیاد نمی شود
کیوان معینی
سلام
خسته نباشید و سپاس خدمت نویسنده مقاله بابت این مقاله مفید و کاربردی .
و همچنین تقدیر از مدیران خوش برخورد و متین سایت نیک آموز
( یه پیشنهاد : لطفا اگه براتون مقدوره برای صفحات مقاله ،خروجی pdf بزارین . بازم ممنون)
حمیدقلیپور
با سلام
Hamid J. Fard
نکته اول: ” با معین کردن درصد Fill Factor به SQL می گوییم که صفحاتمان تا چند درصد پر باشند تا کمی فضای خالی برای درج رکوردهای جدید وجود داشته باشد.” نکته اینجا است که فضای خالی برای تغییرات رکوردهای موجود در صفحه است نه درج رکورد جدید. Fillfactor از عملیات Page Split جلوگیری می کند و در نتیجه از External Fragmentation.
مسعود طاهری
سلام حمید جان متشکر از نظرات خوبت
Hamid J. Fard
مسعود طاهری
بلی دقیقا به خاطر اینکه این مورد را نشان دهم از قصد دیتا تایپ Char که از نوع Fixed Length است را ایجاد کردم. در خصوص Varchar هم همین جریان وجود دارد. (استفاده Fillfactor به ازای درج رکورد جدید).
Hamid J. Fard
مسعود طاهری
سلام
Hamid J. Fard
مسعود طاهری
سلام مجدد
مسعود طاهری
البته بهتر است که دیتا تبدیل به Varchar شود. اما اگر مطمئن هستیم که واقعا دیتا دقیقا طولش ثابت است و همیشه همان فضا را اشغال می کتد و درصد Null Value کم است بهتر است Fixed Length دیتا تایپ استفاده کنیم.
Hamid J. Fard
البته در نظر داشته باشید که مقاله این که شما منبع دادید برای SQL Server 2005 است که درست است ولی از SQL Server 2008 به بعد روند اختصاص داده در Storage Engine تغییر کرده است.
مسعود طاهری
سلام حمید جان
بابک جهانگیری
لطفا وضعیت را در SQL Server 2016 نیز تست بفرمایید که آیا Fixed Length بازهم فضای کامل را اشغال می کند؟
مسعود طاهری
بابک عزیز سلام
مسعود طاهری
یک نکته دیگر بابک عزیز
مهدی ربانی ذبیحی
با سلام و تشکر از مقاله خوبتون.من هم با دوست عزیز موافقم در صورت خروجی pdf ماندگاری مقالات بیشتر میشه با تشکر
فرشید علی اکبری
سلام
مسعود طاهری
این مقدار مابین است.
مسعود طاهری
در نظر داشته باشید که وجود فضای خالی بیش از حد در Pageها باعث بوجود آمدن Internal Fragmentation شده که این موضوع دردسرهای خاص خودش را به همراه دارد
Hamid J. Fard
برای تمامی ایندکس های نباید یک مقدار تعیین شود. اگر جدول شما تغییرات زیاد دارد این مقدار باید بین 70 تا 80 باشد تا از عملیات Page Split جلوگیری شود. همانطور که گفتم هزینه عملیات Page Split , Forwarded Records بیشتر از این است که یک مقدار حافظه بیشتر تخصیص داده شود. تیم سازنده در مایکروسافت مقدار 70 را پیشنهاد می کنند.
مسعود طاهری
حمید با توجه به اینکه امکان پاسخ تو در تو تا 8 سطح فعال مجبور شدم ادامه بحث را اینجا …
جواد زبیدی