Πώς βρίσκουμε την απόδοση των υπάρχων Indexes και ποία καινούργια Indexes μας προτείνει ο SQL Server

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
• Τι είναι η PostgreSQL και πως κάνουμε μία πλήρης εγκατάσταση - 2 Σεπτέμβριος 2024
• Πώς ρυθμίζουμε το replication του GoldenGate ώστε να διαβάζει από το Standby του Oracle Data Guard - 2 Αύγουστος 2024

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

Τι πρέπει να θυμόμαστε

• Όταν ένας πίνακας έχει clustered index σημαίνει ότι τα δεδομένα αποθηκεύονται με την σειρά που είναι το πεδίο/πεδία που έχουμε ορίσει το clustered index. Ο κάθε πίνακας μπορεί να έχει ένα μόνο clustered index.
• Όταν ένας πίνακας δεν έχει clustered index ονομάζεται heap και τα δεδομένα του αποθηκεύονται άναρχα στον δίσκο. Οπότε στα αποτελέσματα του script θα δούμε ότι ο κάθε πίνακας στο index type θα έχει σίγουρα ή clustered index ή heap και ποτέ και τα δύο.
• Οι non-clustered indexes έχουν τον ρόλο του ευρετήριου, αποτελούν μια αντιγραφή των δεδομένων στα πεδία που έχει φτιαχτεί συν των πεδίων που έχουν συμπεριληφθεί (include) σε ένα διαφορετικό object.
• Τα πεδία των indexes δεν πρέπει να υπερκαλύπτονται π.χ. αν έχουμε ένα non-clustered index στους [αριθμούς τηλεφώνων] και ένα στη [περιοχή] , [στον αριθμό τηλεφώνων] το πρώτο index δεν χρειάζεται οπότε θα πρέπει να διαγραφεί. (για λόγους συντήρησης τους και μνήμης)
• Το μέγεθος του πίνακα (είτε clustered είτε heap) και του κάθε non-clustered index μπορούμε να το μετρήσουμε και σε pages. Το κάθε page είναι 8KB, οπότε άμα το πολλαπλασιάσουμε με το πλήθος τους βρίσκουμε το μέγεθος του πίνακα / non-clustered index.
• Όσο αλλάζουν οι εγγραφές ενός πίνακα κατακερματίζονται δηλαδή χάνεται η σειρά τους αυτό ονομάζεται fragmentation. Για να μειωθεί θα πρέπει να κάνουμε το index rebuild είτε reorganize αν το ποσοστό fragmentation είναι μικρό.
• Όσο από λιγότερα pages αποτελείται είναι λογικό να έχει πιο εύκολα μεγαλύτερο fragmentation αλλά δεν επηρεάζεται τόσο η απόδοση. π.χ. Αν σε πίνακα με ένα page προστεθεί ένα δεύτερο θα έχουμε κατευθείαν 50% fragmentation.
• Κάποια dynamic views που μας παρέχουν τις πληροφορίες που χρειαζόμαστε, αδειάζουν κάθε φορα που γίνεται επανεκκίνηση το SQL Server service.

Πώς βρίσκουμε το fragmentation των indexes

Με το παρακάτω script που έχω φτιάξει βρίσκουμε γρήγορα το fragmentation του κάθε index με σειρά pages. Μπορούμε αφαιρόντας από σχόλιο το –and dbtables.[name] in (‘car_calc’) να βρούμε μόνο για συγκεκριμένο πίνακα:

select distinct 
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
clustertype.index_type_desc as 'IndexType',
clustertype.avg_fragmentation_in_percent,
clustertype.page_count,
clustertype.index_type_desc
FROM sys.indexes AS dbindexes 
INNER JOIN sys.dm_db_index_physical_stats  (DB_ID(), NULL, NULL, NULL, NULL) as clustertype ON clustertype.[object_id] = dbindexes.[object_id] AND clustertype.index_id = dbindexes.index_id
INNER JOIN sys.tables dbtables on dbtables.[object_id] = clustertype.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
WHERE 1=1
--and dbindexes.[name] is not NULL
--and clustertype.index_type_desc = 'NONCLUSTERED INDEX'
--and dbtables.[name] in ('car_calc')
ORDER BY page_count desc,dbtables.name desc

Indexes που προτείνει ο SQL Server

Ο SQL Server όταν βλέπει ότι κάποια query χρειάζονται index σε κάποιο πεδίο που χρησιμοποιήται προτείνει την δημιουργία τους. Επειδή δεν έχει πάντα δίκαιο είναι απαραίτητη η αξιολόγηση τους. Με το παρακάτω script μας εμφανίζονται όσους προτείνει:

SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

Πώς βρίσκουμε πότε φτιάχτηκε ο κάθε Index

Με το παρακάτω script βρίσκουμε πότε φτιάχτηκε ο κάθε index.

select 
    i.name as IndexName, 
    o.name as TableName, 
	co.[name] as ColumnName,
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    o.create_date
from sys.indexes i 
inner join sys.objects o on i.object_id = o.object_id and o.type_desc = 'USER_TABLE'
inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where 1=1
and i.[type] in (1,2)
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal;

Πώς βρίσκουμε πότε έγινε ο κάθε Index τελευταία φορά Rebuild

Ο SQL Server δεν αποθηκεύει την πληροφορία του πότε έγινε rebuild τελευταία φορά ο index, όμως αποθηκεύει την τελευταία φορά που ενημερώθηκαν τα στατιστικά. Στην default συμπεριφορά κάθε φορά που εκτελείται index rebuild εκτελείται και το statistics update οπότε γνωρίζοντας την ημερομηνία του δεύτερου γνωρίζουμε και του πρώτου.

Με το παρακάτω script βρίσκουμε την ημερομηνία των τελευταίων στατιστικών:

select OBJECT_NAME(object_id) [TableName], 
       name [IndexName],
       stats_date(object_id, stats_id) [LastStatsUpdate]
from sys.stats
where name not like '_WA%'
--and stats_date(object_id, stats_id) IS NOT NULL
and OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
--and OBJECT_NAME(object_id) = 'pinakas'
ORDER BY LastStatsUpdate DESC

Πώς βρίσκουμε πόσο χρησιμοποιείται ο κάθε Index

Κάποιες φορές δημιουργούμε indexes που δεν χρησιμοποιούνται, αυτό έχει σαν αποτέλεσμα να καταλαμβάνουν χώρο από την μνήμη (RAM) του SQL Server χωρίς όφελος, επίσης ο αυξημένος αριθμός indexes καθυστερεί τα inserts/deletes/updates αφού η ενημέρωση πρέπει να γίνει και σε αυτούς.

Με το παρακάτω script μπορούμε να δούμε για το κάθε index πόσες φορές προσπελάστηκε, με ποιόν τρόπο και πότε ήταν η τελευταία φορά:

select   OBJECT_NAME(S.[OBJECT_ID]) AS [object name], 
         i.[NAME] AS [index name], 
         s.USER_SEEKS, 
         s.USER_SCANS, 
         s.USER_LOOKUPS, 
         s.USER_UPDATES,
		 s.last_user_seek,
		 s.last_user_scan,
		 s.last_user_lookup,
		 s.last_user_update
from     SYS.DM_DB_INDEX_USAGE_STATS as S 
         inner join SYS.INDEXES I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
where    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Πηγές:

• sys.indexes (Transact-SQL)
• sys.dm_db_index_physical_stats (Transact-SQL)
• sys.dm_db_missing_index_details (Transact-SQL)