Exemple de compte rendu d'audit

Serveur SQL01, collecte du 27 août à 14:18, authentification Windows.

Contexte de la collecte

La collecte a duré 106 secondes et les 82 requêtes ont abouti. Aucune permission n’a manqué, donc aucun constat n’est absent faute de droits.

Un point à garder en tête pour tout ce qui suit. L’instance a redémarré le 15 août à 17:38, il y a 11,9 jours. Tous les compteurs cumulés, attentes, entrées/sorties par fichier, usage des index, portent sur cette fenêtre et pas au-delà. Le Query Store d’ERPPROD, lui, remonte au 27 juillet, ce qui donne un mois complet sur les requêtes.

Savez-vous pourquoi le serveur a redémarré le 15 août ? Le journal d’erreurs commence à ce démarrage et ne dit rien de ce qui l’a précédé.

Trois bases utilisateur : ERPPROD (56 320 Mo), ERPOLD (19 098 Mo), DBADMIN (50 Mo). SQL Server 2022 en 16.0.4250.1, soit le CU24, Standard Edition 64 bits, machine virtuelle, 16 processeurs logiques, 64 Go de RAM.

Configuration du serveur

Occupation mémoire

La mémoire maximale est limitée à 59 392 Mo, soit 58 Go sur les 64 Go de la machine. Cela laisse 6 Go à Windows, et le compteur de mémoire physique disponible est descendu à 1 736 Mo au moment de la collecte, c’est-à-dire 2,65 % de la RAM.

L’option Lock Pages in Memory est active et 57,4 Go sont verrouillés. Les pages verrouillées ne peuvent pas être récupérées par Windows sous pression. La marge de 6 Go est donc la seule dont dispose le système, et elle est un peu juste.

Le cache lui-même se porte bien : Database Cache Memory est à 47 654 Mo et le Page Life Expectancy à 14 959 secondes pour une cible calculée à 4 350. Il n’y a aucun signe de manque de mémoire côté données. Ce n’est donc pas urgent, mais je descendrais le plafond de 2 Go pour redonner de l’air au système.

EXEC sys.sp_configure N'max server memory (MB)', N'57344'
RECONFIGURE WITH OVERRIDE

Y a-t-il un agent de sauvegarde, un antivirus ou un agent de supervision qui tourne sur cette machine ? Je vois passer les requêtes d’un outil de supervision dans le cache de plans, et ces processus consomment aussi la mémoire hors de SQL Server.

Tuning du parallélisme

L’option « degré maximum de parallélisme » est à 1. Aucune requête ne peut donc utiliser plus d’un processeur, sur une machine qui en a seize.

La machine n’a pas de NUMA matériel : hardware_numa_present est à faux et il n’y a qu’un seul nœud mémoire. Les deux nœuds que SQLOS affiche sont du soft-NUMA automatique, fabriqué parce qu’un nœud dépassait huit processeurs logiques. Le calcul de MAXDOP se fait donc sur la machine entière, et le collecteur propose lui-même la valeur 8.

MAXDOP à 1 est le bon réglage pour SharePoint et pour quelques progiciels qui l’exigent. Est-ce le cas de votre ERP ? Si l’éditeur l’impose par contrat de support, il faut le laisser et le dire dans la documentation d’exploitation. Sinon, c’est une limite que vous vous imposez sans contrepartie.

Ce qu’elle coûte se voit dans le Query Store. La requête la plus lourde du mois, un SELECT DISTINCT sur ORDERS, a consommé 4 516 secondes de processeur en une seule exécution le 21 août, sur un seul thread. Avec MAXDOP 8, elle aurait pu se répartir.

Le seuil de coût pour le parallélisme est resté à 5, la valeur par défaut du moteur, définie il y a presque trente ans. Il faut monter les deux ensemble.

EXEC sys.sp_configure N'max degree of parallelism', N'8'
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
RECONFIGURE WITH OVERRIDE

Les deux options sont dynamiques, il n’y a pas de redémarrage à prévoir. Comme aucune attente CXPACKET n’existe aujourd’hui sur cette instance, il faudra regarder le profil d’attente une semaine après le changement.

Cache de plans

Le cache contient 20 287 plans pour 4 959 Mo, dont 14 595 plans à usage unique qui pèsent 2 855 Mo, soit 57,6 % de la mémoire du cache. Le clerk CACHESTORE_SQLCP est le deuxième consommateur de mémoire de l’instance, derrière le buffer pool.

L’option « optimize for ad hoc workloads » est désactivée, et c’est le réflexe qu’on aurait ici. Il ne servirait à rien. Les plans à usage unique de type Adhoc ne pèsent que 89,7 Mo. Les 2 759 Mo restants sont des plans Prepared, c’est-à-dire des requêtes paramétrées envoyées par le pilote applicatif, et l’option ne les touche pas.

Le levier est donc du côté de l’application. Le pilote envoie 13 496 formes de requêtes préparées différentes qui ne sont jamais réutilisées. Cela ressemble à des littéraux concaténés dans un texte de requête par ailleurs paramétré. Je détaille plus loin, dans la partie sur les requêtes.

Vous pouvez quand même activer l’option, elle ne coûte rien et elle couvrira les 89 Mo d’ad hoc.

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
RECONFIGURE WITH OVERRIDE

