بالعربي شرح مشاكل ال Indexes وطرق علاجها وصيانتها

في هذا المقال سوف نوضح اسلوب عمل صيانة لل Indexes داخل ال SQL Server لمتابعة ادائها المستمروهو مفيد لمديرين قواعد البيانات ومسئولين ال IT واي مهتم باداء قواعد بيانات SQL Server
ويمكنك مراجعة المقال السابقة الخاص بتوضيح الفرق بين انواع ال Indexes من هنــــــا

تجزئة البيانات (Fragmentation)

البيانات داخل ال SQL Server في نهاية المطاف يتم تخزينها في ملفات قاعدة البيانات على ال HardDisk واي بيان داخل ال SQL Server يتم كتابته في صورة وحدات تسمى Pages داخل الملفات او بمعنى اخر تستطيع ان تعتبر ال Page هي اقل وحدة قياس للبيانات داخل الملفات. وحيث ان البيانات يتم تعديلها باستمرار فذلك يؤدي الى مشكلة التجزئة (Fragmentation) ويوجد نوعين من مشاكل التجزئة هما internal fragmentation  و external fragmentation.

تجزئة داخلية  internal fragmentation

 ومعناه ان ال Pages المكتوب بها البيانات بها مساحات فارغة اي ان الPage ليست مستغلة بالكامل ولتوضيح المشكلة تخيل معي ان لديك جدول للموظفين وان لديك Index على حقل كود الموظف مثلا وان اجمالي بيانات هذا الIndex يتم تخزينها في 10 Pages ولكن بسبب مشكلة ال internal fragmentation وان ال Pages غير مستغلة بالكامل تم تخزين هذا ال Index على 20 Page اي ضعف عدد ال Pages. اذا عند عمل اي عملية استعلام تستخدم هذا ال Index (عمل Where على عمود كود الموظف مثلا) سيتم قراءة 20 Page بدلا من قراءة 10 فقط مما يؤدي الى التأثير السلبي على اداء ال Index.

تجزئة خارجية External fragmentation

 وهذه المشكلة المقصود بها ان ترتيب ال Pages المخزن بها البيانات فعليا داخل الملفات في ال HardDisk مختلف عن ترتيب ال Key الخاص بال Index . وهذه المشكلة تؤثر على الاداء بشكل سلبي حيث مثلا لقراءة بيانات هذا ال Index تقوم ابرة القراءة في الHardDisk بالتنقل الغير مرتب لقراءة البيانات الفعلية من الملفات بينما لو كانت مرتبة بال Key الخاص بال Index كانت ستتم عملية القراءة sequential read بشكل اسرع.

الشكل التالي يوضح انواع ال fragmentation

انواع ال fragmentation ماخوذ من هنا

وقد تحدث هذه المشاكل نتيجة حذف البيانات او عمل تعديل update لبيانات بحجم اكبر مما كانت مخزنة عليه  مما يؤدي الى عمل Page Split لتجزئة تخزين البيانات التي كانت مخزنة في Page واحدة على 2 Pages او بعض الاسباب الاخرى.

FILLFACTOR

عند انشاء Index جديد او اعادة بناء Index حالي تتيح قاعدة البيانات Option باسم FILLFACTOR  وهذا ال Option هو عبارة عن نسبة المساحة التي يتم التخزين عليها داخل ال Page و الافتراضي لها 100 اي لا يتم ترك اي مساحة فارغة داخل ال Page (في حالة كتابة ال FillFactor بصفر يعتبرها ال SQL Server انها 100).
هذه الخاصية تتيح تجنب مشكلة ال Page Split فمثلا تخيل معي انك قمت بتغيير قيمة ال FillFactor الى 80 فبذلك انت قمت بترك مساحة فارغة داخل كل Page بحوالي 20% فعند ادخال بيانات جديدة او تعديل بيانات بحجم اكبر مثلا تكون هناك مساحة فارغة داخل ال Page يمكن الكتابة عليها مما يقلل من مخاطر ال Page Split.
ولكن لا شيء دون ثمن حيث عند استخدام ال FillFactor دون الحاجة لها سيؤدي الى حدوث مشكلة ال Internal Fragmentation بشكل كبير فلذلك يجب استخدامها بحذر.

 كيف يمكن اكتشاف ال  Fragmentation ؟

 من الاشياء الهامة الفحص الدوري على البيانات واكتشاف مشاكل التجزئة (fragmentation) لما لها من اثر سيء على اداء قواعد البيانات وتوفر ال SQL Server دالة Function لاكتشاف مشاكل ال Fragmentation وهي sys.dm_db_index_physical_stats ويمكن استخدامها بالاستعلام التالي

