Rebuild و Reorganize کردن ایندکس‌ها

در این مقاله روش بهینه سازی ایندکس‌ها در SQL Server با استفاده از دو ابزار Rebuild و Reorganize شرح داده خواهد شد.

در این مقاله چگونگی reorganize و rebuild کردن ایندکس‌ تکه تکه شده (Fragmented Index) در SQL Server 2014 با استفاده از محیط SSMS یا SQL Server Management Studio و T-SQL یا Transact-SQL شرح داده خواهد شد. SQL Server Database Engine به صورت خودکار ایندکس‌ها را هنگام ایجاد داده‌های پایه توسط عملیات‌های درج (Insert)، به روز رسانی (Update) یا حذف (Delete)، نگهداری می‌کند. با گذشت زمان این تغییرات می‌تواند باعث تکه تکه شدن یا پارگی (Fragmentation) ایندکس‌ها در دیتابیس شوند (تکه‌تکه شدن-Fragmentation : در ذخیره‌سازی کامپیوتری پدیده‌ای است که در آن فضای ذخیره‌سازی به‌طور ناکارآمد استفاده می‌شود و منجر به کاهش ظرفیت واقعی ذخیره‌سازی می‌شود. همچنین به آن فضای هدر رفته نیز اطلاق می‌گردد؛ جهت کسب اطلاعات بیشتر به مقاله پارگی رجوع کنید). Fragmentation زمانی به وجود می‌آید که ایندکس‌ها دارای صفحاتی باشند که مرتب‌سازی منطقی، بر اساس مقدار کلید (Key Value) ، با مرتب‌سازی فیزیکی درون داده‌های فایل (Data File) مطابقت و همخوانی نداشته باشد. تکه تکه شدن بیش از حد ایندکس‌ها می‌تواند کارایی Queryها را کاهش دهد و باعث شود برنامه شما با سرعتی بسیار کند اجرا گردد.

می‌توان تکه تکه شدن ایندکس‌ را توسط Reorganize و Rebuild کردن آن ایندکس برطرف کرد. برای ایندکس‌های پارتیشن‌بندی شده (Partitioned) که بر اساس یک طرح پارتیشن ایجاد شده است، می‌توانید از این دو روش جهت اعمال بر روی یک ایندکس کامل و یا یک پارتیشن از یک ایندکس استفاده کنید. Rebuild کردن یک ایندکس، ایندکس را از بین برده و آن را مجدد ایجاد می‌کند که این ویژگی تکه تکه شدگی را از بین برده، فضای دیسک را با فشرده کردن صفحات بر اساس تنظیمات Fill Factor موجود و مشخصی آزاد می‌کند و سطر‌های ایندکس‌ها را در صفحاتی پیوسته مرتب خواهد کرد (Fill Factor مقداری است که درصد استفاده هر صفحه Leaf-Level را که با داده پر شده است مشخص می‌کند. جهت کسب اطلاعات بیشتر مقاله What is Fill Factor and what is the best value for it? را مطالعه نمایید).

هنگامی که از کلمه کلیدی ALL استفاده گردد، تمامی ایندکس‌ها در جدول در یک تراکنش مستقل بازسازی (Rebuild) خواهند شد. سازماندهی مجدد (Reorganizing) یک ایندکس از حداقل منابع سیستمی استفاده می‌کند. Reorganizing پایین‌ترین سطح (Leaf Level-سطح برگ: پایین‌ترین گره‌های یک درخت گره‌های برگ نام دارند) ایندکس‌های کلاستر شده و غیر کلاستر را بر روی جداول (Tables) و نماها (Views) با استفاده از مرتب‌سازی صفحات در پایین‌ترین سطح ادغام می‌کند تا مرتب‌سازی نودهای برگ در پایین‌ترین سطح با منطق همخوانی داشته باشد. reorganization همچنین صفحات ایندکس‌ها را فشرده می‌کند که این فشرده سازی بر اساس مقدار Fill Factor موجود صورت خواهد ‌پذیرفت.

 

شناسایی تکه تکه شدگی (Fragmentation)

اولین قدم در تصمیم گیری اینکه از کدام روش ادغام (defragmentation) استفاده شود، آنالیز و تعیین درجه تکه تکه شدن ایندکس‌هاست. با استفاده از تابع سیستمی sys.dm_db_index_physical_stats می‌توانید Fragmentation را بر روی یک ایندکس خاص، تمامی ایندکس‌ها در جداول یا نماهای ایندکس شده، تمامی ایندکس‌ها در یک دیتابیس و یا تمامی ایندکس‌ها در تمامی دیتابیس‌ها شناسایی کنید. همچنین برای ایندکس‌های پارتیشن بندی شده، sys.dm_db_index_physical_stats اطلاعات Fragmentation را برای هر پارتیشن بدست می‌آورد.

