Je voudrai faire cet article pour revenir sur des concepts basiques mais important.

Une base de données est constituée notamment de tables. Ces tables sont constituées notamment de colonnes, d’index et de statistiques. Jusqu’ici rien de nouveau…

L’une des choses qu’il faut étudier pour faire en sorte que votre base de données soit optimisée est l’exploitation de ces index et statistiques.

Rappel :

Les index :

• Les index permettent sur une ou plusieurs colonnes de faire un trie sur les données (comme dans un annuaire). Le parcours des données n’en sera que plus facile pour le SGBD.
• Lors de la création d’une PRIMARY KEY, un index est automatiquement créé : un index cluster. Il est unique pour chaque table.
• D’autres index pourront être créés : des index « non-cluster »
• Un index aura forcément une statistique associée.
• Lors d’une requête si une fonction est appliquée sur le champ possédant un index, ce dernier ne sera pas utilisé (exemple : substring, YEAR(), etc.) pareil pour l’opérateur LIKE.
• De plus, pour un index sur plusieurs colonnes, si dans une requête, l’ensemble de ces colonnes n’est pas appelé alors cet index ne sera pas utilisé

Les statistiques :

• Les statistiques permettent d’avoir le nombre d’occurrences de chaque valeur d’une colonne. Elles représentent la distribution des valeurs.
• SQL server se base sur ces statistiques pour évaluer le coût du plan d’exécution. En effet, lors de l’exécution d’une requête si le nombre de lignes avec le critère demandé est beaucoup plus petit que le nombre de lignes total de la table alors SQL server utilisera l’index sinon il parcourra toute la table. Le nombre de lignes avec le critère demandé correspondant au nombre d’occurrences  défini par les statistiques.
• Lors de la création d’un index, une statistique est automatiquement créée
• Des statistiques peuvent être créées sur des champs ne possédant pas d’index.
• Les statistiques peuvent être créées automatiquement par SQL server grâce à l’option AUTO UPDATE STATISTICS

Evaluez votre base de données : les index de vos tables ainsi que les statistiques avec la date de leurs dernières mises à jour :
SELECT
SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
, [sObj].[name] AS [ObjectName]
, CASE
WHEN [sObj].[type] = ‘U’ THEN ‘Table’
WHEN [sObj].[type] = ‘V’ THEN ‘View’
END AS [ObjectType]
, [sIdx].[index_id] AS [IndexID] — 0: Heap; 1: Clustered Idx; > 1: Nonclustered Idx;
, ISNULL([sIdx].[name], ‘N/A’) AS [IndexName]
, CASE
WHEN [sIdx].[type] = 0 THEN ‘Heap’
WHEN [sIdx].[type] = 1 THEN ‘Clustered’
WHEN [sIdx].[type] = 2 THEN ‘Nonclustered’
WHEN [sIdx].[type] = 3 THEN ‘XML’
WHEN [sIdx].[type] = 4 THEN ‘Spatial’
WHEN [sIdx].[type] = 5 THEN ‘Reserved for future use’
WHEN [sIdx].[type] = 6 THEN ‘Nonclustered columnstore index’
END AS [IndexType]
, [sCol].[name] AS [ColumnName]
, CASE
WHEN [sIdxCol].[is_included_column] = 0×1 THEN ‘Yes’
WHEN [sIdxCol].[is_included_column] = 0×0 THEN ‘No’
WHEN [sIdxCol].[is_included_column] IS NULL THEN ‘N/A’
END AS [IsIncludedColumn]
, [sIdxCol].[key_ordinal] AS [KeyOrdinal]
, [sObj].create_date
, [sObj].modify_date
,PhysicalStats.page_count as [Page_Count],
CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Size (MB)],
CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Frag %],
ParititionStats.row_count AS [Row Count],
CASE WHEN ParititionStats.row_count =0 THEN 0 ELSE CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024)
/ ParititionStats.row_count)END AS [Index Size/Row (Bytes)]
, STATS_DATE([sIdx].object_id, [sIdx].index_id) as derniereDateMAJStatistique
FROM
[sys].[indexes] AS [sIdx]
INNER JOIN [sys].[objects] AS [sObj]ON [sIdx].[object_id] = [sObj].[object_id]
LEFT JOIN [sys].[index_columns] AS [sIdxCol]ON [sIdx].[object_id] = [sIdxCol].[object_id] AND [sIdx].[index_id] = [sIdxCol].[index_id]
LEFT JOIN [sys].[columns] AS [sCol]ON [sIdxCol].[object_id] = [sCol].[object_id] AND [sIdxCol].[column_id] = [sCol].[column_id]
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS PhysicalStats ON
PhysicalStats.index_id = [sIdx].Index_id and PhysicalStats.object_id = [sIdx].object_id
LEFT JOIN sys.dm_db_partition_stats ParititionStats ON ParititionStats.index_id = [sIdx].index_id
and ParititionStats.object_id = [sIdx].object_id
WHERE
[sObj].[type] = ‘U’
ORDER BY [sObj].modify_date, [sObj].create_date , SCHEMA_NAME([sObj].[schema_id]), ColumnName

Cette requête  permet d’avoir une vision des indexs et de leurs statistiques  :

• la taille de l’index
• Le nombre de pages utilisé par cet index
• La fragmentation de l’index
• au regard du nombre de lignes total présent dans la table
• La dernière mise à jour de la statistique qui lui est associée
• repérer les tables sans index : table avec un type d’index ‘HEAP’

Pour des index et statistiques à jour, il ne faut pas oublier de mettre en place un plan de maintenance avec les deux étapes de :

• Rebuild des index
• Update des statistiques

Avez vous des requêtes d’analyses à nous faire partager?