Forum aux questions sur SQL&Configuration de la mémoire, profilage des performances, définition du facteur de remplissage, et bien plus encore

Par Nancy Michell

Configuration de la mémoire

Conseil : Création simplifiée de profils

Saviez-vous que vous pouvez désormais corréler PerfMon avec le générateur de profils de SQL Server 2005 ?

Peut-être avez-vous remarqué des pics d'utilisation du processeur, une consommation excessive de la mémoire ou des performances insuffisantes sur l'Analyseur de performances de votre ordinateur SQL Server et vous êtes demandé à quoi étaient dues ces anomalies de performance. Avant SQL Server 2005, vous deviez utiliser le générateur de profils pour capturer une trace, puis consulter les processus système dans Enterprise Manager et enfin capturer les journaux de l'Analyseur de performances, qui, bien sûr, nécessitaient l'activation de PerfMon. Et après tout ce travail avec tous ces outils, vous deviez encore réconcilier manuellement les événements entre les différents outils pour essayer de comprendre la source de cette perte de performances. Et pour cela, il n'y avait qu'une solution possible : passer au crible les différents journaux. Il s'agissait vraiment une tâche ingrate, mais indispensable pour identifier de façon sûre vos problèmes de performances.

Avec SQL Server 2005, vous devez toujours conserver une trace et examiner les journaux PerfMon, mais le générateur de profils vous permet désormais de les joindre. Vous pouvez parcourir vos instructions T-SQL et le générateur de profils affichera automatiquement, de façon graphique, ce qui s'est produit. Si vous cliquez sur l'interface utilisateur de l'Analyseur de performances dans le générateur de profils, vous accédez directement à l'instruction qui correspond à cet horodatage. Ceci permet de gagner beaucoup de temps en termes de dépannage de l'environnement SQL Server.

Voyons comment joindre des journaux PerfMon au générateur de profils :

1. Lancez l'Analyseur de performances et commencez à capturer les informations à partir du serveur de base de données.
2. Créez un journal de comptage dans la section Journaux et alertes de performance et nommez ce nouveau journal.
3. Ajoutez de nouveaux compteurs, tels que % Temps processeur. Vous pouvez également faire débuter la journalisation de façon manuelle ou planifiée à l'aide de l'onglet Planifier.
4. Cliquez sur OK et, si vous avez retenu l'option manuelle, lancez la journalisation.
5. Associez une trace à votre instance de SQL Server à l'aide du générateur de profils. Pour ce faire, cliquez sur Nouvelle trace dans le menu Fichier. Incluez l'heure de début et l'heure de fin dans votre trace, puis donnez-lui un nom et configurez-la de façon à l'enregistrer dans un fichier. Enfin, vous devez simuler une certaine activité de transactions sur votre serveur, puis arrêter la capture des données de l'Analyseur de performances et du générateur de profils.
6. Dans le générateur de profils, sélectionnez Importer les données de performances dans le menu Fichier. Ensuite, choisissez l'emplacement où vous avez enregistré le journal PerfMon et sélectionnez Fichier | Ouvrir | Trace. Enfin, sélectionnez l'emplacement où vous avez enregistré la trace du générateur de profils.

Une fois cette opération terminée, il sera plus facile de comprendre les effets que certaines instructions SQL ont sur le temps de traitement.

Q J'essaie de déterminer la meilleure configuration mémoire pour mes zones SQL Server™. L'administrateur qui m'a précédé a configuré Boot.ini sur chaque ordinateur pour utiliser 12 Go de RAM sur un cluster à deux nœuds de SQL Server 2000 de la façon suivante : Yes /PAE NO /3GB (aucun paramètre AWE défini pour SQL Server). Avec 12 Go de RAM disponibles, dois-je supprimer l'option /3GB de Boot.ini, activer AWE et accorder à SQL Server environ 10 Go sur les 12 Go disponibles ? Les ordinateurs qui exécutent SQL Server ne comportent rien d'autre, donc aucune autre application ne nécessite de la mémoire.

R Oui, vous devez activer les extensions AWE (address windowing extension) et réserver la majeure partie de la mémoire vive à SQL Server. 10 Go me semble une valeur raisonnable pour une zone SQL Server dédiée de 12 Go. (L'affectation préalable est uniquement valide sous SQL Server 2000. À partir de SQL Server 2005, l'utilisation d'AWE n'est plus statique et peut être modifiée à la volée). L'utilisation conjointe des options /3GB et /PAE par rapport à la simple utilisation de l'option /PAE, qui est la seule à être requise, a toujours soulevé de grands débats. Seuls les paramètres /PAE et AWE doivent être activés. Cependant, je suggère d'utiliser les deux commutateurs parce que certains facteurs sont à prendre en considération.