مجموعه نتایج برگشتی توسط تابع sys.dm_db_index_physical_stats شامل ستون‌های زیر می‌باشد:

 

 

پس از اینکه درجه Fragmentation مشخص گردید، از جدول زیر جهت تعیین بهترین روش جهت تصحیح این تکه تکه‌ شدگی استفاده می‌کنیم:

 

 

Rebuild کردن یک ایندکس می‌تواند به صورت Online و یا Offline صورت گیرد. در صورتی که Reorganize کردن یک ایندکس همواره به صورت Online انجام می‌شود. جهت دستیابی به در دسترس بودن ایندکس‌ها، مشابه ویژگی Reorganize، می‌بایست انجام عملیات Rebuild ایندکس‌ها نیز به صورت Online انجام گیرد.

این مقادیر یک دستورالعمل نامناسب را برای تعیین آن نقطه‌ای که می‎بایست جابه‌جایی بین ALTER INDEX REORGANIZE و ALTER INDEX REBUILD صورت گیرد، فراهم می‌آورد. اگر چه مقادیر واقعی ممکن است در یک مورد نسبت به مورد دیگری متفاوت باشد. این مسئله مهمی است که شما بتوانید بهترین آستانه (Threshold) را برای محیط خود بیابید. در صورتی که تکه تکه شدن در سطوح بسیار پایین (کمتر از 5 درصد) باشد، نمی‌بایست از هیچ کدام ازین دستورات استفاده کرد، زیرا مزیت حذف چنین مقدار کوچکی از تکه تکه شدگی اغلب با هزینه بالای Reorganize و Rebuild کردن ایندکس‌ها از بین می‌رود.

نکته: در حالت کلی، تکه تکه شدن بر روی ایندکس‌های کوچک اغلب غیر قابل کنترل است. صفحات ایندکس‌های کوچک بر روی بازه‌های (Extents) درهمی ذخیره می‌شود (Extentها واحدی پایه‌ای هستند که بوسیله آن فضا مدیریت می‌شود. یک Extent هشت صفحه فیزیکی متوالی با سایز 64kb است. این به این معنی است که دیتابیس‌های SQL Server، در هر مگابایت 16 Extent دارند. جهت کسب اطلاعات بیشتر مقاله Understanding Pages and Extents را مطالعه نمایید). Extentهای درهم بوسیله بیش از 8 Object به اشتراک گذاشته می‌شود، بنابراین تقسیم کردن یک ایندکس کوچک ممکن است پس از Reorganize و Rebuild کردن ایندکس کاهش نیابد.

 

محدودیت‌ها

• ایندکس‌هایی با بیش از 128 Extent، در دو فاز عملیات Rebuild بر روی آنها اجرا می‌شود: منطقی و فیزیکی. در فاز منطقی، واحدهای تخصیص موجود که توسط ایندکس استفاده شده است، جهت اعطای مجوز به آنها مشخص می‌شوند، سطرهای داده کپی شده و مرتب می‌شوند، سپس به واحدهای تخصیص جدید که برای ذخیره ایندکس Rebuild ذخیره شده اند، منتقل می‌شوند. در فاز فیزیکی، واحدهای تخصیص که پیش تر برای اعطای مجوز انتخاب شده اند به صورت فیزیکی در یک تراکنش‌هایی کوتاه که در پس زمینه اتفاق می‌افتد رها می‌شوند.
• تا هنگامی که یک ایندکس Reorganize می‌شود، ویژگی‌های ایندکس‌ها مشخص نخواهد شد.

دسترسی‌ها

به دسترسی ALTER بر روی جدول یا نما نیاز می‌باشد. کاربر می‌بایست در Server Role عضو Sysadmin و یا در Database Role، عضو db_ddladmin و db_owner باشد.

 

مشخصات ایندکس‌ها

• در Object Explorer، پوشه دیتابیس مورد نظر را جهت تقسیم بندی ایندکس‌ها بسط دهید.
• پوشه Table و سپس جدول موردنظر را بسط دهید.
• از پوشه Indexes، بر روی ایندکسی که می‌خواهید تقسیم بندی آن را بررسی کنید، راست کلیک کنید و گزینه Properties را انتخاب نمایید.

• در صفحه باز شده بر روی Fragmentation کلیک کنید.

در صفحه Fragmentation اطلاعات زیر در دسترس می‌باشد:

