Jak znaleźć nieużywane indeksy?

Pracuję nad hurtownią danych. Mam tabele z rekordami do 200 milionów. Niektóre z tych tabel mają ponad 20 indeksów (nie mogę podać powodu, dla którego zostały utworzone). Powoduje to, że utrzymanie tych indeksów jest zbyt bolesne i ma bezpośredni wpływ na zadanie importu DWH zarówno pod względem wydajności, jak i czasu wykonywania.

Jak znaleźć najmniej używane indeksy w każdej tabeli? (aby się ich pozbyć)

Wypróbuj ten skrypt, pomógł mi w przeszłości:

-- Unused Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

Odkryłem, że darmowy skrypt BlitzIndex Brenta Ozara Unlimited (napisany przez Kendrę Little) jest najlepszym sposobem izolowania niepotrzebnych indeksów (a także indeksów, które warto dodać, indeksów powielających działanie innych indeksów itp.)

http://www.brentozar.com/blitzindex/

Informuje, ile razy jakikolwiek indeks został odczytany od czasu ostatniego zresetowania zliczeń statystyk (lub utworzenia / odtworzenia indeksu).

Wydaje mi się, że pamiętam Brenta Ozara, który powiedział w webcastie, że dobrą zasadą jest nie więcej niż 10 indeksów dla tabeli, która jest często odczytywana, 20 dla tabel, które są danymi statycznymi / historycznymi / archiwalnymi, które nie będą się często zmieniać.

Jeśli nadal masz problemy z szybkością importu, oznacza to, że baza danych nie jest aktywnie przeszukiwana (być może poza godzinami pracy). Korzystne może być upuszczenie indeksu, zaimportowanie danych, a następnie ponowne zastosowanie indeksów. (Statystyki zostaną oczywiście zresetowane). Powodem tego jest to, że indeks (y) będą aktualizowane w miarę wchodzenia każdego rekordu, porządkowania stron, a to zajmuje czas i operacje we / wy dysku. Budowanie indeksów po wymaga pojedynczego skanowania tabeli.

Brak twardej i szybkiej reguły może wymagać eksperymentowania z tym w zależności od rodzaju indeksu i danych. Indeksy powinny być regularnie przeglądane, gdy zmieniają się potrzeby / zapytania.

Spróbuj tego:

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
             I.[NAME] AS [INDEX NAME], 
             USER_SEEKS, 
             USER_SCANS, 
             USER_LOOKUPS, 
             USER_UPDATES 
    FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
             INNER JOIN SYS.INDEXES AS I 
               ON I.[OBJECT_ID] = S.[OBJECT_ID] 
                  AND I.INDEX_ID = S.INDEX_ID 
    WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Raj

Do zapytania Raja dodałem datę i kod ostatniego użycia.

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
             I.[NAME] AS [INDEX NAME], type_desc,
             coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) as LastUsed,
             USER_SEEKS, 
             USER_SCANS, 
             USER_LOOKUPS, 
             USER_UPDATES ,
             last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup,
             'drop index ['+I.[NAME]+'] on ['+OBJECT_NAME(S.[OBJECT_ID])+'];' as DropStatement
    FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
             INNER JOIN SYS.INDEXES AS I 
               ON I.[OBJECT_ID] = S.[OBJECT_ID] 
                  AND I.INDEX_ID = S.INDEX_ID 
    WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
    order by type_desc,coalesce(last_user_seek,last_user_scan,last_user_lookup,last_system_scan,last_system_seek,last_system_lookup) desc