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

بهینه‌سازی SQL Server: راهنمای کامل انتقال جداول و ایندکس‌ها به فایل‌گروپ‌ها

انتقال جداول و ایندکس‌ها به فایل‌گروپ‌های مختلف در SQL Server یک استراتژی حیاتی برای بهینه‌سازی عملکرد پایگاه داده و مدیریت فضای ذخیره‌سازی است. این فرآیند به مدیران پایگاه داده (DBA) اجازه می‌دهد تا I/O را در دیسک‌های فیزیکی مختلف توزیع کرده و دسترسی به داده‌ها را تسریع بخشند. در حالی که انتقال دستی یک شیء به شیء دیگر ممکن است، زمانی که با تعداد زیادی جدول و ایندکس مواجه هستیم، نیاز به یک روش خودکار و انبوه داریم. این مقاله به شما نشان می‌دهد که چگونه می‌توان این کار را به شکلی سازمان‌یافته و کارآمد انجام داد.

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

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

SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) AS SchemaName,
    OBJECT_NAME(OBJECT_ID) AS TableName,
    i.name AS IndexName,
    type_desc AS IndexType,
    ds.name AS FilegroupName,
    p.partition_number,
    p.rows
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 0 -- Exclude heaps initially, we'll handle them separately
ORDER BY SchemaName, TableName, IndexName;

این کوئری اطلاعاتی در مورد نام شمای جدول، نام جدول، نام ایندکس، نوع ایندکس (مثلاً کلاستر شده یا غیرکلاستر شده)، نام فایل‌گروپ فعلی، شماره پارتیشن و تعداد ردیف‌ها را از نمایه‌های سیستم استخراج می‌کند. این اطلاعات برای برنامه‌ریزی عملیات انتقال جداول و ایندکس‌ها به فایل‌گروپ جدید حیاتی است.

با داشتن این اطلاعات، می‌توانیم اسکریپت‌های پویا برای انتقال اشیاء به فایل‌گروپ‌های جدید تولید کنیم. فرآیند انتقال برای انواع مختلف اشیاء (ایندکس‌های کلاستر شده، غیرکلاستر شده و جداول هیپ) کمی متفاوت است.

انتقال ایندکس‌های کلاستر شده و جداول مرتبط (Clustered Indexes and Tables)

زمانی که یک جدول دارای ایندکس کلاستر شده باشد، داده‌های جدول به صورت فیزیکی بر اساس کلید ایندکس مرتب می‌شوند. انتقال ایندکس کلاستر شده به یک فایل‌گروپ جدید، در واقع کل داده‌های جدول را به آن فایل‌گروپ منتقل می‌کند. این کار با استفاده از دستور `ALTER INDEX … REBUILD … WITH (MOVE TO)` انجام می‌شود. اسکریپت زیر برای تولید دستورات SQL لازم برای این منظور طراحی شده است:

SELECT
    'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(i.object_id) + '].[' + OBJECT_NAME(i.object_id) + '] REBUILD WITH (MOVE TO [' + @NewFilegroupName + ']);'
FROM sys.indexes i
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id = 1 -- Clustered index
    AND ds.name  @NewFilegroupName;

این دستور کوئری SQL یک رشته `ALTER INDEX` را برای هر ایندکس کلاستر شده تولید می‌کند که در فایل‌گروپ هدف (تعریف شده توسط `@NewFilegroupName`) قرار ندارد. این اسکریپت تضمین می‌کند که فقط ایندکس‌های کلاستر شده‌ای که نیاز به جابجایی دارند، هدف قرار می‌گیرند و به فایل‌گروپ جدید منتقل شوند. این عملیات شامل بازسازی ایندکس و انتقال تمامی داده‌های جدول به فایل‌گروپ مقصد می‌شود.

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

ایندکس‌های غیرکلاستر شده به طور مستقل از داده‌های اصلی جدول ذخیره می‌شوند. برای انتقال ایندکس‌های غیرکلاستر شده به یک فایل‌گروپ جدید، باز هم از دستور `ALTER INDEX … REBUILD … WITH (MOVE TO)` استفاده می‌کنیم. این فرآیند بر روی داده‌های جدول تأثیری نمی‌گذارد و فقط ساختار ایندکس را جابجا می‌کند.