Messages de sauvegardes journalisés

Le journal d’erreurs compte 645 lignes sur les douze derniers jours, dont 288 sont des messages de sauvegarde réussie. Presque une ligne sur deux ne sert à rien et noie le reste.

Aucun trace flag global n’est actif et les trois paramètres de démarrage ne contiennent aucun -T. Activez le Trace Flag 3226, en session pour l’effet immédiat et en paramètre de démarrage pour qu’il survive au redémarrage.

DBCC TRACEON (3226, -1)

Compression et checksum des sauvegardes

Les deux options par défaut sont à zéro.

La compression de sauvegarde est disponible en Standard Edition depuis 2008 R2, il n’y a pas de raison de s’en priver. Attention toutefois : vos sauvegardes passent par un périphérique virtuel, donc par un outil externe, et le rapport mesuré sur les 61 sauvegardes d’ERPPROD est de 0,986. Autrement dit elles ne sont pas compressées du tout aujourd’hui, et changer l’option du serveur ne changera rien tant que c’est l’outil externe qui pilote. C’est dans sa configuration à lui qu’il faut regarder.

Le checksum, en revanche, vaut la peine d’être posé.

EXEC sys.sp_configure N'backup checksum default', N'1'
EXEC sys.sp_configure N'backup compression default', N'1'
RECONFIGURE WITH OVERRIDE

L’option remote admin connections à 0 signifie que la connexion administrative dédiée n’est accessible qu’en local. Ce n’est pas grave, mais le jour où l’instance ne répond plus, il faut pouvoir ouvrir une session sur la machine.

Disques et emplacement des fichiers

La latence d’écriture sur tempdb

C’est le point le plus sérieux de cette collecte.

Les quatre fichiers de données de tempdb sont sur le volume G:. Leurs latences moyennes d’écriture, cumulées depuis le démarrage du 15 août, sont de 1 694,7, 1 718,7, 1 720,0 et 1 862,0 millisecondes. Une écriture sur tempdb prend en moyenne une seconde et sept dixièmes.

Le repère habituel est de 5 à 10 ms pour tempdb. On est à plus de cent fois au-dessus, sur une moyenne de douze jours, ce qui exclut un incident ponctuel.

Trois éléments confirment que le problème est bien le chemin d’écriture de ce volume, et pas tempdb ni la charge :

• les mêmes fichiers lisent à 1,5 ms, ce qui est excellent ;
• templog, sur le volume H:, écrit à 3,5 ms ;
• le journal d’erreurs porte le message définitif, le 21 août à 10:17:50 : « SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [G:\TempDB\tempdb.mdf] ». La durée de cette entrée/sortie était de 15 231 ms. Quatre messages « Long Sync IO » sur l’ordonnanceur 5 sont enregistrés le même matin, entre 10:10 et 10:34.

Je vous recommande vraiment de faire remonter ce point à l’équipe qui gère le stockage ou l’hyperviseur, avec ces chiffres. Le volume G: est étiqueté « SysDB » et il ne porte que les quatre fichiers de tempdb, donc rien d’autre ne se dispute la bande passante. Il faut savoir sur quel type de stockage il est posé, s’il partage des broches avec autre chose, et s’il n’y a pas un cache d’écriture désactivé ou une file d’attente saturée au niveau de l’hôte.

Tant que ce n’est pas réglé, toute opération qui passe par tempdb paie ce prix : les tris, les tables temporaires, les jointures qui débordent. Le compteur de création de tables temporaires est à 1 403 par seconde.

Espace libre sur G:

Le même volume mérite une deuxième remarque. Il fait 69,9 Go et il ne reste que 17,8 Go libres.

Les quatre fichiers de tempdb ont été configurés à 6 144 Mo chacun, mais ils occupent aujourd’hui 13 312 Mo chacun, soit 52 Go au total. Ils ont donc grandi, par paliers de 1 Go, et leur taille maximale est illimitée. À la prochaine requête qui déborde, ils continueront jusqu’à remplir le disque.

Fixez une taille maximale, ou redimensionnez les fichiers à leur taille réelle et surveillez le volume.

Le reste de la disposition des fichiers

La séparation est correcte : E: pour les données utilisateur, F: pour les journaux de transactions, G: pour les données de tempdb, H: pour son journal. Les marges d’espace libre y sont confortables, de 38 à 84 %.

Le fichier de données d’ERPPROD, sur E:, écrit à 30,4 ms en moyenne et lit à 1,7 ms. La lecture est bonne, l’écriture est au-dessus de ce qu’on attend d’un volume de données. Le point est moins criant que sur tempdb, mais il pointe dans la même direction et vaut la peine d’être posé dans la même discussion avec l’équipe stockage.

Trois détails, sans gravité. Les fichiers de tempdb mélangent G:\TempDB\ et G:\tempDB\ selon les fichiers. Les fichiers secondaires de tempdb s’appellent tempdb1.mdf, tempdb2.mdf et tempdb3.mdf, alors que la convention veut .ndf pour un fichier secondaire. Et les bases système, ainsi que DBADMIN, vivent sous D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\, sur un moteur qui est en MSSQL16. C’est la trace d’une mise à niveau sur place depuis SQL Server 2016, que les paramètres de démarrage confirment. Tout cela fonctionne, ce n’est pas très grave, mais lors de la prochaine réinstallation cela vaut la peine de repartir propre.

