انتقال جداول بزرگ به FileGroup دیگر در SQLServer

راهنمای جامع انتقال جداول بزرگ به FileGroup دیگر در SQLServer: بهینه‌سازی عملکرد و فضای دیسک

در کار با SQL Server، ممکن است نیاز به انتقال یک جدول بزرگ به یک FileGroup متفاوت پیدا کنید. این فرآیند برای بهبود عملکرد دیتابیس، مدیریت فضای ذخیره‌سازی و سازماندهی بهتر داده‌ها حیاتی است. در حالی که ابزارهایی مانند dbatools cmdlet در PowerShell گزینه‌هایی برای این کار ارائه می‌دهند، این مقاله بر روی روش سنتی‌تر و قدرتمندتر با استفاده از T-SQL و ایندکس کلاستر شده تمرکز دارد. با مطالعه این راهنما، خواهید آموخت که چگونه یک جدول حجیم را به یک گروه فایل دیگر منتقل کنید و از مزایای آن بهره‌مند شوید.

چرا یک جدول را به یک FileGroup دیگر منتقل کنیم؟

دلایل مختلفی برای جابجایی جداول به FileGroupهای جدید وجود دارد:

1. مدیریت فضای ذخیره‌سازی: سازماندهی جداول در FileGroupهای مختلف به شما امکان می‌دهد تا فضای دیسک را بهینه‌تر مدیریت کنید، به خصوص زمانی که نیاز به جداسازی داده‌های فعال از داده‌های آرشیو یا داده‌های کمتر استفاده شده دارید.
2. بهبود عملکرد: با قرار دادن جداول پرکاربرد بر روی دیسک‌های سریع‌تر (SSD) یا FileGroupهایی با RAID متفاوت، می‌توان به طور قابل توجهی عملکرد ورودی/خروجی (I/O) را بهبود بخشید. این کار باعث افزایش سرعت بازیابی داده‌ها و کاهش زمان پاسخگویی می‌شود.
3. پشتیبان‌گیری و بازیابی: داشتن FileGroupهای متعدد می‌تواند استراتژی‌های پشتیبان‌گیری و بازیابی شما را انعطاف‌پذیرتر کند، به خصوص برای دیتابیس‌های بسیار بزرگ.
4. آفلاین کردن FileGroup: در برخی موارد، ممکن است نیاز باشد یک FileGroup را آفلاین کنید (مثلاً برای عملیات نگهداری) بدون اینکه کل دیتابیس را آفلاین کنید.

استفاده از ایندکس کلاستر شده برای انتقال جدول

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

قبل از شروع، با استفاده از پرس و جوی زیر، مطمئن شوید که FileGroupهای مورد نظر شما وجود دارند و آماده استفاده هستند:

SELECT *
FROM sys.filegroups

حالا، برای ایجاد یک ایندکس کلاستر شده و انتقال جدول، از دستور CREATE CLUSTERED INDEX به همراه عبارت WITH (DROP_EXISTING = ON, MOVE TO [NewFileGroup]) استفاده کنید. این عبارت اطمینان حاصل می‌کند که ایندکس موجود حذف شده و مجدداً در FileGroup جدید ساخته شود و همزمان داده‌های جدول نیز به آن FileGroup منتقل شوند.

مثال زیر نحوه ایجاد یک ایندکس کلاستر شده جدید را بر روی ستون Id در جدول MyTestTable و انتقال آن به FileGroup با نام NewFileGroup نشان می‌دهد:

CREATE CLUSTERED INDEX IX_MyTestTable_Id
ON MyTestTable (Id)
WITH (DROP_EXISTING = ON, MOVE TO NewFileGroup)

این دستور، ایندکس کلاستر شده IX_MyTestTable_Id را بر اساس ستون Id ایجاد می‌کند و جدول MyTestTable را به NewFileGroup منتقل می‌کند. اگر جدول شما از قبل ایندکس کلاستر شده ندارد (یک Heap است)، می‌توانید عبارت DROP_EXISTING = ON را حذف کنید.

گزینه‌های پیشرفته برای انتقال ایندکس

1. عملیات آنلاین (ONLINE = ON)

برای دیتابیس‌های با دسترسی بالا که نمی‌توانند زمان از کار افتادگی داشته باشند، می‌توانید عملیات بازسازی ایندکس کلاستر شده را به صورت آنلاین انجام دهید. این گزینه به کاربران اجازه می‌دهد تا در طول فرآیند بازسازی، به جدول دسترسی داشته باشند. توجه داشته باشید که این ویژگی فقط در نسخه‌های Enterprise و Developer SQL Server موجود است.

مثال:

CREATE CLUSTERED INDEX IX_MyTestTable_Id
ON MyTestTable (Id)
WITH (DROP_EXISTING = ON, ONLINE = ON, MOVE TO NewFileGroup)

2. فشرده‌سازی داده‌ها (DATA_COMPRESSION)

برای کاهش فضای ذخیره‌سازی و افزایش عملکرد (با کاهش I/O)، می‌توانید همزمان با انتقال جدول، فشرده‌سازی داده‌ها را نیز اعمال کنید.

مثال برای فشرده‌سازی ردیف:

CREATE CLUSTERED INDEX IX_MyTestTable_Id
ON MyTestTable (Id)
WITH (DROP_EXISTING = ON, MOVE TO NewFileGroup, DATA_COMPRESSION = ROW)

مثال برای فشرده‌سازی صفحه:

CREATE CLUSTERED INDEX IX_MyTestTable_Id
ON MyTestTable (Id)
WITH (DROP_EXISTING = ON, MOVE TO NewFileGroup, DATA_COMPRESSION = PAGE)

