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

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

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

چگونه جداول بلااستفاده را شناسایی کنیم؟

استفاده از نماهای سیستمی (sys.dm_db_index_usage_stats)

SQL Server نماهای سیستمی متعددی را ارائه می‌دهد که می‌توانند به شناسایی جداول بلااستفاده کمک کنند. یکی از مفیدترین آن‌ها sys.dm_db_index_usage_stats است. این نمای مدیریت پویا (DMV) اطلاعاتی در مورد نحوه استفاده (یا عدم استفاده) از ایندکس‌ها در یک پایگاه داده فراهم می‌کند.

این نما شامل اطلاعاتی برای تمامی ایندکس‌های یک پایگاه داده خاص (به استثنای جداول بهینه‌سازی شده برای حافظه) از زمان آخرین راه‌اندازی مجدد نمونه SQL Server است. در اینجا نحوه کوئری گرفتن از آن برای یافتن جداول بدون استفاده از ایندکس آورده شده است:

SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM
    sys.indexes AS i
LEFT OUTER JOIN
    sys.dm_db_index_usage_stats AS ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE
    OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND ius.user_seeks IS NULL
    AND ius.user_scans IS NULL
    AND ius.user_lookups IS NULL
    AND ius.user_updates IS NULL
    AND i.index_id > 0 -- Exclude heaps (tables without a clustered index)
ORDER BY
    TableName;

این کوئری جداولی را شناسایی می‌کند که هیچ استفاده‌ای از ایندکس (جستجوها، اسکن‌ها، یا نگاه کردن‌های کاربر) و هیچ به‌روزرسانی توسط کاربر نداشته‌اند. نمای sys.indexes اطلاعاتی در مورد تمامی ایندکس‌ها ارائه می‌دهد و ما آن را با sys.dm_db_index_usage_stats جوین می‌کنیم. جداول بدون ایندکس کلاستر (هَپ‌ها) با شرط i.index_id > 0 حذف می‌شوند. اگر جدولی ایندکس کلاستر نداشته باشد، به آن هَپ (heap) گفته می‌شود. ممکن است بخواهید این جداول را نیز در نظر بگیرید؛ در این صورت، خط i.index_id > 0 را حذف کنید.

ملاحظات مهم برای sys.dm_db_index_usage_stats:

۱. داده‌های ناپایدار: داده‌های موجود در sys.dm_db_index_usage_stats پس از راه‌اندازی مجدد سرویس SQL Server پاک می‌شوند. این بدان معناست که شما تنها آمار استفاده را از زمان آخرین راه‌اندازی مجدد دریافت می‌کنید.

۲. پایگاه داده آفلاین/فقط خواندنی: اگر یک پایگاه داده برای مدتی آفلاین یا در حالت فقط خواندنی بوده باشد، آمار استفاده آن ممکن است دقیق نباشد یا در دسترس نباشد.

۳. نگهداری ایندکس: بازسازی ایندکس‌ها می‌تواند برخی از آمار استفاده را برای ایندکس‌های تحت تأثیر ریست کند.

۴. پایگاه‌های داده/جداول جدید: پایگاه‌های داده یا جداول تازه ایجاد شده به طور طبیعی در ابتدا آمار استفاده کم یا صفری خواهند داشت.

استفاده از Extended Events

Extended Events یک سیستم نظارتی سبک‌وزن و قدرتمند در SQL Server است که به شما امکان می‌دهد داده‌های دقیقی در مورد فعالیت پایگاه داده جمع‌آوری کنید. شما می‌توانید Extended Events را برای ثبت رویدادهای خاص مربوط به دسترسی به جداول، مانند عملیات SELECT، INSERT، UPDATE و DELETE پیکربندی کنید.

در اینجا یک مثال پایه از نحوه ایجاد یک سشن Extended Event برای نظارت بر استفاده از جداول آورده شده است:

CREATE EVENT SESSION [MonitorTableUsage]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (SET collect_statement=(1)
     ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name)
     WHERE ([sqlserver].[database_name]=N'YourDatabaseName' AND [sqlserver].[object_name] IS NOT NULL)),
    ADD EVENT sqlserver.sql_statement_completed
    (SET collect_statement=(1)
     ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name)
     WHERE ([sqlserver].[database_name]=N'YourDatabaseName' AND [sqlserver].[object_name] IS NOT NULL))
    ADD TARGET package0.asynchronous_file_target
    (SET filename=N'C:\temp\MonitorTableUsage.xel', max_file_size=(5), max_rollover_files=(4));
GO

ALTER EVENT SESSION [MonitorTableUsage]
ON SERVER STATE = START;
GO

به یاد داشته باشید که 'YourDatabaseName' را با نام واقعی پایگاه داده خود جایگزین کرده و مسیر فایل (C:\temp\MonitorTableUsage.xel) را در صورت نیاز تنظیم کنید. پس از جمع‌آوری داده‌ها برای یک دوره کافی، می‌توانید فایل‌های رویداد را کوئری بگیرید تا استفاده از جداول را تجزیه و تحلیل کنید.

ملاحظات مهم برای Extended Events:

۱. سربار (Overhead): با وجود سبک بودن، ثبت رویدادهای گسترده همچنان می‌تواند مقداری سربار ایجاد کند. عملکرد را به دقت نظارت کنید.

۲. ذخیره‌سازی داده: داده‌های رویداد می‌توانند به سرعت رشد کنند، بنابراین اندازه‌های فایل و سیاست‌های rollover را با دقت مدیریت کنید.