Le paramètre /3GB dépend de votre situation. Êtes-vous à cours de zones de mémoire MemToLeave qui doivent figurer dans les 2 ou 3 premiers Go d'espace d'adressage virtuel ? Si vous activez ce paramètre, consommez-vous la mémoire du système d'exploitation ? (Voir support.microsoft.com/kb/316739 pour plus de détails.) Si vous traitez un cluster, vous pouvez définir /3GB sur un nœud et rien sur l'autre. De cette façon, si vous procédez à un test avec /3GB et subissez des problèmes, vous pouvez relayer l'instance sur l'autre nœud très rapidement. N'oubliez pas que si vous avez plus de 16 Go de mémoire vive, le paramètre /3GB n'est pas pris en charge.

Avec /3GB, vous augmentez l'espace d'adressage virtuel (VAS) de 50 %. Par conséquent, les applications qui contribuent à la pression de mémoire sur VAS et non pas seulement sur le cache de données en bénéficient le plus. Heureusement, les serveurs 64 bits, IA64 et x64, éliminent ce facteur mal compris. Les risques de consommation de la mémoire du système d'exploitation ne s'appliquent que si l'ordinateur est dédié à SQL Server. Le fait de ménager 2 Go pour le système d'exploitation reviendrait à tomber dans l'excès inverse. Si le serveur est dédié à SQL Server avec uniquement les services minimaux standard du système d'exploitation, vous pourrez constater que le serveur dispose d'environ 1,3 Go de mémoire libre sur le serveur. Dans ce cas, il est plus intéressant d'accorder 1 Go supplémentaire à SQL Server. Commencez à 10 Go, utilisez PerfMon pour contrôler la mémoire disponible pendant une longue période pour déterminer la partie inactive, puis réglez-la en conséquence. Tenez compte du fait que vous subirez un fort taux d'échange de pages si vous réservez trop de mémoire sous SQL Server 2000, car AWE n'est pas aussi dynamique que sous SQL Server 2005. Pour déterminer si le paramètre /3GB doit être utilisé, il est essentiel de le tester dans votre environnement.

Noms d'instance pour la réplication

Q Puis-je désormais utiliser l'adresse IP de mon serveur lors de la réplication de SQL Server 2005 pour indiquer l'instance à répliquer ? Sous SQL Server 2000, selon « Réplication entre les ordinateurs exécutant SQL Server dans les domaines non approuvés ou sur Internet » (support.microsoft.com/kb/321822), ceci entraînerait des erreurs, mais je ne sais pas si c'est toujours vrai.

R Lors de la spécification des instances de serveur qui doivent participer à la réplication, vous devez fournir le nom d'instance enregistré de SQL Server. Par exemple, vous devez utiliser le nom d'instance de SQL Server lors de la spécification des paramètres Publisher ou Subscriber dans les procédures de réplication stockées ou dans les paramètres de connexion de l'agent de réplication sur la ligne de commande. Si le nom réseau de l'instance de SQL Server diffère du nom d'instance inscrit, les connexions de réplication des agents échoueront.

Si le nom de réseau de l'instance et le nom d'instance de SQL Server diffèrent, envisagez d'ajouter le nom d'instance de SQL Server en tant que nom réseau valide. L'une des façons de définir un autre nom réseau consiste à l'ajouter au fichier des hôtes locaux. Le fichier des hôtes locaux se trouve par défaut dans WINDOWS\system32\drivers\etc ou WINNT\system32\drivers\etc. Par exemple, si le nom d'ordinateur est comp1, l'ordinateur a une adresse IP de 10.193.17.129 et le nom d'instance est inst1/instname, ajoutez l'entrée suivante dans le fichier d'hôte :

10.193.17.129 inst1

SQL Server Integration Services

Q Je suis en train d'installer un cluster actif/actif pour SQL Server 2005 (Edition Enterprise 64 bits avec deux serveurs) et j'aurai un total de quatre instances de SQL Server 2005. SQL Server Integration Services (SSIS) sera nécessaire pour toutes les instances. Que pouvez-vous me dire sur la mise en cluster SSIS et son effet sur les plans de maintenance ?

R Il est certes possible de mettre en cluster le service SSIS, mais ceci n'est pas nécessaire et vous risquez de subir différents problèmes, notamment l'absence de prise en charge de la délégation (voir msdn2.microsoft.com/aa337083) et d'instances multiples. Vous ne pouvez avoir qu'une seule instance en cours d'exécution par nœud.

Auparavant, SSIS devait être installé, pas exécuté mais simplement installé, pour que l'Assistant de plan de maintenance puisse s'exécuter. Ce n'est toutefois plus le cas dans SQL Server 2005 SP1. Si SSIS ne s'exécute pas, les plans de maintenance peuvent être exécutés par l'agent SQL Server.