SELECT
    'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(i.object_id) + '].[' + OBJECT_NAME(i.object_id) + '] REBUILD WITH (MOVE TO [' + @NewFilegroupName + ']);'
FROM sys.indexes i
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id > 1 -- Non-clustered index
    AND ds.name  @NewFilegroupName;

این قطعه کد SQL دستور `ALTER INDEX` را برای هر ایندکس غیرکلاستر شده‌ای که در حال حاضر در فایل‌گروپ هدف نیست، تولید می‌کند. با اجرای این دستورات، ایندکس‌های غیرکلاستر شده بازسازی شده و به فایل‌گروپ مشخص شده (`@NewFilegroupName`) منتقل می‌شوند که می‌تواند به توزیع بار کاری I/O و بهبود کلی عملکرد پایگاه داده کمک کند.

انتقال جداول هیپ (Heaps)

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

SELECT
    'CREATE CLUSTERED INDEX IX_Temp_Heap_Move ON [' + OBJECT_SCHEMA_NAME(i.object_id) + '].[' + OBJECT_NAME(i.object_id) + '] (' + (
        SELECT TOP 1 c.name FROM sys.columns c WHERE c.object_id = i.object_id ORDER BY c.column_id
    ) + ') WITH (DROP_EXISTING = OFF, ONLINE = OFF) ON [' + @NewFilegroupName + '];' + CHAR(13) + CHAR(10) +
    'DROP INDEX IX_Temp_Heap_Move ON [' + OBJECT_SCHEMA_NAME(i.object_id) + '].[' + OBJECT_NAME(i.object_id) + '];'
FROM sys.indexes i
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.index_id = 0 -- Heap
    AND ds.name  @NewFilegroupName;

این اسکریپت دو دستور را برای هر جدول هیپ که در فایل‌گروپ هدف نیست، تولید می‌کند. ابتدا، یک ایندکس کلاستر شده موقت با نام `IX_Temp_Heap_Move` بر روی اولین ستون موجود در جدول ایجاد می‌شود و ایندکس (و بنابراین کل جدول) به فایل‌گروپ جدید (`@NewFilegroupName`) منتقل می‌شود. سپس، ایندکس کلاستر شده موقت حذف می‌شود تا جدول دوباره به یک هیپ تبدیل شود، اما اکنون در مکان جدید خود در فایل‌گروپ مقصد قرار دارد.

مدیریت جداول پارتیشن‌بندی شده (Partitioned Tables)

توجه به این نکته مهم است که اسکریپت‌های فوق برای جداول و ایندکس‌های غیر پارتیشن‌بندی شده طراحی شده‌اند. جداول و ایندکس‌های پارتیشن‌بندی شده نیاز به رویکرد متفاوتی برای انتقال دارند که پیچیده‌تر است و شامل مدیریت هر پارتیشن به صورت جداگانه می‌شود. برای شناسایی جداول پارتیشن‌بندی شده در پایگاه داده خود، می‌توانید از کوئری زیر استفاده کنید:

SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) AS SchemaName,
    OBJECT_NAME(OBJECT_ID) AS TableName,
    i.name AS IndexName,
    type_desc AS IndexType,
    ds.name AS FilegroupName,
    p.partition_number,
    p.rows
FROM sys.indexes i
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.data_space_id IN (SELECT data_space_id FROM sys.partition_schemes)
ORDER BY SchemaName, TableName, IndexName;

این کوئری اطلاعات مربوط به جداول و ایندکس‌هایی را که از طرح‌های پارتیشن‌بندی استفاده می‌کنند، بازیابی می‌کند. شناسایی این اشیاء به شما امکان می‌دهد تا قبل از اجرای اسکریپت‌های انتقال انبوه برای اشیاء غیر پارتیشن‌بندی شده، آنها را از فرآیند کلی مستثنی کنید یا رویکرد متناسب با پارتیشن‌بندی را در پیش بگیرید.

نتیجه‌گیری

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