بهینه‌سازی ایندکس‌های ناکارآمد(Unused Index) SQL Server افزایش عملکرد

شناسایی و بهینه‌سازی Unused Index در SQL Server: راهنمایی برای افزایش عملکرد

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

چگونه Unused Index را شناسایی کنیم؟

شناسایی Unused Index شامل یک رویکرد سیستماتیک است که از Dynamic Management Views (DMVs) و ابزارهای مانیتورینگ عملکرد SQL Server بهره می‌برد. ما بر روی DMVs تمرکز خواهیم کرد که اطلاعات ارزشمندی در مورد استفاده از ایندکس، تکه‌تکه شدن (fragmentation) و مشکلات احتمالی ارائه می‌دهند.

شناسایی Unused Index با استفاده از DMVs

SQL Server مجموعه‌ای غنی از DMVs را ارائه می‌دهد که می‌تواند به ما در تحلیل عملکرد ایندکس کمک کند. ما به طور خاص به DMVs نگاهی خواهیم انداخت که داده‌هایی در مورد استفاده از ایندکس و Missing Index (missing indexes) ارائه می‌دهند.

DMVهای مربوط به استفاده (Usage DMVs)

این DMVها داده‌هایی در مورد تعداد دفعات استفاده از ایندکس‌ها برای خواندن و نوشتن، و همچنین تعداد جستجوها (seeks)، اسکن‌ها (scans) و lookupها ارائه می‌دهند. حیاتی‌ترین DMV برای این منظور sys.dm_db_index_usage_stats است.

کوئری زیر از sys.dm_db_index_usage_stats برای شناسایی ایندکس‌هایی استفاده می‌کند که تعداد نوشتن‌های بالایی دارند اما خواندن‌های کمی دارند، که نشان می‌دهد ممکن است برای عملکرد مضر باشند.

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_updates AS Writes,
    s.user_seeks + s.user_scans + s.user_lookups AS Reads,
    (s.user_updates * 1.0 / (s.user_seeks + s.user_scans + s.user_lookups + s.user_updates)) * 100 AS WriteToReadRatio
FROM
    sys.dm_db_index_usage_stats s
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 (s.user_seeks + s.user_scans + s.user_lookups) = 0 -- Indexes with no reads
    AND s.user_updates > 0 -- But have writes
ORDER BY
    s.user_updates DESC;

DMVهای مربوط به Missing Index (Missing Index DMVs)

SQL Server همچنین DMVهایی را برای پیشنهاد Missing Index ارائه می‌دهد که می‌توانند عملکرد کوئری را بهبود بخشند. گرچه این‌ها مستقیماً ایندکس‌های “ناکارآمد” را شناسایی نمی‌کنند، اما درک Missing Index به بهینه‌سازی استراتژی کلی ایندکس‌گذاری کمک می‌کند. DMVهای کلیدی در اینجا sys.dm_db_missing_index_details، sys.dm_db_missing_index_groups و sys.dm_db_missing_index_group_stats هستند.

این کوئری به شناسایی Missing Index احتمالی کمک می‌کند که در صورت ایجاد، می‌توانند عملکرد را به طور قابل توجهی افزایش دهند.

SELECT TOP 10
    d.statement AS TableName,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.unique_compiles,
    s.user_seeks,
    s.user_scans,
    s.last_user_seek,
    s.avg_total_user_cost * s.avg_user_impact AS EstimatedImpact
FROM
    sys.dm_db_missing_index_groups g
JOIN
    sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
JOIN
    sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
WHERE
    d.database_id = DB_ID()
ORDER BY
    EstimatedImpact DESC;

Unused Index در مقابل Missing Index

تمایز قائل شدن بین “Unused Index” و “Missing Index” بسیار مهم است.

• Unused Index: ایندکس‌هایی هستند که وجود دارند اما یا به ندرت استفاده می‌شوند، یا سربار قابل توجهی ایجاد می‌کنند (مانند نوشتن‌های زیاد، خواندن‌های کم)، یا تکراری هستند، یا بد طراحی شده‌اند. آن‌ها بدون ارائه مزایای زیاد، منابع را مصرف می‌کنند، یا بدتر از آن، عملکرد را کاهش می‌دهند.
• Missing Index: ایندکس‌هایی هستند که وجود ندارند اما توسط بهینه‌ساز کوئری به عنوان مواردی که می‌توانند برای بهبود عملکرد کوئری‌های خاص مفید باشند، پیشنهاد می‌شوند.

