Diagnostiquer les problèmes de performances

Comment diagnostiquer les problèmes de performances dans SQL Server

Si votre serveur SQL présente des lenteurs, vous devez absolument recueillir des indices, pour ne pas supposer des problèmes et des solutions qui ne correspondent pas à la nature du problème.
Si vous étiez médecin, prescririez-vous un traitement sans effectuer de diagnostic ?

Hypothèses

D’où peuvent provenir les problèmes de performance ?

D’un serveur mal dimensionné;
D’une mauvaise configuration de SQL Server ou des bases de données;
De requêtes mal écrites qui sont trop longues;
D’allers-retours excessifs entre le client et le serveur;
De blocages dus à trop de verrouillage;
D’exécution de triggers, de transactions trop longues;
D’un manque d’index;
D’attentes, par exemple sur le parallélisme des requêtes;
Du code client, en non pas de SQL Server;
De problèmes de plans d’exécution, de compilation et de parameter sniffing.

Comment vérifier les hypothèses ?

Serveur mal dimensionné

SQL Server peut très bien fonctionner sur une machine moyennement dimensionnée, si la base de données et les requêtes sont optimisées.

Quelques pistes :

Interrogez la requête suivante : Paul Randal, tell me where it hurts et observez les attentes les plus communes. Observez bien le signal time pour juger de la pression sur les CPU.
Interrogez le Query Store, rapport « Overall Resource Consumption »
Vérifiez les compteurs de buffer (cache de données) à l’aide de la requête suivante.
Faut-il désactiver l’hyperthreading ? Cela dépend. En général, vous pouvez. Si c’est un host VMWare, laissez-le activé, VMWare se débrouille bien avec l’HT.

Y a-t-il un problème de disque ?

Pour déterminer si les performances des disques sont à blamer, vous pouvez :

Suivre les compteurs de performance
- Disque Physique / Moyenne disque s/lecture sur le disque des fichiers de données et de tempdb.
- Disque Physique / Moyenne disque s/écriture sur le disque des fichiers de journaux de transactions et de tempdb.
- Quelques idées de valeurs dans cet article de blog.
Vérifiez les latences d’IO sur les fichiers à l’aide de la requête suivante.
Utiliser l’outil Diskspd (Vous trouvez ici un article en anglais sur son utilisation)

Mauvaise configuration de SQL Server ou des bases de données

Pour savoir si la configuration de l’instance est concernée :

parallélisme ?
- jouez un peu avec.
- regardez les attentes de type CXPACKET et, depuis SQL Server 2022, CXSYNC_PORT et CXSYNC_CONSUMER. CXCONSUMER fait partie du fonctionnement normal d’un plan parallèle. Si vous en avez beaucoup, regardez si l’hyperthreading est activé.
- The sys.dm_os_latch_stats DMV contains information about the specific latch waits that have occurred in the instance, and if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking during query execution, and reducing the ‘max degree of parallelism’ sp_configure option may be required to resolve the problems.

Parallélisme

Pour optimiser le parallélisme sur l’instance, Configurer les éléments suivants :
- Seuil de coût pour le parallélisme – à mettre à 50
- Degré maximum de parallélisme – 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
Depuis SQL Server 2016, vous pouvez configurer le degré de parallélisme par base de données, à l’aide d’une configuration scopée. L’option s’applique à la base courante, d’où le USE (ici sur la base de démonstration PachaDataFormation).

USE [PachaDataFormation];
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;

Configuration des bases

auto close – ça peut se sentir – toujours à OFF
auto shrink – toujours à OFF. Cette option ne devrait même pas exister.
création automatique des statistiques – toujours à ON
mise à jour automatique des statistiques – toujours à ON
En bref : laissez les options par défaut des bases.
niveau de compatibilité – le plus élevé possible pour bénéficier des améliorations du moteur relationnel, en surveillant les régressions de requêtes avec le Query Store lors du changement.
Gérez au besoin la version du moteur d’estimation de cardinalité (legacy cardinality estimation) selon les erreurs d’estimation de cardinalité dans vos requêtes.

Requêtes mal écrites qui sont trop longues

Utilisez le Query Store pour identifier les requêtes les plus consommatrices.
- vidéo : Nicolas Souquet – Tout savoir sur le Query Store.
- vidéo : Steven Naudet
Utilisez une session d’évènements étendus :
- Exemples de sessions sur mon Github
- Pour apprendre à utiliser les évènements étendus, vous trouvez sur ma chaîne Youtube une vidéo complète sur le sujet.
Requêtez la vue dm_exec_query_stats
Utilisez, en temps réel, la procédure sp_whoisactive
Si vous avez des procédures stockées, utilisez la vue dm_exec_procedure_stats

Allers-retours excessifs entre le client et le serveur

Plus difficile à identifier. Le comportement attendu est un excès de petites requêtes répétitives, qui exécutent des allers-retours unitaires entre le client et le serveur, au lieu de requêtes ensemblistes. C’est un comportement à changer dans le code client.

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 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.
- vidéo : Steven Naudet

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 et cherchez les attentes de type LCK...
Si vous trouvez des attentes de ce type, approfondissez le diagnostic à l’aide des instructions suivantes.
Vous pouvez aussi planifier une vérification toutes les quelques minutes avec le script suivant.
Surveillez les transactions actives et les longues transactions. Vous pouvez utiliser cette requête sur mon Github 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
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
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, ou version pour Azure SQL Database

Manque d’index

Vérifiez le rapport d’index manquants.
Vérifiez que les index sont bien utilisés avec le rapport d’utilisation des index.
Vous pouvez vous référer à l’article suivant.
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.

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, regardez les tables qui ont eu beaucoup de modifications.
  - article un peu ancien
- regardez le plan d’exécution réel
- Utilisez 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.

Expertise SQL Server Besoin de services avec SQL Server ? Contactez-moi et parlons de votre projet