Page fullness: میانگین پر بودن صفحات ایندکس را به درصد نمایش می‌دهد. 100% به این معناست که صفحات ایندکس به طور کامل پر هستند و 50% به این معنی است که به طور میانگین هر صفحه ایندکس تا نیمه پر شده است.

Total fragmentation: درصد تقسیم بندی منطقی را نمایش می‌دهد. در واقع تعداد صفحات ایندکس که به ترتیب ذخیره نشده است را نمایش می‌دهد.

Average Row Size: میانگین اندازه سطر Leaf-Level را نمایش می‌دهد.

Depth: تعداد سطوح ایندکس را که شامل Leaf-Level نیز می‌باشد نمایش می‌دهد.

Forwarded Records: تعداد رکوردها در یک پشته که دارای نقاط ارسال مجدد به سایر مکان‌های داده است (این حالت در زمان به روز رسانی، هنگامی که فضایی خالی جهت ذخیره سطری جدید در مکان اصلی وجود نداشته باشد رخ می‌دهد).

Ghost Rows: تعداد سطرهایی که جهت حذف انتخاب شده‌اند، اما هنوز حذف نشده‌اند. این سطرها هنگامی که سرور مشغول نباشد توسط یک عملیات پاکسازی حذف می‌شوند. این مقدار، سطرهایی را که به علت انجام تراکنش ایزوله (Snapshot Isolation Transaction) بازگردانده شده‌اند شامل نمی‌شود.

Index Type: نوع ایندکس را نشان می‌دهد. مقادیر ممکن در این قسمت عبارتند از Clustered Index ،Nonclustered Index و Primary XML. جداول همچنین می‌توانند به عنوان یک پشته هم ذخیره شوند (بدون ایندکس‌ها)، اما پس از آن صفحه تنظیمات این ایندکس نمی‌تواند باز شود.

Leaf-Level Rows: تعداد سطرهای Leaf-Level را نمایش می‌دهد.

Maximum Row Size: سایز سطر Leaf-Level را نمایش می‌دهد.

Pages: تعداد کل صفحات داده را نمایش می‌دهد.

ID Partition :Partition ID یک b-tree شامل ایندکس‌ها می‌باشد (یک درخت بی یا B-tree داده‌ ساختاری درختی است که داده‌ها را به صورت مرتب‌شده نگه می‌دارد و جستجو، درج و حذف را در زمان مصرفی لگاریتمی میسر می‌سازد).

Version Ghost Rows: تعداد رکوردهای ghost recorder که به علت انجام یک تراکنش ایزوله بازگردانده شده‌اند.

بررسی تکه تکه شدگی ایندکس با استفاده از Transact-SQL

در Object Explorer به یک Instance از Database Engine خود متصل شوید.

در منوی Standard، بر روی گزینه New Query کلیک کنید.

دستور زیر را در پنجره Query آن وارد نمونه و بر روی دکمه Execute کلیک کنید.

USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012, OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GO

 

خروجی Query فوق ممکن است مجموعه ای به صورت زیر باشد:

index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 

(6 row(s) affected)

بررسی تکه تکه شدگی ایندکس با استفاده از SQL Server Management Studio

Reorganize کردن یک ایندکس

• در Object Explorer، پوشه دیتابیسی که شامل جدولی است که در نظر دارید تقسیم بندی ایندکس‌های را بر روی آن بررسی نمایید، باز کنید.
• پوشه Table را باز کنید و سپس پوشه Indexes را باز کنید.
• بر روی ایندکسی که می‌خواهید reorganize بر روی آن انجام شود راست کلیک کنید و گزینه Reorganize را انتخاب نمایید.

• در صفحه Reorganize Indexes در صورتی که تایید می‌کنید که ایندکس صحیح در قسمت Indexes to be reorganized نمایش داده شده است، بر روی OK کلیک کنید.
• گزینه “Compact large object column data” را جهت تعیین اینکه تمامی صفحاتی که شامل Large Object (LOB) data هستند فشرده سازی بر روی آنها صورت گرفته است انتخاب کنید.

• بر روی OK کلیک کنید.

reorganize کردن تمامی ایندکس‌ها در یک جدول

• در Object Explorer، پوشه دیتابیسی که شامل جدول است که در نظر دارید تقسیم بندی ایندکس‌های را بر روی آن بررسی نمایید، باز کنید.
• پوشه Table را باز کنید.
• بر روی پوشه Indexes کلیک راست کرده و گزینه Reorganize All را انتخاب نمایید.

