Ma base de données est-elle bien indexée ?

Lorsque l'on crée une table avec un clé primaire (et toute table devrait en avoir une !), un index est automatiquement créé. En effet, les contraintes de clé primaire et d'unicité s'appuient toujours sur un index unique pour leur fonctionnement...
Mais on voit trop souvent des bases de données où les seuls index présents sont des clés primaires (malheur aux mauvais concepteurs !).
Faites donc quelques petits tests sur vos bases de données pour avoir une petite idée des compétences des développeurs en matière d'index : voici quelques requêtes utiles.

Quid des clés étrangères ?

Les clés étrangères sont bien souvent de bonnes candidates à des index qui optimiseront les jointures (mais pas toujours, c'est pour cela que SQL Server ne crée pas automatiquement des index sur les clés étrangères). La requête suivante nous fournit la liste des clés étrangères pour lesquel il n'y a pas d'index existant sur les mêmes colonnes :
SELECT 
OBJECT_NAME(fk.parent_object_id) AS ConstraintTableName
, OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName
, fk.name AS ConstraintName
, ( SELECT SUM(rows)
FROM sys.indexes ixx
JOIN sys.partitions px ON ixx.object_id = px.object_id
AND ixx.index_id = px.index_id
WHERE ixx.object_id = fk.parent_object_id
AND ixx.index_id < 2
) AS TableRowCount
, STUFF(( SELECT ', ' + colname AS [text()]
FROM ( SELECT COL_NAME(parent_object_id, parent_column_id) AS colname
FROM sys.foreign_key_columns
WHERE constraint_object_id = fk.object_id
) x
FOR
XML PATH('')
), 1, 1, '') AS ColumnsList
, 'CREATE NONCLUSTERED INDEX nci$' + fk.name + ' ON [' + SCHEMA_NAME(fk.schema_id) + '].['
+ OBJECT_NAME(fk.parent_object_id) + '] ('
+ STUFF(( SELECT ', ' + colname AS [text()]
FROM ( SELECT COL_NAME(parent_object_id, parent_column_id) AS colname
FROM sys.foreign_key_columns
WHERE constraint_object_id = fk.object_id
) x
FOR
XML PATH('')
), 1, 1, '') + ')' AS CreateIndexCmd
FROM sys.foreign_keys fk
WHERE EXISTS ( SELECT *
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
AND NOT EXISTS ( SELECT *
FROM sys.index_columns ic
WHERE ic.object_id = fkc.parent_object_id
AND ic.column_id = fkc.parent_column_id
-- AND ic.key_ordinal = fkc.constraint_column_id -- facultatif : teste si colonnes dans le même ordre
) )
ORDER BY TableRowCount DESC
, OBJECT_NAME(fk.parent_object_id);

Quel est le nombre et le type d'index dans ma base ?

La requête suivante nous montrera en un seul coup d'oeil quelle est l'état de l'indexation des tables de ma base de données : mes clés primaires sont-elles cluster ou non cluster ? ai-je d'autres index ? ai-je des tables sans index ?


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT
PK
, COUNT(*) OVER ( PARTITION BY PK ) AS [Nb Tables]
, SUM(CASE WHEN NbNonClustIndexes = 0
AND HasClustIndex = 0 THEN 1
ELSE 0
END) OVER ( PARTITION BY PK ) AS [Without Any Index]
, SUM(HasClustIndex) OVER ( PARTITION BY PK ) AS [Has Clustered Index]
, COUNT(*) OVER ( PARTITION BY PK ) - SUM(HasClustIndex) OVER ( PARTITION BY PK ) AS [Has No Clustered Index]
, SUM(CASE WHEN NbNonClustIndexes = 0 THEN 1
ELSE 0
END) OVER ( PARTITION BY PK ) AS [Without Any NonClust Index]
, SUM(HasUniqueConstraint) OVER ( PARTITION BY PK ) AS [Has Unique Constraint]
, SUM(HasIdentity) OVER ( PARTITION BY PK ) AS [Has Identity Column]
, SUM(HasFullText) OVER ( PARTITION BY PK ) AS [Has FullText Index]
, SUM(HasRowGuidCol) OVER ( PARTITION BY PK ) AS [Has RowGuid Column]
, SUM(HasTextImage) OVER ( PARTITION BY PK ) AS [Has TextImage Column]
, SUM(HasTimeStamp) OVER ( PARTITION BY PK ) AS [Has TimeStamp Column]
, SUM(HasTrigger) OVER ( PARTITION BY PK ) AS [Has After Trigger]
FROM ( SELECT [Schema]
, TableName
, Id
, rows
, [Size (MB)]
, CASE WHEN HasPK = 0 THEN 'No PK'
WHEN HasPK = 1
AND HasClustPK = 1 THEN 'Clustered'
ELSE 'NonClustered'
END AS PK
, HasClustIndex
, NbNonClustIndexes
, HasFullText
, HasRowGuidCol
, HasTextImage
, HasTimeStamp
, HasIdentity
, HasTrigger
, HasUniqueConstraint
FROM ( SELECT t.TABLE_SCHEMA AS 'Schema'
, t.TABLE_NAME AS TableName
, OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') AS Id
, i.rows
, CAST (CAST (SUM(CAST (s.reserved AS BIGINT)) AS DECIMAL(18, 2)) * 8192 / 1024 / 1024 AS DECIMAL(18,
2)) AS 'Size (MB)'
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasPrimaryKey') AS HasPK
, ( SELECT OBJECTPROPERTY(object_id, 'CnstIsClustKey')
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME
+ ']')
AND type = 'PK'
) AS HasClustPK
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasClustIndex') AS HasClustIndex
, ( SELECT COUNT(*)
FROM sys.indexes
WHERE object_id = OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']')
AND index_id > 1
AND type = 2
) AS NbNonClustIndexes
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasActiveFulltextIndex') AS HasFullText
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasRowGuidCol') AS HasRowGuidCol
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasTextImage') AS HasTextImage
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasTimeStamp') AS HasTimeStamp
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasIdentity') AS HasIdentity
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'HasAfterTrigger') AS HasTrigger
, OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'TableHasUniqueCnst') AS HasUniqueConstraint
FROM INFORMATION_SCHEMA.TABLES t
JOIN sysindexes i ON OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') = i.id
AND i.indid < 2
JOIN sysindexes s ON OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') = s.id
AND s.indid IN ( 0, 1, 255 )
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID('[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']'),
'IsMSShipped') = 0
AND t.TABLE_NAME <> 'sysdiagrams' -- seulement tables utilisateur
AND t.TABLE_NAME <> 'dtproperties'
GROUP BY OBJECT_ID('[' + t.TABLE_SCHEMA + '].' + t.TABLE_NAME)
, t.TABLE_CATALOG
, t.TABLE_SCHEMA
, t.TABLE_NAME
, i.rows
) AS TablesProperties
) AS ListTables;
Bonne indexation...