ایندکس گمشده: چرا پیشنهادهای SQL Server را کورکورانه اجرا نکنید

SQL Server هر بار که کوئری‌ای را بهینه می‌کند و به این نتیجه می‌رسد که «اگر ایندکسی روی این ستون‌ها بود، سریع‌تر می‌شد»، آن آرزو را در یک DMV ثبت می‌کند. این فهرست طلاست — و اگر بی فکر اجرا شود، سم.

دیدن فهرست

SELECT TOP (20)
       DB_NAME(d.database_id)                       AS db,
       OBJECT_NAME(d.object_id, d.database_id)      AS table_name,
       s.user_seeks + s.user_scans                  AS uses,
       s.avg_user_impact                            AS impact_pct,
       s.avg_total_user_cost                        AS avg_cost,
       s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) AS score,
       d.equality_columns, d.inequality_columns, d.included_columns
FROM   sys.dm_db_missing_index_group_stats s
JOIN   sys.dm_db_missing_index_groups g  ON g.index_group_handle = s.group_handle
JOIN   sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
WHERE  d.database_id = DB_ID()
ORDER  BY score DESC;

ستون score ترکیبی است از هزینهٔ کوئری، درصد بهبود تخمینی و تعداد دفعاتی که لازم شده. مرتب‌سازی بر اساس آن، منطقی‌تر از مرتب‌سازی بر اساس avg_user_impact تنهاست — چون بهبود ۹۹٪ روی کوئری‌ای که ماهی یک بار اجرا می‌شود، ارزش یک ایندکس تازه را ندارد.

چهار دلیل که نباید کورکورانه اجرا کنید

۱. هر ایندکس، نوشتن را کند می‌کند

ایندکس ساختار جداگانه‌ای است که باید در هر INSERT، UPDATE و DELETE به‌روز شود. جدولی با دوازده ایندکس یعنی هر درج، سیزده نوشتن. روی جدولی که بار نوشتنش سنگین است، افزودن ایندکس می‌تواند سیستم را در مجموع کندتر کند — و این کندی در فهرست ایندکس‌های گمشده دیده نمی‌شود، چون آن فهرست فقط خواندن را می‌بیند.

۲. پیشنهادها هم‌پوشان‌اند

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

۳. ترتیب ستون‌ها را خودتان باید تصمیم بگیرید

DMV ستون‌ها را در دو دستهٔ «تساوی» و «نامساوی» می‌دهد و ترتیب داخل هر دسته دلخواه است. ولی ترتیب ستون‌های کلید ایندکس، تعیین‌کننده است: ستون‌های شرط تساوی اول، بعد ستون نامساوی. ستونی که انتخاب‌پذیری بیشتری دارد (مقادیر متنوع‌تر) معمولاً باید جلوتر باشد.

۴. آمار از ری‌استارت پاک می‌شود

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

روش درست

1. فهرست را با معیار ترکیبی بگیرید و فقط چند مورد بالا را بردارید.
2. ایندکس‌های موجود همان جدول را ببینید. شاید ایندکسی هست که با یک ستون INCLUDE اضافه، نیاز را پوشش می‌دهد — بهتر از ساختن ایندکس تازه.

   SELECT i.name, i.type_desc, i.is_unique,
          STUFF((SELECT ', ' + c.name
                 FROM sys.index_columns ic
                 JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.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 key_cols,
          s.user_seeks, s.user_scans, s.user_updates
   FROM   sys.indexes i
   LEFT   JOIN sys.dm_db_index_usage_stats s
          ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID()
   WHERE  i.object_id = OBJECT_ID(N'dbo.Orders');

3. یک ایندکس بسازید که چند پیشنهاد هم‌خانواده را پوشش دهد.
4. در محیط آزمایشی با بار واقعی بسنجید، نه با یک اجرای تکی.
5. بعد از چند روز، user_seeks آن ایندکس را ببینید. اگر صفر است، اشتباه ساخته‌اید؛ حذفش کنید.

طرف دیگر سکه: ایندکس‌های بی‌استفاده

کم‌تر کسی این را نگاه می‌کند، در حالی که هزینهٔ خالص است: ایندکسی که خوانده نمی‌شود ولی در هر نوشتن به‌روز می‌شود.

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM   sys.indexes i
JOIN   sys.dm_db_index_usage_stats s
       ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE  s.database_id = DB_ID()
  AND  i.type_desc = N'NONCLUSTERED'
  AND  i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND  s.user_seeks + s.user_scans + s.user_lookups = 0
  AND  s.user_updates > 1000
ORDER  BY s.user_updates DESC;

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

خلاصه

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

در DBMug این موضوع عمداً به‌شکل «ایراد» ارائه می‌شود نه «دستور»: پیشنهاد با شاهدش، تخمین بهبود، هزینهٔ نوشتن، و اسکریپت آماده — ولی تصمیم و اجرا با تیم خودتان. ابزاری که خودش ایندکس می‌سازد، همان تله را خودکار کرده است.