Réglage et optimisation des performances de Microsoft SQL Server pour développeurs

Sur cette page

Réglage et optimisation des performances de MS SQL Server pour les développeurs - Partie 3 : Configuration des performances
Vue d'ensemble
Informations de configuration de SQL Server
sysconfigures
syscurconfigs
Gestion de la mémoire et des E/S disque
Utilisation de la mémoire par SQL Server.
Ressources globales
Options de configuration relatives à la mémoire
Mémoire
DBCC MEMUSAGE
DBCC MEMUSAGE
tempdb en mémoire vive (RAM)
Altération de la tempdb en mémoire vive
Cache de données SQL Server
Page incorrecte
Page libre
Hachage du gestionnaire de tampons
Exemple
Point de contrôle
Processus des points de contrôle
Processus d'écriture différée
Vidage des pages incorrectes
E/S asynchrones
Max E/S async
Max E/S d'écriture différée
Configuration du Gestionnaire de tampons
Forcer les pages à rester en cache
Syntaxe partielle
Traitement de la lecture anticipée
Configuration de la lecture anticipée
Examen et contrôle de la lecture anticipée
Cache de procédures SQL Server
Organisation du cache de procédures
Dimensionnement du cache de procédures
Option de configuration du cache de procédures
Autres options de configuration SQL Server
Max Worker Threads
Logwrite Sleep
Configuration des sessions et des bases de données
Options de base de données
Options de table

Réglage et optimisation des performances de MS SQL Server pour les développeurs - Partie 3 : Configuration des performances

DAT 412
Présenté à Tech-Ed 1997

Adam Shapiro
Directeur de programme
Microsoft Corporation

Vue d'ensemble

Informations de configuration de SQL Server

Microsoft® SQL Server™ offre plusieurs options de configuration d'un serveur SQL Server qui affectent son comportement. Les plus importantes seront présentées dans ce module.

À l'exception d'un petit nombre d'options devant être modifiées pour être en conformité avec votre environnement matériel, la plupart de ces options ne doivent pas être modifiées.

Les informations de configuration du système sont stockées dans les quatre emplacements suivants :

sysconfigures

Ce tableau système contient une ligne par option de configuration pouvant être définie par un utilisateur. Il contient les options de configuration qui ont été définies avant le dernier démarrage de SQL Server, plus les options de configuration dynamique qui ont été définies depuis le dernier démarrage de SQL Server.

syscurconfigs

Ce tableau système contient une entrée pour chacune des options de configuration, tout comme sysconfigures, mais syscurconfigs contient les valeurs utilisées, ainsi quequatre entrées décrivant la structure de configuration.

Bien qu'il soit présenté sous forme de tableau dans sysobjets, c'est, en fait, un pseudo tableau qui est créé uniquement sur demande. Si vous consultez la ligne sysindexes de syscurconfigs, vous constaterez qu'elle ne comporte pas de ligne de données.

Les valeurs de configuration peuvent être modifiées par le biais de SQL Enterprise Manager ou en exécutant la procédure stockée système sp_configure. Si vous exécutez sp_configure pour modifier une option de configuration, vous devez exécuter l'instruction RECONFIGURE. SQL Enterprise Manager, par contre, exécute automatiquement l'instruction RECONFIGURE.

Bloc de configuration

L'instruction RECONFIGURE installe une option de configuration modifiée. Cela signifie que le contenu du tableau syscurconfigs est écrit sur les quatre premières pages de l'unité MASTER. Ces pages sont appelées bloc de configuration. Ses valeurs sont lues dans le tableau sysconfigures à partir de l'unité MASTER lors du lancement suivant de SQL Server.

Registre Windows NT

Lorsque SQL Server est lancé, il lit le bloc de configuration depuis l'unité MASTER, dont l'emplacement est précisé dans le Registre Microsoft Windows NT® .

Gestion de la mémoire et des E/S disque

La quantité de mémoire allouée à SQL Server est probablement le plus important ajustement de configuration.

Utilisation de la mémoire par SQL Server.

Ressources globales

Les ressources globales sont celles utilisées par tous les processus et toutes les bases de données. La zone des ressources globales est essentiellement constituée de pointeurs vers d'autres structures telles que les structures qui commandent chaque base de données définie sur le serveur, les tables de hachage des pages, les extensions et les procédures, les informations sur le jeu de caractères, l'ordre de tri du serveur, etc. Cette zone comprend aussi le code d'exécution de SQL Server.

Connexions utilisateur

Chaque connexion utilisateur nécessite environ 27 Ko de mémoire SQL Server pour les structures du serveur, plus 3 Ko pour les structures ODS (Open Data Services), soit 30 Ko au total. Cette mémoire est utilisée pour la structure d'état de processus (PSS), les descripteurs de transaction, les descripteurs de session, le cache de protection, l'enregistrement de la connexion et la zone de travail de l'analyseur, toutes ces parties étant allouées à partir du segment créé par SQL Server au moment de son initialisation.

En outre, une pile globale de 1 Mo est réservée aux connexions utilisateur, mais cette mémoire n'est pas immédiatement validée. Si pour son exécution, SQL Server a besoin de plus d'espace de pile, de la mémoire supplémentaire est validée jusqu'à la limite de 1 Mo. Chaque nouveau thread (c'est-à-dire, une connexion utilisateur en cours d'utilisation) nécessite environ 20 Ko de la pile.

Ainsi, chaque connexion utilisateur nécessite au total 50 Ko jusqu'à la limite du paramètre max worker threads ; par la suite, chaque connexion nécessite 30 Ko.

Verrous, bases de données ouvertes et objets ouverts

La mémoire est allouée aux verrous, aux bases de données ouvertes et aux objets ouverts, sur la base de la valeur de configuration de chacun d'eux. Les valeurs suivantes peuvent être utilisées pour estimer la mémoire totale consommée par ces options :

Verrou	60 octets
Base de données ouverte	162 octets
Objet ouvert	240 octets

Pour obtenir plus d'informations sur l'utilisation réelle de la mémoire pour un système, reportez-vous à l'indicateur de trace 3635 avant d'exécuter DBCC MEMUSAGE.

Cache de procédures et cache de données

Le cache de procédures et le cache de données seront expliqués en détail ultérieurement dans ce module.

Options de configuration relatives à la mémoire

Mémoire

Cette option définit la taille de la mémoire disponible, par unité de 2 Ko. La valeur initiale est déterminée par le programme d'installation setup sur la base de la quantité de mémoire de l'ordinateur.

Pour optimiser la quantité de mémoire d'un système, vous devez soustraire la mémoire nécessaire à Windows NT (et aux autres applications du système si l'ordinateur n'est pas entièrement dédié à SQL Server) au total de la mémoire physique.

