SQL Server Performances – CHAPITRE 2 – Faut-il paramétrer, régler, tuner son instance ?

Voici une série d’article comportant 10 chapitres destinés à tout ceux qui veulent obtenir de meilleurs performances avec SQL Server… C’est à l’occasion des JO de paris, que j’ai eu l’idée de cette série d’articles sur les performances… Ou plus exactement comment remédier aux mauvaises performances de vos bases de données avec SQL Server, lorsque c’est le cas…

Chapitre précédent : CHAPITRE 1 – SQL Server, gestion de la performance… et la VM dans tout ça ?

L’installeur de SQL Server permet de régler quelques paramètres comme le niveau de parallélisme ou la mémoire maximale utilisable par l’instance. Mais il en existe bien d’autres et certains peuvent être très utile pour soulager le serveur et augmenter les performances…

0 – L’instance SQL Server

Comme tous les SGBD qu’ils soient Relationnels ou non, SQL Server utilise énormement les ressources de la machine. Il consomme :

du disque du fait du stockage d’importantes masses de données et surtout de la persistance même en cas de crash;
de la mémoire car les données sont manipulées exclsusivement en mémoire (cache) pour que les temps de réponse soient acceptables;
du processeur, car beacoup de manipulation de données dans les requêtes nécessiste des calculs de comparaisons, d’ordonnancement de groupage;

Sans parler de la forte sollication du réseau lorsqu’il existe de très nombreux utilisateurs en concurrence…

En conséquence SQL Server devrait être installé sur une machine dédié et aucune autre application ne doit tourner dans on entourage, pas même un antivirus…

0.1 – De multiples instances ?

Bien qu’il soit possible de faire tourner de multiples instances sur une même machine cela n’est pas conseillé en production. Sinon, il convient d’isoler et de limiter les ressources…
L’isolation étant possible pour :

les CPU via les masques d’affinité (voir ci-après);
la mémoire via le paramètre « max server memory » en utilisant la procédure sp_configure (voir ci-après)
les disques en mettant chaque instance (voire chaque base de données) sur un disque physique différent (voir le chapitre consacré au stockage).

En tout état de cause il n’y a aucun intérêt à avoir plusieurs instances avec plusieurs versions de SQL Server. Une instance SQL Server étant multiversion, vous pouvez avoir des bases tournant en version 2008, 2008R2, 2012, 2014, 2016, 2017, 2019, 2022…
Cela vous fera faire de grandes économies sachant que la moindre instance de SQL Server nécessite 4 coeurs au minimum et qu’à ce tarif il vaut mieux mutualiser sur un seul serveur plutôt que d’avoir de nombreuses instances… D’autant qu’on y gagne en administration, surveillane et tuning…

Reste que bien des éditeurs veulent avoir un serveur dédié, mais ceci n’a aucun sens car vous pouvez isolez vos différentes bases par de multiples moyens, par exemple utiliser les bases de données autonomes (CONTAINED DATABASE) pour résoudre les problématiques de divergence des collations, ou le gouverneur de ressources pour cloisonner des quotas de disque, CPU ou RAM…

0.2 – Des services applicatifs ?

Bien entendu aucun service applicatif ni autre serveur (web par exemple) ne doit s’exécuter sur la machine dédié à l’instance SQL Server, ni aucune application cliente et surtout graphique comme SSMS ou le bureau à distance, maladie que l’on trouve régulièrement dans certaines entreprises du fait des développeurs. Un service de base de données est là pour écouter des requêtes et renvoyer des jeux de données, pas pour faire de la peinture. Et le couple RDP + SSMS coute souvent plusieurs Go de RAM !

0.3 – Un antivirus ?

Là aussi on ne devait pas mettre d’antivirus sur un serveur de bases de données. Quand je dis cela à mes clients, ils écarquillent les yeux et me tancent ouvertement en criant au fou… Sauf que cela est parfaitement possible si vous placez votre serveur SQL dans une DMZ avec une seule machine d’accès (une VM c’est ideal que l’on éteint après usage) avec SSMS dessus… Dans le cas contraire, la liste des exclusions nécessaire à ce que l’antivirus ne pénalise pas le serveur de base de données est longue, longue, longue…. À moins d’opter pour l’antivirus de Microsoft qui règle toute seul ce genre de problèmes et s’avère l’un des meilleurs à ce jour…

0.4 – Des services à désactiver ?

Oh que oui ! Malheureusement il n’est pas simple de savoir lesquels sont inutiles pour faire fonctionner SQL Server et Windows… Mais à l’évocation de leurs noms on se doute bien que certains n’offrent aucun intérêt à rester actifs, comme la biométrie, le spouleur d’impression ou le bluetooth !

1 – Les paramètres d’instance

Ils sont lisible dans le vue système sys.configurations. J’utilise la requête suivante pour les lire :

SELECT * 
FROM   sys.configurations 
ORDER  BY name;

SELECT *

FROM sys.configurations

ORDER BY name;

Et la procédure

sp_configure

1	sp_configure

Seule façon de les modifier.

ATTENTION : par défaut vous ne voyez pas tous les paramètres de configuration. Pour les voir tous et pouvoir les modifier, lancez les commandes suivantes :

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

1 2	EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

Désormais si vous lancez la procédure sp_configure comme suit :

EXEC sp_configure;

1	EXEC sp_configure;

Alors la liste est complète et vous pouvez modifier les paramètres avancés.

NOTE : certains paramètres de configuration ne concernent pas les performances et ne sont par conséquent pas mentionnés dans cet article. D’autres sont inactifs car obsolètes, mais conservé pour des raisons de rétrocompatibilité.

1.1 – Paramètres pour la récupération accélérée (nouveau en 2022) :

La récupération d’une base de données se produit au moment ou l’instance redémarre.
Du fait de l’écriture asynchrone des données (tables et index) dans les fichiers, en cas d’arrêt d’une instance, soit programmée, soit brutal, certaines transactions finalisées en mémoire n’ont pas reporté les nouvelles informations de la mémoire aux disques.
Il faut alors les reconstituer depuis le fichier du journal de transactions, qui, lui, est écrit de manière synchrone en reportant les anciennes valeurs depuis les fichiers de données et en réappliquant les transactions présente dans le journal.
Les nouvelles valeurs des dernières transactions, sont alors remise en mémoire cache.
Pour informations, ces informations stockées qui sont stockées dans des pages de manière identique en mémoire et sur le disque, sont appelées « pages sales » (dirty pages) alors qu’elles contiennent les bonnes données, mais que le report dans les fichiers des tables et index n’a pas encore été effectué.

Cette phase de récupération des bases de données peut prendre plus ou moins de temps.
Pour en minimiser la durée SQL Server permet d’agir en parallèle en dédiant un certain nombre de threads et de la mémoire…
Ces réglages portent sur la quantité de thread, la mémoire et la limite de durée (timeout)…
Le mécanisme de récupération accélérée en parallèle s’appelle Multi-Threaded Version Cleanup (MTVC).

Paramètres modifiables :

ADR Cleaner Thread Count : nombre de thread agissant en parallèle pour la récupération accélérée : ne pas dépasser le nombre de bases de données actives en production, ni le nombre de cœurs de la machine. Un bon réglage devrait être compris entre 50 et 75 % des cœurs. Valeur par défaut 1.

ADR Preallocation Factor : nombre de blocs de 512 pages (4 Mo) alloués en mémoire cache pour contenir les données les versions de modification des opérations du DML. Un bon réglage doit tenir compte de la contention mémoire. Si l’instance dispose de beaucoup de mémoire et que cette mémoire se trouve peu utilisée, alors augmentez cette valeur sera bénéfique. Autrement cela peut s’avérer contre performant et le journal des événements montrer des entrées avec le tag « PreallocatePVS« . valeur par défaut 4.

ADR cleaner retry timeout (min) : le « cleaner » est un processus asynchrone de nettoyage de versions des pages. Ce processus sort de veille régulièrement pour dégager du cache les pages qui ne sont devenues inutiles. Ceci afin d’éviter au moment de la récupération de parcourir trop de pages non nécessaire. Ce nettoyeur doit acquérir un verrou exclusif qui peut être en conflit avec des utilisateurs concurrent. D’où le timeout nécessaire pour abandonner provisoirement son action. Valeur par défaut de 15 minutes.

ADR signifiant Accelerated Database Recovery…

1.2 – Masques d’affinité

Les paramètres suivants :

affinity mask
affinity64 mask
affinity I/O mask
affinity64 I/O mask

Permettent de sélectionner un masque d’affinité au niveau des cœurs a utiliser pour une instance, soit pour la mémoire (moteur logique : affinity mask / affinity64 mask), soit pour l’accès au stockage persistant (moteur physique affinity I/O mask / affinity64 I/O mask).

Normalement aucun paramétrage ne doit être fait à ce niveau, et vos instances SQL Server de production doivent être dédiées, ce qui signifie qu’aucune autre instance SQL Server ne doit tourner à côté et qu’une autre service, sauf ceux du système ne doit être actif. Cela exclue bien entendu toute application qu’elle soit sous forme d’exécutable ou de DLL.

NOTE : ces réglages concerne jamais que les 64 premiers processeurs et un même processeur (numéroté de 0 à 63), ne dois jamais être attribué aux deux moteurs. Dans le cas de machine de plus de 64 cœurs, utilisez le réglage SET PROCESS AFFINITY de la commande ALTER SERVER CONFIGURATION à la place de sp_configure.

