مثالهای عملی
مثال 1: بازسازی همه ایندکسهای جدول آزمایشی
دو ایندکس ساخته و با یک فرمان ALL بازسازی میشوند.
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);
CREATE INDEX IX_IndexLab_CustomerID ON #IndexLab(CustomerID);
ALTER INDEX ALL ON #IndexLab REBUILD;
SELECT name, is_disabled FROM tempdb.sys.indexes WHERE object_id = OBJECT_ID(N'tempdb..#IndexLab') AND index_id > 0;
| شاخص | خروجی نمونه | توضیح |
|---|
| IX_IndexLab_OrderDate | 0 | نتیجه نمایشی |
| IX_IndexLab_CustomerID | 0 | نتیجه نمایشی |
ALL کدنویسی را کوتاه میکند اما هزینه همه ساختارها را یکسان تحمیل میکند.
مثال 2: Reorganize همه ایندکسها
برای جدول کوچک آزمایشی تمام ایندکسها به صورت آنلاین مرتب میشوند.
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);
CREATE INDEX IX_IndexLab_CustomerID ON #IndexLab(CustomerID);
ALTER INDEX ALL ON #IndexLab REORGANIZE;
| شاخص | خروجی نمونه | توضیح |
|---|
| Action | REORGANIZE ALL | نتیجه نمایشی |
اگر بعضی ایندکسها نیاز متفاوت داشته باشند، فرمان جداگانه دقیقتر است.
مثال 3: بازسازی ALL با گزینههای مشترک
MAXDOP و SORT_IN_TEMPDB برای تمام ایندکسهای جدول اعمال میشود.
ALTER INDEX ALL ON dbo.Sales
REBUILD WITH
(
SORT_IN_TEMPDB = ON,
MAXDOP = 2,
FILLFACTOR = 95
);
| شاخص | خروجی نمونه | توضیح |
|---|
| MAXDOP | 2 | نتیجه نمایشی |
| FILLFACTOR | 95 | نتیجه نمایشی |
یک Fill Factor مشترک برای همه ایندکسها همیشه مناسب نیست؛ الگوی کلیدها ممکن است متفاوت باشد.
مثال 4: اندازهگیری هزینه پیش از ALL
مجموع صفحات و تعداد ایندکسها برای برآورد فضا و زمان گزارش میشود.
SELECT
IndexCount = COUNT(DISTINCT index_id),
TotalPages = SUM(page_count),
EstimatedSizeMB = SUM(page_count) * 8.0 / 1024
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.Sales'), NULL, NULL, 'LIMITED')
WHERE index_id > 0;
| شاخص | خروجی نمونه | توضیح |
|---|
| IndexCount | 7 | نتیجه نمایشی |
| TotalPages | 485000 | نتیجه نمایشی |
| EstimatedSizeMB | 3789.06 | نتیجه نمایشی |
برآورد اندازه قبل از ALL از رشد ناگهانی لاگ یا کمبود فضای SORT_IN_TEMPDB جلوگیری میکند.
مثال 5: عملیات روی یک Partition برای همه ایندکسها
در جدول پارتیشنبندیشده، Partition هدف در تمام ایندکسهای همتراز بازسازی میشود.
ALTER INDEX ALL ON dbo.FactSales
REBUILD PARTITION = 12
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 2);
| شاخص | خروجی نمونه | توضیح |
|---|
| Partition | 12 | نتیجه نمایشی |
| Scope | ALL indexes | نتیجه نمایشی |
همترازی ایندکسها و پشتیبانی گزینهها را قبل از اجرای Partition-level بررسی کنید.
مثال 6: جلوگیری از ALL هنگام ورودی NULL
نام جدول الزامی است و از ساخت Dynamic SQL ناقص جلوگیری میشود.
DECLARE @Schema sysname = N'dbo',
@Table sysname = NULL;
IF NULLIF(LTRIM(RTRIM(@Table)), N'') IS NULL
THROW 51030, N'نام جدول برای ALTER INDEX ALL الزامی است.', 1;
| شاخص | خروجی نمونه | توضیح |
|---|
| Result | Controlled error 51030 | نتیجه نمایشی |
هرگز NULL را به معنی همه جدولهای پایگاه داده تفسیر نکنید.
مثال 7: گزارش ایندکسهای Disable پیش از ALL
ساختارهای غیرفعال جدا میشوند تا اثر REBUILD ALL روشن باشد.
SELECT name, type_desc, is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Sales')
AND index_id > 0
ORDER BY index_id;
| شاخص | خروجی نمونه | توضیح |
|---|
| Index | is_disabled | نتیجه نمایشی |
| IX_Sales_Date | 1 | نتیجه نمایشی |
| IX_Sales_Customer | 0 | نتیجه نمایشی |
REBUILD ALL میتواند ایندکسهای غیرفعال را فعال کند؛ این اثر باید از پیش تأیید شده باشد.
مثال 8: ثبت عملیات گروهی
آمار همه ایندکسها پیش از عملیات Snapshot و سپس نتیجه گروهی ثبت میشود.
INSERT dbo.IndexMaintenanceSnapshot
(CapturedAt, ObjectID, IndexID, Fragmentation, PageCount)
SELECT
SYSDATETIME(), object_id, index_id,
avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.Sales'), NULL, NULL, 'SAMPLED');
ALTER INDEX ALL ON dbo.Sales REBUILD;
SELECT N'Completed' AS BatchState;
| شاخص | خروجی نمونه | توضیح |
|---|
| BatchState | Completed | نتیجه نمایشی |
Snapshot پیش از عملیات امکان مقایسه و ممیزی نتیجه را فراهم میکند.
مثال 9: اصلاح استفاده کورکورانه از ALL
اگر فقط یک ایندکس Fragmentation بالا دارد، همان ایندکس هدف قرار میگیرد.
SELECT
i.name,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.Sales'), 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 >= 30;
| شاخص | خروجی نمونه | توضیح |
|---|
| Index | Fragmentation | نتیجه نمایشی |
| IX_Sales_Date | 38.20 | نتیجه نمایشی |
نتیجه نشان میدهد چرا هدفگیری یک ایندکس میتواند از REBUILD ALL کمهزینهتر باشد.
مثال 10: مقایسه کارایی سیاست گروهی و انتخابی
زمان تاریخی اجرای ALL با مجموع عملیات انتخابی مقایسه میشود.
SELECT
Strategy,
AvgDurationSeconds = AVG(DurationSeconds),
AvgLogGrowthMB = AVG(LogGrowthMB)
FROM dbo.IndexMaintenanceHistory
WHERE TableName = N'dbo.Sales'
AND StartedAt >= DATEADD(day, -90, SYSDATETIME())
GROUP BY Strategy;
| شاخص | خروجی نمونه | توضیح |
|---|
| Strategy | AvgDuration | LogGrowth |
| ALL | 1280 | 7420 |
| SELECTIVE | 410 | 1860 |
داده تاریخی بهترین راه برای اثبات سود یا زیان سیاست ALTER INDEX ALL است.
سؤالات متداول
سؤال متداول 1: ALTER INDEX ALL دقیقاً چه کاری انجام میدهد؟
این فرمان بخشی از چرخه مدیریت فیزیکی یا چرخه عمر ایندکس است. اثر دقیق آن به نوع فرمان، نوع ایندکس و State فعلی بستگی دارد؛ بنابراین پیش از اجرا Catalog Viewها و مستند نسخه نصبشده را بررسی کنید.
سؤال متداول 2: آیا ALTER INDEX ALL برای افراد مبتدی مناسب است؟
یادگیری Syntax ساده است، اما اجرای تولیدی نیازمند شناخت قفل، Log، tempdb، Availability و برنامه بازگشت است. ابتدا روی پایگاه آزمایشی و نسخه پشتیبان تمرین کنید.
سؤال متداول 3: هزینه اجرای ALTER INDEX ALL چگونه برآورد میشود؟
اندازه ایندکس، page_count، نرخ تغییر داده، سرعت ذخیرهساز، مدت پنجره و رشد لاگ را اندازه بگیرید. برای برآورد دقیقتر میتوان از خدمات مشاوره و تحلیل کارایی SQL Server استفاده کرد.
سؤال متداول 4: آیا اجرای ALTER INDEX ALL میتواند سرعت سامانه را بیشتر کند؟
ممکن است، اما تضمینی نیست. بهبود تنها وقتی رخ میدهد که مشکل واقعی با ساختار ایندکس مرتبط باشد؛ Query Store و معیارهای قبل و بعد باید اثر را ثابت کنند.
سؤال متداول 5: تفاوت ALTER INDEX ALL با فرمانهای نزدیک چیست؟
REBUILD ساختار را دوباره میسازد، REORGANIZE مرتبسازی تدریجی است، DISABLE استفاده را متوقف میکند، DROP حذف دائمی است و PAUSE، RESUME و ABORT چرخه عملیات Resumable را مدیریت میکنند.
سؤال متداول 6: برای اجرای سازمانی ALTER INDEX ALL چه خدماتی لازم است؟
طراحی Job، پایش، گزارش خطا، آزمون بازیابی و تنظیم آستانهها بخشهای اصلی هستند. تیم آموزش یا مشاوره پایگاه داده میتواند اسکریپت را با SLA و معماری همان سازمان هماهنگ کند.
سؤال متداول 7: خطای رایج در ALTER INDEX ALL چیست؟
اجرای فرمان با نام یا State نامعتبر، کمبود فضا، محدودیت Edition، قفل Schema و فراموش کردن وابستگیها از خطاهای رایجاند. ERROR_NUMBER و ERROR_MESSAGE را ثبت و خطا را دوباره THROW کنید.
سؤال متداول 8: ALTER INDEX ALL چه اثری بر Performance دارد؟
اثر میتواند هم مثبت و هم منفی باشد. CPU، I/O، Waitها، رشد Log و زمان Queryهای مهم را در بازهای قابل مقایسه ثبت کنید و به یک درصد Fragmentation اکتفا نکنید.
سؤال متداول 9: Best Practice اجرای ALTER INDEX ALL چیست؟
دستور را هدفمند، تکرارپذیر، قابل ثبت و دارای Guard Clause بنویسید. نام اشیا در Dynamic SQL باید از Catalog اعتبارسنجی و با QUOTENAME محافظت شود.
سؤال متداول 10: ALTER INDEX ALL در کدام نسخههای SQL Server کار میکند؟
Syntax پایه بسیاری از فرمانها قدیمی است، ولی گزینههایی مانند IF EXISTS، ONLINE، WAIT_AT_LOW_PRIORITY و RESUMABLE در نسخهها و Editionهای متفاوت عرضه شدهاند. سازگاری دقیق را با نسخه سرور و مستندات همان نسخه کنترل کنید.