۳. تجزیه داده‌ها: تجزیه و تحلیل داده‌های خام Extended Event می‌تواند پیچیده باشد و نیاز به درک شمای رویداد دارد.

۴. پیکربندی: تنظیم فیلترهای خاص برای جداول، به ویژه برای تعداد زیادی از جداول، می‌تواند پیچیده باشد.

استفاده از SQL Server Audit

SQL Server Audit به شما امکان می‌دهد رویدادهای سطح سرور و سطح پایگاه داده، از جمله دسترسی به جداول را ممیزی کنید. این یک راه حل قوی‌تر برای امنیت و انطباق است، اما می‌توان از آن برای ردیابی استفاده از جداول نیز بهره برد. برای این کار، باید یک شیء ممیزی (audit object)، یک مشخصه ممیزی سرور (server audit specification) و یک مشخصه ممیزی پایگاه داده (database audit specification) ایجاد کنید.

در اینجا یک مثال ساده آورده شده است:

-- Create a Server Audit
CREATE SERVER AUDIT [TableAccessAudit]
TO FILE
( FILEPATH = N'C:\temp\'
  ,MAXSIZE = 5 MB
  ,MAX_ROLLOVER_FILES = 4
  ,RESERVE_DISK_SPACE = OFF)
WITH
( QUEUE_DELAY = 1000
  ,ON_FAILURE = CONTINUE
  ,AUDIT_GUID = 'D5A8E1C2-8B0C-4E1B-9A7F-6E0C0A4D5B1F'); -- Replace with a unique GUID
GO

ALTER SERVER AUDIT [TableAccessAudit]
WITH (STATE = ON);
GO

-- Create a Database Audit Specification
USE [YourDatabaseName]; -- Replace with your database name
GO

CREATE DATABASE AUDIT SPECIFICATION [TableUsageAuditSpec]
FOR SERVER AUDIT [TableAccessAudit]
ADD (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON);
GO

-- You might need to add specific audit actions for tables, e.g.,
-- ADD (SELECT ON OBJECT::[dbo].[YourTableName] BY [public])
-- ADD (INSERT ON OBJECT::[dbo].[YourTableName] BY [public])
-- ADD (UPDATE ON OBJECT::[dbo].[YourTableName] BY [public])
-- ADD (DELETE ON OBJECT::[dbo].[YourTableName] BY [public])

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

ملاحظات مهم برای SQL Server Audit:

۱. سربار (Overhead): ممیزی می‌تواند سربار قابل توجهی ایجاد کند، به ویژه برای محیط‌هایی با تراکنش بالا. رویدادهای قابل ممیزی را با دقت انتخاب کنید.

۲. فضای ذخیره‌سازی: لاگ‌های ممیزی می‌توانند فضای دیسک زیادی را اشغال کنند. برای ذخیره‌سازی و آرشیو برنامه‌ریزی کنید.

۳. پیچیدگی: راه‌اندازی و مدیریت ممیزی‌ها می‌تواند پیچیده‌تر از سایر روش‌ها باشد.

۴. دقت (Granularity): دستیابی به ممیزی با دقت بسیار بالا برای هر جدول و هر نوع عملیات ممکن است به داده‌ها و سربار بیش از حد منجر شود.

استفاده از ابزارهای شخص ثالث

چندین ابزار شخص ثالث قابلیت‌های نظارت و گزارش‌دهی پیشرفته‌ای را برای SQL Server ارائه می‌دهند. این ابزارها اغلب رابط‌های کاربرپسندی برای ردیابی استفاده از جداول، شناسایی گلوگاه‌های عملکرد و تولید گزارش‌های جامع فراهم می‌کنند. نمونه‌هایی از این ابزارها عبارتند از Redgate SQL Monitor، ApexSQL Monitor و SolarWinds Database Performance Analyzer.

ملاحظات مهم برای ابزارهای شخص ثالث:

۱. هزینه: این ابزارها معمولاً دارای هزینه‌های لایسنس هستند.

۲. یکپارچه‌سازی: اطمینان حاصل کنید که ابزار به خوبی با محیط موجود شما یکپارچه می‌شود.

۳. ویژگی‌ها: ویژگی‌ها را ارزیابی کنید تا مطمئن شوید که نیازهای خاص شما برای شناسایی جداول بلااستفاده را برآورده می‌کنند.

۴. منحنی یادگیری: ممکن است استفاده از ابزارهای پیچیده شخص ثالث به یک منحنی یادگیری نیاز داشته باشد.

نتیجه‌گیری

شناسایی و حذف جداول بلااستفاده یک گام حیاتی در حفظ یک پایگاه داده SQL Server یا Azure SQL سالم و کارآمد است. در حالی که sys.dm_db_index_usage_stats یک نمای کلی سریع ارائه می‌دهد، Extended Events و SQL Server Audit ردیابی جامع‌تر و پایدارتری را فراهم می‌کنند، البته با پیچیدگی و سربار بالاتر. ابزارهای شخص ثالث می‌توانند این فرآیند را با ویژگی‌های پیشرفته و رابط‌های کاربرپسند ساده‌تر کنند. با بازبینی منظم پایگاه داده خود برای یافتن جداول بلااستفاده، می‌توانید فضای ذخیره‌سازی را آزاد کرده، زمان بک‌آپ‌گیری را بهبود بخشیده و عملکرد کلی پایگاه داده را افزایش دهید که به یک محیط پایگاه داده بهینه‌تر و مقرون‌به‌صرفه‌تر کمک می‌کند.