Figure 01 – Une machine avec 72 cœurs, seuls les 64 premiers peuvent être attribués à la RAM ou aux disques

1.3 – NUMA

Paramètre : automatic soft-NUMA disabled

Qu’est-ce que NUMA ?
Dans mon livre SQL Server 2014, je donnais cette explication :
Autrefois, lorsque les serveurs (machine physique) avaient peu de CPUs et que la notion de cœur n’existait pas encore, l’utilisation de la mémoire était uniforme. Chaque CPU accédait à la totalité de la RAM
Avec l’augmentation du nombre de CPUs et de cœurs sont apparu de plus en plus fréquemment des conflits d’accès aux pages mémoire (deux cœurs distinct tentant d’accéder à la même page en mémoire). […]
Les constructeurs d’ordinateurs ont alors réagit en proposant un accès non uniforme à la mémoire (Non Uniform Memory Access : NUMA), c’est à dire dédier des pans entiers de la mémoire à des processeurs par affinité. En général l’alignement se fait au niveau de la notion de CPUs.

Soft NUMA est une version logique de NUMA qui a été introduite parce que les processeurs ont de plus en plus de cœurs… Dès que SQL Server détecte plus de 8 cœurs physiques, il active le processus soft NUMA qui permet d’agir comme un NUMA physique afin de diviser la mémoire cache en différentes zones avec une affinité d’exclusivité des cœurs aux partitions de la mémoire…

Figure 02 – Partitionnement Soft NUMA sur une machine à 72 cœurs. 4 Partitions sont créées malgré un unique processeur à 36 cœurs physiques

Mais vous pouvez choisir de désactiver Soft NUMA par masochisme !

Voir § 2.3.

Petite anecdote, Soft NUMA a été développé par Microsoft dans ses labos pour pouvoir effectuer des test de SQL Server à partir de la version 2008 R2 (2010) dans des conditions extrêmes… mais les ingénieurs se sont vite aperçu des bienfaits au niveau des performances qu’apportait cette solution qui sera implantées à partir de le version 2016…

1.4 – Accélération matérielle

Depuis la version 2022 de SQL Server il est possible de décharger le calcul de certaines charges de travail (compression, décompression, chiffrement…) sur des périphériques matériels afin d’en accélérer l’exécution. Encore faut-il que votre machine possède de tels dispositifs particuliers…

Ainsi, via certains processeur Intel (famille « scalable » et D), le système Quick Assist Technology permet d’accélérer la compression/décompression des sauvegardes SQL Server. Pour cela 3 paramètres sont disponibles :

hardware offload config : paramètre de configuration de l’accélération matérielle
hardware offload enabled : activation de l’accélération matérielle
hardware offload mode : mode d’activation de l’accélération matérielle

Notez que ces paramètres sont encore mal documentés. Le premier semble indiqué quel outil est utilisé pour cette accélération matérielle (à ce jour seul Intel QAT existe…). Ignorez-le. Le second est important et indique si vous voulez ou non, utiliser cette accélération matérielle (0 = non, 1 = oui). Le troisième semble indiquer le mode d’accélération (0 devrait être « indifférent », 1 devrait être « soft » et 2 devrait être « matériel »). Mais tout ceci est à confirmer…

ATTENTION : il faut déjà commencer par installer Intel QAT, si votre processeur en supporte la technologie si vous voulez utiliser l’accélération matérielle.

NOTA : actuellement en virtualisation cette technologie n’est supportée que par Microsoift Hyper-V et non par VMWare…

Voir § 2.5 pour la mise en œuvre.

1.5 – Sauvegardes

La technique de sauvegarde à chaud de SQL Server est extrêmement rapide et non bloquante car elle consiste à copier binairement page par page des données des fichiers de la base en évitant les pages vides, puis la portion du journal des transactions pour les opérations d’écriture ayant eu lieu pendant la sauvegarde. En comparaison, certains SGBDR comme MySQL/MariaDB ou PostGreSQL ne sont pas capable de réaliser une sauvegarde de cette manière car ils ne gèrent pas le stockage directement dans le moteur SQL (ce que fait SQL Server) et par conséquent délèguent à la couche OS les opérations de bas niveau comme la lecture ou l’écriture dans les fichiers des disques. Il en résulte pour ces derniers des lenteurs importantes car ils agissent par création d’un script en calculant les commandes SQL de type CREATE, ALTER et INSERT pour pouvoir reconstituer logiquement la base ou encore en agissant par la copie des fichiers (ce qui suppose au moins de figer la base)… Notons que dans les faits ce ne sont pas des sauvegardes mais des « vidages » appelés « dump« .

Il est possible d’accélérer encore le processus de sauvegardes de SQL Server en y ajoutant la compression…. Mais oui, cela accélère notablement les processus !
En effet, ce sont les écritures sur les disques qui sont généralement les opérations les plus lentes… En compressant on diminue le nombre des IO et l’on va donc généralement plus vite, et cela aussi bien pour la sauvegarde que pour la restauration…

Pour ce faire, deux paramètres peuvent être réglés :

backup compression default : active (1) ou non (0) la compression
backup compression algorithm : permet le choix 0 (par défaut) , 1 (MS_XPRESS) ou 2 (QAT_DEFLAT) de l’algorithme de compression

La valeur 2 concerne l’algorithme de compression QAT_DEFLAT disponible uniquement si Intel QAT est activé (voir ci-avant), ou bien en mode software. Certaines mesures montre que le temps de sauvegarde peut être divisé par deux à l’aide de cet algorithme.

Nous vous conseillons d’opter pour la compression des sauvegardes systématiquement…

Mais vous êtes en droit de vous poser la question : en quoi la compression des sauvegardes améliore t-elle les performances ?
La compression utilise un peu plus de CPU que lorsqu’il n’y a pas de compression, mais diminue singulièrement (souvent facteur 5) le nombre des accès aux disques du fait de la réduction du volume engendré par la compression. Or le disque est le système le plus lent dans un ordinateur, même doté des meilleurs SSD. C’est donc, plus de disponibilité du disque pour la concurrence ! Donc globalement plus de performances…

1.6 – Blocked Process Treshold

Ce paramètre permet de définir le seuil (durée en secondes) à partir duquel un blocage peut être intercepté. Durée possible entre 5 et 86400 secondes. On peut alors utiliser le profiler SQL et intercepter les rapports de blocage ou encore utiliser les événements étendus (XEvent).

Figure 03 – Capture des blocages de SQL Server via les événements étendus

NOTA : ceci n’améliorera pas les performance directement, mais permettra de constater quels sont les processus bloqués les plus courants et tenter d’y remédier. Or les blocages ce sont du temps perdu, donc des ressources gâchées, donc des performances globalement moindres…

1.7 – Réglage du parallélisme

Deux paramètres sont disponibles :

cost threshold for parallelism (par défaut 5)
max degree of parallelism (par défaut 0)

Un mot sur le parallélisme… Trop de parallélisme tue le parallélisme. En effet si une requête s’exécute en utilisant tous les threads, alors une seule requête ne sera exécutée simultanément. Si trop de requêtes sont parallélisées, alors il y aura contention du fait d’un goulet d’étranglement au niveau de l’accès aux threads…

1.7.1 – Max Degree Of Parallelism (DOP)

Ce paramètre permet de limiter le nombre de thread qu’une même requête peut utiliser pour s’exécuter. La valeur par défaut 0 indique que c’est le moteur qui choisit. Dans la version 2022 de SQL Server, vous pouvez éventuellement laisser cette valeur par défaut car les améliorations non négligeables apportées dans l’optimiseur dans le cadre de l’Intelligent Query Processing font que le degré de parallélisme va s’adapter petit à petit pour chaque requête. C’est un apport lié au Query Store qui est désormais activé en standard dans toute nouvelle base mise en compatibilité 2022 et qui apprend par essais successif d’exécution quelle est la valeur du nombre de thread pour une requête spécifique (DOP feedback)…

Dans un article sur un benchmark comparatif entre SQL Server et PostGreSQL sur le SIG, que j’ai écrit et publié sur developpez.com, je m’étais amusé à mesurer les effets du parallélisme d’exécution d’une requête présentant la plupart de ces opérations en parallèle :

Figure 04 – SQL Server Plan d’exécution requête parallélisé

En limitant le nombre de thread de 1 à 48, les temps de réponse étaient les suivants :

Figure 05 – mesure du parallélisme et temps de réponse d’une requête

Ou l’on constate qu’entre 24 et 48 cœurs, aucune amélioration du temps de réponse n’est visible.

Entre 1 et 2 cœurs on passe de 2,4 secondes à 1,4 (facteur 1.71 d’amélioration)
Entre 1 et 4 cœurs on passe de 2,4 secondes à 0,8 (facteur 3 d’amélioration)
Entre 1 et 8 cœurs on passe de 2,4 secondes à 0,45 (facteur 5,33 d’amélioration)
Entre 1 et 16 cœurs on passe de 2,4 secondes à 0,3 (facteur 8 d’amélioration)

Mais entre 12 et 16 on passe de 0,35 à 0.3 (facteur 1,16 d’amélioration)

la courbe est intéressante car au delà de 12 cœurs le gain est minime et certaines régressions fortuites apparaissent…
Mais à 12 cœurs sur 48 que comptait cette machine combien de requêtes ainsi parallélisées puis-je passer au même moment ? La théorie nous indique que 48 /12 = 4… (Le regretté Pierre Desproges nous disait qu’il aimerait bien vivre en théorie… Parce qu’en théorie tout se passe bien !). Sauf qu’il faut quand même quelques threads de disponibles pour Windows (ou Linux) et SQL Server…

