# Optimiser les requêtes SQL

> Méthode pour optimiser les requêtes SQL Server : statistiques IO et TIME, Query Store, évènements étendus, traitement ligne à ligne, index, statistiques.
>
> Source : https://www.pachadata.com/docs/howtos/performances/requetes/
> Auteur : Rudi Bruchez · mis à jour le 2026-10-14
> Version Markdown de la page, pour les outils et les LLM. La page HTML fait référence.


> Quelle méthodologie pouvez-vous utiliser pour optimiser vos requêtes Transact-SQL ?

## Hypothèses

Les problèmes de performance de requêtes peuvent provenir de :

1. Un mauvais modèle de données ;
1. De requêtes mal écrites qui sont trop longues ;
1. D'un comportement unitaire et non ensembliste (_RBAR_) ;
1. D'exécution de triggers, de transactions trop longues;
1. D'un manque d'index;
1. D'attentes, par exemple sur le parallélisme des requêtes;
1. De problèmes de plans d'exécution, de compilation et de *parameter sniffing*.

## Corriger un mauvais modèle de données

Il est difficile de corriger un mauvais modèle de données. Vous pouvez :

- Utiliser la compression ROW ou PAGE pour diminuer la taille physique des tables et améliorer les IO, au prix d'un surcoût CPU.
- Corriger petit à petit le schéma en utilisant des vues comme couche d'abstraction pour remodéliser les tables sans avoir à modifier le code client. Les mises à jour sur les vues peuvent être reproduites avec des déclencheurs INSTEAD OF.

## Diagnostiquer les requêtes mal écrites et trop longues