Configuration des bases de données

Le mode de récupération et les sauvegardes

Les trois bases sont en mode de récupération SIMPLE.

En SIMPLE, la sauvegarde du journal de transactions est impossible. Il n’existe donc aucun point de restauration entre deux sauvegardes complètes. Si ERPPROD est perdue à 20 heures, vous restaurez l’état de 22:17 la veille et vous perdez la journée de travail.

La question du RPO vous appartient et elle se tranche avec le métier. Si perdre une journée d’activité sur ERPPROD est acceptable, SIMPLE est le bon choix et il n’y a rien à changer. Sinon, il faut passer ERPPROD en FULL et planifier des sauvegardes de journal. DBADMIN contient déjà les procédures d’Ola Hallengren, donc dbo.DatabaseBackup est probablement disponible pour planifier la cadence sans rien écrire de nouveau.

Ce que je constate en revanche, c’est que la question ne se pose nulle part dans la configuration actuelle. Ce point avait déjà été soulevé lors de la collecte précédente et rien n’a bougé, ce qui est normal en deux semaines. A-t-il été porté au métier ?

Restaurations de test

L’historique des restaurations de l’instance est vide. Pas une seule restauration enregistrée, jamais, alors que l’historique des sauvegardes remonte à un an.

Une sauvegarde qui n’a jamais été relue n’est pas encore une sauvegarde. Il faudrait au moins une restauration d’ERPPROD sur une machine de test, une fois, pour mesurer la durée et vérifier que l’outil externe rend bien un jeu de fichiers exploitable. Combien de temps prendrait aujourd’hui la remise en service d’ERPPROD ? Personne ne le sait, et c’est le chiffre que le métier vous demandera le jour où ça arrivera.

Haute disponibilité

Il n’y a aucun dispositif de bascule. Always On est désactivé, il n’y a ni groupe de disponibilité, ni miroir, ni log shipping, et la machine n’est pas en cluster. Une panne de la machine virtuelle arrête l’ERP jusqu’à sa remise en route.

Cela peut être parfaitement assumé si le RTO négocié le permet. Est-ce le cas ?

Niveau de compatibilité

Les trois bases sont au niveau de compatibilité 130, celui de SQL Server 2016, sur un moteur qui est en 160.

C’est le point le plus rentable de ce compte rendu par rapport à son coût. Toutes les configurations de portée base liées à 2019 et 2022 sont déjà activées sur les trois bases : PARAMETER_SENSITIVE_PLAN_OPTIMIZATION, CE_FEEDBACK, MEMORY_GRANT_FEEDBACK_PERSISTENCE, OPTIMIZED_PLAN_FORCING. Elles sont toutes inertes tant que le niveau de compatibilité ne monte pas. Vous payez la licence 2022 et vous exécutez un optimiseur de 2016.

Le changement n’est pas anodin, car il change les plans d’exécution. La méthode sûre passe par le Query Store, qui est déjà actif sur ERPPROD avec un mois d’historique :

ALTER DATABASE [ERPPROD] SET COMPATIBILITY_LEVEL = 160

et si une requête régresse, forcer son ancien plan depuis le Query Store le temps de comprendre. C’est une opération à faire sur une fenêtre calme, avec une semaine de surveillance derrière.

Dans le même mouvement, l’option QUERY_OPTIMIZER_HOTFIXES est à zéro sur les trois bases, donc les correctifs d’optimiseur livrés depuis la sortie du produit ne s’appliquent pas.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON

Query Store

Le Query Store n’est activé que sur ERPPROD. Il y tourne en READ_WRITE, en mode de capture AUTO, et il contient 18 223 requêtes et 33 462 plans sur un mois. C’est ce qui a permis l’essentiel de l’analyse des requêtes ci-dessous.

Il occupe 797 Mo sur les 1 000 alloués. Le nettoyage automatique par la taille est actif, donc il ne passera pas en lecture seule, mais il va commencer à perdre de l’historique. Montez la limite à 2 Go si l’espace le permet.

Sur ERPOLD et DBADMIN, activez-le aussi. Le coût est négligeable et il donne la visibilité qui manque aujourd’hui.

ALTER DATABASE [ERPOLD] SET QUERY_STORE = ON
ALTER DATABASE [ERPOLD] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

Propriétaire de DBADMIN

La base DBADMIN n’a pas de propriétaire. Le SID enregistré ne résout plus vers aucun login de l’instance, ce qui veut dire que le compte qui l’a créée a disparu. ERPPROD et ERPOLD appartiennent à sa, ce qui est bien.

Le propriétaire sert à des fonctionnalités avancées de sécurité et aux permissions entre bases. Un propriétaire orphelin finit par poser des problèmes qu’on met du temps à relier à leur cause.

ALTER AUTHORIZATION ON DATABASE::[DBADMIN] TO [sa]

Collations

ERPPROD et ERPOLD sont en Latin1_General_CI_AS. DBADMIN et tempdb sont en SQL_Latin1_General_CP1_CI_AS.

Comme les tables temporaires sont créées dans tempdb avec la collation de tempdb, toute requête qui compare une colonne de type texte d’une table temporaire avec une colonne d’une table d’ERPPROD déclenche l’erreur 468. Vous n’en voyez probablement pas, sinon vous le sauriez, parce que le code applicatif doit déclarer ses colonnes explicitement ou ne pas faire ce genre de jointure. C’est donc plus un piège dormant qu’un problème actuel. À noter pour le jour où quelqu’un écrit un script d’exploitation.

La base ERPOLD

ERPOLD occupe 19 Go et je n’y vois aucune activité. Sur ses 1 082 index, pas un seul n’a de ligne dans les statistiques d’usage depuis le démarrage du 15 août. Les 20 880 lectures relevées sur son fichier de données s’expliquent par le CHECKDB et la sauvegarde. Ses statistiques les plus anciennes datent d’il y a un an.

Cette base sert-elle encore ? Si c’est un ancien environnement ou une base reprise d’un précédent système, elle continue de coûter en sauvegarde, en CHECKDB et en réindexation toutes les nuits. Une mise hors ligne, ou un passage en lecture seule, retirerait tout cela du plan de maintenance.

Analyse des attentes

Sur les douze jours écoulés depuis le redémarrage, en retirant les attentes d’inactivité, le total est de 55 479 secondes réparties ainsi :

Ce profil ne désigne aucune ressource saturée, et c’est un résultat en soi. Il n’y a pas de pression processeur : la part de signal, c’est-à-dire le temps passé à attendre la libération d’un processeur, est de 6,5 % du total, alors que le seuil d’attention est vers 20 à 25 %. THREADPOOL cumule 450 millisecondes sur 489 attentes, pour 704 threads de travail configurés, donc il n’y a pas non plus de famine de threads. Il n’y a aucune attente CXPACKET, ce qui est la conséquence directe de MAXDOP à 1.

Deux lignes méritent qu’on s’y arrête.

WRITELOG en tête à 3,2 ms de moyenne n’est pas un problème de stockage. Le volume F: écrit à 3,0 ms, ce qui est correct. C’est le nombre qui frappe : 6,5 millions d’attentes, 8,2 millions de vidages de journal et 171,6 Go écrits dans les journaux en douze jours, pour 50,2 millions de transactions. La charge fait beaucoup de très petites transactions, et chacune paie son aller-retour sur le disque. C’est un motif applicatif, sur lequel je reviens ci-dessous.

PREEMPTIVE_OS_AUTHENTICATIONOPS en deuxième position est le signe le plus net de la collecte : 3,72 millions d’authentifications Windows en douze jours, soit environ trois par seconde en moyenne. Ce ne sont pas des requêtes, ce sont des ouvertures de connexion.

Les attentes de sauvegarde en troisième et quatrième position, avec 17,5 ms d’attente moyenne sur BACKUPIO, disent que la cible de sauvegarde est lente. Cela se voit aussi dans ASYNC_IO_COMPLETION : 227 attentes à 12,5 secondes chacune.

Les attentes de verrou restent modestes. LCK_M_X totalise 766 attentes pour 341 secondes, soit 445 ms de moyenne, ce qui est long par attente mais représente 0,6 % du temps. Sur ERPPROD, l’index IX_INVOICE_LINES_05 porte à lui seul 1 514 escalades de verrou et 21,5 secondes d’attente. C’est localisé sur la table INVOICE_LINES et cela mérite un coup d’œil, pas une alerte.

Optimisation des requêtes

Les connexions

Voici les requêtes les plus exécutées du mois sur ERPPROD, telles que le Query Store les compte :

La quatrième ligne est la clé. select usertype,type,name from systypes est une requête que les pilotes clients lancent une fois par connexion, pour construire leur table de correspondance de types. Elle a été exécutée 3,1 millions de fois en un mois.

En dessous, toute une famille de requêtes de démarrage de session tourne autour de 2 083 900 exécutions : lecture de la table des comptes, du profil utilisateur, du mot de passe, de la configuration du locataire, des dialogues client. Ce sont les mêmes chiffres à quelques unités près, donc c’est bien la même séquence, répétée à chaque ouverture.

Cela cadre exactement avec les 3,72 millions d’authentifications mesurées côté attentes, et avec 1,22 million d’appels à la validation de la politique de mot de passe. L’application ouvre et referme des connexions en continu, au lieu de les prendre dans un pool.

Chaque connexion coûte une authentification Windows, une négociation, puis une quinzaine de requêtes de contexte avant la première requête utile. Le gain est double : on retire ce trafic du serveur, et on retire au passage une bonne part des 13 496 plans préparés à usage unique qui occupent 2,7 Go de cache.

C’est un sujet pour l’éditeur plutôt que pour vous. Deux questions à lui poser : le pool de connexions est-il activé dans la configuration du connecteur, et si oui, y a-t-il quelque chose dans la chaîne de connexion qui empêche la réutilisation ? Un changement de contexte de base, un SET particulier ou une option d’usurpation d’identité suffisent à rendre chaque connexion unique aux yeux du pool.

Problèmes de SARGabilité

Ces requêtes sur la table ROUTES reviennent en boucle :

SELECT ROUTES.CROSS_DOCK FROM ROUTES
WHERE (ROUTES.EXTERNAL_REF LIKE '%4471102983'
  AND ROUTES.SHIP_TYPE IN ('A1','B1','A2','B2','C1')
  AND ROUTES.CONSIGNEE_ID NOT IN ('4010','4025','4088'))
  AND (ROUTES.TENANT_ID = 1)

Le LIKE commence par un caractère générique. Aucun index ne peut être utilisé pour une recherche, donc la table entière est parcourue à chaque fois. ROUTES compte 3 215 081 lignes pour 2 042 Mo.

Le coût mesuré : une variante à 592 exécutions consomme 1 836 secondes, soit 3,1 secondes par exécution, dont la quasi-totalité en processeur. Une autre variante à 416 exécutions en consomme 1 275. En cumulant les variantes du même motif présentes dans le Query Store, on dépasse les 3 300 secondes de processeur sur le mois, pour une requête qui ramène une seule colonne.

Notez aussi que chaque valeur recherchée produit une requête distincte, puisque le numéro est concaténé dans le texte au lieu d’être passé en paramètre. C’est une des sources du gonflement du cache de plans.

La correction est côté application. Si EXTERNAL_REF contient un identifiant composite dont vous ne cherchez que la fin, il faut soit stocker cette fin dans une colonne dédiée et l’indexer, soit stocker la valeur inversée et chercher avec un LIKE 'xxx%', qui lui est SARGable. Voir mon article sur le sujet : https://www.pachadata.com/docs/articles/sqlserver/performances/sargabilite/

Les gros rapports

Trois exécutions isolées dominent la consommation du mois :

La première a tourné une heure et quarante-cinq minutes le 21 août à 09:39, en pleine journée de travail, et a lu 219 millions de pages. La troisième est un SELECT * sans clause WHERE sur une table de 46 millions de lignes.

Sur les cinquante requêtes les plus coûteuses, les huit premières concentrent 54 % du processeur, et la première à elle seule 15,8 %.

Ces trois-là ressemblent à des extractions manuelles plutôt qu’à du trafic applicatif. Pouvez-vous me confirmer qu’il s’agit bien de requêtes lancées à la main, et par qui ? Si ce sont des exports récurrents, ils ont leur place la nuit, ou dans une base de rapport. Et le SELECT * FROM ORDER_LINES du 12 août mérite d’être expliqué à son auteur.

Structure des tables

C’est le constat le plus structurant de cette collecte, et c’est aussi celui sur lequel vous avez le moins la main.

Dans ERPPROD, les 482 tables sont des heaps. Aucune n’a de clé primaire. Dans ERPOLD, c’est 455 tables sur 455. Il n’y a pas davantage de clé étrangère ni de contrainte CHECK dans ces deux bases : les seuls objets de contrainte sont 2 241 et 1 800 valeurs par défaut. Il n’y a pas non plus une seule vue, procédure stockée, fonction ou trigger.

Autrement dit, le schéma est un ensemble de tables plates, l’intégrité référentielle est entièrement dans l’application, et tout le code SQL est envoyé par le client. C’est un choix d’éditeur, sans doute hérité d’un portage depuis un autre moteur, et vous ne le changerez pas par une commande. Mais il faut le connaître, parce qu’il explique presque tout le reste.

Forwarded records

Quand on met à jour une ligne d’un heap et que la nouvelle version ne tient plus dans sa page, SQL Server laisse un pointeur à l’ancien emplacement et déplace la ligne. Le pointeur s’appelle un forwarded record. Chaque lecture par les index non-clustered suit alors deux sauts au lieu d’un, et ces sauts ne se résorbent jamais tout seuls.

Sur ERPPROD, le compteur totalise 225 687 712 lectures de forwarded records depuis le 15 août, réparties sur 18 heaps. À elle seule, dbo.ORDER_LINES en porte 198 152 342. Elle compte 46 464 519 lignes, dont 1 238 500 sont actuellement déplacées, soit 2,67 %.

Ces compteurs se lisent dans un seul sens. Un chiffre élevé est fiable, rien ne le gonfle. Un chiffre bas ne prouverait rien, parce qu’ils repartent de zéro sans prévenir. Ici c’est 225 millions en douze jours, il n’y a pas d’ambiguïté.

Le remède immédiat est une reconstruction du heap, qui replace les lignes et supprime les pointeurs :

ALTER TABLE dbo.ORDER_LINES REBUILD

Deux réserves à connaître. En Standard Edition, cette opération est hors ligne : la table est verrouillée pendant toute sa durée, et il s’agit de 15 Go. Elle reconstruit aussi tous les index non-clustered de la table, ce qui rallonge d’autant. Il faut donc la planifier sur une fenêtre d’arrêt, et la mesurer sur une copie avant de la lancer en production.

Surtout, ce n’est pas définitif. Sans index clustered, les forwarded records vont se reformer au rythme des mises à jour, et il faudra recommencer. Il faut donc soit ajouter cette reconstruction au plan de maintenance pour les gros heaps, soit poser la question à l’éditeur : est-ce que l’ERP supporte qu’on crée un index clustered sur ses principales tables ? La réponse est souvent non pour des raisons de support, mais elle vaut la peine d’être demandée, parce que c’est la seule solution qui tienne dans la durée. Nous pouvons en parler.

Indexation

Une réserve avant tout chiffre

L’instance a redémarré il y a 11,9 jours et les statistiques d’usage des index sont reparties de zéro à ce moment-là. La fenêtre ne couvre aucune clôture mensuelle. Tout ce qui suit sur les index inutilisés est donc une liste de candidats à confirmer, pas une liste de suppressions. Il faut refaire le relevé après trente jours pleins d’activité avant de supprimer quoi que ce soit.

Index maintenus et jamais lus

Sur les 711 index non-clustered d’ERPPROD, 43 n’ont servi aucune lecture tout en absorbant des écritures. Ils pèsent 2 828 Mo. Les principaux :

Ceux-là sont maintenus, ils sont réindexés toutes les nuits, ils occupent le buffer pool, et ils n’ont jamais servi une seule lecture en douze jours. Le premier a subi 5,8 millions de mises à jour pour rien.

498 autres index n’ont aucune ligne dans les statistiques d’usage, ce qui n’est pas la même chose. Ils n’ont été ni lus ni écrits, donc leur table ne sert peut-être plus du tout. Ils ne pèsent que 124 Mo au total, c’est une question de ménage plutôt que de performance.

Le préfixe IX_LEGACY_* sur des index d’ERPPROD est intrigant. Est-ce que ces index viennent d’une reprise depuis l’ancienne base, ou d’une fonctionnalité d’interface qui n’est plus utilisée ?

Index en doublon

Sept paires d’index d’ERPPROD ont exactement les mêmes colonnes de clé, et dix autres paires se recouvrent par le préfixe. Sur ERPOLD, cinq et six.

Dans chaque paire, l’un des deux est unique et l’autre ne l’est pas. Le non unique est celui qu’on garde le moins volontiers, puisqu’il n’apporte ni contrainte ni sélectivité supplémentaire. Là encore, l’accord de l’éditeur est nécessaire avant de toucher à des index qu’il a créés.

Index manquants

L’optimiseur a enregistré 144 suggestions sur ERPPROD, dont plusieurs avec un impact estimé à 100 %.

Le motif TENANT_ID, EXTERNAL_KEY revient sur quatre tables différentes avec un impact de 100 %. C’est une recherche par clé externe, sans doute une interface d’échange, et elle parcourt aujourd’hui des tables de plusieurs millions de lignes. Celle-là vaut la peine d’être créée.

Attention en revanche à ne pas appliquer les suggestions telles quelles. Le moteur propose un index par forme de requête et il n’en fusionne jamais deux : sur PARCELS, deux suggestions ne diffèrent que par une liste de colonnes incluses qui reprend presque toute la table. Il faut consolider avant de créer, et créer peu.

Sur PARCELS, la version raisonnable ressemble à ceci, à valider avec l’éditeur :

CREATE NONCLUSTERED INDEX IX_PARCELS_TENANT_EXTKEY
ON dbo.PARCELS (TENANT_ID, EXTERNAL_KEY)

Tailles, types de données et compression

Compression

Rien n’est compressé. Sur ERPPROD, 1 192 des 1 193 unités de stockage sont en compression NONE, pour 52 285 Mo réservés et 645 millions de lignes. Sur ERPOLD, c’est 1 082 sur 1 082 pour 1 848 Mo. Aucune table n’est partitionnée.

La compression de données est disponible en Standard Edition depuis SQL Server 2016 SP1, donc vous y avez droit. Sur des tables de ce type, la compression ROW donne couramment 20 à 40 % et la compression PAGE davantage, au prix d’un peu de processeur à la lecture. Comme le processeur n’est pas la ressource contrainte ici, et que les entrées/sorties le sont, l’échange est favorable.

Les candidats, par taille :

Il faut chiffrer avant d’agir. La collecte n’a pas exécuté l’estimation, parce que c’est une opération coûteuse qui n’est pas faite par défaut :

EXEC sp_estimate_data_compression_savings 'dbo', 'ORDER_LINES', NULL, NULL, 'PAGE'

Bonne nouvelle au passage : sur les heaps, la compression s’applique par le même ALTER TABLE ... REBUILD qui supprime les forwarded records. Les deux opérations se font en une seule fenêtre.

ALTER TABLE dbo.ORDER_LINES REBUILD WITH (DATA_COMPRESSION = PAGE)

Une exception à surveiller : dbo.ROUTES est la seule table qui mélange déjà deux réglages de compression, NONE et ROW selon les unités de stockage. Quelqu’un a commencé quelque chose. Savez-vous qui, et pourquoi ça s’est arrêté ?

Archivage

Quinze tables d’ERPPROD dépassent le million de lignes, pour 51,4 Go au total. Elles portent toutes des colonnes de type datetime, donc l’ancienneté des données est mesurable.

dbo.ORDER_LINES, avec ses 46,5 millions de lignes et ses 23,5 Go index compris, est à elle seule la moitié de la base. dbo.APP_JOB_HISTORY compte 1 531 402 lignes et porte un nom d’historique. Aucun travail de purge applicatif n’existe dans l’Agent : le seul qui purge quelque chose ne touche que msdb.

Jusqu’à quand faut-il conserver les lignes d’ORDER_LINES ? Si une rétention métier existe, la purge ou le partitionnement rendraient à la fois de l’espace et du temps de sauvegarde et de CHECKDB. Il faudrait commencer par mesurer l’étalement réel :

SELECT YEAR(CREATED_AT) AS annee, COUNT(*) AS lignes
FROM dbo.ORDER_LINES WITH (READUNCOMMITTED)
GROUP BY YEAR(CREATED_AT) ORDER BY annee

Vous avez aussi 63 tables vides sur les 200 plus grosses d’ERPPROD, et 100 sur les 200 d’ERPOLD. Aucune ne porte de nom suspect, donc ce sont sans doute des tables du modèle applicatif jamais alimentées. À quoi servent-elles ?

Types de données

Sur les 5 480 colonnes relevées dans ERPPROD, il y a 566 colonnes datetime et 449 colonnes float. Aucune colonne text, ntext ou image, ce qui est déjà une bonne chose, et pratiquement pas d’Unicode : 14 colonnes seulement.

Le float m’intrigue. Les textes de requêtes du Query Store montrent des paramètres @P1 float sur ce qui ressemble à des montants et des poids. Un float est un type approché : deux additions successives ne donnent pas forcément le même résultat qu’une somme, et une comparaison d’égalité peut échouer sur des valeurs qu’on croit identiques. Pour des montants, c’est decimal qu’il faut. Là encore, c’est le schéma de l’éditeur, mais la question mérite d’être posée s’il y a déjà eu des écarts de centimes.

Le datetime est le vieux type, avec sa résolution de 3,33 millisecondes et sa plage à partir de 1753. datetime2 fait mieux et occupe moins de place. Ce n’est pas grave, c’est un point de modernisation pour le jour où l’éditeur reprend son schéma.

Plans de maintenance

Trois travaux existent dans l’Agent, tous à jour et sans échec : 54 exécutions en 30 jours, zéro échec. Ils s’appuient sur les scripts d’Ola Hallengren, ce qui est le bon choix.

Le CHECKDB tourne toutes les semaines sur les trois bases et n’a rien trouvé, ni le 16 ni le 23 août. Il n’y a aucune page suspecte enregistrée. De ce côté-là tout va bien.

Recalcul des statistiques

C’est le point à corriger dans ces plans.

Le recalcul passe par sp_MSforeachdb et sp_updatestats. Cette procédure fait son travail, mais elle laisse le taux d’échantillonnage par défaut, que le moteur calcule en fonction de la taille de la table. Sur dbo.ORDER_LINES, le résultat est un échantillonnage à 0,58 %. Les statistiques d’une table de 46 millions de lignes sont donc construites sur 270 000 lignes tirées au hasard.

Sur une table aussi grosse et aussi mise à jour, ce n’est pas suffisant. Les histogrammes ratent la distribution réelle, l’optimiseur estime mal, et on retrouve les plans à 219 millions de lectures logiques vus plus haut.

Vous avez déjà IndexOptimize en place, et il sait recalculer les statistiques avec un taux choisi. Il vaut mieux lui confier les deux travaux plutôt que de faire tourner sp_updatestats derrière :

EXECUTE dbo.IndexOptimize
  @Databases = 'ALL_DATABASES',
  @Indexes = 'ALL_INDEXES',
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y',
  @StatisticsSample = 100,
  @Execute = 'Y', @LockTimeout = 1800, @TimeLimit = 5400

Un échantillonnage à 100 % sur toutes les bases serait probablement trop long dans la fenêtre disponible. Commencez par mesurer la durée sur ERPPROD seule, et si c’est trop, réservez le FULLSCAN aux plus grosses tables et laissez le défaut ailleurs.

Il y a un signe corroborant dans les attentes : WAIT_ON_SYNC_STATISTICS_REFRESH totalise 720 secondes sur 2 636 attentes, soit 273 ms payés à l’intérieur de requêtes utilisateur pendant que le moteur recalcule une statistique périmée. Passer ERPPROD en recalcul asynchrone déplacerait ce coût hors du chemin de la requête :

ALTER DATABASE [ERPPROD] SET AUTO_UPDATE_STATISTICS_ASYNC ON

Défragmentation

IndexOptimize tourne tous les jours avec ses seuils par défaut, 5 % et 30 %, et une limite de temps de 5 400 secondes. Il en a utilisé 2 701 la dernière fois. Il travaille donc correctement et sans excès : seuls cinq index non-clustered dépassent 30 % de fragmentation dans le relevé, sur de petits volumes.

Une précision de lecture, parce que le relevé peut induire en erreur. Vingt heaps apparaissent à 99,6 ou 99,8 % de fragmentation. Ce chiffre vient d’un scan en mode LIMITED, où la mesure de fragmentation ne veut rien dire pour un heap : le scan SAMPLED des mêmes objets donne 0 %. Ce n’est donc pas ce chiffre qu’il faut regarder pour les heaps, mais le nombre de forwarded records, traité plus haut.

Le vrai manque est que IndexOptimize ne traite pas les heaps. Comme toutes vos tables en sont, la maintenance quotidienne s’occupe des index et laisse les tables elles-mêmes de côté.

Notifications d’échec

Aucun des trois travaux n’a de notification configurée : ni journal des événements, ni courriel, ni net send, ni pager. Et Database Mail XPs est à zéro, donc la messagerie de base de données n’est même pas activée sur l’instance.

