آموزش ALTER INDEX REORGANIZE در SQL Server

مقدمه

REORGANIZE صفحات سطح برگ ایندکس را به‌صورت تدریجی مرتب و فشرده می‌کند. عملیات آنلاین و کم‌حجم‌تر از Rebuild است و معمولاً برای Fragmentation میانی مناسب‌تر است، ولی Statistics را مانند Rebuild به‌طور کامل تازه نمی‌کند و برای ایندکس Disable شده قابل اجرا نیست.

در این راهنما فرمان ALTER INDEX ... REORGANIZE از Syntax پایه تا کنترل خطا، پایش، سناریوی سازمانی و تصمیم کارایی بررسی می‌شود. همه مثال‌ها برای Microsoft SQL Server نوشته شده‌اند و پیش از استفاده در تولید باید با نام اشیا، نسخه و Edition محیط شما تطبیق داده شوند.

بازگشت به راهنمای جامع دستورات نگهداری ایندکس در SQL Server

تعریف، نحو و اجزای فرمان

Syntax استاندارد

ALTER INDEX index_name
    ON [schema_name].[table_name]
    REORGANIZE
    WITH (LOB_COMPACTION = ON);

پارامترها و پیش‌نیازها

index_name ایندکس هدف را مشخص می‌کند.
PARTITION دامنه عملیات را در جدول پارتیشن‌بندی‌شده محدود می‌کند.
LOB_COMPACTION فشرده‌سازی صفحات LOB را کنترل می‌کند.
ALLOW_PAGE_LOCKS ایندکس باید برای اجرای Reorganize فعال باشد.

نوع خروجی

دستور خروجی جدولی ندارد و پس از پایان کنترل به Session بازمی‌گردد. اندازه‌گیری اثر آن با DMVهای فیزیکی و زمان ثبت‌شده انجام می‌شود.

کاربرد واقعی

برای نگهداری آنلاین ایندکس‌هایی با پراکندگی متوسط، سامانه‌های همیشه‌فعال و Partitionهای مشخص کاربرد دارد.

در محیط عملیاتی، فرمان باید در یک Change کنترل‌شده اجرا شود. ظرفیت فایل داده و لاگ، نقش سرور، Replicaها، قفل‌های جاری و بازه اوج مصرف پیش از اقدام ثبت شوند. اجرای موفق فنی بدون ارزیابی اثر بر کاربران، معیار کافی برای پایان کار نیست.

مثال‌های عملی

مثال 1: مرتب‌سازی آنلاین صفحات برگ

ایندکس آزمایشی بدون ساخت مجدد کامل، به‌صورت تدریجی مرتب می‌شود.

DROP TABLE IF EXISTS #IndexLab;
    CREATE TABLE #IndexLab
    (
        RowID int IDENTITY(1,1) NOT NULL,
        OrderDate date NOT NULL,
        CustomerID int NOT NULL,
        Amount decimal(12,2) NOT NULL
    );

    INSERT INTO #IndexLab (OrderDate, CustomerID, Amount)
    VALUES ('2026-01-01', 101, 125000),
           ('2026-01-02', 102, 98000),
           ('2026-01-03', 101, 143500);

    CREATE INDEX IX_IndexLab_OrderDate
    ON #IndexLab (OrderDate);

    ALTER INDEX IX_IndexLab_OrderDate ON #IndexLab REORGANIZE;
    SELECT N'Completed' AS ReorganizeState;

شاخص	خروجی نمونه	توضیح
ReorganizeState	Completed	نتیجه نمایشی

REORGANIZE عملیات سبک‌تر و همیشه آنلاین است، ولی برای پراکندگی بسیار شدید ممکن است کافی نباشد.

مثال 2: فشرده‌سازی LOB هنگام Reorganize

برای ایندکس دارای ستون‌های LOB، رفتار پیش‌فرض فشرده‌سازی به‌صورت صریح مشخص می‌شود.