3. انتقال ایندکس‌های غیرکلاستر شده (Non-Clustered Indexes)

هنگامی که یک ایندکس کلاستر شده را به یک FileGroup جدید منتقل می‌کنید، ایندکس‌های غیرکلاستر شده به طور خودکار به FileGroup جدید منتقل نمی‌شوند. شما باید ایندکس‌های غیرکلاستر شده را به صورت جداگانه بازسازی و به FileGroup مورد نظر خود منتقل کنید.

مثال:

CREATE NONCLUSTERED INDEX IX_MyTestTable_Name
ON MyTestTable (Name)
WITH (DROP_EXISTING = ON, MOVE TO NewFileGroup)

4. انتقال جداول Heap (بدون ایندکس کلاستر شده)

اگر جدول شما یک Heap است (یعنی ایندکس کلاستر شده ندارد)، می‌توانید با ایجاد یک ایندکس کلاستر شده و سپس حذف آن، جدول را به یک FileGroup دیگر منتقل کنید.

مثال:

-- ایجاد یک ایندکس کلاستر شده موقت و انتقال Heap به FileGroup جدید
CREATE CLUSTERED INDEX Temp_CI
ON MyTestTable (Id)
WITH (MOVE TO NewFileGroup)
GO

-- حذف ایندکس کلاستر شده موقت برای بازگرداندن جدول به وضعیت Heap
DROP INDEX Temp_CI ON MyTestTable
GO

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

5. انتقال ایندکس‌های Columnstore

اگر از ایندکس‌های Columnstore استفاده می‌کنید، فرآیند انتقال مشابه است. شما می‌توانید آن‌ها را بازسازی کرده و به FileGroup دیگری منتقل کنید.

مثال:

CREATE CLUSTERED COLUMNSTORE INDEX IX_MyTestTable_CCI
ON MyTestTable
WITH (DROP_EXISTING = ON, MOVE TO NewFileGroup)

6. انتقال جداول پارتیشن‌بندی شده

برای جداول پارتیشن‌بندی شده، شما نیاز دارید که Function و Scheme پارتیشن‌بندی را تعریف کنید و سپس ایندکس کلاستر شده را بر روی Scheme پارتیشن‌بندی ایجاد کنید تا هر پارتیشن در FileGroup مربوطه قرار گیرد.

مثال:

-- ایجاد Partition Function
CREATE PARTITION FUNCTION MyRangePF (INT)
AS RANGE LEFT FOR VALUES (100, 200, 300)
GO

-- ایجاد Partition Scheme و نگاشت پارتیشن ها به FileGroup های مختلف
CREATE PARTITION SCHEME MyRangePS
AS PARTITION MyRangePF
TO (PRIMARY, NewFileGroup, AnotherFileGroup, [SomeOtherFileGroup])
GO

-- ایجاد ایندکس کلاستر شده بر اساس Partition Scheme
CREATE CLUSTERED INDEX IX_MyTestTable_Id
ON MyTestTable (Id)
ON MyRangePS (Id)
WITH (DROP_EXISTING = ON)

این مثال نشان می‌دهد که چگونه می‌توان یک جدول پارتیشن‌بندی شده را مدیریت کرد و پارتیشن‌های آن را بر روی FileGroupهای مختلف توزیع کرد.

بررسی نتایج

پس از انجام عملیات انتقال، می‌توانید با استفاده از پرس و جوی زیر، اطمینان حاصل کنید که جدول و ایندکس‌های آن به FileGroupهای جدید منتقل شده‌اند:

SELECT o.name AS [Table Name],
       i.name AS [Index Name],
       i.type_desc AS [Index Type],
       ds.name AS [FileGroup Name]
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE o.name = 'MyTestTable'

این پرس و جو، نام جدول، نام ایندکس، نوع ایندکس و FileGroup مربوط به هر ایندکس را نمایش می‌دهد و به شما کمک می‌کند تا صحت انتقال داده‌ها را تأیید کنید.

بازیابی فضای دیسک (Shrink Database)

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

مثال:

-- مشاهده فضای استفاده شده و آزاد در فایل ها
SELECT name, size/128.0 AS [Size MB],
       size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [AvailableSpaceInMB]
FROM sys.database_files
WHERE type_desc = 'ROWS'

-- کاهش حجم فایل دیتابیس (فایل منطقی به نام 'MyDatabase_Data' را فشرده می کند)
DBCC SHRINKFILE (N'MyDatabase_Data' , 0, TRUNCATEONLY)

توجه داشته باشید که عملیات SHRINKFILE باید با احتیاط انجام شود، زیرا می‌تواند منجر به تکه‌تکه شدن (Fragmentation) شود و عملکرد دیتابیس را در آینده تحت تأثیر قرار دهد. بهتر است این کار را در زمان‌های کم ترافیک انجام دهید و پس از آن، ایندکس‌ها را بازسازی کنید.

نتیجه‌گیری

انتقال جداول بزرگ به FileGroupهای مختلف یک تکنیک قدرتمند در مدیریت SQL Server است که می‌تواند به بهبود عملکرد، مدیریت بهتر فضای ذخیره‌سازی و افزایش کارایی دیتابیس کمک کند. با استفاده از روش‌های توضیح داده شده در این مقاله، از جمله بازسازی ایندکس کلاستر شده و استفاده از گزینه‌هایی مانند ONLINE و DATA_COMPRESSION، می‌توانید این عملیات را به صورت مؤثر و کارآمد انجام دهید. همیشه قبل از انجام چنین تغییرات مهمی، پشتیبان‌گیری کامل از دیتابیس را فراموش نکنید.