Raisons pour lesquelles je préconise sur des serveurs ayant plus de 16 cœurs logique de limiter le parallélisme à un nombre de thread du quart du nombre total de cœurs moins un. Par exemple si votre instance SQL Server est installée sur un serveur ayant 24 cœurs (hyperthreading compris), alors limitez le parallélisme à 5 threads (24/4 – 1).

1.7.2 – Cost treshold for parallelism

Ce paramètre permet de définir les requêtes candidates à être parallélisée en fonction d’un seuil de coût de la requête.
Le coût d’une requête est une mesure estimée (un nombre réel sans unité) qui permet à l’optimiseur de choir le meilleur plan d’exécution de la requête en analysant différentes hypothèses basées sur l’enchainement des opérations et le choix des algorithmes, chaque plan d’exécution de la requête pouvant avoir un coût estimé différent)

Lorsque l’optimiseur a calculé un plan optimal, celui-ci est affecté d’un coût qui, s’il dépasse le seuil de parallélisation, va être ré étudié sous l’angle du parallélisme. Si le nouveau coût de la requête parallélisée est moindre que le coût de la requête monothread, alors c’est le plan d’exécution parallélisé qui sera choisi pour exécuter la requête sinon, la requête sera traitée avec un unique thread.

Or ce seuil de coût a été fixé à 5 lors de la sortie de la version 7 de SQL Server (64 bits) en 1999… À cette époque il était peu courant d’avoir un serveur doté de plus d’un processeur (la notion de cœur dans un même processeur n’existait pas et les architecture multiprocesseur étaient limité à 4…).
En 2024, le processeur que j’utilise pour ma machine personnelle (une station de travail HP Z8 Fury) possède un CPU Intel Xeon W9 3475X doté de 36 cœurs physiques soit 72 logiques grâce à l’hyperthreading…).

Tout cela pour vous dire que le seuil de coût de requête fixé à 5 par défaut pour envisager de ré étudier le plan d’exécution de la requête en parallèle est bien trop bas… Je le fixe ordinairement à :

12 pour de petites bases dont la taille est inférieur à 10 Go;
25 pour des bases moyennes de moins de 100 Go;
50 pour de grandes bases de moins de 1 To;
100 pour de très grandes bases de données > 1 To;

1.8 – Index create memory

Ce paramètre contrôle la quantité maximale de mémoire initialement allouée pour les opérations de tri nécessaire à la création d’index. Valeur par défaut 0 : auto-configuration.

ATTENTION : ce paramètre est corrélé avec le paramètre min memory per query. La valeur de index create memory devrait toujours être supérieure au min memory per query.

L’intérêt de ce réglage n’apparait que si vous créez des index pendant les heures de pleine production et que vous voulez en accélérer l’exécution. Dans ce cas fixez une valeur assez importante pour ce paramètre, mais à condition que vous ayez beaucoup de RAM…

NOTA : à manier avec beaucoup de précautions

1.9 – Lightweight pooling

L’activation de ce paramètre binaire permet de passer du mode thread au mode fibre. Le mode thread se caractérise par le fait qu’un processus qui a démarré sur un cœur ne va par forcément revenir sur le même cœur obligeant à un recalcul du contexte. Par exemple une requête mono-thread va démarrer sur le cœur 7 être « endormie » pour servir la concurrence (time-sharing), puis être réveillée pour continuer son exécution, mais a ce moment, le cœur 7 peut être indisponible. On lui donnera alors un autre cœur ce qui l’oblige à changer certaines liaisons (registres, adresse mémoire…) et c’est du temps de perdu.

Le mode fibre permet d’éviter ces changement de contexte et obliger le processus a s’exécuter de nouveau sur le même cœur… Mais il faut qu’il soit disponible…

Un tel changement est favorable à une meilleure performance, à condition de limiter fortement le nombre de cœurs utilisés simultanément par une même requête dans le cadre du parallélisme, sinon un effet contraire risque de se produire…. Il est donc intéressant pour des charges de travail consitué presque exclusivement par de très nombreuses petites requêtes en coût (cas du trading bancaire par exemple).
Dans un tel cas, je limite à 2 le DOP sur des machines de 24 coeurs à 32, 3 sur des machines de 34 à 72 coeurs et 4 au delà.

1.10 – Full Text (indexation textuelle)

Le mécanisme d’indexation textuelle de SQL Server est un processus asynchrone qui peut être exécuter au fil de l’eau, c’est à dire dès que les transactions d’écritures (INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP TABLE…) sont finalisées…

L’utilisation en mode recherche de l’indexation textuelle nécessite des ressources CPU. Le paramètre :

max full-text crawl range : valeur par défaut 4 (partitions)

Peut être modifié pour traiter avec un plus grand parallélisme l’exécution de la recherche. Ne pas dépasser le nombre de cœurs de la machine.

1.11 – Réglage de la mémoire cache

Deux paramètres permettent de régler le cache de SQL Server :

min server memory (MB) : limite basse du cache
max server memory (MB) : limite haute du cache

ATTENTION : ne pas régler la limite haute du cache peut conduire à une instabilité des processus hôte de la machine… Il est donc impératif de régler au moins le paramètre max server memory

Si vous spécifiez une limite basse, SQL Server n’acquiert pas d’un seul coup la quantité de mémoire indiqué comme cache, mais lorsqu’il aura atteint cette limite, il ne redescendra pas en dessous. Si vous utilisez une VM, vous pouvez mettre cette limite basse à une valeur proche de la limite haute (par exemple 80%) de façon a empêcher un balooning trop important.

Il est particulièrement important de préciser une limite haute de la mémoire, sinon SQL Server étant programmer pour acquérir toute la mémoire disponible, cela peut se traduire par une instabilité de la machine du fait des processus externes qui peuvent se trouver en état de famine au niveau des ressources…

Cette limite de mémoire est celle du cache, pas de l’exécutable SQL Server qui lui aussi doit avoir de la mémoire, d’autant plus si vous utilisez des procédures étendues et d’autres objets (DLL, OA…)… Et en sus il faut de la RAM pour l’OS.

Pour une utilisation ordinaire de SQL Server j’utilise la formule suivante :

RAM_physique - 4 Go pour SQL Server - 2 Go pour l'OS - 10% du tout pour le confort des autres services dont un probable antivirus...

Par exemple, pour une machine dotée de 256 Go de RAM, ma valeur de max server memory sera de : (256 – 6) * 0.9 = 230 400 Mo.

Si vous utilisez des processus .net comme le SIG, le type HierarchyId, du XML ou du JSON, baissez encore cette limite car la machine virtuelle .net (SQL CLR) doit utiliser de la mémoire pour exécuter ses calculs. De même si vous activez des mécanismes d’audit ou de trace.

Enfin, dans la version standard, il existe des limites particulières :

128 Go pour le cache des objets relationnels (tables et index)
+ 32 Go pour l’indexation verticale
+ 32 Go pour les tables « in memory »
Et encore d’autres pour les modules SSIS, SSAS et SSRS

Globalement une aide précieuse est le Guide d’architecture de gestion de la mémoire

1.12 – Réglages des threads de travail

Le paramètre max worker thread permet de spécifier le nombre maximal de threads de travail que va utiliser le moteur SQL. Par défaut la valeur 0 procède à un réglage automatique dépendant de la version de SQL Server et du nombre de cœurs.

Pour une machine de 4 cœurs ou moins, la valeur est de 512.

Pour une machine entre 6 et 64 cœurs, la formule de calcul est la suivante :
(nombre_de_cœurs_logique - 4) * 16 + 512

Pour une machine possédant plus de 64 cœurs, la formule de calcul est la suivante :
(nombre_de_cœurs_logique - 4) * 32 + 512

Par exemple avec une machine possédant 24 cœurs, le nombre de threads de travail sera de 832. Pour 72 cœurs il sera de 2 688.

Ce réglage est extrêmement délicat et nécessite une étude approfondie pour savoir si d’autres éléments ne sont pas à l’origine des problèmes de performances. Autrement dit ce n’est qu’en dernier recours que l’on doit toucher à ce réglage.

1.13 – Mémoire minimale par requête

le paramètre Min memory per query permet de spécifier la quantité minimal de mémoire d’exécution de chaque requête. Valeur par défaut 1 024 Ko. Ce paramètre est corrélé avec le paramètre Index create memory.

Dans le mesure ou l’optimiseur tente de prédire la quantité optimale de mémoire à utiliser pour chaque requête, mieux vaut laisser ce paramètre à un niveau très bas, et la valeur par défaut est généralement la bonne solution. D’autant qu’avec l’arrivée d l’Intelligent Query Processing et le Query Store, ces mesures sont réajustées en fonction des exécutions successives des requêtes, via le Memory Grant Feedback.

Néanmoins dans de très rares cas de bases de données au fonctionnement atypique (j’ai eu l’occasion de modifier ce paramètre avec une base de données faisant exclusivement du « text mining« ) il est intéressant d’augmenter cette valeur à conditions d’avoir une faible concurrence, pas mal de RAM dédié au cache et enfin si les requêtes nécessitent chacune beaucoup de mémoire.

1.14 – Taille des paquets réseaux