SELECT  '[' + dbschemas.[name] + '].[' + dbtables.[name] + ']' AS TableName ,
        dbindexes.[name] AS IndexName ,
        indexstats.alloc_unit_type_desc ,
        indexstats.avg_fragmentation_in_percent ,
        indexstats.avg_page_space_used_in_percent ,
        indexstats.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
        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

ORDER BY indexstats.avg_fragmentation_in_percent DESC ,
        avg_page_space_used_in_percent ASC;

مخرجات هذا الاستعلام كالتالي

• avg_fragmentation_in_percent
• نسبة ال External Fragmentation وهذه القيمة نريدها اقرب الى الصفر 
• avg_page_space_used_in_percent
• نسبة ال Internal Fragmentation وهذه القيمة نريدها اقرب الى 100  

فبالاستعلام السابق يمكن اكتشاف ال Indexes التي بها مشاكل Fragmentation فيمكن تعديله مثلا بعمل Where لجلب الIndexes التي ال External Fragmentation لها اكبر من 20 وهكذا.
 ولكن ماذا بعد ان اكتشفنا ال Indexes التي بها مشاكل؟

Index Rebuild And Reorganize

من الاساليب المتبعة لحال مشكلة ال Fragmentation اما عمل Index Reorganize او Index Rebuild

اولا Index Reorganize

وفي هذه العمليه يقوم ال SQL Server بفحص كل Page خاصة بال Leaf Level الخاص بال Index فاذا وجد مساحة فارغة في ال Page يقوم باخذ البيانات من ال Page التالية ووصعها فيها وهكذا وفي النهاية يقوم بحذف ال Pages التي تركت فارغة بعد ذلك البيانات في ال Leaf Level يتم اعادة توزيعها مما يجعل تريبها الفعلي قريب جدا من ترتيب ال Key الخاص بال Index.

وميزة عملية ال Reorganize انها تعالج مشكلة ال Internal Fragmentation وايضا مشكلة ال External Fragmentation لل Leaf Level ولكن بشكل مبسط في حالة ان نسبة ال External Fragmentation هي 30% او اقل بينما اكثر من ذلك لا تعالجها.

واهم ميزة لهذه العملية انها تتم Online اي ان كل ال Indexes التي يحدث لها  Reorganize يمكن عمل استعلام عليها ولا يحدث لها Lock اثناء ال Reorganiz.

الكود التالي يوضح طريقة عمل ال  Reorganiz

ALTER INDEX Index_Name ON Table_Name REORGANIZE ;

ثانيا Index Rebuild

في هذه العملية يتم حذف ال Index واعادة انشاءه من جديد وبالتالي تجد ان اثرها على مشكلة ال Fragmentation كبير جدا حيث انه تم اعدة انشاء ال Index بالكامل ولكن في بعض الاحيان لا يتم ازالة ال Fragmentation بنسبة 100% وذلك لان عملية ال Rebuild يتم توزيعها على عدد ال Cores في ال Processor وبالتالي قد تتبقى نسبة بسيطة من ال Fragmentation ولكم يوجد option يجعلها تنفذ على Core واحد ان اردت.

طبعا من التوضيح السابق يتضح ان عملية ال Rebuild نتيجتها افضل بكثير ولكن المشكلة انها عملية ثقيلة على موارد ال Server وتستهلك وقت كبير كلما كان حجم البيانات كبير كما انها يمكن ان تتم Online ولكن في ال Enterprise Edtion غير ذلك لا يمكن اجرائها Online مما يؤدي الى عمل Lock على ال Index طوال فترة ال Rebuild .

الكود التالي يوضح طريقة عمل الRebuild

ALTER INDEX Index_Name ON Table_Name REBUILD ;

 انا شخصيا افضل عمل Rebuild عن ال Reorganize حيث ان النتيجة بعد ال Rebuild تكون تحسن ملحوظ في اداء ال Index ولكن مشكلة ال Lock هي اكبر عائق كما انها تستهلك ال CPU وال RAM بشكل كبير ولذلك اقوم بعمل Index Rebuild في الاوقات التي يكون العمل قليل او منعدم على قاعدة البيانات مثلا يوم الجمعة الثالثة صباحا. كما انه من الافضل عمل Rebuild للIndexs التي تحتاج ذلك التي يمكن ايجادها باستخدام الاستعلام الخاص باكتشاف ال Fragmentation

-----------------------------------------------

تم بحمد الله

مراجع

APRESS : Pro SQL Server Administration