یکی از مسائل مهم در هر DBMS از جمله MSSQL بحث index ها است، وقتی استفاده از مفاهیم این چنین مطرح میشود، شاید بنظر تاثیر مثبتی در عملکرد سرویس داشته باشد اما عدم توجه به مفاهیم در استفاده از آن، ممکن است در طولانی مدت مشکلاتی را بوجود آورده که طعم شیرنی اول را تبدیل به تندی کند. در این مقال می خواهیم به یک نکته بسیار مهم که ممکن است برای هر DBA با کسب تجربه در چندین سال فعالیت در این حوزه ، رخ دهد اشاره کنیم، با این مقدمه که:
ایندکس در واقع مرتب کردن داده های یک جدول بر اساس یک ستون بود، که می توانست موجب ارتقاء پروفورمنس در زمان جستجو در بین رکورد های آن جدول باشد، که بسته به اینکه تمامی رکوردهای یک جدول را بر اساس یک ستون مرتب می کنیم (Cluster) و با اینکه با پذیرفتن مقداری افزونگی داده های یک ستون را مجدد در فضای مجزا و با یک ترتیب دیگر نگهداری میکنیم (non-Cluster) به دو نوع تقسیم بندی میشود. از طرفی این را هم قبول کردیم که حفظ ترتیب ایجاد شده، به دلیل اضافه و حذف رکوردها تقریبا غیرممکن است و هرکاری کنیم بالاخره این ترتیب به هم خواهد خورد و اصلاحا دچار Fragment میشویم و ایندکس در اینصورت کارایی خود را از دست داده و استفاده از آن نه تنها موجب ارتقاء سرعت نشده بلکه به دلیل اضافه کاری lookup ها، موجب کندی خواهد شد. البته با استفاده از پارامتر fill Factor و با استفاده ازفضای گپ بین رکورد ها می توانسیتم، فاصله زمانی ساخت ایندکس تا نامرتب شدن آن را اضافه کنیم تا در یک Schedule مشخص، ایندکس کارایی خود را از دست ندهد و نهایت در بازه های بلند تری دچار بحث Fragment شویم.
نهایت ایندکس فرگننت خواهد شد و میزان فرگمنت آن را با یک عدد مشخص می کردیم که و فرآیند هایی برای بازسازی آن معرفی کردیم، این فرآیند های با قوانینی معرفی شد:
- اگر میزان فرگمنت ایندکس بین 5 تا 30 درصد بود، ایندکس را اصلاحا Reorganized می کنیم، بدین معنی که دیتا را مرتب خواهیم کرد
- اگر میزان فرگمنت بیش از30 درصد بود، ایندکس را اصلاحا rebuild می کنیم، بدین معنی که دیتا از ابتدا نوشته و با ترتیب مشخص شده می نویسیم.
این دو قانون با توجه به کویری زیر قابل پیاده سازی بود، بدین ترتیب که چند جدول را با هم جوین میکردیم تا برسیم به، میزان فرگمنت و دستورات لازم جهت اقدام لازم:
SELECT
dbschemas.[name] as [Schema],
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
case when indexstats.avg_fragmentation_in_percent >=30 then ‘Use [MyDataBase] ALTER INDEX [‘+ dbindexes.[name]+’] ON [‘ + dbschemas.[name] + ‘].[‘ + dbtables.[name] +’] REBUILD ‘+ CHAR(13) + CHAR(10) +’ ‘
when indexstats.avg_fragmentation_in_percent between 5 and 29.9 then ‘Use [MyDataBase] ALTER INDEX [‘+ dbindexes.[name]+’] ON [‘ + dbschemas.[name] + ‘].[‘ + dbtables.[name] +’] Reorganize ‘+ CHAR(13) + CHAR(10) +’ ‘ end as query
FROM [MyDataBase].sys.dm_db_index_physical_stats (DB_ID(‘[MyDataBase]’), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN [MyDataBase].sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN [MyDataBase].sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN [MyDataBase].sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID(‘MyDataBase’) and dbindexes.[name] is not NULL and indexstats.avg_fragmentation_in_percent>5
and ( dbindexes.name not like ‘%IX_CC%’ and dbindexes.name not like ‘%IX_ICC%’ and dbindexes.name not like ‘%IX_IC%’)
ORDER BY indexstats.avg_fragmentation_in_percent desc
حال نکته جالب این مقاله اینگونه مطرح میشود که یکسری index داریم ، که این index ها شاید از دید قوانین بالا باید rebuild شوند، ولی بعد از rebuild مجدد فرگمنت هستند! حتی موردی دیده شده که درصد فرگمنت ایندکس بعد از Rebuild بیشتر هم شده! (لزوم استفاده از Reorganized قبل از رسیدن به آستانه Rebuild در این پاراگراف حس میشود) با بررسی ها به عمل آمده مشخص شد این پدیده حاصل فرگمنت دیسک و … نیست، ربطی به ماهیت سرور و دیسک ها، دیتابیس و جداول و … ندارد، چرا که جدول و شاخص مربوطه را به سرور دیگر و در دیتابیس دیگر منتقل کرده، گرچه میزان فرگمنت بعد از Rebuild کاهش محسوسی داشت، ولی به مقدار صفر نرسید! گام اول شک به توع دیتا بود، ولی مفهوم index این شک را خنثی میکرد، بنظر پاسخ این مسئله در ساختار ایندکس یا درخت دودویی آن بود، که با بررسی به عمل آمده مشخص شد:
اگر بخواهیم خیلی ساده به موضوع نگاه کنیم، درخت بالا، درخت مرتب سازی اطلاعات است، این اطلاعات در یکسری Page قرار میگیرند، که Page ها نیز در یکسری Extend قرار میگیرند، Extend ها می توانند uniform (حاوی page های یک جدول باشد) و با uniform نباشند ( داخل extend ، page متنوعی قرار گرفته باشد) ، آنچه مشخص است این Extend ها می تواند حاوی page هایی باشد که Intermediate Level درخت فوق را شامل باشد، فرگمنت ایندکس که با یک درصد مشخص میشود، عموما در Leaf Level هست، اما ممکن است در Intermediate Level رخ دهد، زمانی که حالت دوم رخ می دهد و رکوردهای جدول کم باشند، ممکن است با rebuild کردن index به دلیل کم بودن تعداد رکورد ها و متناسب با آن تعداد Page ها، بازهم رخ دهد! یعنی چون تعداد page ها intermediate کم است، مجدد در Extend های زیادی توزیع شود و دلیلش مشخصا کم بودن page ها نسبت به Extend ها بوده که موجب پراکندگی خواهد شد، پس هر چندبار هم که ما Rebuild کنیم، به دلیل اینکه دیتا را از ابتدا می نویسیم، مجدد همین اتفاق رخ خواهد داد، ولی اگر Reorganize کنیم، چون داده ها را مرتب کرده و از اول نمی نویسد، میزان Fragment به صفر خواهد رسید، پس می توانیم قانون سوم را نیز اضافه کنیم که:
- زمانی که تعداد Page ها یک index زیر 550 بود، و آن ایندکس فرگمنت شده (عموما جدول های کوچک) در صورت فرگمنت بدون ایندکس از Reorganized استفاده می کنیم.
سوال: چرا تا به حال این نکته مورد توجه نبوده؟
جواب: اگر قوانین 1 و 2 را رعایت کرده باشیم، وقتی میزان فرگمنت تا 30 درصد است که طبق قانون 1 ، Reorganized میکنیم ، و نمیگزاریم که به درصد بالای 30 برسیم، ولی اگر قبل از Schedule فرآیند بازسازی ایندکس ها، برای آن ایندکس با page کم، درصد 30 را رد کرده باشیم، دیگر با rebuild مشکل فرگمنت حل نخواهد شد!!!
نهایت کویری Rebuild ما خواهد شد:
SELECT
dbschemas.[name] as [Schema],
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
case when indexstats.page_count <=550 and indexstats.avg_fragmentation_in_percent > 5 then ‘Use [ShatelCRM_Run] ALTER INDEX [‘+ dbindexes.[name]+’] ON [‘ + dbschemas.[name] + ‘].[‘ + dbtables.[name] +’] Reorganize ‘+ CHAR(13) + CHAR(10) +’ ‘
when indexstats.avg_fragmentation_in_percent >=30 then ‘Use [ShatelCRM_Run] ALTER INDEX [‘+ dbindexes.[name]+’] ON [‘ + dbschemas.[name] + ‘].[‘ + dbtables.[name] +’] REBUILD ‘+ CHAR(13) + CHAR(10) +’ ‘
when indexstats.avg_fragmentation_in_percent between 5 and 29.9 then ‘Use [ShatelCRM_Run] ALTER INDEX [‘+ dbindexes.[name]+’] ON [‘ + dbschemas.[name] + ‘].[‘ + dbtables.[name] +’] Reorganize ‘+ CHAR(13) + CHAR(10) +’ ‘ end as query
FROM [ShatelCRM_Run].sys.dm_db_index_physical_stats (DB_ID(‘[ShatelCRM_Run]’), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN [ShatelCRM_Run].sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN [ShatelCRM_Run].sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN [ShatelCRM_Run].sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID(‘ShatelCRM_Run’) and dbindexes.[name] is not NULL and indexstats.avg_fragmentation_in_percent>5
and ( dbindexes.name not like ‘%IX_CC%’ and dbindexes.name not like ‘%IX_ICC%’ and dbindexes.name not like ‘%IX_IC%’)
ORDER BY indexstats.avg_fragmentation_in_percent desc
جهت یادگیری مباحث SQL می توانید از طریق دوره آموزش SQL Server کندو اقدام نمایید.
