未使用 / 書き込み専用のインデックスを特定する

コマンド

USE [Musteri];
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.name                          AS index_name,
       ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) AS reads,
       ISNULL(us.user_updates, 0)      AS writes,
       ps.used_mb,
       us.last_user_seek, us.last_user_scan,
       N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id))
         + N'.' + QUOTENAME(OBJECT_NAME(i.object_id)) + N';' AS drop_stmt_draft
FROM   sys.indexes i
  LEFT JOIN sys.dm_db_index_usage_stats us
         ON us.database_id = DB_ID() AND us.object_id = i.object_id AND us.index_id = i.index_id
  CROSS APPLY (SELECT CAST(SUM(p.used_page_count) * 8 / 1024.0 AS DECIMAL(18,1)) AS used_mb
               FROM sys.dm_db_partition_stats p
               WHERE p.object_id = i.object_id AND p.index_id = i.index_id) ps
WHERE  OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND  i.type_desc = N'NONCLUSTERED'
  AND  i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
  AND  ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) = 0
ORDER BY writes DESC, ps.used_mb DESC;

-- Usage counters since:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

概要

再起動以降に読み取られていない (seek/scan/lookup = 0) が書き込みコストは発生している非クラスタ化インデックスを、サイズとともに一覧表示し、DROP INDEX ステートメントの下書きを生成する。

使用する場面

書き込みの多いテーブルで INSERT/UPDATE が遅い場合、ストレージとメンテナンス (再構築、CHECKDB) 時間を減らすため、またはインデックスの統合前に。

注意

カウンターは再起動、データベースのオフライン化 / デタッチ、および (一部の古い 2012/2014 ビルドでは) インデックスの再構築でリセットされる。月末 / 年末レポートでのみ使用されるインデックスは、短い期間では「未使用」に見える。削除前に少なくとも 1 回の完全な業務サイクルを観察し、AG では読み取り可能セカンダリでの使用状況を別途確認する (カウンターはレプリカごと)。一意インデックスは読み取られなくても制約を強制するため除外される。読み取り専用クエリのため安全。