Είναι γνωστό ότι οι indexes αποτελούν την κηροζίνη των queries.

Το να φτιάξω ένα index είναι εύκολο. Το επιλέξω όμως ποιο ή ποια πεδία θα γίνουν index είναι το δύσκολο καθώς θα πρέπει να ληφθούν πολλοί παράμετροι υπόψη.

Στο SQL Server από το 2005 και μετά έχω στην διάθεση μου αρκετά εργαλεία τα οποία μπορώ να χρησιμοποιήσω και τα οποία μου προτείνουν την δημιουργία indexes με σκοπό την βελτίωση του performance.

Σε κάθε όμως περίπτωση δεν θα πρέπει τυφλά να προχωρώ στην δημιουργία των προτεινόμενων αλλά θα πρέπει να αναλύσω τα δεδομένα μας ώστε να είμαι 100% πεπεισμένος ότι θα έχει αποτέλεσμα η δημιουργία ενός index και μετά να προχωρήσω στην υλοποίηση του.

Σε αυτά που έχω στην διάθεση μου με τα οποία ο SQL Server μου επικοινωνεί τις προτάσεις του για την δημιουργία indexes είναι τα DMV sys.dm_db_missing_index_*.

Συνδυαστικά αυτά μεταξύ τους δίνουν αρκετά καλή πληροφορία την οποία και μπορώ να αξιοποιήσω αρκεί πρώτα να λάβω υπόψη μερικά στοιχεία όπως ότι δεν παρέχουν προτάσεις για τον τύπο του index (clustered, nonclustered), δεν προτείνουν partitioning, στις περιπτώσεις που συμπεριλαμβάνουν πολλά πεδία δεν προτείνουν την ιδανική σειρά αυτών στον index. Όμως όλα αυτά δεν με εμποδίζουν να τα χρησιμοποιήσω και να κάνω δουλειά με αυτά καθώς όπως έχω αναφέρει πρώτα γίνεται αξιολόγηση.

Finding Missing Indexes

Προσωπικά χρησιμοποιώ το παρακάτω query με το οποίο μπορώ να βγάλω ασφαλή συμπεράσματα για τους indexes που λείπουν.

Τα αποτέλεσματα που αυτό μου δίνει είναι το improvement_pct το οποίο μου δείχνει ένα αρκετά καλό estimation για το κέρδος που θα έχω με την δημιουργία του συγκεκριμένου index. Επίσης σημαντική πληροφορία είναι τα last_user_seek & last_user_scan με τα οποία μπορώ να καταλάβω πότε έχουν γίνει τελευταία φορά οι διαδικασίες αυτές και από αυτό μπορώ να καταλάβω την σημαντικότητα του index καθώς αν οι ημερομηνίες είναι μακρινές σημαίνει ήταν ενδεχομένως ένα ad-hoc query που ίσως δεν θα επαναληφθεί στο μέλλον. Για αυτό το λόγο και λέω πάντα ότι πρέπει να έχουν γίνει πρώτα μερικές εκτελέσεις σε ένα διάστημα μερικών ημερών των οποίων τα αποτελέσματα πρέπει να έχω κρατήσει ώστε να μπορώ να κάνω την αξιολόγηση μου συγκρίνοντας αυτά.

SELECT 
        db_name(mid.database_id) AS database_name
    ,    OBJECT_NAME( mid.OBJECT_ID , mid.database_id) AS table_name
    ,    p.TableRows AS table_rows
    ,    cast ( migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) as decimal(12,2)) AS improvement_pct
    ,    migs.last_user_seek AS last_user_seek
    ,    migs.last_user_scan AS last_user_scan
    ,    'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID,mid.database_id) + '_' 
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') 
        + CASE
          WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_'
          ELSE ''
          END
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']','') + ']'
        + ' ON ' + mid.statement
        + ' (' + ISNULL (mid.equality_columns,'')
        + CASE 
          WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' 
          ELSE '' 
          END
        + ISNULL (mid.inequality_columns, '') + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_statement
FROM    sys.dm_db_missing_index_groups AS mig
JOIN    sys.dm_db_missing_index_group_stats as migs
        ON migs.group_handle = mig.index_group_handle
JOIN    sys.dm_db_missing_index_details AS mid
        ON mig.index_handle = mid.index_handle
