Wer schon einmal den SQL Server Optimierungsratgeber verwendet hat, um sich Empfehlungen für fehlende Indizes generieren zu lassen, kennt Indizes mit Namen, die in etwa so aussehen:

_dta_index_Ticket_5_1346103836__K1_5_20
_dta_index_Ticket_5_1346103836__K1_K5_20
_dta_index_Ticket_5_1346103836__K2_K6_K14_K1_K8_K13
_dta_index_Ticket_5_1346103836__K5_K1_K2_K8_K6_3_13_14_36

Lauter Indizes mit kryptischen Namen, die einem nicht wirklich weiterhelfen, wenn man später wissen will, welchem Zweck welcher Index dient bzw. welche Spalten überhaupt indiziert sind.

Generell ist zu empfehlen, zunächst solche durch Anwendungen erzeugte Indizes kritisch zu überprüfen. Es besteht die Gefahr einer Überindizierung, was eine Datenbank nur unnötig vergrößert. Außerdem wird der Aufwand, den die Datenbank für DML Operationen auf den betroffenen Tabellen treiben muss unnötig vergrößert, da ja auch bei jeder Operation Indizes aktualisiert werden müssen.

Hat man nun überflüssige Indizes wieder entfernt, könnte man hingehen und fleißig alle übrig gebliebenen Indizes manuell umbenennen, was aber bei einem größeren System mit vielen Tabellen echte Arbeit bedeutet und nicht mal eben nebenbei zu erledigen ist. Was also tun?

Eine Möglichkeit ist, ein Skript zu schreiben, das für jeden Index einen Namen generiert und den Index dann entsprechend umbenennt. Folgendes Skript erzeugt eine Liste mit Statements zur Umbenennung:

USE [myDB]
SELECT  'EXEC sp_rename '''
        + [s].[name] + '.' + [o].[name] + '.' + [i].[name] + ''', '''
        + CASE WHEN [i].[is_unique] = 1 AND [i].[is_primary_key] = 0 THEN 'UI_'
               WHEN [i].[is_primary_key] = 1 THEN 'PK_'
               ELSE 'IX_'
          END
        + LOWER([s].[name]) + '_' + [o].[name]
        + CASE WHEN [i].[is_primary_key] = 0
               THEN '_' + LOWER([Index_Columns].[index_columns_key])
               ELSE ''
          END
        + ''', ''INDEX'''
FROM    [sys].[objects] AS [o]
JOIN    [sys].[schemas] AS [s] ON [o].[schema_id] = [s].[schema_id]
JOIN    [sys].[indexes] AS [i] ON [i].[object_id] = [o].[object_id]
CROSS APPLY (
             SELECT LEFT([index_columns_key], LEN([index_columns_key]) - 1)
                      AS [index_columns_key]
             FROM   (
                     SELECT (
                             SELECT TOP 3
                                    [c].[name] + '_'
                             FROM   [sys].[index_columns] AS [ic]
                             JOIN   [sys].[columns] AS [c] ON  [ic].column_id =
                                                                    [c].column_id
                                                AND [ic].[object_id] =
                                                                    [c].[object_id]
                             WHERE  [ic].is_included_column = 0
                                    AND [i].[object_id] = [ic].[object_id]
                                    AND  [i].index_id = [ic].index_id
                             ORDER BY key_ordinal
                            FOR
                             XML PATH('')
                            ) AS [index_columns_key]
                    ) AS [Index_Columns]
            ) AS [Index_Columns]
WHERE   [o].[type] = 'u'
        AND [o].[is_ms_shipped] = 0
        AND [i].[type_desc] <> 'HEAP'
ORDER BY [s].[name], [o].[name], [i].[name]

Als Ergebnis erhält man eine Liste von T-SQL Statements, die z.B. so aussehen:

EXEC sp_rename 'qbus.Ticket._dta_index_Ticket_5_1346103836__K1_5_20',
               'IX_qbus_Ticket_id','INDEX'
EXEC sp_rename 'qbus.Ticket._dta_index_Ticket_5_1346103836__K1_K5_20',
               'IX_qbus_Ticket_id_sapticketid','INDEX'
EXEC sp_rename 'qbus.Ticket._dta_index_Ticket_5_1346103836__K2_K6_K14_K1_K8_K13',
               'IX_qbus_Ticket_type_floc_createdate', 'INDEX'
EXEC sp_rename 'qbus.Ticket._dta_index_Ticket_5_1346103836__K5_K1_K2_K8_K6_3_13_14_36',
               'IX_qbus_Ticket_id_type_status', 'INDEX'

Der neue Indexname setzt sich dabei wie folgt zusammen:

<Prefix>_             PK: Primary Key, UI: Unique Index, IX: Nonunique Index
<Schemaname>_         in Kleinbuchstaben
<Tabellenname>_
<Indizierte Spalten>  in diesem Fall werden maximal drei Spalten in den Namen übernommen
                      (durch "_" getrennt, in Kleinbuchstaben), wenn der Index kein
                      Primärschlüssel ist.

In dem Beispiel von oben heißen die Indizes also anschließend:

IX_qbus_Ticket_id
IX_qbus_Ticket_id_sapticketid
IX_qbus_Ticket_type_floc_createdate
IX_qbus_Ticket_id_type_status

Je nach Anwendungsfall kann man das Skript anpassen und die Anzahl Spaltennamen verändern, die im Index eingeschlossenen Spalten hinzunehmen oder auch eine laufende Nummer anhängen, um die Indexnamen eindeutig macht. In jedem Fall erhält man ein einheitliches Namensschema, das auch nachträglich nochmal angepasst werden kann ohne großen Aufwand zu erzeugen.

!!!Achtung!!! Vorsichtig sollte man sein, wenn man in Queries Abfragehinweise (QUERY Hints) verwendet: Der INDEX Hinweis dient dazu, den SQL Server anzuweisen, bei der Ausführung bestimmte namentlich aufgeführte Indizes zu verwenden, was natürlich nicht mehr funktioniert, wenn die Indizes umbenannt worden sind. Und…

Es gibt (leider) Anwendungen, bei denen die Namen von Tabellen und Spalten Sonderzeichen enthalten. Solche Fälle, in denen dann ungültige Indexnamen generiert werden, werden von meinem Skript noch nicht behandelt.

Wie immer bei solchen Tipps möchte ich abschließend sagen, dass das Skript mir zwar sehr gute Dienste geleistet hat, jeder der es verwenden möchte dies aber natürlich auf eigene Gefahr tut und es noch niemandem geschadet hat ein (funktionierendes!) Backup seiner Datenbank in der Hinterhand zu haben. 😉

Category: Blog
Schlagwörter: