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 ?

1. D’un serveur mal dimensionné;
2. D’une mauvaise configuration de SQL Server ou des bases de données;
3. De requêtes mal écrites qui sont trop longues;
4. D’allers-retours excessifs entre le client et le serveur;
5. De blocages dus à trop de verrouillage;
6. D’exécution de triggers, de transactions trop longues;
7. D’un manque d’index;
8. D’attentes, par exemple sur le parallélisme des requêtes;
9. Du code client, en non pas de SQL Server;
10. 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 Randall, 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 ? La seule chose configurable qui peut changer les choses.
  • jouez un peu avec.
  • regardez les attentes de type CXPACKET. 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.

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1 ;

Configuration des bases

• auto close – ça peut se sentir – toujours à FALSE
• auto shrink – toujours à FALSE. Cette option ne devrait même pas exister.
• création automatique des statistiques – toujours à vrai
• mise à jour automatique des statistiques – toujours à vrai
• 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.
• 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 Randall, 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_triggers_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 – code pour Azure à adapter pour on prem

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, videz le cache de plans à l’aide de la commande suivante : DBCC FREEPROCCACHE. Est-ce que cela résout le problème ? Vous pouvez exécuter cette commande au lieu de redémarrer un serveur SQL.

Vérifiez les statistiques : - 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 actuel
  • 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

[Besoin de services avec SQL Server ? Contactez-moi]