Pourquoi les réglages par défaut posent problème
SQL Server doit pouvoir s'installer aussi bien sur un portable de développeur que sur un serveur de 64 cœurs. Ses réglages par défaut privilégient donc la compatibilité, et certains n'ont presque pas changé depuis la fin des années 1990. Microsoft ne peut pas les modifier sans risquer de perturber des millions d'installations existantes : c'est à l'équipe qui exploite le serveur de les ajuster.
Or beaucoup d'instances ont été installées par un intégrateur, un éditeur ou un administrateur système pressé, qui a validé l'assistant sans revenir sur ces paramètres. Ce sont les premiers points que nous contrôlons lors d'un audit SQL Server, parce qu'ils se corrigent en quelques minutes et profitent à toutes les applications de l'instance.
1. La mémoire maximale n'est pas plafonnée
Par défaut, le paramètre max server memory vaut 2 147 483 647 Mo : autrement dit, aucune limite. SQL Server prend alors toute la mémoire disponible, et le système d'exploitation, l'antivirus ou l'agent de sauvegarde se retrouvent à court. Résultat : pagination, lenteurs inexpliquées, et parfois un serveur qui ne répond plus en pleine charge.
À faire : laisser au système au moins 4 Go ou 10 % de la mémoire, selon ce qui est le plus élevé, et davantage si d'autres services tournent sur la machine. Depuis SQL Server 2019, l'assistant d'installation propose une valeur recommandée, mais une instance mise à niveau conserve son ancien réglage.
2. Le seuil de parallélisme est resté à 5
Le paramètre cost threshold for parallelism fixe le coût estimé à partir duquel une requête peut être exécutée en parallèle sur plusieurs cœurs. Sa valeur par défaut, 5, a été calibrée pour le matériel de 1998. Aujourd'hui, presque toutes les requêtes dépassent ce seuil : de petites requêtes sont découpées sur plusieurs threads, passent plus de temps à se coordonner qu'à travailler, et monopolisent des cœurs dont d'autres requêtes auraient besoin.
À faire : partir de 50, puis ajuster en observant le coût des requêtes réelles dans le Query Store ou le cache des plans.
3. Le degré de parallélisme n'est pas limité
Avec max degree of parallelism à 0, valeur par défaut des versions antérieures à 2019, une seule requête peut utiliser tous les cœurs du serveur. Sur une machine de 32 ou 64 cœurs, un rapport un peu lourd suffit à faire attendre toutes les autres sessions.
À faire : suivre la recommandation de Microsoft, soit au plus 8, et au plus le nombre de cœurs par nœud NUMA. La valeur exacte se valide ensuite avec la charge réelle.
4. tempdb est sous-dimensionnée
tempdb sert à tous les tris, jointures par hachage, tables temporaires et versions de lignes. Avec un seul fichier de données, les sessions se disputent les pages d'allocation et des attentes PAGELATCH apparaissent. Depuis SQL Server 2016, l'installation crée plusieurs fichiers, mais les instances mises à niveau depuis une version plus ancienne, ou retouchées à la main, ont souvent un seul fichier, des fichiers de tailles différentes ou une croissance en pourcentage.
À faire : un fichier de données par cœur logique, jusqu'à 8, tous de même taille et avec la même croissance fixe, idéalement sur un stockage rapide dédié.
5. Les plans d'exécution à usage unique encombrent la mémoire
Sans l'option optimize for ad hoc workloads, SQL Server conserve le plan complet de chaque requête, même de celles qui ne seront jamais réexécutées. Avec un ORM ou beaucoup de SQL dynamique, le cache se remplit de milliers de plans inutiles, au détriment des données.
À faire : l'activer. Le plan complet n'est alors mis en cache qu'à la deuxième exécution d'une requête. C'est sans risque sur la quasi-totalité des instances.
6. Auto-shrink et auto-close sont activés
L'option AUTO_SHRINK réduit automatiquement les fichiers d'une base quand elle a de l'espace libre. Chaque réduction désorganise les index, la maintenance suivante les reconstruit et fait regrossir le fichier, puis la réduction recommence : de l'I/O dépensée pour rien. Elle est désactivée par défaut, mais nous la retrouvons régulièrement activée. AUTO_CLOSE, activée par défaut sur les bases créées avec l'édition Express, ferme la base dès la dernière déconnexion : la connexion suivante paie un redémarrage complet, cache vide.
À faire : désactiver les deux sur toute base de production.
7. Le mode de récupération ne correspond pas aux sauvegardes
Une nouvelle base hérite du mode de récupération de la base système model, en général FULL sur les éditions Standard et Enterprise. En mode FULL, sans sauvegardes régulières du journal, le fichier journal grossit jusqu'à remplir le disque. Le réflexe courant est alors de basculer la base en SIMPLE, ce qui règle le problème de place mais supprime toute possibilité de restaurer à un instant précis : après un incident à 15 h, on repart de la sauvegarde de la nuit.
À faire : décider base par base de la perte de données acceptable (RPO), puis aligner le mode de récupération et la fréquence des sauvegardes du journal sur cet objectif.
8. Aucune maintenance n'est planifiée
Par défaut, SQL Server ne sauvegarde rien et ne vérifie jamais l'intégrité des bases. Sans travaux planifiés, une corruption peut rester silencieuse pendant des mois, jusqu'à contaminer toutes les sauvegardes conservées.
À faire : planifier les sauvegardes complètes, différentielles et du journal, un DBCC CHECKDB régulier, la mise à jour des statistiques, et tester une restauration complète au moins une fois par trimestre. Une sauvegarde jamais restaurée n'est qu'une hypothèse.
Le script pour tout contrôler
Ces requêtes sont en lecture seule et peuvent être lancées sans risque en production :
/* 1. Réglages de l'instance */
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'cost threshold for parallelism',
'max degree of parallelism', 'optimize for ad hoc workloads');
/* 2. Fichiers de données de tempdb (nombre, taille, croissance) */
SELECT name, size * 8 / 1024 AS taille_mo, growth, is_percent_growth
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';
/* 3. Options des bases utilisateur */
SELECT name, recovery_model_desc, is_auto_shrink_on, is_auto_close_on
FROM sys.databases
WHERE database_id > 4;
/* 4. Dernières sauvegardes complètes et du journal */
SELECT d.name, d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS derniere_complete,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS dernier_journal
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;Et, une fois les valeurs validées pour votre serveur, les corrections de l'instance :
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 57344; /* exemple : serveur de 64 Go */
EXEC sp_configure 'cost threshold for parallelism', 50; /* point de départ */
EXEC sp_configure 'max degree of parallelism', 8; /* selon cœurs et nœuds NUMA */
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;Ces changements ne demandent pas de redémarrage, mais certains vident le cache des plans : appliquez-les en dehors des heures de pointe, un par un, en notant l'état avant et après. Les valeurs ci-dessus sont des points de départ, pas des vérités universelles : un serveur mutualisé, une instance d'entrepôt de données ou une machine virtuelle très contrainte appellent d'autres réglages.
Au-delà des réglages
Ces huit points ne sont qu'une partie de ce que nous passons en revue. Un audit complet couvre aussi les performances réelles, la sécurité, la haute disponibilité et le cycle de vie de vos versions, avec un rapport priorisé et les scripts de correction : voir notre offre d'audit SQL Server. Et si vos utilisateurs se plaignent déjà de lenteurs, commencez par notre méthode pour trouver la vraie cause d'un SQL Server lent.