La taille des paquets réseau est fixée dans SQL Server par le paramètre Network packet size dont la valeur par défaut est de 4 096 octets. Ceci est une valeur moyenne bien adaptée à des bases de données de type OLTP. Néanmoins deux cas de figure peuvent se présenter. La plupart des requêtes sont très petites (par exemple dans le cas de bases de données alimentées à coup d’INSERT / UPDATE unitaires par des robots dans une chaine de production – cas qui m’est arrivé chez Pasteur Mérieux pour la fabrication de Vaccins) et dans ce cas porter la taille à 512 qui est la valeur minimale, ou bien, à l’inverse, si la base de données sert pour de l’EAI et donc essentiellement pour l’import / export de lots de données via des opérations de copies en bloc de type BULK LOAD (comme c’est le cas avec BizTalk).

Cependant il faut tenir compte des éléments suivants :

La taille de paquet réseau maximale pour les connexions chiffrées est 16 383 octets;
Si MARS est activé, une en-tête de 16 octets est ajoutée pour le chiffrement et il faut réduire cette taille à 16368 octets pour les connexions chiffrées;
Pour toute valeur supérieure à 8 060 octets SQL Server doit effectuer plusieurs passes afin d’allouer la quantité de mémoire et il se peut que ceci s’avère impossible conduisant à une exception du type « Failed to reserve contiguous memory« . Autrement dit il faut avoir beaucoup de RAM et diminuer la taille du cache…

1.15 – Optimisation des requêtes « ad hoc »

Non il ne s’agit pas du capitaine d’une célèbre bande dessinée consacré à un chien nommé Milou et son maître… Le terme ad hoc, ne fait pas non plus référence au poisson…

Le terme ad hoc viens du latin et signifie littéralement, nous dit Robert, « destiné expressément à un usage »… Les requêtes ad hoc sont donc des requêtes dénuées d’un contexte généraliste, c’est à dire celles venant des applicatifs par opposition aux requêtes intégrées dans des routines SQL comme les procédures stockées, les fonctions définies par les utilisateur (UDF) et les déclencheurs…

C’est un des paramètre les plus important à régler et il devrait toujours être sur 1.

Petite explication… SQL Server a été inventé par Sybase en 1983 (donc un des tous premiers systèmes de bases de données relationnels avec Oracle puis IBM DB2) et est basé, architecturalement parlant, sur Ingres. Sybase fut l’un des pionniers du domaine du SGBD Relationnel. Jugez du peu… Sybase invente successivement le client/serveur, la notion de trigger (il sera très répandu dans les salles des marchés des banques…), le cache des procédures, les procédures stockées et l’optimisation statistiques…. Les autres comme Oracle, n’ayant fait que piller ce que Sybase avait inventé. Le cache des procédures consiste à mettre en cache les plans d’exécution des différentes requêtes figurant dans les procédures stockées comme dans les déclencheurs. Ceci parce qu’un plan de requête coute cher en ressource et temps à calculer et qu’il est préférable de reprendre un plan d’exécution de requête mis en cache plutôt que d’en générer un nouveau… Pour information PostGreSQL ne possède pas de cache de plan de requête sauf pour les requêtes « préparées » mais supprime cela du cache dès que l’objet « prepared statement » du code du programme est fermé… ce qui en limite l’usage ! MySQL et MariaDB n’ont aucun cache des plans de requête ce qui oblige a recalculer le plan d’exécution pour chaque requête envoyée au serveur.

Donc, SQL Server met en cache tous les plans de requêtes et les réutilise… Sauf que cela prend de la place et parfois cela peut s’avérer inutile. En effet, pour les requêtes applicatives, donc les fameuses ad hoc… comme les développeurs adorent triturer la génération de ces requêtes par assemblage de bouts de chaines de caractères plutôt que de faire des requêtes « préparées » (qui, il faut bien le dire, s’avèrent très limitées fonctionnelles parlant), alors la plupart des requêtes lancées par l’applicatif sont uniques et génèrent des plans d’exécutions qui, pour la plupart, ne sont jamais utilisés plus d’une fois et polluent le cache sans en offrir le moindre avantage. En moyenne un plan faisant plusieurs centaines de Mo… Pour cela Microsoft a adopté une stratégie, qui n’est pas celle par défaut, consistant à créer un « stub » du plan (comprenez bout, moignon, talon…) en gros cela revient à stocker une « empreinte » du plan d’exécution lorsqu’il est calculé pour la première fois, avec un compteur du nombre de fois, ce qui réduit drastiquement l’utilisation du cache. Ainsi lorsque ce même plan est recalculé une comparaison est finalement effectué avec les « stubs » en cache, et s’il y a correspondance alors le plan complet est mis en cache à la place du « stub »…

Pour savoir quel est le volume des plans mis en cache pour les requête ad hoc, lancez la requête suivante :

SELECT CAST(SUM(size_in_bytes) / POWER(1024.0, 2) AS DECIMAL(16,3)) 
          AS ADHOC_QUERY_PLAN_CACHE_SIZE_MB 
FROM sys.dm_exec_cached_plans 
WHERE objtype = 'Adhoc';

SELECT CAST(SUM(size_in_bytes) / POWER(1024.0, 2) AS DECIMAL(16,3))

AS ADHOC_QUERY_PLAN_CACHE_SIZE_MB

FROM sys.dm_exec_cached_plans

WHERE objtype = 'Adhoc';

1-16 – Mise en « in memory » des métadonnées de la base tempDB

Depuis la version 2019, SQL Server permet de mettre les tables système de la base tempdb en mode « in memory » afin d’accélérer le traitement de lourdes charges de travail utilisant de nombreuses tables temporaires implicite (tri, regroupement…) afin de diminuer la contention d’accès à ces tables et donc de servir plus rapidement les demandes de création de tables temporaires.

ATTENTION : bien que ce paramètre figure dans ceux positionnable par sp_configure (tempdb metadata memory-optimized), il est fortement recommandé de ne pas le faire par cette commande, mais par une requête de type ALTER SERVER CONFIGURATION

Voir paragraphe suivant…

NOTA : comme tout ce qui est en « in memory » il est nécessaire d’avoir une importante quantité de RAM afin de dégager de la place pour mettre ces métadonnées de tempdb en mémoire, sous peine d’un crash du moteur SQL Server avec l’exception 701 indiquant que le système est à court de mémoire… Dans un tels cas, vous pouvez mesurer l’utilisation de la mémoire et mieux organiser votre système, à l’aide de document suivant :

Memory-optimized tempdb metadata (HkTempDB) out of memory errors

Voir § 2.5

2 – Réglages au niveau instance

La commande :

ALTER SERVER CONFIGURATION SET ...

1	ALTER SERVER CONFIGURATION SET ...

Permet d’aller plus loin encore dans les réglages de l’instance. Elle est à la fois équivalente sur certains point à sp_configure, mais va aussi plus loin et de manière généralement plus simple. Nous ne présentons que les points susceptibles d’améliorer les performances…

2.1 – Réglage des affinités pour les processus logiques

Voir § 1.2.
Syntaxe de la commande :

Figure 07 – Syntaxe de la commande ALTER SERVER CONFIGURATION SET PROCESS AFFINITY

Bien entendu vous ne pouvez pas préciser deux fois le même item, ni avoir des chevauchement d’items.

Ceci permet de régler les cœurs associés aux processus logiques du moteur et d’en régler l’affinité avec les nœuds NUMA.

NOTA : à manier avec précaution…

ATTENTION : version Enterprise seulement…

2.2 – Extension du cache des données

La commande :

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ...

1	ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ...

Permet de réaliser un cache de second niveau. Le cache primaire est celui disponible en mémoire vive (RAM). Un cache de second niveau peut être créé et dans ce cas les données du cache en mémoire vive ayant été les plus anciennement accédées seront déversées dans ce cache de second niveau et y seront extirpées si besoin est vers le cache primaire pour être lues ou modifiées. Bien entendu il est nécessaire que ce cache soit positionné sur un stockage bien plus rapide que le stockage ordinaire des données. C’est en général un stockage de type NVMe ou Optane (Intel).

Pour être efficace, le cache de second niveau devrait être de l’ordre de 4 à 8 fois la taille du cache en RAM (32 fois est le maximum). Par exemple si vous avez 256 Go de RAM et avez fixé le cache de SQL Server à 200 Go, alors vous devez créer un cache de second niveau compris entre 800 et 3 200 Go. À titre d’exemple :

ALTER SERVER CONFIGURATION
   SET BUFFER POOL EXTENSION 
      ON (FILENAME = 'D:\SQL_Server\BufferPool\CacheExtension.bpe',    
          SIZE = 1 GB);

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION

ON (FILENAME = 'D:\SQL_Server\BufferPool\CacheExtension.bpe',

SIZE = 1 GB);

Ce paramétrage fait partie des indispensables quand vous avez des bases de données de grande dimensions (plusieurs dizaines de To…).

2.3 – Activation de NUMA au niveau « software »

La commande :

ALTER SERVER CONFIGURATION SET SOFTNUMA { ON | OFF };

1	ALTER SERVER CONFIGURATION SET SOFTNUMA { ON \| OFF };

Permet d’émuler un NUMA par software si votre machine ne compte qu’un seul processeur, ou encore de subdiviser des nœuds NUMA trop importants en nombre de cœurs. Voir § 1.3

2.4 – Mise en « in memory » des métadonnées de la base tempdb et pool de mémoire tampon hybride

La commande :

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ...

1	ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ...

Permet de forcer l’utilisation de la mémoire pour les métadonnées de la base tempdb mais aussi du pool de tampons hybride.

Nous avons déjà parlé au § 1.16 de la fonctionnalité de mise en mémoire pour les métadonnées de la base tempdb, et sa mise en œuvre se fait à l’aide de cette commande avec la syntaxe suivante :