Idéalement, vous devez allouer le plus de mémoire possible à SQL Server sans entraîner l'utilisation de la pagination. L'Analyseur de performances Windows NT peut vous aider à déterminer le seuil du système. Le compteur Défauts de page/s de l'objet Mémoire indique si des erreurs de pagination sont actuellement générées. Dans l'affirmative, cela signifie que SQL Server utilise trop de mémoire. Le seuil varie en fonction du système.

Le tableau ci-dessous suggère des valeurs de démarrage appropriées pour allouer la mémoire à SQL Server. Ces valeurs sont prévues pour un ordinateur dédié à SQL Server.

Mémoire vive (Mo)	Allocation de mémoire SQL Server (en Mo)	Allocation de mémoire SQL Server (en unités de 2 Ko)
16	4	2,048
24	8	4,096
32	16	8,192
64	40	20,480
128	100	51,210
256	216	110,592
512	464	237,568

Cette valeur doit être modifiée uniquement lorsque de la mémoire est ajoutée ou supprimée d'un système, ou lorsque l'utilisation du système change.

Lorsque l'option memory est reconfigurée, elle entraîne la modification automatique de l'option de configuration free buffers . La valeur configurée de free buffers sera définie à 5 % de la nouvelle taille de la mémoire. La signification de cette valeur sera expliquée ultérieurement.

La valeur maximale de l'option memory est de 2 Go. À noter cependant, que l'option memory ne comprend pas les besoins en mémoire de tempdb si vous avez placé tempdb en mémoire vive en utilisant l'option tempdb in RAM.

Bases de données ouvertes

Cette option définit le nombre maximal de bases de données pouvant être ouvertes simultanément sur SQL Server. La valeur par défaut est 20. Étant donné que les bases de données ouvertes utilisent de la mémoire, il est possible que l'augmentation de cette valeur vous oblige à augmenter la mémoire dédiée au serveur.

Objets ouverts

Cette option définit le nombre maximal d'objets de base de données pouvant être ouverts simultanément sur SQL Server. La valeur par défaut est 500.

Augmentez cette valeur si SQL Server affiche un message vous informant que vous avez dépassé le nombre d'objets ouverts. Étant donné que les objets ouverts utilisent de la mémoire, il est possible que l'augmentation de cette valeur vous oblige à augmenter la mémoire dédiée au serveur.

Connexions utilisateur

Cette option définit le nombre maximal de connexions simultanées à SQL Server autorisé. Le nombre réel de connexions possibles peut être inférieur à cette valeur, en fonction de l'environnement de votre base de données.

Le nombre de connexions utilisateur autorisé dépend de la version. Pour SQL Workstation, le nombre de connexions utilisateur est de 15, et pour SQL Server, de 32 767. Toutefois, le nombre réel est basé sur des limites pratiques qui varient en fonction de votre application et de votre matériel.

Utilisez cette instruction pour obtenir le nombre maximal de connexions utilisateur paramétrable sur votre système :

SELECT @@max_connexions

Il n'existe pas de formule pour déterminer le nombre de connexions à autoriser pour chaque utilisateur. Ce nombre doit être défini selon les besoins du système et des utilisateurs. Les utilisateurs qui exécutent des applications DB-Library ou ODBC peuvent établir plusieurs connexions dans une application. Sur un système multi-utilisateurs, les connexions occasionnelles peuvent être partagées entre les utilisateurs.

Verrous

Cette option définit le nombre de verrous disponibles. Les verrous ne sont pas partagés de la même manière que les bases de données ouvertes et les objets de base de données ouverts. La valeur par défaut est 5 000.

Augmentez cette valeur si SQL Server affiche un message vous informant que vous avez dépassé le nombre de verrous disponibles. Étant donné que chaque verrou consomme de la mémoire (32 octets par verrou), il est possible que l'augmentation de cette valeur vous oblige à augmenter la mémoire dédiée au serveur.

Cache de procédures

Cette option définit le pourcentage de mémoire allouée au cache de procédures après que les besoins en mémoire de SQL Server ont été satisfaits. Les besoins en mémoire de SQL Server correspondent à la somme de mémoire nécessaire aux verrous, bases de données ouvertes, objets ouverts, connexions utilisateur, ainsi qu'au code lui-même et aux ressources globales. Le reste de la mémoire est divisé entre le cache de procédures et le cache de données, conformément au pourcentage défini par cette option de configuration.

Le cache de procédures est la zone de mémoire dans laquelle sont stockées les dernières procédures utilisées. Le cache de procédures est aussi utilisé lorsqu'une procédure est en cours de création et lorsqu'une requête est en cours de compilation. La valeur par défaut est de 30 pour l'option de configuration du cache de procédures, ce qui donne au cache de procédures 30 % du reste de la mémoire après que les besoins en mémoire de SQL Server ont été satisfaits. Le cache de données dispose des 70 % restants.

Puisque la valeur optimale de cette option de configuration est différente d'une application à une autre, sa modification peut améliorer les performances de SQL Server. Par exemple, si vous exécutez plusieurs procédures ou requêtes ad hoc différentes, votre application fera plus appel au cache de procédures, et vous devrez augmenter cette valeur. C'est le cas de nombreuses applications en cours de développement.

Si vous ajoutez de la mémoire afin d'améliorer le taux de présence dans le cache de données, le pourcentage du cache de procédures peut être réduit pour conserver une taille relativement constante.

DBCC MEMUSAGE

DBCC MEMUSAGE

L'option MEMUSAGE de l'instruction DBCC permet d'obtenir un rapport détaillé sur l'utilisation de la mémoire. Ce rapport recense trois types d'informations :

• Allocation de la mémoire au serveur au moment du démarrage

• Quantité de mémoire utilisée par les 20 plus grands objets du cache de tampons.

• Quantité de mémoire utilisée par les 20 plus grands objets du cache de procédures : procédures stockées, déclencheurs, vues, règles et paramètres par défaut.

Si plusieurs copies d'un objet se trouvent dans le cache de procédures, DBCC MEMUSAGE calcule la mémoire qu'elles utilisent. Parmi ces copies, certaines peuvent être des versions pré-compilées de l'objet (arborescences) et d'autres des versions compilées (plans). DBCC MEMUSAGE indique la taille des arborescences et des plans ainsi que le nombre total d'arborescences et de plans dans le cache.

tempdb en mémoire vive (RAM)

La base de données tempdb est un espace de travail utilisé pour le tri et pour la création des tables temporaires dans quelques opérations de jonction. Elle est aussi utilisée lorsque les programmeurs créent des tables temporaires explicites ou des procédures stockées temporaires.

L'option de configuration tempdb in RAM permet à la base de données tempdb de résider en mémoire. Dans quelques situations spécifiques, ceci peut avoir un impact positif sur les performances. Toutefois, si tempdb in RAM est utilisée de manière inappropriée, elle peut consommer de la mémoire qui serait autrement utilisée pour le cache de données SQL Server, et ceci peut altérer les performances.

Dans la plupart des cas, il vaut mieux utiliser la mémoire vive disponible en tant que cache de données plutôt que comme emplacement de tempdb. Les données de tempdb seront elles-mêmes mises en cache en utilisant l'algorithme LRU du cache de données SQL Server.

L'utilisation de tempdb in RAM peut accélérer les opérations de la base de données tempdb, mais cela diminue la mémoire disponible pour le cache de données SQL Server, ce qui peut entraîner une baisse du taux de présence dans le cache. La mémoire utilisée pour tempdb in RAM est allouée séparément de la réserve définie par l'option memory, et le serveur doit être configuré en conséquence.

Par exemple, si vous définissez 100 Mo de tempdb in RAM, il est possible que vous deviez réduire de 100 Mo la valeur de l'option memory afin de libérer de la mémoire à cet effet. Par contre, l'allocation de toute la mémoire disponible à SQL Server, sans en réserver une partie pour tempdb in RAM, peut augmenter le taux de présence dans le cache. SQL Server mettra en cache toutes les opérations d'E/S du disque, y compris celles qui concernent tempdb.

La quantité limitée de mémoire vive disponible sur de nombreux ordinateurs constitue une contrainte pour la taille de mémoire autorisée de tempdb lorsqu'elle est placée en mémoire vive. Si des besoins d'accroissement non prévus pour tempdb se concrétisent, ceci peut poser un problème. Si c'est le cas, ne placez pas tempdb en mémoire vive.

Il est généralement plus avantageux d'allouer la mémoire vive disponible au cache de données SQL Server que d'en allouer une grande partie à tempdb in RAM. Toutefois, l'utilisation de tempdb in RAM peut être bénéfique si toutes les conditions suivantes sont satisfaites :

• Le système dispose de suffisamment de mémoire vive, à savoir, plus de 64 Mo (généralement 128 Mo ou plus).

• Les applications ont un emplacement de référence tel que le taux de présence dans le cache SQL Server est faible, même avec un espace de cache de données important.

• Les applications exécutent de nombreuses opérations tempdb . Vous pouvez observer la taille de tempdb avec l'Analyseur de performances. Examinez dans l'objet SQL Server le compteur Max TempDB Space Used (Mb). Ce compteur affiche l'espace maximal, en méga-octets, utilisé par la base de données tempdb au cours de la session de l'Analyseur de performances SQL.

• Les opérations tempdb sont adaptées à la taille de la base de données tempdb autorisée par la configuration de la mémoire vive.

Si vous décidez de placer tempdb en mémoire vive, il est conseillé de vérifier objectivement le bénéfice des performances ainsi obtenu. Pour cela :

1. Sélectionnez une requête ou un petit jeu de requêtes qui représentent les opérations intensives les plus fréquemment exécutées de tempdb.

2. Recommencez plusieurs fois en notant le temps d'exécution.

3. Reconfigurez tempdb **** en mémoire vive, réexécutez les mêmes requêtes et notez la différence.

Si le niveau d'amélioration n'est pas significatif, il est conseillé de réattribuer la mémoire vive au cache de données SQL Server.

Le placement de tempdb en mémoire vive est sûr et n'altère ni l'intégrité ni la possibilité de restauration de la base de données, parce que tempdb est uniquement utilisée pour les opérations intermédiaires et est entièrement reconstituée à chaque redémarrage du serveur.

L'option tempdb in RAM est un outil pouvant avoir un impact sur les performances, mis à disposition pour les cas où l'analyse en montrerait le bénéfice. Dans quelques cas, il peut procurer une amélioration significative des performances, mais ne doit pas être utilisé sans discrimination.

Altération de la tempdb en mémoire vive

La base de données tempdb peut être étendue, y compris tempdb en mémoire vive. Toutefois, lorsque tempdb réside en mémoire vive, elle ne peut être altérée que 10 fois sans nécessiter la fermeture et le redémarrage du serveur. L'altération de tempdb lorsqu'elle réside en mémoire vive implique que pour chaque altération de la base de données, une nouvelle "portion" de mémoire contiguë soit allouée à tempdb. Cette mémoire, même si elle est contiguë, n'est pas nécessairement située à côté des portions existantes de tempdb en mémoire vive. Pour obtenir une performance maximale, le serveur doit être arrêté et redémarré après une altération de tempdb.

Suppression de tempdb de la mémoire vive

Vous pouvez supprimer tempdb de la mémoire vive en remettant la valeur de configuration à 0. Cette action entraînera la création de tempdb sur une unité par défaut, avec une taille par défaut de 2 Mo.

Remarque Si tempdb se trouve en mémoire vive au moment d'une mise à jour, elle sera déplacée hors de la mémoire vive. Une unité de disque par défaut doit disposer d'un espace disponible minimal (2 Mo) pour créer tempdb. Sinon, le démarrage de SQL Server échouera. Afin d'imposer temporairement à tempdb 2 Mo en mémoire vive, utilisez l'indicateur /f sur la ligne de commande de SQLSERVR.EXE.

Cache de données SQL Server

Le cache de données est un ensemble de tampons (ou de cadres de page) utilisé pour accueillir les pages qui ont été lues à partir du disque. Il est contenu dans la mémoire allouée à SQL Server. Il est aussi appelé cache de pages ou cache de tampons.

La quantité de mémoire allouée à SQL Server est probablement l'ajustement de configuration le plus important à effectuer. Et ce, parce que la quantité du cache de données est directement proportionnelle à la quantité de mémoire allouée. La présence d'une page dans le cache de données dispense d'une lecture physique (à partir du disque), opération la plus coûteuse que SQL Server puisse exécuter.

Définition de quelques termes utilisés dans cette section comprennent :

Page incorrecte

Page dont le contenu a changé suite à des instructions de modification des données, et qui n'a pas encore été écrite sur le disque.

Page libre

Page qui n'est utilisée par aucun processus et qui n'est pas incorrecte.

Chaîne LRU/MRU

Liste chaînée de pages, ordonnées de la moins récemment utilisée à la plus récemment utilisée.

Hachage du gestionnaire de tampons

Les pages du cache de données sont hachées pour permettre à SQL Server de les trouver rapidement ou de déterminer qu'elles ne se trouvent pas dans le cache. L'option de configuration hash buckets définit le nombre de "compartiments" qui seront utilisés pour le hachage des pages du cache de données dans la mémoire. Plus le nombre de compartiments de hachage est élevé, plus SQL Server trouvera rapidement une page référencée.

Le but est de limiter la taille de la chaîne pour tout compartiment de hachage particulier. Plus le nombre de compartiments est élevé, plus les chaînes sont courtes. Même un très grand cache de données peut être parcouru rapidement en n'utilisant qu'un petit nombre de compartiments de hachage.

Exemple

Le nombre par défaut de compartiments de hachage est de 8 Ko environ. Si chaque compartiment de hachage est de longueur optimale (quatre pages), SQL Server peut gérer un cache de données de :

