Vous êtes ici :
Ce script fournit la liste des index manquants retournée par le moteur SQL, pour toute l’instance ou pour la base courante (modifier la ligne en remarque).
Il fournit aussi la suggestion de commande de création d’index (mais cela reste une suggestion à évaluer avec toutes les précautions nécessaires…)
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
/*------------------------------------------------------------------- [SCRIPT] Index manquants [DATABASE] Base courante ou toutes les databases (voir clause WHERE) [DESCRIPTION] Liste des index manquants [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT DB_NAME(mid.database_id) AS DBName , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , migs.last_user_seek , migs.last_user_scan , mid.[statement] AS [Database.Schema.Table] , mid.equality_columns , mid.inequality_columns , mid.included_columns -- nombre de colonnes index et include , COALESCE( ( LEN(mid.equality_columns) - LEN(REPLACE(mid.equality_columns, ',', '')) + 1 ) , 0) + COALESCE( ( LEN(mid.inequality_columns) - LEN(REPLACE(mid.inequality_columns, ',', '')) + 1 ) , 0) AS NbIndexCols , COALESCE( ( LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1 ) , 0) AS NbIncludedCol -- statistiques , migs.unique_compiles , migs.user_seeks , migs.user_scans , migs.avg_total_user_cost , migs.avg_user_impact -- commande de création d'index basée sur equality, inequality, include -- pour information et analyse seulement ! , 'CREATE INDEX [ix_' + LEFT(REPLACE( REPLACE( SUBSTRING( mid.statement , CHARINDEX( '.' , mid.statement , CHARINDEX('.', mid.statement) + 1) + 1 , LEN(mid.statement)) , '[' , '') , ']' , '') + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + '_' + REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '_'), 120) + '] ON ' + SUBSTRING( mid.statement , CHARINDEX('.', mid.statement) + 1 , CHARINDEX( '.' , SUBSTRING( mid.statement , CHARINDEX('.', mid.statement) + 1 , LEN(mid.statement))) - 1) + '.' + SUBSTRING( mid.statement , CHARINDEX('.', mid.statement, CHARINDEX('.', mid.statement) + 1) + 1 , LEN(mid.statement)) + ' (' + COALESCE(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + COALESCE(mid.inequality_columns, '') + ')' + COALESCE(' INCLUDE (' + mid.included_columns + ')', '') --+' WITH (ONLINE=ON)' AS CreateCmd FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle --WHERE mid.database_id = DB_ID() -- activer pour filtrer sur la base courante ORDER BY DBName , index_advantage DESC OPTION ( RECOMPILE ); |
Table of Contents