ALTER INDEX IX_Documents_Category
    ON dbo.Documents
    REORGANIZE WITH (LOB_COMPACTION = ON);

شاخص	خروجی نمونه	توضیح
LOB_COMPACTION	ON	نتیجه نمایشی

فشرده‌سازی LOB می‌تواند زمان و I/O را بیشتر کند؛ برای حجم بزرگ در پنجره مناسب اجرا شود.

مثال 3: انتخاب بازه Fragmentation میانی

فقط ایندکس‌هایی که بین آستانه پایین و بالا هستند برای Reorganize انتخاب می‌شوند.

SELECT
        i.name,
        ips.avg_fragmentation_in_percent,
        ips.page_count
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
    JOIN sys.indexes AS i
      ON i.object_id = ips.object_id
     AND i.index_id = ips.index_id
    WHERE ips.page_count >= 1000
      AND ips.avg_fragmentation_in_percent >= 10
      AND ips.avg_fragmentation_in_percent < 30
      AND i.name IS NOT NULL;

شاخص	خروجی نمونه	توضیح
Index	Fragmentation	page_count
IX_Sales_Date	18.70	18420

این Query فقط نامزدها را نشان می‌دهد؛ پیش از ساخت Dynamic SQL، بار کاری و نوع ایندکس را هم بررسی کنید.

مثال 4: Reorganize یک Partition

برای جدول پارتیشن‌بندی‌شده فقط Partition مشخص مرتب می‌شود.

ALTER INDEX IX_FactSales_OrderDate
    ON dbo.FactSales
    REORGANIZE PARTITION = 12
    WITH (LOB_COMPACTION = ON);

شاخص	خروجی نمونه	توضیح
Partition	12	نتیجه نمایشی
Result	Reorganized	نتیجه نمایشی

عملیات پارتیشنی حجم لاگ و زمان را محدود می‌کند و برای بارهای آرشیوی مناسب است.

مثال 5: به‌روزرسانی Statistics پس از Reorganize

چون Reorganize آمار را مانند Rebuild تازه نمی‌کند، Update Statistics جدا اجرا می‌شود.

ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REORGANIZE;

    UPDATE STATISTICS dbo.Sales IX_Sales_OrderDate
    WITH SAMPLE 50 PERCENT;

    SELECT STATS_DATE(OBJECT_ID(N'dbo.Sales'), 2) AS StatisticsUpdatedAt;

شاخص	خروجی نمونه	توضیح
StatisticsUpdatedAt	2026-07-22 22:10:00	نتیجه نمایشی

نرخ Sample را با حجم داده و دقت موردنیاز تنظیم کنید؛ FULLSCAN همیشه بهترین انتخاب عملی نیست.

مثال 6: رفتار امن هنگام نبود نامزد

اگر DMV ردیفی برنگرداند، پیام روشن تولید می‌شود و هیچ دستور DDL اجرا نمی‌گردد.

DECLARE @CandidateCount int;

    SELECT @CandidateCount = COUNT(*)
    FROM sys.dm_db_index_physical_stats
        (DB_ID(), OBJECT_ID(N'dbo.Sales'), NULL, NULL, 'LIMITED')
    WHERE page_count >= 1000
      AND avg_fragmentation_in_percent BETWEEN 10 AND 29.999;

    IF COALESCE(@CandidateCount, 0) = 0
        SELECT N'نامزد Reorganize وجود ندارد' AS ResultText;

شاخص	خروجی نمونه	توضیح
ResultText	نامزد Reorganize وجود ندارد	نتیجه نمایشی

نبود نامزد یک نتیجه سالم است و نباید به خطا یا اجرای Rebuild بدون دلیل تبدیل شود.

مثال 7: کنترل Page Lock

پیش از اجرا بررسی می‌شود که ایندکس اجازه Page Lock دارد؛ این گزینه برای Reorganize لازم است.