• در صفحه Recognize Indexes، بررسی کنید که ایندکس صحیح در قسمت Indexes to be reorganized نمایش داده شده است. جهت حذف یک ایندکس از قسمت Indexes to be reorganized ایندکس موردنظر را انتخاب کنید و سپس دکمه Delete را بفشارید.
• گزینه “Compact large object column data” را جهت تعیین اینکه تمامی صفحاتی که شامل Large Object (LOB) data هستند فشرده سازی بر روی آنها صورت گرفته است انتخاب کنید.
• بر روی OK کلیک کنید.

Rebuild کردن یک ایندکس

• در Object Explorer، پوشه دیتابیسی که شامل جدول است که در نظر دارید تقسیم بندی ایندکس‌های را بر روی آن بررسی نمایید، باز کنید.
• پوشه Table را باز کنید و سپس پوشه Indexes را باز کنید.
• بر روی ایندکسی که می‌خواهید rebuild بر روی آن انجام شود راست کلیک کنید و گزینه rebuild را انتخاب نمایید.

در صفحه Rebuild Indexes، بررسی کنید که ایندکس صحیح در قسمت Indexes to be rebuild نمایش داده شده است. سپس بر روی دکمه OK کلیک کنید.

• بر روی OK کلیک کنید.

rebuild کردن تمامی ایندکس‌ها در یک جدول

• در Object Explorer، پوشه دیتابیسی که شامل جدول است که در نظر دارید تقسیم بندی ایندکس‌های را بر روی آن بررسی نمایید، باز کنید.
• پوشه Table را باز کنید.
• بر روی پوشه Indexes کلیک راست کرده و گزینه Rebuild All را انتخاب نمایید.

• در صفحه Recognize Indexes، بررسی کنید که ایندکس (های) صحیح در قسمت Indexes to be rebuild نمایش داده شده است. جهت حذف یک ایندکس از قسمت Indexes to be rebuild ایندکس موردنظر را انتخاب کنید و سپس دکمه Delete را بفشارید.
• بر روی OK کلیک کنید.

استفاده از Transact-SQL

Reorganize کردن یک ایندکس تقسیم بندی شده

• در قسمت Object Explorer، به یک Instance از Database Engine متصل شوید.
• در قسمت بالای صفحه بر روی New Query کلیک کنید.
• دستور زیر را به عنوان مثال در قسمت صفحه Query وارد کنید و بر روی دکمه Execute کلیک کنید.

"text-align: left;">USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode -- index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee> REORGANIZE ; GO>

Reorganize کردن تمامی ایندکس‌ ها در یک جدول

• در قسمت Object Explorer، به یک Instance از Database Engine متصل شوید.
• در قسمت بالای صفحه بر روی New Query کلیک کنید.
• دستور زیر را به عنوان مثال در قسمت صفحه Query وارد کنید و بر روی دکمه Execute کلیک کنید.

USE AdventureWorks2012;

GO

— Reorganize all indexes on the HumanResources.Employee table.

ALTER INDEX ALL ON HumanResources.Employee

REORGANIZE ;

GO

Rebuild کردن یک ایندکس تقسیم بندی شده

• در قسمت Object Explorer، به یک Instance از Database Engine متصل شوید.
• در قسمت بالای صفحه بر روی New Query کلیک کنید.
• دستور زیر را به عنوان مثال در قسمت صفحه Query وارد کنید و بر روی دکمه Execute کلیک کنید. نمونه زیر تنها یک ایندکس را بر روی جدول Employee، rebuild می‌کند.

USE AdventureWorks2012;

GO

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee

REBUILD;

GO

 

Rebuild کردن تمامی ایندکس‌ها در یک جدول


USE AdventureWorks2012;

GO

ALTER INDEX ALL ON Production.Product

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,

STATISTICS_NORECOMPUTE = ON);

GO

• در قسمت Object Explorer، به یک Instance از Database Engine متصل شوید.
• در قسمت بالای صفحه بر روی New Query کلیک کنید.
• دستور زیر را به عنوان مثال در قسمت صفحه Query وارد کنید. این مثال کلمه کلیدی ALL را که تمامی ایندکس‌ها را با یک جدول پیوند می‌دهد، مشخص می‌کند.

آیا می‌دانید که در سیستم مانیتورینگ بینا می‌توان با استفاده از مانیتورینگ پایگاه داده به وضعیت عملکرد شاخص‌های سطح دیتابیس و Instance و وضعیت کارایی دیتابیس در SQL Server پی برد؟

جهت اطلاع از نحوه انجام rebuild و reorganize بر روی SQL 2016 و بالاتر به لینک زیر رجوع کنید.

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

جهت اطلاع از نحوه افزایش سرعت در SQL با ایجاد ایندکس از طریق Tune Up مقاله افزایش سرعت در SQL با ایجاد ایندکس در SQL از طریق Tune Up را مطالعه نمایید.