پاکسازی Schema دیتابیس SQL Server راهنمای جامع بهینه سازی

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

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

شناسایی جداول و ستون‌های بلااستفاده

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

برای مشاهده جداول خالی در دیتابیس فعلی، می‌توانید از این کوئری استفاده کنید:

SELECT
    t.name AS TableName,
    s.name AS SchemaName
FROM
    sys.tables t
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.is_ms_shipped = 0 -- Exclude system tables
    AND OBJECTPROPERTY(t.object_id, 'TableHasRows') = 0;

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

ردیابی استفاده از ستون‌ها

شناسایی ستون‌های بلااستفاده کمی چالش‌برانگیزتر است، زیرا SQL Server به طور پیش‌فرض مکانیزمی برای ردیابی مستقیم استفاده از ستون ندارد. با این حال، می‌توانیم با بررسی وابستگی‌ها و استفاده از قابلیت‌هایی مانند Dynamic Management Views (DMVs) در موارد خاص، به سرنخ‌هایی دست یابیم.

برای بررسی ستون‌هایی که در هیچ ایندکس یا کلید خارجی (Foreign Key) استفاده نشده‌اند، می‌توانیم از این کوئری استفاده کنیم:

SELECT
    c.name AS ColumnName,
    t.name AS TableName,
    s.name AS SchemaName
FROM
    sys.columns c
INNER JOIN
    sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
    sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN
    sys.foreign_key_columns fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
WHERE
    t.is_ms_shipped = 0
    AND ic.object_id IS NULL -- Not part of any index
    AND fkc.parent_object_id IS NULL -- Not part of any foreign key
ORDER BY
    TableName, ColumnName;

این کوئری ستون‌هایی را شناسایی می‌کند که نه در ایندکس‌ها و نه در کلیدهای خارجی مشارکت دارند. اگرچه این به تنهایی دلیلی بر بلااستفاده بودن یک ستون نیست (ممکن است در کوئری‌های SELECT یا INSERT/UPDATE/DELETE استفاده شود)، اما می‌تواند نقاطی را برای بررسی عمیق‌تر مشخص کند. برای ردیابی دقیق‌تر استفاده از ستون، نیاز به ابزارهای مانیتورینگ پیشرفته، آنالیز کوئری‌ها یا پروفایلینگ خواهید داشت.

بررسی و بهینه‌سازی ایندکس‌ها

ایندکس‌های بلااستفاده یا با استفاده کم می‌توانند فضای دیسک را هدر داده و عملکرد عملیات INSERT، UPDATE و DELETE را کاهش دهند. SQL Server DMVs اطلاعات ارزشمندی در مورد استفاده از ایندکس‌ها ارائه می‌دهند.

برای یافتن ایندکس‌هایی که استفاده نشده‌اند یا استفاده بسیار کمی دارند، می‌توانید از این کوئری استفاده کنید:

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
    s.user_updates AS TotalWrites,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update
FROM
    sys.dm_db_index_usage_stats s
INNER JOIN
    sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE
    OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.name IS NOT NULL
ORDER BY
    (s.user_seeks + s.user_scans + s.user_lookups) ASC;

این کوئری اطلاعات مربوط به تعداد دفعات جستجو، اسکن و به روزرسانی ایندکس‌ها را نشان می‌دهد. ایندکس‌هایی با مقدار `TotalReads` نزدیک به صفر و `TotalWrites` بالا، کاندیدای خوبی برای غیرفعال کردن یا حذف هستند، زیرا آن‌ها سربار به سیستم اضافه می‌کنند بدون اینکه فایده‌ای در بازیابی داده داشته باشند.

شناسایی رویه‌ها و توابع بلااستفاده

رویه‌های ذخیره شده (Stored Procedures)، توابع (Functions) و ویوها (Views) نیز می‌توانند با گذشت زمان بلااستفاده شوند. ردیابی استفاده از آن‌ها دشوارتر از جداول و ایندکس‌ها است، زیرا SQL Server به طور مستقیم آمار استفاده از این اشیاء را ثبت نمی‌کند. با این حال، می‌توانیم از `sys.sql_modules` برای بررسی وابستگی‌ها و یافتن اشیائی که هیچ مرجع خارجی ندارند، استفاده کنیم.