SELECT name, allow_page_locks
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Sales')
      AND name = N'IX_Sales_OrderDate';

    ALTER INDEX IX_Sales_OrderDate ON dbo.Sales
    SET (ALLOW_PAGE_LOCKS = ON);

    ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REORGANIZE;

شاخص	خروجی نمونه	توضیح
allow_page_locks	1	نتیجه نمایشی
Result	Completed	نتیجه نمایشی

تغییر ALLOW_PAGE_LOCKS باید با سیاست هم‌زمانی سامانه هماهنگ شود و صرفاً برای عبور از خطا انجام نشود.

مثال 8: ثبت پیشرفت عملیات

در یک Job نگهداری، زمان و نتیجه Reorganize ثبت می‌شود تا روندهای طولانی شناسایی شوند.

DECLARE @Start datetime2(0) = SYSDATETIME();
    ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REORGANIZE;

    INSERT dbo.IndexMaintenanceLog
        (IndexName, StartedAt, FinishedAt, ResultText)
    VALUES
        (N'IX_Sales_OrderDate', @Start, SYSDATETIME(), N'Reorganized');

شاخص	خروجی نمونه	توضیح
ResultText	Reorganized	نتیجه نمایشی

Reorganize درصد پیشرفت قابل توقف Resumable ندارد؛ مدت‌های تاریخی برای برآورد پنجره بسیار مفیدند.

مثال 9: اصلاح خطای ایندکس Disable شده

ابتدا وضعیت ایندکس بررسی می‌شود؛ ایندکس Disable شده باید Rebuild شود و قابلیت Reorganize ندارد.

IF EXISTS
    (
        SELECT 1
        FROM sys.indexes
        WHERE object_id = OBJECT_ID(N'dbo.Sales')
          AND name = N'IX_Sales_OrderDate'
          AND is_disabled = 1
    )
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;
    ELSE
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REORGANIZE;

شاخص	خروجی نمونه	توضیح
is_disabled	1	نتیجه نمایشی
اقدام اصلاحی	REBUILD	نتیجه نمایشی

این شاخه‌بندی از خطای تلاش برای Reorganize روی ساختار غیرفعال جلوگیری می‌کند.

مثال 10: تصمیم بین Reorganize و Rebuild

بر پایه اندازه و Fragmentation، دستور مناسب انتخاب و فقط برای نام کنترل‌شده اجرا می‌شود.

DECLARE @Frag decimal(6,2) = 22.5,
            @Pages bigint = 18000;

    IF @Pages < 1000 OR @Frag < 10
        SELECT N'SKIP' AS Decision;
    ELSE IF @Frag < 30
    BEGIN
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REORGANIZE;
        SELECT N'REORGANIZE' AS Decision;
    END
    ELSE
    BEGIN
        ALTER INDEX IX_Sales_OrderDate ON dbo.Sales REBUILD;
        SELECT N'REBUILD' AS Decision;
    END;

شاخص	خروجی نمونه	توضیح
Decision	REORGANIZE	نتیجه نمایشی

آستانه‌ها نقطه شروع هستند؛ Query Store و زمان واقعی اجرا باید آن‌ها را برای هر سامانه تنظیم کند.

ملاحظات Performance

Reorganize در واحدهای کوچک‌تر کار می‌کند و Log را تدریجی مصرف می‌کند، اما روی ایندکس بسیار بزرگ ممکن است مدت طولانی داشته باشد. پس از آن معمولاً باید نیاز Update Statistics جداگانه بررسی شود.

برای اندازه‌گیری معتبر، زمان Queryهای منتخب، Logical Read، Waitها و رشد Transaction Log را در یک بازه کاری مشابه مقایسه کنید. Cache گرم یا سرد، اجرای هم‌زمان Jobها و تغییر حجم داده می‌تواند نتیجه را منحرف کند؛ بنابراین یک نمونه منفرد مبنای تصمیم بلندمدت نیست.

خطاهای رایج