JOIN    (
        SELECT SUM(p.rows) AS TableRows, p.OBJECT_ID
        FROM sys.partitions AS p 
        WHERE p.index_id = 0 or p.index_id = 1
        GROUP BY p.index_id,p.OBJECT_ID
        ) as p 
        ON mid.OBJECT_ID = p.OBJECT_ID
WHERE    mid.database_ID = DB_ID()
ORDER BY table_name , improvement_pct DESC;

Finding Unused Indexes

Όπως όπως είπα και στην αρχή του άρθρου αυτού οι indexes είναι η κηροζίνη των queries, αλλά αυτό το ρόλο μπορούν να παίξουν μόνοι οι σωστοί indexes, αυτοί δηλαδή που καλύπτουν τις ανάγκες των queries.

Αρκετοί δημιουργούν indexes κυρίως από άγνοια με αποτέλεσμα να επιβαρύνονται τα transactions άσκοπα με την ενημέρωση indexes που πότε δεν χρησιμοποιούνται.

Για να μπορέσω να εντοπίσω τέτοιους indexes που δεν χρησιμοποιούνται τρέχω σε τακτά χρονικά διαστήματα πριν αποφασίσω την διαγραφή τους το παρακάτω query κρατώντας φυσικά τα αποτελέσματα κάθε φορά ώστε να κάνω τις απαραίτητες συγκρίσεις.

SELECT 
        db_name(ius.database_id)    AS database_name
    ,    o.name                        AS table_name
    ,    p.TableRows                    AS table_rows
    ,    i.name                        AS index_name
    ,    i.index_id                    AS index_id
    ,    STATS_DATE(o.object_id, i.index_id) as create_date
    ,    ius.user_seeks                AS user_seek
    ,    ius.user_scans                AS user_scans
    ,    ius.user_lookups            AS user_lookups
    ,    ius.user_updates            AS user_updates
    ,    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(ius.OBJECT_ID)) AS drop_statement
FROM    sys.dm_db_index_usage_stats AS ius
JOIN    sys.indexes AS i 
        ON i.index_id = ius.index_id AND ius.OBJECT_ID = i.OBJECT_ID
JOIN    sys.objects AS o 
        ON ius.OBJECT_ID = o.OBJECT_ID
JOIN    sys.schemas AS s 
        ON o.schema_id = s.schema_id
JOIN    (
            SELECT SUM(p.rows) AS TableRows, p.index_id, p.OBJECT_ID
            FROM sys.partitions AS p 
            GROUP BY p.index_id, p.OBJECT_ID
        ) as p 
        ON p.index_id = ius.index_id AND ius.OBJECT_ID = p.OBJECT_ID
WHERE 
        OBJECTPROPERTY(ius.OBJECT_ID,'IsUserTable') = 1 
        AND 
        ius.database_id = DB_ID()
        AND 
        i.type_desc = 'nonclustered'
        AND 
        i.is_primary_key = 0
        AND 
        i.is_unique_constraint = 0
        AND 
        ( ius.user_lookups = 0 AND ius.user_scans = 0 AND ius.user_seeks = 0 )
ORDER BY create_date, ius.user_updates DESC;

Από αυτό το query βλέπω τα seek, scans, lookups (σημείωση έχω βάλει στο where να παίρνω μόνο τα μηδενικά) , την ημερομηνία δημιουργίας του index και πόσα updates έχουν γίνει πάνω σε αυτό.

Από τις συγκρίσεις που κάνω στα αποτελέσματα ένας index που έχει δημιουργηθεί πριν πχ 6 μήνες και συνεχίζει να έχει μηδενικά seek, scans, lookups είναι υποψήφιος για διαγραφή.

ΠΡΟΣΟΧΗ ΠΡΟΣΟΧΗ ΠΡΟΣΟΧΗ

Tα DMVs αυτά κρατάνε πληροφορίες από την στιγμή που έχει ξεκινήσει το SQL Server service. Αν κάποιος έχει κάνει πρόσφατα restart αυτό θα πρέπει να περιμένει ένα εύλογο χρονικό διάστημα μέσα στο οποίο έχει εκτελεστεί όλη η ποικιλία των ερωτημάτων που γίνονται στην database ώστε να μπορεί να βγάλει ασφαλή συμπεράσματα.

//antonch