برای یافتن رویه‌ها و توابعی که هیچ وابستگی ورودی به آن‌ها پیدا نشده است، می‌توانید از این کوئری استفاده کنید:

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    p.name AS ProcedureName,
    p.create_date,
    p.modify_date
FROM
    sys.procedures p
LEFT JOIN
    sys.sql_expression_dependencies sed ON p.object_id = sed.referenced_id
WHERE
    sed.referenced_id IS NULL AND p.is_ms_shipped = 0
ORDER BY
    p.modify_date DESC;

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

مدیریت سایر اشیاء Schema پایگاه داده

سایر اشیاء مانند تریگرها (Triggers)، محدودیت‌ها (Constraints) و اشیاء از کار افتاده (Deprecated Objects) نیز باید بررسی شوند.
تریگرها: تریگرهای بلااستفاده یا با عملکرد ضعیف می‌توانند به طور قابل توجهی بر عملکرد دیتابیس تأثیر بگذارند. بررسی `sys.triggers` و `sys.trigger_events` می‌تواند در شناسایی آن‌ها کمک کند.
محدودیت‌ها (Constraints): محدودیت‌های بلااستفاده یا تکراری می‌توانند سربار غیرضروری ایجاد کنند.
فایل‌گروپ‌های خالی (Empty Filegroups): اگر فایل‌گروپ‌هایی دارید که دیگر هیچ فایلی به آن‌ها اختصاص داده نشده یا هیچ شیئی در آن‌ها ذخیره نمی‌شود، می‌توانید آن‌ها را حذف کنید.

نقشه‌برداری وابستگی‌ها (Dependency Mapping)

برای تصمیم‌گیری ایمن در مورد حذف اشیاء، درک وابستگی‌های آن‌ها حیاتی است. SQL Server ابزارهایی برای مشاهده وابستگی‌ها ارائه می‌دهد:

SELECT
    OBJECT_NAME(referencing_id) AS ReferencingObject,
    o.type_desc AS ReferencingObjectType,
    OBJECT_NAME(referenced_id) AS ReferencedObject,
    o2.type_desc AS ReferencedObjectType
FROM
    sys.sql_expression_dependencies sed
INNER JOIN
    sys.objects o ON sed.referencing_id = o.object_id
INNER JOIN
    sys.objects o2 ON sed.referenced_id = o2.object_id
WHERE
    OBJECT_NAME(referenced_id) = 'YourTableName' -- Replace with the object you want to check
    AND sed.is_caller_dependent = 0;

این کوئری تمام اشیائی که به یک `YourTableName` مشخص ارجاع می‌دهند را نشان می‌دهد. استفاده از این ابزار قبل از حذف هر شیء، خطرات را به حداقل می‌رساند.

استراتژی پاکسازی و پیاده‌سازی

پس از شناسایی اشیاء کاندید برای پاکسازی، یک استراتژی گام به گام ضروری است:
1. بررسی دقیق: هر شیء را به دقت بررسی کنید تا از عدم استفاده آن اطمینان حاصل کنید. با توسعه‌دهندگان و کاربران در صورت لزوم مشورت کنید.
2. بک‌آپ‌گیری: همیشه قبل از انجام تغییرات عمده در Schema پایگاه داده، از دیتابیس خود بک‌آپ کامل بگیرید.
3. غیرفعال کردن (Disable) یا تغییر نام (Rename): به جای حذف فوری، ابتدا اشیاء مشکوک را غیرفعال یا تغییر نام دهید. این کار به شما امکان می‌دهد تا در صورت بروز مشکل، به راحتی به حالت قبل برگردید.

ALTER INDEX YourIndexName ON YourTableName DISABLE;

EXEC sp_rename 'YourTableName', 'YourTableName_Deprecated';

4. مانیتورینگ: پس از غیرفعال کردن یا تغییر نام، سیستم را برای مدتی مانیتور کنید تا مطمئن شوید که هیچ جزء از برنامه به آن شیء نیاز ندارد.
5. حذف (Drop): پس از یک دوره مانیتورینگ موفق، می‌توانید با اطمینان اشیاء را حذف کنید.

DROP TABLE YourTableName_Deprecated;

DROP INDEX YourIndexName ON YourTableName;

نتیجه‌گیری

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