4 x 8 Ko = 32 Ko pages = 64 Mo

En utilisant le nombre maximal de compartiments de hachage (256 Ko) et la longueur optimale de la chaîne de hachage, SQL Server peut gérer un cache de données de :

4 x 256 Ko = 1024 Ko pages = 2 Go

Si les chaînes ont toutes une longueur inférieure à quatre pages, la configuration est optimale. Si la longueur de la chaîne est supérieure, augmentez la valeur de l'option de configuration hash buckets.

Ces compartiments de hachage consommant très peu de mémoire, SQL Server ajuste le nombre de compartiments de hachage utilisés sur un nombre premier, le plus proche de la valeur entrée. La valeur par défaut est de 7 993 compartiments de hachage, et la valeur maximale de 265 003.

DBCC BUFCOUNT peut être utilisé pour vérifier l'efficacité de la configuration de hachage utilisée.

DBCC TRACEON(3604)
GO
DBCC BUFCOUNT
GO
**** LES DIX PLUS LONGUES CHAÎNES ****
nombre = 20 taille de la chaîne = 2
nombre = 276 taille de la chaîne = 2
nombre = 532 taille de la chaîne = 2
nombre = 1044 taille de la chaîne = 2
nombre = 1300 taille de la chaîne = 2
nombre = 1556 taille de la chaîne = 2
nombre = 1812 taille de la chaîne = 2
nombre = 2324 taille de la chaîne = 2
nombre = 3092 taille de la chaîne = 2
nombre = 3604 taille de la chaîne = 2
La plus petite chaîne est : 0
La taille moyenne de la chaîne est : 0.671668

Point de contrôle

Processus des points de contrôle

Le processus des points de contrôle fonctionne en permanence sur SQL Server, généralement en état de veille. Vous pouvez exécuter la procédure sp_who pour le visualiser. Toutes les minutes, SQL Servet émet un point de contrôle pour inspecter le journal des transactions de chaque base de données. S'il détermine que suffisamment de travail a été effectué depuis le dernier point de contrôle, SQL Server émet un autre point de contrôle pour cette base de données.

La quantité de travail effectuée est déterminée par la valeur de l'option de configuration recovery interval, définie en minutes. Le processus des points de contrôle détermine si le nombre de transactions se trouvant dans le journal correspond au temps de récupération spécifié.

Instruction CHECKPOINT

L'instruction CHECKPOINT peut être émise manuellement par l'administrateur de base de données ou l'administrateur système.

Description du processus des points de contrôle

Lorsque SQL Server émet un point de contrôle, il écrit toutes les pages incorrectes sur le disque. Les pages de chaque base de données sont reliées entre elles de sorte que le point de contrôle puisse rapidement trouver les pages de la base de données soumise au point de contrôle.