- Vérifiez les statistiques d'IO et de temps des requêtes
  - `SET STATISTICS IO ON` -- affiche les statistiques d'IO par table accédée dans la requête. L'unité est la page (8 ko). Les statistiques sont séparées en IO logiques (dans le _buffer_) et IO physiques (lectures physiques et anticipées).
  - `SET STATISTICS TIME ON` -- affiche les statistiques de temps par requête. Les statistiques sont séparées en temps de compilation (création du plan d'exécution) et d'exécution. Les temps sont le temps CPU et la durée totale de l'exécution, envoi des résultats au client compris.
- Utilisez le Query Store pour identifier les requêtes les plus consommatrices.
  - [vidéo : Nicolas Souquet – Tout savoir sur le Query Store](https://youtu.be/Adwtl1QtYvI).
  - [vidéo : Steven Naudet](https://youtu.be/EVchvLCk-IQ)
- Utilisez une session d'évènements étendus :
  - [Exemples de sessions sur mon Github](https://github.com/rudi-bruchez/tsql-scripts/tree/master/extended-events)
  - Pour apprendre à utiliser les évènements étendus, vous trouvez sur ma chaîne Youtube [une vidéo complète sur le sujet](https://youtu.be/SC7MfYsd-p0).
- Requêtez la vue [dm_exec_query_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution-stats/query_stats.sql)
- Utilisez, en temps réel, la procédure [sp_whoisactive](https://github.com/amachanic/sp_whoisactive/releases)
- Si vous avez des procédures stockées, utilisez la vue [dm_exec_procedure_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/execution-stats/stored-procedures/procedure-execution-analysis.sql)

## Comportement unitaire

Trop d'allers-retours entre le client et le serveur pose de grands problèmes de performances. On a appelé ce comportement RBAR : _Row By Agonizing Row_.

Le comportement souhaité est : obtenir le plus de données possibles dans une requête ensembliste, et effectuer le moins d'allers-retours possibles. Comme quand vous faites vos courses : Vous achetez tout en une fois au supermarché, et vous revenez chez vous avec toutes vos courses.

- Cherchez, avec une session d'évènements étendus sans filtre de coût de requête, des appels répétitifs de requêtes semblables, par exemple des suites d'inserts ou des `SELECT` unitaires semblables avec des changements de paramètres.
- Requêtez la vue [dm_exec_query_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/master/diagnostics/execution-stats/query_stats.sql) en cherchant des requêtes peu consommatrices mais exécutées très souvent (triez sur la colonne `execution_count`)
- Utilisez le Query Store pour identifier les requêtes les plus exécutées.
  - [vidéo : Nicolas Souquet – Tout savoir sur le Query Store](https://youtu.be/Adwtl1QtYvI).
  - [vidéo : Steven Naudet](https://youtu.be/EVchvLCk-IQ)


Pour cela :

- Envoyez des tableaux à vos procédures stockées. Vous avez plusieurs méthodes :
  - Un paramètre XML, parsé avec la méthode `nodes()`, en minuscules : les méthodes du type `xml` sont sensibles à la casse ;
  - Un paramètre en JSON, parsé avec `OPENJSON` (niveau de compatibilité 130 au minimum). Le paramètre est en `NVARCHAR(MAX)`, ou, depuis SQL Server 2025, du type natif `json` ;
  - Un paramètre en chaîne de caractères, parsé avec `STRING_SPLIT()` (niveau de compatibilité 130 au minimum). L'ordre des éléments renvoyés n'est garanti qu'avec l'argument `enable_ordinal` et un tri sur la colonne `ordinal` ;
  - Un paramètre en type table ;
- Vérifiez toujours le comportement de votre application avec une session d'évènements étendus sur le serveur.
- En Entity Framework, assurez-vous que vous utilisez le bon [_loading_](https://learn.microsoft.com/en-us/ef/core/querying/related-data/lazy).

## Parallélisme

- Pour optimiser le parallélisme sur l'instance, Configurer les éléments suivants :
  - [Seuil de coût pour le parallélisme](https://learn.microsoft.com/fr-fr/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option) -- à mettre à **50**
  - [Degré maximum de parallélisme](https://learn.microsoft.com/fr-fr/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option) -- ne pas le laisser à 0 si vous avez plus de huit processeurs. 4 est souvent une bonne valeur pour les serveurs OLTP. Voir les [recommandations](https://learn.microsoft.com/fr-fr/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option#recommendations)

- Depuis SQL Server 2016, vous pouvez configurer le degré de parallélisme par base de données, à l'aide d'une [configuration scopée](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql). L'option s'applique à la base courante, d'où le `USE` (ici sur la base de démonstration `PachaDataFormation`).

```sql
USE [PachaDataFormation];
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;
```

## Blocages dus à trop de verrouillage

Les blocages sont des attentes sur des verrous posés par d'autres sessions.

Y a-t-il des blocages ?

- Interrogez la requête suivante : [Paul Randal, tell me where it hurts](https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) et cherchez les attentes de type `LCK...`
- Si vous trouvez des attentes de ce type, approfondissez le diagnostic [à l'aide des instructions suivantes](../blocages/).
- Vous pouvez aussi planifier une vérification toutes les quelques minutes avec [le script suivant](https://github.com/rudi-bruchez/tsql-scripts/blob/main/diagnostics/locking/monitor-blocking.sql).
- Surveillez les transactions actives et les longues transactions. Vous pouvez utiliser [cette requête sur mon Github](https://github.com/rudi-bruchez/tsql-scripts/blob/c0530ebe80d5e413e4b57950fb0447e2e43825f0/diagnostics/execution/active-transactions.sql) pour voir les transactions actives.

## Exécution de triggers, de transactions trop longues

- Identifiez le coût de vos déclencheurs à l'aide de la vue de diagnostic [sys.dm_exec_trigger_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/master/diagnostics/execution-stats/trigger-stats.sql)
- Cherchez les blocages éventuels avec les outils de la section précédente.
- Si vous avez des procédures stockées, utilisez la vue [dm_exec_procedure_stats](https://github.com/rudi-bruchez/tsql-scripts/blob/master/diagnostics/execution-stats/stored-procedures/procedure-execution-analysis.sql)
- Si vous soupçonnez des déclencheurs, des procédures stockées ou des appels de fonctions, créez une session d'évènements étendus avec l'évènement `sp_statement_completed` : [script pour SQL Server](https://github.com/rudi-bruchez/tsql-scripts/blob/main/extended-events/on-prem/monitor-procedure-execution.sql), ou [version pour Azure SQL Database](https://github.com/rudi-bruchez/tsql-scripts/blob/master/extended-events/azure-sql-database/trace-procedure-create.sql)

## Manque d'index

- Vérifiez le [rapport d'index manquants](https://github.com/rudi-bruchez/tsql-scripts/blob/master/index-management/missing-indexes.sql).
- Vérifiez que les index sont bien utilisés avec [le rapport d'utilisation des index](https://github.com/rudi-bruchez/tsql-scripts/blob/master/index-management/index-usage.sql).
- Vous pouvez vous référer [à l'article suivant](https://rudi.developpez.com/sqlserver/tutoriel/vuesdm-index/).
- Utilisez le Database Tuning Advisor sur une requête ou une charge de travail.
- Si vous avez des requêtes plutôt analytiques, essayez les index ColumnStore.

## Problèmes de plans d'exécution, de compilation et de *parameter sniffing*

Lorsque les problèmes se posent, retirez du cache le plan de la requête en cause avec `DBCC FREEPROCCACHE (plan_handle)`, ou videz le cache de plans d'une seule base avec `ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE`. Est-ce que cela résout le problème ? Vous pouvez exécuter ces commandes au lieu de redémarrer un serveur SQL. `DBCC FREEPROCCACHE` sans argument vide le cache de toute l'instance et force la recompilation de toutes les requêtes : à éviter en production.

Vérifiez les statistiques avec cette [requête de diagnostic](https://github.com/rudi-bruchez/tsql-scripts/blob/main/database-information/statistics/statistics.sql).

## Erreur d'estimation de cardinalité

- un signe classique : la requête se dégrade avec le temps. Planifiez un recalcul des statistiques plus régulièrement.
    - vérifiez les statistiques avec [ce script](https://github.com/rudi-bruchez/tsql-scripts/blob/main/database-information/statistics/statistics.sql), regardez les tables qui ont eu beaucoup de modifications.
        - [article un peu ancien](https://rudi.developpez.com/sqlserver/tutoriel/statistiques/)
    - regardez le plan d'exécution **réel**
    - Utilisez [plan explorer](https://www.solarwinds.com/free-tools/plan-explorer)
    - lancez un recalcul avec `UPDATE STATISTICS `
    - Activez l'ancien moteur d'estimation de cardinalité.
    - ajoutez l'option dans la requête `OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))`
    - regardez si on utilise des variables de type table, surtout si la base est à un niveau de compatibilité inférieur à 150 : l'optimiseur y estime alors une seule ligne. Depuis le niveau 150, la compilation différée des variables table utilise leur nombre réel de lignes.


---

## L'auteur

Rudi Bruchez, consultant SQL Server indépendant depuis 2006 : audit de performance, optimisation, migrations, haute disponibilité et formation. Pour un audit ou une intervention sur votre serveur : https://www.pachadata.com/contact/ (audit à distance : https://www.pachadata.com/services/audit-sql-server-a-distance/).