Au lieu de mettre SSIS en cluster, vous pouvez envisager de le faire fonctionner en tant que service autonome et de modifier MsDtsSrvr.ini.xml pour pointer vers toutes les instances en cours d'exécution. Ceci vous permet de gérer vos packages à partir de n'importe quel nœud et d'offrir le niveau de disponibilité attendu par la plupart des clients, sans subir les problèmes associés à la mise en cluster du service.

Pour plus d'informations sur les échecs de création de plan de maintenance, consultez l'article de la base de connaissances à l'adresse support.microsoft.com/kb/909036.

Temps d'exécution étranges

Q Pendant mon test de chargement sur ma zone SQL Server 2005 SP1, SQL Server Profiler a enregistré de nombreuses valeurs négatives pour le temps d'exécution de procédure stockée et dans certains cas, le temps d'exécution de procédure stockée ne concordait pas avec le résultat de la soustraction de l'heure de démarrage de l'heure de fin.

R Différents facteurs peuvent affecter la création de rapports sur le temps d'exécution de procédure stockée et autres temps de performance dans SQL Server Profiler. Tenez compte du fait que SQL Server 2005 comptabilise le temps d'exécution en millisecondes, et que si vous utilisez des technologies qui modifient l'unité de mesure, vous obtiendrez des rapports incohérents et des temps d'exécution incorrects.

Par exemple, si vous utilisez d'autres profils d'alimentation, une autre version de processeur ou la technologie AMD Cool 'n Quiet, vous modifiez les fréquences du processeur, qui ne correspondent plus à ce que SQL Server Profiler attend lors du calcul de l'heure d'exécution.

Un article de la base de connaissances, support.microsoft.com/kb/931279, explique les symptômes, différentes causes et certaines solutions.

Conseil : Vérifiez votre facteur de remplissage

Supposons que vous avez verre rempli d'eau et que vous tentez d'y verser un peu plus d'eau. Que se passe-t-il ? L'eau déborde.

La même chose se produit avec SQL Server. Lorsqu'une nouvelle ligne est ajoutée à une page d'index déjà complète, SQL Server déplace la moitié des lignes vers une nouvelle page pour faire de la place à la nouvelle. Cette opération est appelée « fractionnement de page ». Ce processus ménage de la place pour les nouveaux enregistrements, mais il prend du temps et consomme beaucoup de ressources. En outre, il peut provoquer des fragmentations, ce qui peut avoir un impact négatif sur les opérations d'E/S. Comment puis-je l'éviter ?

Pour éviter ce genre de situation, vous devez déterminer de façon dynamique la valeur du facteur de remplissage. Lorsqu'un index est créé ou est reconstitué, la valeur du facteur de remplissage détermine le pourcentage d'espace sur chaque page de niveau feuille à remplir avec des données, en réservant le reste pour la croissance future. Par exemple, la configuration d'un facteur de remplissage de 60 signifie que 40 % de chaque page de niveau feuille reste vide afin de pouvoir prolonger l'index au fur et à mesure que des données sont ajoutées dans la table sous-jacente.

La valeur par défaut du facteur de remplissage est toujours 0, ce qui est correct dans la plupart des cas. En fait, un facteur de remplissage de 0 signifie que le niveau feuille est rempli à son maximum, mais une partie de l'espace est conservé pour au moins une ligne d'index supplémentaire. (Les facteurs de remplissage 0 et de 100 sont semblables.)

Vous pouvez définir la valeur du facteur de remplissage pour les index pendant l'exécution d'une instruction CREATE INDEX ou ALTER INDEX, ou vous pouvez configurer cette valeur directement au niveau du serveur de façon à ce que tous les nouveaux index créés utilisent la valeur par défaut.

L'exemple suivant définit la valeur du facteur de remplissage au niveau du serveur sur 70 %, ce qui signifie que vous disposez de 30 % d'espace libre pour les expansions. Naturellement, vous devez tester en détail cette option avant de l'implémenter dans un environnement de production.

USE Master;
GO
SP_Configure 'show advanced options',1;
GO
SP_Configure 'Fill Factor', 70;
GO
--You must restart SQL Server Engine for changes to take effect.

Que se passe-t-il si vous souhaitez configurer le facteur de remplissage au niveau des index individuels ? Supposons que vous créez la table suivant et souhaitez mettre en place un index unique sur la colonne Col_A avec une valeur de remplissage de 70. La commande se présentera de la façon suivante :

--Create an Item table
USE Item_DB;
GO
CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200));
GO;

--Create a unique index on colum Col_A of Item table with a Fill Factor value of 70
CREATE UNIQUE INDEX AK_Index ON Item (Col_A)
WITH (FillFactor = 70);
GO

Comment pouvez-vous identifier le facteur de remplissage de chaque index ? Vous pouvez interroger sys.index pour obtenir la valeur du facteur de remplissage pour tous les index d'une base de données, de la façon suivante :

USE Item_DB;
GO
SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL;
GO

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.