استفاده برای Fragmentation بسیار شدید بدون مقایسه.
فراموش کردن Update Statistics.
تلاش روی ایندکس Disable شده.
خاموش بودن ALLOW_PAGE_LOCKS.
اجرای مکرر روی ایندکس کوچک.

بهترین روش‌ها

نامزدها را با page_count فیلتر کنید.
Statistics و Query Store را پس از اجرا بررسی کنید.
LOB_COMPACTION را آگاهانه انتخاب کنید.
مدت عملیات را در Job ثبت کنید.

اسکریپت نگهداری را در کنترل نسخه قرار دهید، برای آن Owner مشخص کنید و خروجی اجرا را قابل جست‌وجو نگه دارید. عملیات بدون مالک و هشدار ممکن است نیمه‌تمام یا فراموش شود.

سؤالات متداول

سؤال متداول 1: ALTER INDEX ... REORGANIZE دقیقاً چه کاری انجام می‌دهد؟

این فرمان بخشی از چرخه مدیریت فیزیکی یا چرخه عمر ایندکس است. اثر دقیق آن به نوع فرمان، نوع ایندکس و State فعلی بستگی دارد؛ بنابراین پیش از اجرا Catalog Viewها و مستند نسخه نصب‌شده را بررسی کنید.

سؤال متداول 2: آیا ALTER INDEX ... REORGANIZE برای افراد مبتدی مناسب است؟

یادگیری Syntax ساده است، اما اجرای تولیدی نیازمند شناخت قفل، Log، tempdb، Availability و برنامه بازگشت است. ابتدا روی پایگاه آزمایشی و نسخه پشتیبان تمرین کنید.

سؤال متداول 3: هزینه اجرای ALTER INDEX ... REORGANIZE چگونه برآورد می‌شود؟

اندازه ایندکس، page_count، نرخ تغییر داده، سرعت ذخیره‌ساز، مدت پنجره و رشد لاگ را اندازه بگیرید. برای برآورد دقیق‌تر می‌توان از خدمات مشاوره و تحلیل کارایی SQL Server استفاده کرد.

سؤال متداول 4: آیا اجرای ALTER INDEX ... REORGANIZE می‌تواند سرعت سامانه را بیشتر کند؟

ممکن است، اما تضمینی نیست. بهبود تنها وقتی رخ می‌دهد که مشکل واقعی با ساختار ایندکس مرتبط باشد؛ Query Store و معیارهای قبل و بعد باید اثر را ثابت کنند.

سؤال متداول 5: تفاوت ALTER INDEX ... REORGANIZE با فرمان‌های نزدیک چیست؟

REBUILD ساختار را دوباره می‌سازد، REORGANIZE مرتب‌سازی تدریجی است، DISABLE استفاده را متوقف می‌کند، DROP حذف دائمی است و PAUSE، RESUME و ABORT چرخه عملیات Resumable را مدیریت می‌کنند.

سؤال متداول 6: برای اجرای سازمانی ALTER INDEX ... REORGANIZE چه خدماتی لازم است؟

طراحی Job، پایش، گزارش خطا، آزمون بازیابی و تنظیم آستانه‌ها بخش‌های اصلی هستند. تیم آموزش یا مشاوره پایگاه داده می‌تواند اسکریپت را با SLA و معماری همان سازمان هماهنگ کند.

سؤال متداول 7: خطای رایج در ALTER INDEX ... REORGANIZE چیست؟

اجرای فرمان با نام یا State نامعتبر، کمبود فضا، محدودیت Edition، قفل Schema و فراموش کردن وابستگی‌ها از خطاهای رایج‌اند. ERROR_NUMBER و ERROR_MESSAGE را ثبت و خطا را دوباره THROW کنید.

سؤال متداول 8: ALTER INDEX ... REORGANIZE چه اثری بر Performance دارد؟

اثر می‌تواند هم مثبت و هم منفی باشد. CPU، I/O، Waitها، رشد Log و زمان Queryهای مهم را در بازه‌ای قابل مقایسه ثبت کنید و به یک درصد Fragmentation اکتفا نکنید.