تمرکز اصلی ما در این مقاله بر شناسایی و اصلاح Unused Index است.

چگونه Unused Index را اصلاح کنیم؟

هنگامی که Unused Index شناسایی شدند، گام بعدی تدوین استراتژی برای اصلاح آن‌هاست. این معمولاً شامل ترکیبی از حذف، اصلاح یا ادغام ایندکس‌ها می‌شود.

حذف ایندکس‌های تکراری

ایندکس‌های تکراری یک مشکل رایج هستند که در آن چندین ایندکس بر روی ستون‌های یکسان، با ترتیب یکسان و اغلب با ستون‌های شامل شده (included columns) یکسان وجود دارند. آن‌ها فضای دیسک را مصرف کرده و سربار عملیات تغییر داده (درج، به‌روزرسانی، حذف) را افزایش می‌دهند، بدون اینکه مزایای عملکردی اضافی برای کوئری ارائه دهند.

این کوئری به شما کمک می‌کند تا ایندکس‌های تکراری را در پایگاه داده خود شناسایی کنید.

WITH IndexInfo AS (
    SELECT
        OBJECT_NAME(i.object_id) AS TableName,
        i.name AS IndexName,
        i.index_id,
        STUFF((
            SELECT ', ' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH('')
        ), 1, 2, '') AS KeyColumns,
        STUFF((
            SELECT ', ' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
            ORDER BY c.name
            FOR XML PATH('')
        ), 1, 2, '') AS IncludedColumns
    FROM
        sys.indexes i
    WHERE
        OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
)
SELECT
    a.TableName,
    a.IndexName AS DuplicateIndex1,
    b.IndexName AS DuplicateIndex2,
    a.KeyColumns,
    a.IncludedColumns
FROM
    IndexInfo a
JOIN
    IndexInfo b ON a.TableName = b.TableName
                AND a.KeyColumns = b.KeyColumns
                AND ISNULL(a.IncludedColumns, '') = ISNULL(b.IncludedColumns, '')
                AND a.index_id < b.index_id
ORDER BY
    a.TableName, a.KeyColumns;

اقدام: ایندکس تکراری کم‌کاربردتر یا کم‌فایده‌تر را شناسایی کرده و آن را حذف کنید. همیشه یک ایندکس که هدف مورد نظر را برآورده می‌کند، نگه دارید.

اصلاح ایندکس‌های همپوشان (Overlapping Indexes)

ایندکس‌های همپوشان زمانی رخ می‌دهند که یک ایندکس زیرمجموعه‌ای از ستون‌های ایندکس دیگر را پوشش دهد. به عنوان مثال، یک ایندکس بر روی (A, B, C) با یک ایندکس بر روی (A, B) همپوشانی دارد. اگرچه آن‌ها به طور دقیق تکراری نیستند، اما همچنان می‌توانند منجر به ناکارآمدی شوند.

کوئری زیر برای یافتن ایندکس‌های همپوشان در پایگاه داده شما طراحی شده است.

SELECT
    OBJECT_NAME(i1.object_id) AS TableName,
    i1.name AS Index1Name,
    i2.name AS Index2Name,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i1.object_id AND ic.index_id = i1.index_id AND ic.is_included_column = 0
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 2, '') AS Index1KeyColumns,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i2.object_id AND ic.index_id = i2.index_id AND ic.is_included_column = 0
        ORDER BY c.name
        FOR XML PATH('')
    ), 1, 2, '') AS Index2KeyColumns
FROM
    sys.indexes i1
JOIN
    sys.indexes i2 ON i1.object_id = i2.object_id AND i1.index_id < i2.index_id