Une fois émis (soit automatiquement par SQL Server, soit manuellement avec l'instruction CHECKPOINT), le point de contrôle comporte deux phases. Dans la première phase, SQL Server identifie toutes les pages qui doivent être vidées. Si la journalisation du point de contrôle est activée (par l'émission de l'indicateur de trace 3502), le journal des erreurs SQL Server dispose d'informations similaires aux suivantes :

Ptctrl dbid 6 started (4000) (début du point de contrôle)
Ptctrl dbid 6 phase 1 ended (0) (fin de la 1ere phase)
Ptctrl dbid 6 complete. (fin de la 2e phase et du point de contrôle)

Dans la seconde phase, le thread du point de contrôle vide toutes les pages identifiées. Cette phase dure beaucoup plus longtemps que la première, en fonction du nombre de pages à vider, de la vitesse du sous-système de disque, du niveau de saturation du serveur et d'autres facteurs. Lorsque toutes les pages incorrectes sont vidées du cache de données sur le disque, le point de contrôle est terminé.

Durant le processus de point de contrôle, le nombre d'écritures (Objet : SQLServer, Compteur : E/S – Écritures pages /sec.) peut augmenter. Ce compteur est généralement sur zéro. Pendant le processus des points de contrôle, au fur et à mesure que de nouvelles transactions nécessitent des pages de données qui n'ont pas été vidées par le thread du point de contrôle, SQL Server vide ces pages à la demande. Cette activité est normale.

Un point de contrôle est émis pour chaque base de données lorsque l'administrateur système émet une instruction SHUTDOWN. Les points de contrôle sont également émis lorsque SQL Server est en cours de démarrage ; après restauration, chaque base de données est soumise au point de contrôle.

Remarque Si l'option de base de données trun. log on chkpt. est activée, le journal est vidé chaque fois que le processus des points de contrôle est déclenché.

Processus d'écriture différée

SQL Server gère un processus (visible en exécutant sp_who) nommé écriture différée, qui démarre automatiquement le vidage des tampons lorsque le nombre de tampons disponibles tombe sous le seuil déterminé par l'option de configuration free buffers.

Le processus d'écriture différée évite d'avoir à effectuer fréquemment des points de contrôle en vue de créer des tampons disponibles. La taille des lots d'instructions E/S utilisés par l'écriture différée peut être définie par l'option de configuration max lazywrite IO.

Vidage des pages incorrectes

SQL Server vide les pages du cache sur le disque dans les conditions suivantes :

• Lorsqu'un point de contrôle est émis.

• Lorsqu'une nouvelle page doit être transférée dans le cache.

  Lorsque SQL Server détermine qu'une page qui est demandée ne se trouve pas dans le cache et qu'il n'y a pas de tampon dans la liste des tampons libres, il recherche un tampon libre dans la chaîne de pages du cache. À la fin de l'examen de la chaîne entière, la page du plus ancien tampon est vidée sur le disque.

  Si aucun tampon non utilisé n'est disponible, et si aucun tampon ne se trouve dans la liste des tampons libres, le processus requérant la page est suspendu jusqu'à ce qu'une page soit disponible.

• À la fin de SELECT INTO ou à la fin de chaque lot d'instructions de bcp.

  Lorsque le bcp rapide (version non consignée dans le journal) est utilisé, ou lorsqu'une table est créée avec SELECT INTO, les lignes nouvellement insérées ne sont pas consignées dans le journal. Par ailleurs, certaines ou la totalité d'entre elles peuvent encore se trouver dans le cache. Pour éviter de perdre des données, en cas d'arrêt de SQL Server sans point de contrôle, toutes les pages incorrectes sont vidées sur le disque à la fin du lot d'instructions bcp ou de SELECT INTO.

• Lorsqu'une transaction est terminée, les pages du journal sont vidées.

  Lorsqu'une transaction est soit validée soit abandonnée, les pages du journal sont vidées sur le disque.

• Lorsqu'une page est divisée.

  Lorsqu'une page est divisée, la page nouvellement allouée est immédiatement vidée sur le disque.

• Lorsque LOAD DATABASE est exécutée.

  Lorsqu'une base de données est en cours de restauration par l'instruction LOAD DATABASE, toutes les pages du vidage sont écrites directement sur le disque. De plus, les pages de la base de données qui ne font pas partie de la sauvegarde sont également initialisées et vidées sur le disque. Par exemple, si vous chargez une sauvegarde à partir d'une base de données de 4 Mo dans une base de données de 10 Mo, les 6 Mo de pages restantes seront toutes initialisées et écrites sur le disque.

• Lorsque le processus d'écriture différée est exécuté.

  Le processus d'écriture différée démarre automatiquement le vidage des tampons lorsque le nombre de tampons disponibles tombe sous un certain seuil. Si aucun tampon n'est disponible pour le vidage, l'écriture différée consigne un message dans le journal des erreurs.

E/S asynchrones

Niveaux de performance accrus sur certains sous-systèmes de disque

Pour comprendre l'impact des E/S asynchrones sur les performances selon le type de sous-système de disque utilisé, nous considérerons trois types de sous-systèmes de disque. Le premier est un contrôleur non intelligent relié à quatre unités de disque. Le second comporte quatre contrôleurs non intelligents, chacun étant relié à une unité de disque. Le troisième est un contrôleur intelligent connecté à quatre unités de disque.

Contrôleur unique et quatre unités de disque

Voyons tout d'abord le transfert de données avec un contrôleur unique non intelligent et quatre unités. Au cours de la séquence de transfert vers l'extérieur, le pilote de périphérique transfère un tampon de données vers la mémoire tampon interne du contrôleur. Cela s'effectue rapidement via l'accès direct à la mémoire (DMA), la mémoire partagée ou les E/S programmées, en quelques centaines de microsecondes (aux vitesses habituelles des bus). Puis, le contrôleur (avec plus ou moins d'assistance du pilote de périphérique) doit commander à l'unité les opérations de positionnement. Ceci peut prendre jusqu'à 50 millisecondes, c'est-à-dire un temps de transfert des centaines de fois plus long que le transfert bus-contrôleur.

Ensuite, les données sont transférées de la mémoire tampon du contrôleur vers l'unité de disque, au taux de transfert déterminé par le type d'unité. Il peut également se produire un temps d'attente avant le démarrage du transfert pendant lequel, sur de nombreux systèmes, le pilote de périphérique et la tâche qui l'a appelé doivent simplement attendre que l'unité de disque dur soit disponible. Les opérations ne peuvent être exécutées sur la seconde unité et sur les unités suivantes que lorsque la première unité a terminé, car le contrôleur ne possède pas la logique nécessaire au suivi de plusieurs opérations en cours.

Quatre contrôleurs reliés à des unités indépendantes

Pour un système équipé de quatre contrôleurs reliés à une unité distincte, utilisant l'entrelacement Windows NT, une séquence de transfert peut commencer immédiatement sur le contrôleur ou l'unité suivante. Dans ce cas, chacune des quatre unités peut se trouver dans une phase différente du transfert, car chaque unité possède son propre contrôleur.

L'utilisation des E/S asynchrones Windows NT dans cette configuration matérielle peut être avantageuse, car il est possible de constituer un ensemble de demandes d'E/S en attente, que le sous-système traite en parallèle, quatre à la fois. La vitesse à laquelle le sous-système traite les requêtes étant variable, il peut être utile de constituer un ensemble de requêtes en attente depuis SQL Server afin que le sous-système soit utilisé selon sa capacité. En fonction de plusieurs facteurs spécifiques au système, il peut être avantageux de reconfigurer SQL Server pour permettre un plus grand nombre d'E/S asynchrones. À noter toutefois que les capacités d'extension de la plupart des systèmes ne permettent pas l'utilisation d'un contrôleur par unité.

Un contrôleur intelligent relié à quatre unités de disque.

La technologie actuelle permet d'intégrer les capacités de plusieurs contrôleurs non intelligents dans un contrôleur intelligent unique. Celui-ci accepte rapidement les demandes d'E/S multiples en provenance du pilote de périphérique, ce qui permet d'exécuter simultanément des opérations de transfert vers les unités reliées, qui sont généralement regroupées en un ensemble RAID. Dans ce cas, en fonction de la capacité et de la configuration du contrôleur, la reconfiguration de SQL Server pour permettre un plus grand nombre d'E/S asynchrones peut entraîner un gain de performance. La valeur utilisée varie en fonction du serveur et du contrôleur (en outre, pour un serveur ou un contrôleur donné, elle varie en fonction de la configuration du sous-système de disques, et pour une configuration de sous-système donnée, elle varie en fonction des caractéristiques d'E/S de l'application).

Max E/S async

L'option de configuration max async IO configure le nombre d'E/S asynchrones pouvant être émises. La valeur par défaut, de 8, ne doit être modifiée que sur des systèmes de bases de données définies sur de multiples unités physiques de bases de données qui résident sur des disques physiques distincts ou sur des systèmes qui tirent parti de l'entrelacement du disque.

Pour déterminer la valeur optimale de l'option de configuration max async IO, il est recommandé d'effectuer une série de tests, soit avec le kit de banc d'essai Microsoft TPC-B, soit avec un test d'évaluation propre à l'entreprise, en utilisant le paramètre de ligne de base 8, puis en augmentant lentement la valeur selon les résultats des tests suivants. Lorsqu'aucun gain de performance n'apparaît, la valeur optimale est trouvée. Si vous n'effectuez pas de tests empiriques, il est recommandé de conserver la valeur par défaut.

SQL Server doit être redémarré après chaque modification de cette option de configuration pour qu'elle prenne effet.

Max E/S d'écriture différée

L'option de configuration max lazywrite IO règle la priorité des lots d'instructions E/S asynchrones exécutés par le processus d'écriture différée. Ceci est comparable à max async IO, qui commande les lots d'instructions E/S tels que la copie en bloc et les points de contrôle, mais max lazywrite IO est spécifique à l'écriture différée. Cette option doit être configurée uniquement sur les systèmes multi-disques durs. Elle est configurée dynamiquement jusqu'à concurrence de la valeur spécifiée par max async IO.

Les modifications de cette option de configuration prennent effet immédiatement.

Configuration du Gestionnaire de tampons

Free buffers

free buffers détermine le seuil de tampons disponibles sur le système. La valeur minimale est 20 et la valeur maximale est égale à la moitié du nombre de tampons disponibles lors du démarrage du serveur. L'écriture différée garantit que le nombre de tampons disponibles sur le système ne tombe pas sous ce seuil.

Cette option est automatiquement modifiée par le système chaque fois que l'option memory est modifiée; free buffers est définie à 5 % de la mémoire disponible. Lorsque l'option memory est modifiée, un message décrivant la modification des free buffers est affiché. Après cette modification, free buffers peut être reconfiguré manuellement sur toute valeur autorisée.

Sort Pages

L'option Sort Pages détermine le nombre maximal de pages qui seront affectées au tri par utilisateur. Pour les systèmes qui exécutent des tris importants, l'augmentation de ce nombre peut améliorer la performance. Étant donné que le tri de pages supplémentaires consomme de la mémoire, l'augmentation de cette valeur peut vous obliger à augmenter la quantité de mémoire dédiée au serveur.

Hash Buckets

Cette option définit le nombre de compartiments utilisés pour hacher les pages dans les tampons de mémoire. Si la valeur spécifiée n'est pas un nombre premier, le plus proche est utilisé. Par exemple, la définition de 8 000 crée 7 993 compartiments de hachage (valeur par défaut). Sur les systèmes ayant une grande quantité de mémoire, cette valeur peut être augmentée pour permettre un accès plus rapide aux données qui résident dans le cache de données. Pour les systèmes disposant de 160 Mo ou moins de mémoire, 7 993 est une valeur appropriée. Cette option ne prend effet que lorsque le serveur est arrêté puis redémarré.

Recovery Interval

Cette option définit le nombre maximal de minutes par base de données dont SQL Server a besoin pour mener à bien ses procédures de récupération en cas d'un échec du système. La valeur par défaut est de 5 minutes par base de données.

La modification de l'option recovery interval dépend de votre application et de son utilisation. Par exemple, pour garantir l'écriture fréquente des modifications sur le disque, vous pouvez réduire la valeur de recovery interval en cas de grande activité d'actualisation. La réduction de recovery interval entraîne des points de contrôle plus fréquents, ce qui ralentit légèrement le système. À l'opposé, une définition de recovery interval trop élevée peut entraîner un temps de restauration trop long et donc inacceptable.

Forcer les pages à rester en cache

Syntaxe partielle

sp_tableoption @TableNamePattern [,'pintable'] [,true | false ]

La définition de l'option pintable sur true indique à SQL Server de conserver la table ainsi que tous ses index dans le cache de données. Les pages appartenant à une table figée ne seront pas supprimées du cache de données pour libérer de l'espace. Les modifications sur ce type de table sont entièrement consignées dans le journal et, les processus d'écriture différée et de points de fonctionnent normalement.

Les tables sont identifiées comme étant figées par la définition d'un bit d'état dans la table sysobjects.

Cette procédure stockée ne provoque pas automatiquement le transfert de la table et de ses index en mémoire ; en revanche, dès que les pages de données et leurs index sont transférées dans le cache de données, elles y demeurent jusqu'à l'arrêt de SQL Server ou jusqu'à ce que la valeur pintable soit réglée sur false. Pour transférer rapidement une table figée en mémoire, vous pouvez y accéder par une simple commande telle que SELECT COUNT (column_name) FROM table_name en spécifiant une colonne qui ne comporte pas d'index non ordonné.

Figer certaines tables fréquemment utilisées peut améliorer de manière significative les performances dans certains environnements. Assurez-vous toutefois que vous disposez toujours d'une grande quantité de cache de données disponible après avoir figé des tables.

Il est possible d'utiliser des caractères génériques pour figer ou libérer plusieurs tables en même temps. Si la valeur true ou false n'est pas spécifiée, la commande restitue le paramètre utilisé de la valeur pintable pour la ou les table(s) spécifiée(s).

Remarque La taille de la table n'est pas limitée, mais elle ne doit pas consommer la totalité du cache.

Forcer les pages d'index à rester en cache

L'indicateur de trace 1081 permet aux pages d'index d'effectuer un "second parcours" du cache de données. Lorsque SQL Server doit supprimer du cache une page d'index pour y transférer une nouvelle page, il choisit une page différente à moins que cette page d'index particulière ait déjà été ignorée une fois. De cette manière, les pages d'index sont autorisées à rester plus longtemps dans le cache de données.

Traitement de la lecture anticipée

La lecture anticipée est également appelée recherche parallèle de données (PDS, Parallel Data Scan). SQL Server utilise cette technique pour réduire le nombre de lectures physiques nécessaire au traitement d'une requête. Si un certain nombre de pages requises ne sont pas trouvées dans le cache, SQL Server peut démarrer d'autres threads qui lisent les pages qui pourraient être requises par le traitement en cours du serveur.

La lecture anticipée peut être lancée chaque fois que SQL Server effectue une recherche horizontale de données. Ceci peut comprendre les recherches de tables, d'index au niveau feuille pour les index non ordonnés, les instructions DBCC et UPDATESTATISTICS.

Configuration de la lecture anticipée

RA Cache Miss Limit

La lecture anticipée est lancée lorsque ce nombre de pages NE se trouve PAS dans le cache de données pendant une recherche horizontale de données par SQL Server. La définition de RA Cache Miss Limit sur 1 entraîne une requête de lecture anticipée chaque fois que l'accès à une page de données se fait à partir du disque, ce qui peut entraîner une baisse notable des performances.

RA Pre-fetches

Cette option détermine jusqu'où le gestionnaire de lecture anticipée va lire (sur la base d'une étendue) avant que le gestionnaire d'extraction anticipée ne devienne inactif. Une valeur de trois signifie que pour chaque requête postée, le gestionnaire de lecture anticipée conserve trois étendues anticipées à partir de la position actuelle de la recherche, le long de la chaîne de pages.

RA Cache Hit Limit

La lecture anticipée s'arrête lorsque ce nombre de pages requises se trouve déjà dans le cache et redémarre à la première limite ignorée suivante. Cette technique est utilisée pour détecter les situations dans lesquelles le gestionnaire de lecture anticipée trouve tout dans le cache, et ne présente pas d'intérêt pour la requête. La valeur par défaut de 4 est suffisante pour la plupart des systèmes.

RA Worker Threads

Chaque thread gère un nombre de structures configurable (voir l'option RA Slots Per Thread ), chacune de ces structures (emplacements) représentant une plage de requêtes individuelle. Cette option doit être définie sur le nombre maximal d'utilisateurs simultanés sur le système. Un avertissement est consigné dans le journal des erreurs si le nombre de threads nécessitant des lectures anticipées dépasse le nombre d'emplacements RA configuré. La définition de cette valeur à zéro désactive la lecture anticipée.

RA Slots Per Threads

Cette option détermine le nombre de requêtes simultanées que chaque thread de service de lecture anticipée gère. Le nombre de threads multiplié par le nombre d'emplacements équivaut au nombre total de requêtes par lecture anticipée simultanées que le système prend en charge. La valeur par défaut est suffisante pour la plupart des systèmes. Si votre système possède un sous-système d'E/S efficace, vous pouvez augmenter le nombre de requêtes qu'un thread unique peut traiter.

RA Delay

Il peut s'écouler un léger délai entre le moment où le thread exécutant la requête appelle la lecture anticipée et le moment où le système d'exploitation réactive le thread de lecture anticipée. Cette option définit la durée pendant laquelle le thread exécutant la requête se met veille avant de reprendre le travail, ce qui garantit le redémarrage du thread de lecture anticipée. Sa définition à zéro désactive essentiellement la lecture anticipée, car le thread exécutant la requête capture toujours la page suivante avant la réactivation de la lecture anticipée.

Examen et contrôle de la lecture anticipée

DBCC SQLPERF(RASTATS) recense quatre statistiques. Un exemple de sortie est présenté ci-dessous :

Valeur	Statistique
Pages en lecture anticipée trouvées dans le cache	297.0
Pages en lecture anticipée placées dans le cache	12933.0
RA Physique IO	1644.0
Emplacements utilisés	0.0

La table suivante indique la signification de chacune des quatre valeurs recensées :

Statistique	Définition
Pages en lecture anticipée trouvées dans le cache	Nombre de pages que le gestionnaire de lecture anticipée a trouvé dans le cache lors de l'exécution de requêtes.
Pages en lecture anticipée placées dans le cache	Nombre de pages que le gestionnaire de lecture anticipée a transféré vers le cache.
RA Physical IO	Nombre de lectures de 16 Ko que le gestionnaire de lecture anticipée a effectué.
Emplacements utilisés	Nombre d'emplacements de lecture anticipée en cours d'utilisation par les requêtes actives. À noter qu'une requête unique peut utiliser plusieurs emplacements de lecture anticipée.

Cache de procédures SQL Server

Organisation du cache de procédures

Le cache de procédures contient les éléments suivants :

• Proc Buffer Array

  Il existe autant d'emplacements de tampon contenant des procédures que de pages de cache, stockés dans tableau fixe. Chaque emplacement de tampon de procédure utilise 122 octets du cache de procédures. Dans la sortie DBCC MEMUSAGE, l'espace nécessaire à l'accueil du tableau de tampons de procédures se nomme Proc Headers.

• Proc Headers

  Chaque emplacement de tampon de procédure utilisé pointe vers un en-tête de procédure, qui est la première page d'un plan ou d'une arborescence de procédures dans le cache. Cette première page contient les informations sur la gestion de la mémoire telles que les adresses des autres pages du plan ou de l'arborescence. Elle contient aussi un pointeur vers la première instruction du plan ainsi que la procédure appelante, le cas échéant. Cette structure consomme 606 octets des 2 Ko de la première page. Le reste des 2 Ko de la page est disponible pour le plan ou l'arborescence. En fonction de la taille du plan ou de l'arborescence, il peut y avoir de nombreuses pages supplémentaires associées au plan ou à l'arborescence. Dans la sortie DBCC MEMUSAGE, l'espace disponible devant servir de cache de procédures se nomme Proc Cache Buffs.

• Pages utilisées contenant les plans ou les arborescences des requêtes.

• Pages actives contenant les plans des procédures en cours d'exécution.

• Pages libres disponibles pour de nouveaux plans.

Les plans multiples pour la même procédure possèdent chacun leur propre emplacement de tampon de procédure et leur propre en-tête de procédure.

Dimensionnement du cache de procédures

Option de configuration du cache de procédures

La valeur de procedure cache détermine la quantité de mémoire utilisée par SQL Server pour stocker les dernières procédures stockées utilisées, créer de nouvelles procédures stockées et compiler les nouvelles requêtes.

procedure cache définit le pourcentage de mémoire allouée au cache de procédures après que les besoins en mémoire de SQL Server aient été satisfaits. Les besoins en mémoire de SQL Server correspondent à la somme de la mémoire nécessaire aux verrous, aux connexions utilisateur, au code, etc. Le reste de la mémoire est partagé entre le cache de procédures et le cache de données, conformément au pourcentage défini par cette option de configuration.

La quantité de cache de procédures nécessaire aux procédures stockées, déclencheurs, vues, règles et options par défaut dépend de leur nombre et de leur taille. Souvenez-vous que si des utilisateurs multiples accèdent à la même procédure stockée, SQL Server sera obligé de créer une autre copie du plan de la procédure, s'il n'existe pas de copie non utilisée dans le cache.

SQL Server doit être redémarré pour que la modification de ce paramètre prenne effet.

Analyse de l'utilisation du cache de procédures

DBCC MEMUSAGE peut être utilisé pour analyser les 20 plus longues procédures dans le cache de procédures.

Le rapport présenté par DBCC MEMUSAGE peut aider à anticiper la quantité d'espace nécessaire au cache de procédures. Puisque le rapport recense la taille des plans dans le cache, vous pouvez multiplier ce nombre par le nombre escompté d'utilisateurs simultanés.

Le cache de procédures peut se remplir dans les deux cas suivants :

• Un grand nombre de petites procédures peut remplir le tableau des emplacements de tampons contenant des procédures, même s'il y a encore des pages disponibles, ou des pages utilisées inactives qui peuvent être remplacées.

• Plusieurs procédures longues peuvent utiliser toutes les pages disponibles, même s'il peut encore y avoir des emplacements disponibles dans le tableau des tampons de procédures.

Si le cache de procédures est plein et qu'une nouvelle procédure ne peut être exécutée, l'erreur 701 est affichée :

Mémoire système insuffisante pour exécuter cette requête.

Compteurs de l'Analyseur de performances

Objet : SQLServer – Cache de procédures
Compteurs : Taille du cache de procédures et Taux de présence dans le cache

Le compteur Taille du cache de procédures indique la taille du cache de procédures dans des pages de 2 Ko. Ce compteur ne change que lorsque vous modifiez la valeur des options de configuration memory ou procedure cache, et que vous redémarrez SQL Server.

Le compteur Taux de présence dans le cache analyse le pourcentage du cache de procédures utilisé par les procédures stockées, les déclencheurs, les vues, les règles et les paramètres par défaut mis en cache.

Sur un système ayant atteint sa vitesse de croisière, le taux de présence dans le cache doit être de l'ordre de 90 à 95 %.

Si le compteur Taux de présence dans le cache indique un pourcentage très inférieur, cela signifie que vous avez alloué trop de mémoire au cache de procédures et que vous gaspillez de la mémoire qui pourrait être utilisée pour le cache de données. Vous devez, par conséquent, diminuer la valeur du cache de procédures, redémarrer SQL Server et reconsulter ce compteur.

Si le compteur Taux de présence dans le cache indique un pourcentage toujours supérieur à 95 %, il se peut que vous n'ayez pas alloué assez de mémoire au cache de procédures. Dans ce cas, vous devez augmenter la valeur de l'option de configuration procédure cache, redémarrer SQL Server et poursuivre le processus d'analyse.

Autres options de configuration SQL Server

Max Worker Threads

Cette option détermine le nombre de threads de travail qui sont disponibles pour les processus de SQL Server. SQL Server utilise les services de thread natifs du système d'exploitation. Il existe plusieurs threads de travail. Chaque réseau pris en charge simultanément par SQL Server est géré par un thread ou plus. Un autre thread traite les points de contrôle des bases de données, et un groupe de threads traite tous les utilisateurs.

L'option max worker threadspermet de contrôler le nombre de threads alloué au groupe d'utilisateurs. Lorsque le nombre de connexions utilisateur est inférieur à max worker threads, chaque connexion se voit allouer un thread. Dès que le nombre de connexions dépasse max worker threads, le groupement (la mise en pool) des threads intervient. Par ailleurs, si la valeur configurée des threads de travail est dépassée, c'est le thread de travail suivant terminant sa tâche en cours qui traite la requête. La valeur par défaut est de 255.

Logwrite Sleep

Cette option définit le nombre de millièmes de seconde pendant lesquelles une transcription dans le journal sera retardée si le tampon n'est pas plein. Ceci permet à d'autres utilisateurs d'ajouter des données au tampon du journal et de diminuer les besoins de transcriptions physiques dans le journal. Les valeurs acceptables pour cette option sont de 1 à 500. La valeur spéciale de --1 signifie que la transcription dans le journal ne sera pas retardée. Avec la valeur par défaut de zéro, le serveur n'attend que si d'autres utilisateurs sont prêts à exécuter.

Les modifications de ce paramètre prennent effet immédiatement.

Priority Boost

Indique si l'exécution de SQL Server doit être prioritaire sur celle d'autres processus du même ordinateur. Si cette option est définie sur 1, l'exécution de SQL Server est prioritaire. La valeur par défaut est de 0 et ne doit être modifiée que sur les systèmes Microsoft Windows NT® dédiés à SQL Server. Vous devez veiller à ne pas démunir d'autres processus nécessaires (comme le réseau).

SMP Concurrency

Cette option contrôle le nombre de threads que SQL Server libère pour exécution par Windows NT® ce qui, dans la pratique, limite le nombre de processeurs utilisés par SQL Server. Pour un ordinateur à processeur unique, la valeur optimale est de 1. Pour un ordinateur à multiprocesseur symétrique (SMP), la limite varie selon que le serveur est ou non un serveur dédié à SQL Server. Si le serveur n'est pas dédié, la reconfiguration de cette valeur peut entraîner un temps de réponse faible pour d'autres applications fonctionnant sur le même ordinateur. Si le temps de réponse pour d'autres applications n'est pas une question problématique, définissez SMP Concurrency sur --1, "Dedicated SMP Support" ce qui signifie qu'il n'y a pas de limite.

Lorsque SQL Server est installé, SMP Concurrency est défini sur zéro, ce qui implique le mode auto-configuration. Sous ce mode, la limite est définie sur n-1, n étant le nombre de processeurs détectés au démarrage de SQL Server. Pour un ordinateur à processeur unique, cette valeur est définie sur 1.

Vous devez redémarrer SQL Server pour que les modifications de ce paramètre prennent effet.

Set Working Set Size

Cette option force Windows NT à réserver à SQL Server un espace de mémoire physique équivalent à la somme du paramètre memory et de la taille de tempdb si elle se trouve en mémoire vive.

Network Packet Size

Cette option définit la taille des paquets du réseau par défaut sur l'ensemble du serveur. L'application cliente peut remplacer cette valeur. Sur les systèmes utilisant des protocoles de réseau différents, cette option doit être définie sur la taille du protocole le plus utilisé. Elle peut améliorer la performance du réseau lorsque les protocoles du réseau prennent en charge des paquets plus volumineux. . La valeur par défaut est 4 096.

Les modifications de ce paramètre prennent effet immédiatement.

Configuration des sessions et des bases de données

Options de base de données

Les options de base de données ne peuvent être modifiées que par le propriétaire de la base de données ou par l'administrateur système. Puisqu'elles sont stockées dans la base de données master , elles ne peuvent être modifiées par un utilisateur ayant un alias de propriétaire de base de données.

Les options de base de données suivantes peuvent influer sur la performance des requêtes :

lecture seule
utilisateur unique

Dans ces deux cas, aucun verrouillage n'intervient dans les opérations. Nous verrons dans le module suivant que, dans un environnement multi-utilisateur, le conflit des verrous est un aspect critique de la performance.

D'autres options de bases de données peuvent affecter la performance de manière plus indirecte. L'option trunc. Log on chkpt introduit une servitude supplémentaire du système à chaque exécution du processus de points de contrôle du système. Vous pouvez utiliser l'option select into/bulkcopy pour exécuter des opérations de copie en bloc ou SELECT INTO, qui sont bien plus rapides que les autres.

Options de table

Certaines options de table peuvent également affecter la performance. Elles sont définies par sp_tableoption. L'option de table pintable a déjà été décrite dans ce document. L'option insert row lock sera présentée dans le module suivant.

Options de session

Les options de session sont sélectionnées par le commande SET et ne sont effectives que pendant la durée de la session. Si elles sont activées dans une procédure stockée, elles sont effectives jusqu'à la fin de la procédure stockée.

Les options de session suivantes peuvent influer sur la performance des requêtes :

FORCEPLAN

Présente les jointures des processus d'optimisation de SQL Server dans l'ordre de déclaration des tables apparaissant dans la clause FROM. FORCEPLAN remplace essentiellement l'optimiseur.

DEADLOCKPRIORITY {LOW | NORMAL}

Contrôle la manière dont se comporte la session dans une situation d'interblocage. Si la priorité est définie à LOW, le processus sera la première victime d'une situation de blocage. Utilisez l'option NORMAL pour retourner à la session par la méthode de traitement des blocages par défaut.

TRANSACTION ISOLATION LEVEL

Contrôle le comportement par défaut du verrouillage de transaction de toutes les instructions SELECT de SQL Server pour cette connexion. Les diverses valeurs seront étudiées dans le module suivant.

IMPLICIT_TRANSACTIONS

Contrôle si une transaction est lancée de façon implicite lors de l'exécution d'une instruction. Les implications sur la performance de ce comportement seront étudiées dans le module suivant.

DISABLE_DEF_CNST_CHK

Spécifie un contrôle de violation différé temporaire. Les implications sur la performance et la signification de cette option seront expliquées dans un module ultérieur.

Les informations contenues dans ce document représentent l'opinion actuelle de Microsoft sur les points cités à la date de publication. Microsoft s'adapte aux conditions fluctuantes du marché et cette opinion ne doit pas être interprétée comme un engagement de la part de Microsoft ; de plus, Microsoft ne peut pas garantir la véracité de toute information présentée après la date de publication. Ce document a été rédigé uniquement à titre d'information. MICROSOFT N'OFFRE AUCUNE GARANTIE, EXPRESSE OU IMPLICITE, QUANT AU CONTENU DE CE DOCUMENT. Microsoft, Windows et Windows NT sont des marques déposées de Microsoft Corporation. Les autres noms de produits et de sociétés cités peuvent être les marques de leurs propriétaires respectifs.

<< 1 2 3 >>

Dernière mise à jour le mercredi 11 octobre 2000