سؤال متداول 9: Best Practice اجرای ALTER INDEX ... REORGANIZE چیست؟

دستور را هدفمند، تکرارپذیر، قابل ثبت و دارای Guard Clause بنویسید. نام اشیا در Dynamic SQL باید از Catalog اعتبارسنجی و با QUOTENAME محافظت شود.

سؤال متداول 10: ALTER INDEX ... REORGANIZE در کدام نسخه‌های SQL Server کار می‌کند؟

Syntax پایه بسیاری از فرمان‌ها قدیمی است، ولی گزینه‌هایی مانند IF EXISTS، ONLINE، WAIT_AT_LOW_PRIORITY و RESUMABLE در نسخه‌ها و Editionهای متفاوت عرضه شده‌اند. سازگاری دقیق را با نسخه سرور و مستندات همان نسخه کنترل کنید.

سؤالات مصاحبه SQL Server

سؤال مصاحبه 1: چه زمانی ALTER INDEX ... REORGANIZE را در Production اجرا می‌کنید؟

پاسخ خوب باید به اندازه ایندکس، بار کاری، SLA، ظرفیت Log، قفل‌ها و معیار قبل و بعد اشاره کند، نه فقط یک آستانه ثابت.

سؤال مصاحبه 2: چگونه ریسک Dynamic SQL را کاهش می‌دهید؟

نام‌ها از sys.indexes و sys.objects اعتبارسنجی می‌شوند، QUOTENAME اعمال می‌شود و مقادیر داده‌ای با sp_executesql پارامتری می‌شوند.

سؤال مصاحبه 3: چگونه موفقیت عملیات را ثابت می‌کنید؟

با ثبت زمان، State، Fragmentation، page_count، رشد Log و معیارهای Query Store در قبل و بعد، نتیجه قابل دفاع می‌شود.

سؤال مصاحبه 4: تفاوت نگهداری ایندکس و Update Statistics چیست؟

ساختار فیزیکی و توزیع آماری دو مسئله مرتبط ولی جدا هستند. Rebuild معمولاً آمار ایندکس را تازه می‌کند، Reorganize نیاز به تصمیم جدا برای Statistics دارد.

سؤال مصاحبه 5: در Availability Group چه چیزی مهم است؟

حجم Log تولیدشده، نرخ ارسال و Redo در Replicaها، فضای دیسک و مدت پنجره باید هم‌زمان پایش شوند.

چک‌لیست نهایی

نسخه، Edition و پشتیبانی Syntax کنترل شده است.
نام Schema، جدول و ایندکس از Catalog تأیید شده است.
فضای داده، tempdb و Transaction Log بررسی شده است.
معیارهای پیش از اجرا ثبت شده‌اند.
TRY/CATCH، لاگ و مسیر بازگشت آماده است.
نتیجه و اثر کارایی پس از اجرا مقایسه می‌شود.

خدمات برنامه‌نویسی و پایگاه داده

برنامه‌نویسی اصفهان؛ قبول سفارشات برنامه‌نویسی و پایگاه داده: 09131253620. انجام پروژه‌های برنامه‌نویسی، آموزش برنامه‌نویسی و آموزش پایگاه داده SQL Server با رویکرد عملی و سازمانی ارائه می‌شود.

جمع‌بندی

ALTER INDEX ... REORGANIZE زمانی ارزشمند است که مسئله، دامنه و هزینه آن اندازه‌گیری شده باشد. نمونه‌های این مقاله الگوی ساخت فرمان امن، کنترل State، ثبت نتیجه و ارزیابی کارایی را نشان دادند. پیش از اجرای Production، اسکریپت را روی نسخه‌ای مشابه آزمایش و معیار موفقیت را از قبل تعریف کنید.

مشاهده فهرست کامل دستورات نگهداری ایندکس در SQL Server

آموزش ALTER INDEX REORGANIZE در SQL Server با مثال عملی و نکات Performance