ALTER SERVER CONFIGURATION 
   SET MEMORY_OPTIMIZED 
      { ON 
        | OFF
        | [ TEMPDB_METADATA 
            = { ON [ (RESOURCE_POOL = 'nom_pool_ressource') ] 
                | OFF 
              }
        | [ HYBRID_BUFFER_POOL = { ON | OFF }
      };

ALTER SERVER CONFIGURATION

SET MEMORY_OPTIMIZED

{ ON

| OFF

| [ TEMPDB_METADATA

= { ON [ (RESOURCE_POOL = 'nom_pool_ressource') ]

| OFF

}

| [ HYBRID_BUFFER_POOL = { ON | OFF }

};

ON / OFF active à la fois les deux systèmes.

TEMPDB_METADATA = { ON [ (RESOURCE_POOL = ‘nom_pool_ressource’) ] | OFF } permet d’activer ou désactiver uniquement la partie mise en mémoire des métadonnées de la base tempdb, et si activation on peut préciser dans quel pool de ressource ces données doivent aller.

Le pool de mémoires tampons hybride permet à certains objets d’être mis en cache dans des fichiers résidant sur des périphériques de type « mémoire persistante » (PMEM : Persistent Memory), au lieu de récupérer une copie des pages de données depuis le disque et devoir la remettre en cache dans la mémoire RAM volatile par nature. Cela peut être de l’Optane Intel.

Plus plus de compréhension sur ce dernier point, lire : « Hybrid buffer pool »

ATTENTION : version Enterprise seulement…

2.5 – Activation de l’accélération matérielle

La commande :

ALTER SERVER CONFIGURATION
   SET HARDWARE_OFFLOAD { ON | OFF };

1 2	ALTER SERVER CONFIGURATION SET HARDWARE_OFFLOAD { ON \| OFF };

Permet d’activer ou non, l’accélération matérielle. Voir § 1.4

3 – Les réglages au niveau des bases

Il existe deux commandes permettant de paramétrer les bases de données de SQL Server. La première concerne essentiellement les paramètres généraux de fonctionnement logique :

ALTER DATABASE { nom_base | CURRENT } SET ...

1	ALTER DATABASE { nom_base \| CURRENT } SET ...

la seconde concerne des options que nous avons déjà vu précédemment ou des drapeaux de trace qui agissent globalement et qui nous pouvons déporter par surcharge au niveau de la base :

ALTER DATABASE SCOPED CONFIGURATION SET ...

1	ALTER DATABASE SCOPED CONFIGURATION SET ...

Pour cette dernière il faut être dans le contexte de la base dont le comportement est à modifier.

NOTA : comme déjà dit précédemment, nous ne faisons figurer dans ces paragraphes que les options qui nous paraissent concerner les performances…

3.1 – Automatisation des opérations

ALTER DATABASE { nom_base | CURRENT } SET
   { AUTO_CLOSE { ON | OFF }
     | AUTO_SHRINK { ON | OFF }
     | AUTO_CREATE_STATISTICS { OFF | ON 
       [ ( INCREMENTAL = { ON | OFF } ) ] }
     | AUTO_UPDATE_STATISTICS { ON | OFF }
     | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
   }

ALTER DATABASE { nom_base | CURRENT } SET

{ AUTO_CLOSE { ON | OFF }

| AUTO_SHRINK { ON | OFF }

| AUTO_CREATE_STATISTICS { OFF | ON

[ ( INCREMENTAL = { ON | OFF } ) ] }

| AUTO_UPDATE_STATISTICS { ON | OFF }

| AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

}

N’activez jamais AUTO_CLOSE : ceci dégage du cache toutes les tables et index, ainsi que le métadonnées de la base, obligeant à récupérer du disque les informations pour les y replacer en mémoire. Pour les bases de données de le version Express, ce comportement est le comportement par défaut et il reste ainsi en cas de restauration dans toute autre version (Standard, Enterprise…).

N’activez jamais AUTO_SHRINK : ceci diminue physiquement les fichiers de la base. Or une base est toujours en croissance, même si vous la purgez régulièrement (croissance de l’entreprise, nouvelles fonctionnalités, pose d’index…). Nous verrons au chapitre 4 consacré au stockage pourquoi ceci est si important, mais considérez que les fichiers de SQL Server sont l’équivalent de volumes UNIX…

Activez AUTO_CREATE_STATISTICS qui permet à SQL Server de créer de nouvelles statistiques pour l’optimiseur en fonction des requêtes qui doivent être optimisées. Le mode INCREMENTAL permet de ventiler la création des statistiques pour chacune des partition si vos tables ou vos index sont partitionnées et c’est plutôt important d’activer cette option dès que vous mettez en œuvre le partitionnement.

Activez AUTO_UPDATE_STATISTICS qui permet à SQL Server de recalculer en mode échantillon les statistiques de l’optimiseur si ces dernières se révèlent obsolètes, sauf si vous maitrisez parfaitement votre maintenance statistique. Le recalcul étant déclenché par l’exécution d’une requête qui analyse la situation des statistiques et décide, avant de calculer le plan d’exécution de la requête, si celles-ci sont suffisamment à jour (peu de modification) ou bien s’il faut les recalculer. Si vous êtes en édition Enterprise, activez en sus AUTO_UPDATE_STATISTICS_ASYNC qui permet de déclencher ce recalcul en mode asynchrone (un thread passe en permanence pour trouver les statistiques obsolète et les recalculer indépendamment de leur exécution).

3.2 – Corrélation des type DATETIME pour optimisation

Ce paramétrage n’a d’intérêt que si vous avez des types de données DATETIME (et non pas DATETIME2) faisant partie de colonnes clé et référencées à titre de clé étrangères…

ALTER DATABASE { nom_base | CURRENT } SET
   DATE_CORRELATION_OPTIMIZATION { ON | OFF }

1 2	ALTER DATABASE { nom_base \| CURRENT } SET DATE_CORRELATION_OPTIMIZATION { ON \| OFF }

Dans un tel cas il faut mettre en œuvre ce paramètre qui ajoute des statistiques corrélées entre les paires de clés primaire (ou unique) et les clés étrangères qu’elles référence. Ceci parce que Sybase SQL Server est doté d’une étrange erreur congénitale qui fait que le type DATETIME est imprécis à 3ms près… Cela peut paraître minime, mais à quelques millisecondes vous pouvez passer d’un jour à l’autre !

Ceci permettra d’obtenir des plans d’exécution de meilleure qualité !

Au fait, comment déceler que vous en avez besoin ? la requête ci dessous à passer dans toutes vos bases de données de production vous le dira si elle renvoie au moins une ligne…

SELECT C.TABLE_SCHEMA, C.TABLE_NAME, COUNT(*) AS NB_COL
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
          ON RC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
          AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
       JOIN INFORMATION_SCHEMA.COLUMNS AS C
          ON KCU.TABLE_SCHEMA = C.TABLE_SCHEMA
          AND KCU.TABLE_NAME = C.TABLE_NAME
          AND KCU.COLUMN_NAME = C.COLUMN_NAME
WHERE  C.DATA_TYPE = 'datetime'
GROUP  BY C.TABLE_SCHEMA, C.TABLE_NAME;

SELECT C.TABLE_SCHEMA, C.TABLE_NAME, COUNT(*) AS NB_COL

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU

ON RC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA

AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

JOIN INFORMATION_SCHEMA.COLUMNS AS C

ON KCU.TABLE_SCHEMA = C.TABLE_SCHEMA

AND KCU.TABLE_NAME = C.TABLE_NAME

AND KCU.COLUMN_NAME = C.COLUMN_NAME

WHERE C.DATA_TYPE = 'datetime'

GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME;

CONSEIL : le type DATETIME est déconseillé. Dans la mesure du possible remplacer le par le type DTETIME2(n) avec une précision de 3 si vous voulez l’exact équivalent, sinon, avec 0 ou n’importe quelle valeur jusqu’à 7 en fonction des tantièmes de seconde désirés.

3.3 – Journalisation asynchrone

ALTER DATABASE { nom_base | CURRENT } 
   SET DELAYED_DURABILITY = 
      { DISABLED | ALLOWED | FORCED }

ALTER DATABASE { nom_base | CURRENT }

SET DELAYED_DURABILITY =

{ DISABLED | ALLOWED | FORCED }

SQL Server permet de faire de la journalisation, asynchrone. En principe la journalisation des transactions devrait être toujours synchrone, c’est à dire que tant que la finalisation d’une transaction n’est pas définitive tant que les informations concernant cette transaction n’ont pas encore fait l’objet du écriture physique dans le journal des transactions.

Cependant, et afin de gagner du temps de traitement, on peut décider de ne pas journaliser de manière synchrone et donc retarder la durabilité des informations un moment plus tard. C’est le principe de la validation différée…
Ceci permet de gagner du temps en réduisant la latence d’accès au journal comme la contention des ressources d’accès au fichier du journal de transactions. Mais en cas d’arrêt brutal du système, cela conduira probablement à de la perte de données, la base restant intègre…

Une perte de données peut être parfaitement tolérable, si les données des transactions perdues peuvent à tout moment être reconstitué. C’est le cas en particulier des imports de données, de l’archivage des informations et plus généralement de tous les batchs qui opèrent sans interaction humaine en effectuant des calculs, des transferts et translations de données. Mais pour que le système puisse retomber sur ses pattes, il faut rajouter des informations de suivi pour indiquer ou l’on se trouve dans le processus de calcul. En général on ajoutera au système une table de « workflow » avec la date et heure des actions ainsi que leur statut (démarré, finalisé…) et l’on préservera les données d’entrée pour pouvoir à tout moment et à toute étape recommencer ou finaliser le processus.

Il y a donc trois possibilité : interdire la durabilité différé des transaction (DISABLE), l’autoriser (ALLOWED) ou l’obliger systématiquement (FORCED).

Dans le cas de l’autorisation de la journalisation asynchrone, il faut confirmer les transactions explicite à l’aide de l’option WITH ( DELAYED_DURABILITY = ON) au niveau de la commande COMMIT validant la transaction.

3.4 – Allocation mixte des pages dans une même extension

Dans les versions anciennes de SQL Server 2016 les bases de données étaient créées avec des fichiers dont le stockage reposait sur des extensions constituées par des blocs de 8 pages contiguës mixtes, c’est à dire que les 8 pages pouvait provenir de différentes tables ou index. Depuis, et par défaut, les extensions sont uniformes, c’est à dire que toutes les pages occupées ne concerne qu’un seul et même objet, soit une table, soit un index.

ALTER DATABASE { nom_base | CURRENT } 
   SET MIXED_PAGE_ALLOCATION { OFF | ON }

1 2	ALTER DATABASE { nom_base \| CURRENT } SET MIXED_PAGE_ALLOCATION { OFF \| ON }

Permet de modifier le comportement du stockage physique.

ATTENTION : même dans une instance SQL Server 2024, certaines bases peuvent avoir des extensions mixtes du fait de la rétrocompatibilité (restauration d’une base d’une version antérieure).

NOTA : la modification de ce paramètre ne produira ses effets que progressivement au fur et à mesure que les tables et index auront été reconstruits.

Pour des petites bases avec une mémoire cache très réduite (version Express par exemple) préférez le mode mixte. Pour les autres bases, préférez le mode uniforme (MIXED_PAGE_ALLOCATION ON).

3.5 – Paramétrisation des requêtes

La paramétrisation des requêtes est le fait que les valeurs scalaires d’une requête sont remplacées par des variables dont la valeur est inconnue.

ALTER DATABASE { nom_base | CURRENT } 
   SET PARAMETERIZATION { SIMPLE | FORCED }

1 2	ALTER DATABASE { nom_base \| CURRENT } SET PARAMETERIZATION { SIMPLE \| FORCED }

Par défaut SQL Server est en paramétrisation simple des requêtes ce qui signifie qu’il laisse les valeurs exactes des paramètres lorsqu’une requête arrive pour la première fois, mais il remplace ces valeurs scalaires par des variables si la même requête s’exécute plusieurs fois.

Le paramétrage forcé se produit lorsque le moteur de base de données paramètre toute valeur scalaire qui apparaît dans une instruction SELECT, UPDATE, INSERT ou DELETE soumise sous n’importe quelle forme, à quelques exceptions près.

La paramétrisation forcée réduit le nombre de plans d’exécution en cache en permettant leurs réutilisation pour des requêtes similaires. C’est un gain de cache mais certaines requêtes peuvent être moins efficaces. Pour ces dernières vous pouvez utiliser un guide de plan pour rectifier le paramétrage à la volée ou bien activer le Query Store (voir chapitre 3) et forcer les meilleurs plans.

3.6 – Mode de compatibilité

Le mode de compatibilité permet de faire fonctionner une base de données dans n’importe quelle version. Microsoft SQL Server est le seul SGBDR à permettre de faire fonctionner une base de données dans un mode de rétrocompatibilité, c’est à dire dans une version antérieure à celle de l’instance. Par exemple dans une instance 2022, vous pouvez avoir des bases en version 2008, 2012, 2014, 2016, 2017, 2019 et 2022…

Dans chaque version de nombreux changements sont opérés et ne sont disponible, à quelques exceptions près, que si vous réglez le mode de compatibilité avec la version adéquate. Par exemple les index COLUMSTORE (indexation verticale) ne sont apparus qu’avec la version 2012 avec, dans un premier temps, un mode de recherche plutôt de type « row« , en définitive moins efficace que le mode « batch » introduit en version 2014 et systématisé en version 2016 avec, en outre, la clause INCLUDE. Enfin s’est ajouté en 2017 le support des LOBs (NVARCHAR(max)),une amélioration des performances de mise à jour en 2019 et, finalement, l’ordonnancement des données avec la version 2022…

Autre exemple, l’introduction progressives des nouveaux algorithmes de l’Intelligent Query Processing à partir de la version 2017…

Autrement dit, il vaut mieux toujours placer sa base dans la toute dernière version de SQL Server…

Malheureusement, il arrive parfois que des effets de bords contrarient ces avancées et nous verrons au chapitre 3 l’Intelligent Query Processor et comment régler ce genre de problème à l’aide des différentes versions de l’estimateur de cardinalité et plus généralement les drapeaux de trace, mais aussi nous nous arrêterons sur le Query Store (magasin de requêtes – voir chapitre 3) qui suit l’évolution des performances des requêtes et permet de les corriger manuellement ou automatiquement.

La commande pour modifier la compatibilité d’une base de données est la suivante :

ALTER DATABASE { nom_base | CURRENT } 
   SET COMPATIBILITY_LEVEL = niveau [;]

1 2	ALTER DATABASE { nom_base \| CURRENT } SET COMPATIBILITY_LEVEL = niveau [;]

Bien entendu et à tout moment vous pouvez revenir à n’importe quelle version rétrograde de SQL Server…

ATTENTION : ce paramétrage est un paramétrage logique et non physique. Il ne permet pas, par exemple, de sauvegarder une base et de la restaurer dans une instance rétrograde.

Pour information, le mode de rétrocompatibilité est limité par le tableau présenté à la figure 8, suivant :

Figure 08 – Versions de SQL Server et leur codification (limites de rétrocompatibilité)

Ce tableau se lit de la façon suivante : si vous êtes en version 2022 (code 16), vous pouvez rétro-compatibiliser à n’importe quelle version entre 160 (SQL Server 2022) et 100 (2008/2008 R2).

3.7 – Verrouillage optimiste

Globalement il existe deux technique différentes de verrouillage dans le monde des SGBD Relationnels (et en général dans le monde de l’informatique…). L’une appelée verrouillage pessimiste est préventive. L’autre appelée verrouillage optimiste est postventive….

Le verrouillage pessimiste consiste à placer des verrous sur les ressources à manipuler avant de commencer le traitement :

verrous de lecture qui sont partageable et interdisent aux processus concurrent de venir modifier les informations (INSERT, UPDATE, DELETE…), ou la structure de l’objet qui porte ces informations (ALTER, DROP…)
verrous d’écriture qui sont exclusifs et interdise toute autre action concurrente, lecture, modification, altération des objets porteurs de l’information

Une verrou de lecture permet à d’autres processus de lire les mêmes informations et donc un objet peut cumuler de multiples verrous de lecture tandis que les verrous d’écriture ne peuvent protéger qu’un seul processus, le premier qui l’a acquis.

Une fois le traitement achevé, les verrous sont libérés.

Le verrouillage pessimiste consiste à prélever une copie de l’information à traiter et à effectuer toutes les manipulations sur cette copie. Une fois le traitement terminé sur la copie, si ce traitement a modifié les données, recopier les modifications dans l’original, sinon abandonner la copie.
Cependant, si l’original a lui même été modifié entretemps le report de la modification n’est pas possible car il écraserait la modification d’un autre… Le traitement est alors abandonné, une exception est levée et un message d’erreur envoyé. L’usage est de tenter de recommencer la mise à jour…
Notons en outre que le verrouillage pessimiste est généralement hybride, car les valeurs de référence des clés étrangères ne sont pas recopiées avec les copies ce qui nécessite un accès en lecture aux référence et donc un verrou pessimiste…

problème	Pessimiste	Optimiste
Attente	Oui : libération des verrous concurrents pour permettre de poser ses propres verrous	Non, pas de blocage sauf si lecture de valeurs référentielles (clés étrangères)
Ressources externes	Aucune en dehors des verrous	Verrous + copie des informations manipulées
Nettoyage	Aucun en dehors de la libération des verrous	libération des verrous et suppression des copies
Garantie de bonne fin	Une fois tous les verrous acquis, le traitement sera finalisé	Aucune garantie. Si une modification a eu lieu entretemps sur les mêmes informations, abandon de la transaction

Verrouillage pessimiste vs optimiste

Par défaut, SQL Server pratique le verrouillage pessimiste. Ce mode entraine donc de facto des attentes si la concurrence est importante. Mais en dehors de possibles interblocages (appelés verrou mortel ou étreinte fatale… deadlock en anglais) il apporte une garantie de bonne fin au traitement de mise à jour. Depuis la version 2005 de SQL Serve il est possible de modifier ce comportement et d’imposer un verrouillage optimiste systématique ou mixte (verrouillage optimiste ou pessimiste a définir au niveau de chaque session ou transaction).

Si le verrouillage optimiste parait séduisant en réduisant presque à néant toutes les attentes il possède un inconvénient supplémentaire à son incapacité à garantir la bonne fin du traitement, celui de nécessiter des ressources supplémentaire qui peuvent devenir fortes importantes en cas de concurrence élevée et de transactions longues et complexes. En effet, il faut une capacité de stockage complémentaire pour gérer les copies de travail nécessaires au verrouillage optimiste.
SQL Server utilise la base système tempdb optimisé pour ce faire afin de gérer les copies dans un « magasin » de versionnement des lignes des tables.

Certains SGBD Relationnels n’offrent que la possibilité du verrouillage pessimiste, parmi lesquels MySQL, MariaDB et IBM DB2.
D’autres SGBD Relationnels n’offrent que la possibilité du verrouillage optimiste, parmi lesquels Oracle Database ou PostGreSQL.

Microsoft SQL Server offre les deux possibilités et la mixité. Chaque base de données peut donc être placé en mode :

verrouillage pessimiste exclusivement;
verrouillage pessimiste par défaut et optimiste si besoin (mode mixte);
verrouillage optimiste exclusivement.

Cela se fait à l’aide des commandes :

ALTER DATABASE { nom_base | CURRENT } 
   SET ALLOW_SNAPSHOT_ISOLATION { ON | OFF } [;]

1 2	ALTER DATABASE { nom_base \| CURRENT } SET ALLOW_SNAPSHOT_ISOLATION { ON \| OFF } [;]

À ON, cela autorise l’usage du verrouillage optimiste et, pour l’utiliser, il faut démarrer une transaction avec le niveau d’isolation SNAPSHOT.

ALTER DATABASE { nom_base | CURRENT } 
   SET READ_COMMITTED_SNAPSHOT { ON | OFF } [;]

1 2	ALTER DATABASE { nom_base \| CURRENT } SET READ_COMMITTED_SNAPSHOT { ON \| OFF } [;]

Qui force l’usage du verrouillage optimiste en remplacement du verrouillage pessimiste pour toutes les transactions dans la base.

ATTENTION : le comportement des données dans une transaction qui lirait pluieurs fois les mêmes lignes des tables peut conduire à un résultat différent entre le mode optimiste et le mode pessimiste de verrouillage en fonction du niveau d’isolation choisi…

Un dernier point… Vous pouvez aussi faire en sorte que les tables « in memory » soient en verrouillage optimiste en l’activant (ou le désactivant) par la commande :

ALTER DATABASE { nom_base | CURRENT } 
   SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = { ON | OFF } [;]

1 2	ALTER DATABASE { nom_base \| CURRENT } SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = { ON \| OFF } [;]

3.8 – Automatic Tuning

Appelé en version française « Réglage automatique » ce système à commencé d’être introduit avec la verion 2017 pour identifier les régressions de plans de requêtes puis dans Azure SQL pour indexer automatiquement la base de données.

Pour une correction automatique des plans d’exécution des requête il est indispensable d’activer le « Magasin des requêtes » (Query Store) que nous verrons au chapitre 3…

L’indexation automatique de la base n’étant actuellement disponible que dans l’édition Azure SQL, nous n’en parlerons pas…. Mais il y a fort à parier que cette indexation automatique arrive dans les éditions « on premise » futures. Un petit indice nous le fait pressentir : la présence de la colonne « auto_created » dans la vue de gestion sys.indexes…

3.9 – Cible de durée maximale de récupération

La récupération (en anglais « recovery« ) est une phase pendant laquelle une base de données qui revient en ligne après un arrêt brutal, rejoue les dernières transactions dont les modifications (pages dites « sales ») n’ont pas encore été l’objet d’un report des écritures dans les fichiers de données.

En effet, afin de limiter le nombre des opérations physiques d’écriture, dans le but d’une plus grande rapidité de service, les données modifiées qui ont fait l’objet d’écritures physiques dans le journal des transactions, n’ont pas besoin d’être immédaitement recopiées dans les fichiers de données. Ces écritures physiques peuvent attendre car elle sont reproductible à partir de la situation actuelle des données et des transactions qui figurent au journal, transaction qui sont écrites de manière synchrone, de façon à garantir la durabilité des données de la base.
En tout état de cause, le moteur attend, qu’il y ait sufissamment de choses à écrire (quantité) ou que la place en mémoire devienne critique (pression mémoire) pour procéder à une slave d’écriture des pages dites « sales » (dirty pages), c’est à dire les données actuelles en mémoire, mais ancinnes dans les fichiers. L’écriture de ces données étant donc asynchrone.

Donc, en cas d’arrêt brutal et au moment ou le SGBDR redevient opérationnel, chaque base de données analyse le journal des transactions, pour savoir ou elle en était et procéde à mettre à jour en mémoire les dernières données mosidiées qui n’ont pas encore fait l’objet d’un report dans les fichiers de données. Cette pahse étant, bien entendu effectuée avec un accès exclusif du moteur interdisant tout accès externe en lecture comme en écriture…

Or cette phase de récupération peut s’avérer parfois longue, notamment dans des bases fortement sollicitées. Aussi Microsoft SQL Server propose t-il de contrôler la durée de cette phase en la limitant. Si vous décidez d’un temps de récupération faible, mettons 20 secondes, ceci provoqueras des salves d’écriture des fichiers de données plus fréquentes qui peuvent peser sur les performances. Si vous décidez d’un temps de récupération log, par exemple 2 minutes, alors les écritures dans les fichiers de données seront moins fréquentes donc péserons moins sur les perfoemances sauf si vous avez une mémoire limitée…

Pour paraméter ce délai (approximatif) , vous devez utiliser la commande suivante :

ALTER DATABASE { nom_base | CURRENT } 
   SET TARGET_RECOVERY_TIME = entier { SECONDS | MINUTES } [;]

1 2	ALTER DATABASE { nom_base \| CURRENT } SET TARGET_RECOVERY_TIME = entier { SECONDS \| MINUTES } [;]

4 – Les paramètres de l’installation

Ces paramètres sont externes à SQL Server et se règlent dans le système ou par une interface spécifique.

4.1 – Initialisation instantannée des fichiers

Instant FIle Initialization (IFI) est appelé « tâche de maintenance de volume » en français, ce qui n’a pas grand chose à voir avec le schmilblic…

Peut être ne le savez vous pas, mais SQL Server gère ses fichiers (de données, du journal de transactions, des sauvegardes…) comme des volumes UNIX, c’est à dire qu’il acquiert du volume, alors même qu’il est vide, à la manière d’un disque virtuel, et son contenu est formatté de manière particulière avec des blocs physique de 64 Ko eux mêmes découpé en 8 pages de 8 Ko, ceci afin de faciliter les opérations d’entrée/sortie entre le stockage (disque) et la mémoire (RAM). Ce formatage prend généralement du temps…

Si vous activer l’Instant FIle Initialization alors ce formatage n’est pas effectué à la création des fichiers, mais lors des opérations d’écriture, ce qui les retarde. En revanche l’affectation du volume du fichier est alors quasi instatanné.

L’activation de l’Instant FIle Initialization se fait au niveaux des autorisations système (polices de sécurité). Tout d’abord il vous faut récupérer le nom du compte de service de SQL Server par exemple en le copiant de du gestionnaire de configuration :

Figure 09 – Gestionnaire de configuration des services SQL Server et comptes de service

Vous devez lancez l’interface des Stratégies locales de sécurité (SecPol.msc)et allez dans l’item « Attribution des droits utilisateurs » dans l’arboescence, et sélectionner la stratégie « Effectuer des tâches de maintenance de volume » :

Figure 10 – Stratégies de sécurité locales (SecPol)

Associez alors le compte de service de votre instance de SQL Server à ce droit…

Figure 11 – Compte de service SQL Server ajouté aux « taches de maintenance de volume »

Vous devez redémarrer le service SQL Server.

Ceci ne vous oblitère pas d’une bonne gestion du stockage des bases… Point important de l’obtention des performances que nous verrons dans un chapitre ultérieur.

Avantage : lors d’un redémarrage de votre instance, les fichiers de données de la base tempdb qui est recréée à chaque relance, sont instanciés immédiatement. La reprise du fonctionnement de l’instance est alors plus rapide…

Inconvénient : toutes les écritures qui doivent utiliser de nouvelles pages sont retardées pour formatter d’éventuels nouvelles pages et nouveaux blocs de 64 Ko.

Mais l’activation de cette stratégie induit un risque de sécurité non négligeable. En effet, lors de la création de fichier avec IFI activé, les pages non formatées contiennent probablement des informations qui sont celle d’anciens fichiers effacés du disque (NTFS comme REFS ne remettent pas à zéro les bits des fichiers supprimés, cela serait trop couteux et trop long…). Il en résulte que des personnes malintentionnées pourraient alors aller lire ces pages, soit à travers SQL Server (commande DCBB PAGE…) soit via un éditeur de texte lorsque l’instance est au repos.

Pour ma part je ne suis pas favorable à cette pratique, tant du point de vue perofmances que du point de vue sécurité et je pense qu’il vaut mieux gérer le stockage en amont des problématiques, c’est à dire faire du « capacity planning », chose dont nous parlerons dans un chapitre ultérieur.

4.2 – Verrouillage des pages en mémoire

Le verrouillage des pages en mémoire (Lock Page In Memory ou LPIM) est une stratégie de Windows qui empêche le système, autant que faire se peut, de paginer les données de SQL Server dans la mémoire virtuelle (donc sur le disque) en cas de forte sollicitation de la mémoire. LPIM verrouille les pages de données en mémoire physique (RAM) au lieu de les déverser dans le fichier pagefile.sys.

Si vous rencontrez l’erreur 17 890 dans le journal d’événements de SQL Server c’est que votre système rencontre des problèmes de pagination mémoire. Vous devriez alors avoir une dégradation des performances d’accès aux données, même si cette dégradation est lente et progressive.

Pour activer cette fonctionnalité, vous devez lancez l’interface des Stratégies locales de sécurité (SecPol.msc) et allez dans l’item « Attribution des droits utilisateurs » dans l’arboescence, et sélectionner la stratégie « Verrouiller les pages en mémoire« . Ajoutez alors le compte de servcie de SQL Server comme vu au § 4.1.

Ceci est aussi généralement intéressant pour des VM fortement sollicitées.

4.3 – Pages « large »

En mémoire, Windows utilise par défaut des pages de 4 Ko (systèmes 64 bits) pour organiser son cache. SQL Server utilise, lui, des pages de 8 Ko organisées en blocs de 8 pages contigües (soit 64 Ko) appelées extensions. L’activation des pages larges permet à SQL Server d’utiliser des pages de 2 Mo soit 512 fois plus grosses, chacune de ces pages de 2 Mo pouvant accueillir 256 pages de données de 8 Ko de SQL Server ou encore 32 extensions.

Ce bénéfice assez modeste ne peut être réalisé que lorsque de nombreuses conditions sont préalablement remplies :

Vous devez utiliser l’édition Enterprise de SQL Server
Votre instance doit avoir au moins 32 Go de RAM.
Il est nécessaire que le compte de service de l’instance possède l’autorisation de verrouillage des pages en mémoire (« Lock Pages in Memory » – voir ci-avant).
Il est nécessaire d’activer le drapeau de trace 834 et de le maintenir dans les paramètres de démarrage du service SQL Server

Mise en oeuvre :

Allez dans le gestionnaire de configuration de SQL Server et dans l’arborescence cliquez sur « Services SQL Server » afin de faire apparaître les moteurs de vos différentes instances. Pointez sur le service SQL Server de l’instance désirée, cliquez droit et sélectionnez l’item « Propriété » du service. Dans l’onglet « Paramètres de démarrage » tappez la commande :

-T834

-T834

et cliquez sur le bouton « Ajouter ».

Figure 12 – Activation des pages mémoire larges pour SQL Server

N’oubliez pas de redémarrer l’instance MS SQL Server pour forcer le service à lire au démarrage ce drapeau de trace et l’appliquer.

Vérification :

SELECT large_page_allocations_kb 
FROM   sys.dm_os_process_memory

1 2	SELECT large_page_allocations_kb FROM sys.dm_os_process_memory

Conseil :

Le bénéfice de l’utilisation de ce mécanisme est assez rare en pratique et ne doit être adopté que pour de très lourdes charges de travail qui traitent des milliers de transactions par seconde et pour des serveurs dotés d’un très grande qualité de RAM (au moins 256 Go).

4.4 – CEIP

Par défaut les instances sont configurées pour envoyer à Microsoft des informations de fonctionnement et de levé d’exception (CEIP : Customer Experience Improvement Program). Pour des instances fortemment sollicités ou confidentielles (santé, bancaire, énergie, transports, communications, …) il convient de désactiver cette fonctionnalité.

Désactiver avec l’IHM

Lancez l’interface de contrôle de CEIP en recherchant dans le menu Windows « Rapports d’erreurs et d’utilisation SQL Server #### » (ou #### est le numéro de version de SQL Server (2017, 2019, 2022…).

Figure 13 – Rechercher CEIP dans Windows

En cas de pluralité d’instance, prendre la toute dernière (2022 dans notre exemple).

L’interface suivante s’ouvre :

Figure 14 – Interface CEIP

Appuyez sur le bouton « Options » pour connaître les éléments mesurés :

Figure 15 – CEIP détails des coches

Décochez les cases :

« Envoyez les rapports d’erreur… »
« Envoyez les rapports d’utilisation… »

Ceci décoche les éléments dans le détail. Appuyez sur OK pour confirmer.

Vérifier avec le registre

Pour vérifier si les éléments ont bien été désactivés, des clés de registre sont disponibles. Vous pouvez scruter la base de registre comme suit…

Pour les moteurs de données, OLTP (SQL Server) et OLAP (SSAS), les clés à vérifier sont situées dans le chemin :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\{ID_instance}\CPE

1	HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\{ID_instance}\CPE

Et leurs noms sont :

CustomerFeedback
EnableErrorReporting

La valeur présentée de type DWORD doit être 0 (pour refuser).

La chaine de caractères {InstanceID} fait référence au nom d’instance de SQL Server (par défaut ou nommée).

Dans notre cas, (SQL Server 2022), le chemin de la clé doit être :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL16.CEB_2022_02\CPE

1	HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL16.CEB_2022_02\CPE

Figure 16 – Éditeur de registre pour paramétrage CEIP

Au niveau de SQL Server Reporting Services (SSRS), le chemin de la clé à désactiver est le suivant :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\SSRS\CPE

1	HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\SSRS\CPE

Les noms des clés sont les suivants :

CustomerFeedback
EnableErrorReporting

Type de données DWORD.

Valeurs possibles :

0 pour refuser
1 pour accepter

Pour les fonctionnalités partagées, le chemin des clés est :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\{Major Version}

1	HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\{Major Version}

Et leurs noms sont :

CustomerFeedback
EnableErrorReporting

La valeur présentée de type DWORD doit être 0 (pour refuser).

La chaine de caractère {Major Version} fait référence à la version de SQL Server. Par exemple, 140 pour SQL Server 2017, 150 pour 2019, 160 pour 2022.

ATTENTION : ne pas désactiver le login Windows NT SERVICE\SQLTELEMETRY car il est utilisé pour les sessions d’événement étendu. Une telle désactivation entrainerait un manque important des données de diagnostic interne de SQL Server.

4.5 – Mémoire compressé

La compression de la mémoire a été intégrée à partir de la version 7 de Windows (Windows Server 2008 R2) et est activée par défaut depuis la version 10 de Windows (Windows Server 2016/2019).
Si vous ouvrez le gestionnaire de tâche et allez dans l’onglet « Performance » voir la mémoire, vous constaterez en dessous du graphique sur la partie de gauche que vous utilisez probablement de la mémoire compressée…

Figure 17 – État de la mémoire au niveau du gestionnaire de tâche de Windows

Ceci prends un peu de ressources au niveau des processeurs pour compresser et décompresser la mémoire en fonction de l’usage qui en est fait.
En fait cette compression se situe algorithmiquement entre l’utilisation directe de la RAM et la mémoire paginée dans le fichier pagefile.sys.

Si votre serveur dispose d’une grande quantité de mémoire et n’est pas en état de stress sur la RAM, alors la désactivation de cette mémoire compressée libère un peu de cycles CPU qui pourront être utiliés à d’autres tâches. Pour cela, vous devez utiliser PowerShell avec les commandes suivantes :

Get-MMAgent : donne l’état de la mémoire.
Disable-MMAgent -mc ou bien
Disable-MMAgent -MemoryCompression : désactive la mémoire compressée

Bien entendu « Enable » avec les deux dernières commandes, réactive cette mémoire compressé.

Figure 18 – Utilisation de PowerShell pour désactiver la mémoire compressée

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL Spécialiste MS SQL Server - SQLpro(@@)SQLspot.com - SQL SPOT SARL (Paris, PACA)* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com * * le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ * * Expert Microsoft SQL Server, MVP Most valuable Professional - pendant 15 ans * * Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *

SQL Server Performances – CHAPITRE 2 – Faut-il paramétrer, régler, tuner son instance ?

0 – L’instance SQL Server

0.1 – De multiples instances ?

1 – Les paramètres d’instance

1.1 – Paramètres pour la récupération accélérée (nouveau en 2022) :

1.2 – Masques d’affinité

1.3 – NUMA

1.4 – Accélération matérielle

1.5 – Sauvegardes

1.6 – Blocked Process Treshold

1.7 – Réglage du parallélisme

1.7.1 – Max Degree Of Parallelism (DOP)

1.7.2 – Cost treshold for parallelism

1.8 – Index create memory

1.9 – Lightweight pooling

1.10 – Full Text (indexation textuelle)

1.11 – Réglage de la mémoire cache

1.12 – Réglages des threads de travail

1.13 – Mémoire minimale par requête

1.14 – Taille des paquets réseaux

1.15 – Optimisation des requêtes « ad hoc »

1-16 – Mise en « in memory » des métadonnées de la base tempDB

2 – Réglages au niveau instance

2.1 – Réglage des affinités pour les processus logiques

2.2 – Extension du cache des données

2.3 – Activation de NUMA au niveau « software »

2.4 – Mise en « in memory » des métadonnées de la base tempdb et pool de mémoire tampon hybride

2.5 – Activation de l’accélération matérielle

3 – Les réglages au niveau des bases

3.1 – Automatisation des opérations

3.2 – Corrélation des type DATETIME pour optimisation

3.3 – Journalisation asynchrone

3.4 – Allocation mixte des pages dans une même extension

3.5 – Paramétrisation des requêtes

3.6 – Mode de compatibilité

Bien entendu et à tout moment vous pouvez revenir à n’importe quelle version rétrograde de SQL Server…

Pour information, le mode de rétrocompatibilité est limité par le tableau présenté à la figure 8, suivant :

3.7 – Verrouillage optimiste

Verrouillage pessimiste vs optimiste

3.8 – Automatic Tuning

3.9 – Cible de durée maximale de récupération

4 – Les paramètres de l’installation

4.1 – Initialisation instantannée des fichiers

4.2 – Verrouillage des pages en mémoire

4.3 – Pages « large »

4.4 – CEIP

4.5 – Mémoire compressé

Articles récents

Commentaires récents

Archives

Catégories

Méta