Aujourd’hui les travaux réussissent, donc cela ne se voit pas. Le jour où le CHECKDB remonte une corruption ou où la sauvegarde échoue, personne ne l’apprendra par le serveur.

C’est le genre de chose qu’on corrige en une demi-heure et qu’on ne regrette jamais. Il faut configurer Database Mail, créer un opérateur, et le déclarer sur les trois travaux. Il faudrait aussi créer les alertes sur les gravités 19 à 25 et sur les erreurs 823, 824 et 825, qui signalent les problèmes de cohérence d’entrées/sorties. La collecte ne remonte pas les alertes existantes, donc je ne peux pas dire s’il y en a. Y a-t-il une supervision externe qui surveille l’Agent, auquel cas le sujet se pose autrement ?

Sécurité

Le tableau général est sain. Toutes les options de surface d’attaque sont fermées : xp_cmdshell, les procédures OLE Automation, les requêtes distribuées ad hoc, le CLR, le chaînage de propriété entre bases, les scripts externes. Et clr strict security est à 1, ce qui est la bonne valeur. Les comptes de service du moteur et de l’Agent sont des comptes de service gérés de domaine, distincts l’un de l’autre.

Trois points à regarder.

Le login SQL reporting_user est membre du rôle de serveur sysadmin. Ce n’est pas un compte de service, c’est un login SQL nominatif ou applicatif. Vous avez déjà deux groupes Active Directory dans sysadmin, CORP\GRP-SQL-Management et CORP\GRP-SQL-Admins-PROD, ce qui devrait suffire pour les administrateurs. À quoi sert ce compte, et a-t-il vraiment besoin de tous les droits ?

Le login SVC-INTERFACE-PROD a été créé sans vérification de la politique de mot de passe : l’option CHECK_POLICY est à OFF. Son mot de passe date de mars 2022 et rien ne garantit sa complexité. Aucun des cinq logins SQL n’a d’expiration de mot de passe activée.

Le compte sa est activé, il porte toujours son nom, et son mot de passe a été posé en avril 2021. L’authentification mixte est active sur l’instance. Si aucune application n’utilise sa, désactivez-le. Sinon, changez au moins le mot de passe et notez la date.

ALTER LOGIN [sa] DISABLE

Le chiffrement transparent des données n’est activé sur aucune base. Cela se juge selon la sensibilité des données d’ERPPROD et selon l’exposition physique des volumes, dont la collecte ne dit rien. Si le stockage est sur une baie déjà chiffrée au repos, il n’y a pas grand-chose à ajouter.

Supervision

Le dispositif de capture est en place. Le seuil de processus bloqué est à 10 secondes, et une session d’événements étendus « Blocked process » démarre avec l’instance et écrit dans D:\Extended events\Blocked process.xel. Une session « Deadlocks » fait de même. Deux rapports de processus bloqué ont été enregistrés en douze jours, les 19 et 26 août, et aucun deadlock.

Les fichiers existent donc et personne ne les lit. Ce sont exactement les deux occurrences qu’il faudrait ouvrir pour savoir ce qui bloque INVOICE_LINES. Je peux les regarder si vous me les envoyez, ou la prochaine collecte peut les embarquer avec les options qui n’ont pas été passées cette fois.

Un agent de supervision tourne déjà sur l’instance : je vois ses requêtes de compteurs de performance dans le cache de plans, exécutées 376 fois dans l’heure qui a précédé la collecte. Lequel est-ce, et qu’est-ce qu’il remonte aujourd’hui ? S’il collecte déjà les compteurs système, il devrait pouvoir alerter sur la latence des fichiers de tempdb, qui est le point à surveiller en priorité.

Enfin, deux traces de connexions ratées dans les ring buffers : une erreur 18456 en état 8, c’est-à-dire un mot de passe erroné, depuis 192.0.2.45 le 19 août, et vingt ruptures de socket depuis 192.0.2.118 entre le 15 et le 16 août. Deux occurrences en douze jours, ce n’est pas une attaque. Mais les échecs de connexion sont bien journalisés, donc une alerte sur l’erreur 18456 serait facile à ajouter au même dispositif.

Ce qui reste à regarder

Sept requêtes du corpus n’ont pas été exécutées lors de cette collecte, parce que leurs options ne sont pas actives par défaut. Trois d’entre elles porteraient directement sur des points ouverts ci-dessus, et il faudrait les passer au prochain tour :

• --include-blocked-process-reports et --include-deadlock-graphs, pour lire les deux rapports de blocage plutôt que de savoir seulement qu’ils existent ;
• --estimate-compression, pour chiffrer le gain de compression sur les six grosses tables au lieu de l’estimer.

Il reste aussi à regarder, en dehors de l’archive : la configuration du connecteur applicatif, pour le pool de connexions ; la nature du stockage derrière le volume G:, avec l’équipe qui l’exploite ; et les alertes de l’Agent, que la collecte ne remonte pas.

Enfin, le relevé d’usage des index est à refaire dans trente jours, si l’instance ne redémarre pas d’ici là. C’est la condition pour transformer la liste des 43 index jamais lus en liste de suppressions.

Compte rendu produit à partir d’une collecte sql-auditor, github.com/rudi-bruchez/sql-auditor. Rudi Bruchez, expert SQL Server, pachadata.com