WHERE
    OBJECTPROPERTY(i1.object_id,'IsUserTable') = 1
    AND (
        -- Check if i1's key columns are a prefix of i2's key columns, or vice versa
        EXISTS (
            SELECT 1
            FROM sys.index_columns ic1
            JOIN sys.index_columns ic2 ON ic1.object_id = ic2.object_id
                                       AND ic1.column_id = ic2.column_id
                                       AND ic1.key_ordinal = ic2.key_ordinal
            WHERE ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id AND ic1.is_included_column = 0
              AND ic2.object_id = i2.object_id AND ic2.index_id = i2.index_id AND ic2.is_included_column = 0
            GROUP BY ic1.object_id, ic1.index_id, ic2.index_id
            HAVING COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns WHERE object_id = i1.object_id AND index_id = i1.index_id AND is_included_column = 0)
               AND COUNT(*) < (SELECT COUNT(*) FROM sys.index_columns WHERE object_id = i2.object_id AND index_id = i2.index_id AND is_included_column = 0)
        )
        OR
        EXISTS (
            SELECT 1
            FROM sys.index_columns ic1
            JOIN sys.index_columns ic2 ON ic1.object_id = ic2.object_id
                                       AND ic1.column_id = ic2.column_id
                                       AND ic1.key_ordinal = ic2.key_ordinal
            WHERE ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id AND ic1.is_included_column = 0
              AND ic2.object_id = i2.object_id AND ic2.index_id = i2.index_id AND ic2.is_included_column = 0
            GROUP BY ic1.object_id, ic1.index_id, ic2.index_id
            HAVING COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns WHERE object_id = i2.object_id AND index_id = i2.index_id AND is_included_column = 0)
               AND COUNT(*) < (SELECT COUNT(*) FROM sys.index_columns WHERE object_id = i1.object_id AND index_id = i1.index_id AND is_included_column = 0)
        )
    )
ORDER BY
    TableName, Index1Name, Index2Name;

اقدام: ادغام ایندکس‌های همپوشان را در نظر بگیرید. غالباً، ایندکس وسیع‌تر (مثلاً بر روی A, B, C) می‌تواند کوئری‌هایی را که از ایندکس محدودتر (بر روی A, B) استفاده می‌کردند، پوشش دهد. اطمینان حاصل کنید که این ادغام تأثیر منفی بر کوئری‌های حیاتی نداشته باشد.

اصلاح ایندکس‌هایی با استفاده کم

ایندکس‌هایی که به ندرت استفاده می‌شوند (تعداد خواندن‌های کم) همچنان برای هر عملیات تغییر داده سربار ایجاد می‌کنند. آن‌ها فضای دیسک و حافظه را مصرف می‌کنند بدون اینکه کمک زیادی به عملکرد کوئری داشته باشند.

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

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS Reads,
    s.user_updates AS Writes
FROM
    sys.dm_db_index_usage_stats s
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 (s.user_seeks + s.user_scans + s.user_lookups) = 0 -- No reads
    AND s.user_updates = 0 -- No writes either (totally unused since server restart)
ORDER BY
    TableName, IndexName;

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

اصلاح ایندکس‌هایی با نوشتن‌های زیاد و خواندن‌های کم

این‌ها ایندکس‌های “ناکارآمد” کلاسیک هستند که در طول تغییرات داده، منابع را مصرف می‌کنند اما سود کمی برای بازیابی کوئری ارائه می‌دهند. سربار نگهداری چنین ایندکسی اغلب بیشتر از مزایای بهینه‌سازی کوئری آن است.

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

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_updates AS Writes,
    s.user_seeks + s.user_scans + s.user_lookups AS Reads,
    (s.user_updates * 1.0 / (s.user_seeks + s.user_scans + s.user_lookups + s.user_updates)) * 100 AS WriteToReadRatio
FROM
    sys.dm_db_index_usage_stats s
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 (s.user_seeks + s.user_scans + s.user_lookups) = 0 -- Indexes with no reads
    AND s.user_updates > 0 -- But have writes
ORDER BY
    s.user_updates DESC;

اقدام: این ایندکس‌ها کاندیدای اصلی برای حذف هستند. اگر برای گزارش‌های خاص و کم‌تکرار استفاده می‌شوند، ایجاد یک ایندکس پوششی (covering index با ستون‌های شامل شده) یا یک ایندکس فیلتر شده (filtered index) را برای کاهش سربار آن‌ها در نظر بگیرید. در بسیاری از موارد، حذف کامل بهترین گزینه است.

نتیجه‌گیری

شناسایی و اصلاح Unused Index یک فرآیند مداوم و حیاتی برای حفظ عملکرد بهینه SQL Server است. با تحلیل منظم داده‌های DMV برای استفاده از ایندکس، Missing Index، ایندکس‌های تکراری و همپوشانی‌ها، مدیران پایگاه‌داده می‌توانند به طور فعال استراتژی ایندکس‌گذاری خود را بهینه‌سازی کنند. به خاطر داشته باشید، یک استراتژی ایندکس‌گذاری کارآمد نه تنها سرعت کوئری‌ها را افزایش می‌دهد، بلکه سربار عملیات تغییر داده را نیز کاهش می‌دهد و منجر به یک سیستم پایگاه‌داده پاسخگوتر و قدرتمندتر می‌شود. نظارت و تنظیم دقیق و منظم، کلید دستیابی به پتانسیل کامل SQL Server شماست.