Forum aux questions sur SQLTables temporaires, traitement en 64 bits, mise en miroir, etc.
Par Nancy Michell
Tables temporaires
Q : J'ai entendu dire que pour éviter les problèmes de contention, il n'était pas toujours judicieux d'utiliser la base de données temporaire tempdb. Est-ce vrai ? Je l'ai utilisée car je devais récupérer des données réparties dans plusieurs tables et les modifier par la même occasion. Les affichages ne me permettent pas de le faire facilement.
R : Tout à fait. Une base de données temporaire très sollicitée est susceptible de rencontrer des problèmes de contention. Cependant, la contention ne pose généralement problème que dans de rares cas, par exemple lorsque vous disposez de centaines d'ID du profil de service (identificateurs de processus de serveur) impliquant la création et la suppression simultanées d'un grand nombre de tables Temp, comme expliqué dans l'article de la Base de connaissances disponible à l'adresse suivante : support.microsoft.com/kb/328551.
L'utilisation d'une table temporaire (qui peut se trouver ou non dans tempdb) peut tout à fait constituer une solution acceptable. La question est de savoir si l'augmentation de performances obtenue lors de l'extraction de données de la table temporaire compense la surcharge inhérente à la création initiale de la table.
Avant d'écarter cette solution, vous devez prendre en compte la taille, la durée de vie, la fréquence d'utilisation et plus particulièrement le nombre d'instances actives de la ou des tables Temp. Le dimensionnement des serveurs revêt également une importance considérable, car la contention tempdb peut être liée au matériel.
Si vous avez besoin d'un grand nombre de petites tables à courte durée de vie, vous pouvez tenter d'utiliser des variables de table, qui présentent certains avantages par rapport aux tables temporaires. (Voir la réponse à la question suivante pour plus d'informations sur les différences entre les variables de table et les tables Temp. Pour en savoir plus, consultez également le document « Frequently Asked Questions - SQL Server 2000 - Table Variables » [« Forum aux questions - SQL Server 2000 - Variables de table »].)
Dans votre cas, si toutes les requêtes transmises à la base de données doivent passer par les procédures de création de tables Temp, il peut se révéler plus judicieux d'interroger le modèle de données original plutôt que de s'en accommoder.
Q : En quoi consistent les variables de table et quel est leur niveau de performances ? Puis-je réellement les utiliser à la place des tables temporaires ?
R : Les tables temporaires et les variables de table, qui se présentent sous la forme de tables temporaires dans la base de données tempdb, offrent les mêmes fonctionnalités de base. Les variables de table peuvent cependant offrir des performances supérieures lors de l'insertion d'un nombre réduit de lignes. En effet, les variables de table ne tiennent à jour ni les statistiques ni les index, ce qui entraîne une diminution de la charge mémoire. Si la table des matières ne dépasse pas quelques pages de base de données d'une taille de 8 Ko que le moteur de base de données conserve dans le cache de données, il est préférable d'utiliser des variables de table.
À propos de la documentation en ligne SQL Server™ :
- Le comportement d'une variable de table est identique à celui d'une variable locale. Une variable de table possède une portée bien définie, qui correspond à la fonction, à la procédure stockée ou au lot dans lesquels elle est déclarée. Au sein de sa portée, une variable de table peut être utilisée à la manière d'une table standard.
- Les variables de table sont automatiquement nettoyées à la fin de la fonction, de la procédure stockée ou du lot dans lesquels elles sont définies.
- Les variables de table utilisées dans les procédures stockées nécessitent moins de recompilations des procédures stockées que les tables temporaires.
- Les variables de table nécessitent également moins de ressources en termes de verrouillage et de journalisation.
- Dans certains cas, les performances peuvent connaître une amélioration même si les statistiques ne sont pas conservées.
Mise en miroir
Q : J'envisage d'utiliser la mise en miroir de SQL Server 2005 afin de prendre en charge la haute disponibilité. Afin de prendre en charge le basculement automatique, le système s'exécutera en mode haute disponibilité. Je souhaite également exécuter d'autres applications sur le miroir pour utiliser pleinement les ressources. Le serveur miroir prendra en charge deux bases de données : la base de données de production (en tant que miroir de la base principale) et la base de données intermédiaire (en tant que base de données autonome). Il prendra également en charge deux types de clients : le premier est configuré à l'aide du basculement de miroir pour accéder à la base de données de production et le second se connecte directement à la base de données intermédiaire.
De plus, certains packages SSIS (SQL Server Integration Services) s'exécuteront sur le serveur miroir. Ils accéderont aux deux bases de données, transférant ainsi les données de la base de données intermédiaire enregistrée en local vers la base de données de production exécutée sur le serveur principal. Au cours du basculement, les packages SSIS transféreront les données de la base de données intermédiaire enregistrée en local vers la base de données de production enregistrée en local (on parle alors de « basculement »). Existe-t-il des problèmes liés à ces configurations dont je devrais être au courant ?
R : De manière générale, ce type de configuration n'est pas conseillé car après le basculement, la nouvelle entité risque d'être reléguée au second plan.
D'un point de vue fonctionnel, l'apparition de problèmes liés à la mise en miroir est peu probable. Si vous envisagez d'opter pour cette solution, il est cependant conseillé de procéder à un test rigoureux. Vous devez réaliser un test à la charge maximale, en configuration standard et après le basculement. Votre charge maximale doit correspondre à une estimation de la charge maximale ultérieure et non à la charge actuelle. Si vous ne prenez pas en considération les besoins ultérieurs de votre entreprise, la solution cessera de fonctionner si la charge dépasse la capacité disponible.
Cette procédure de test devrait permettre d'identifier les éventuels goulots d'étranglement sur le réseau, le disque et même le processeur, lesquels se traduiraient par des temps de réponse prolongés pour les applications, un débit inacceptable, des erreurs de délais d'attente ou encore des files d'attente d'opérations de rétablissement extrêmement longues sur le serveur miroir (ce qui peut conduire à des temps de basculement incroyablement longs en fonction du nombre réel d'opérations de rétablissement).
Au cours du basculement, l'exécution de packages entraîne également la perte de la connectivité avec l'entité défaillante. Une fois le basculement terminé et la base de données disponible sur la nouvelle entité, les packages doivent pouvoir se reconnecter à la nouvelle entité. Naturellement, cela suppose que les packages intègrent des mécanismes de nouvelle tentative de connectivité et qu'ils gèrent correctement les erreurs de connectivité. Ils doivent par exemple être capables de résoudre les problèmes de connexion.
SQL Server et systèmes d'exploitation 64 bits
Q : Est-il vrai que si j'exécute une version 64 bits d'un système d'exploitation Windows Server®, je dois utiliser SQL Server 2000 Édition Entreprise (version 64 bits) et non SQL Server 2000 (version 32 bits) ?
R : Si l'on considère SQL Server 2000, ia64 d'Intel est la seule architecture native 64 bits prise en charge. Lorsque SQL Server 2000 évoque une prise en charge 64 bits, il désigne donc « ia64 ». L'Édition Entreprise est la seule version de SQL Server 2000 prise en charge sur l'architecture native 64 bits ia64. Aucune Édition Standard de SQL Server 2000 ne s'exécute sur ia64, que ce soit en mode natif en tant qu'application 64 bits ou sous ia64 WOW (Windows® on Windows), sous-système de système d'exploitation capable d'exécuter des applications 32 bits.
Pour un ordinateur AMD x64, SQL Server 2000 vous offre deux possibilités. La première vous permet d'exécuter un système d'exploitation 32 bits, auquel cas vous pouvez exécuter toutes les éditions de SQL Server 2000 et tous les service packs (SP). La seconde vous permet d'exécuter un système d'exploitation 64 bits et SQL Server 2000 (toutes les éditions) SP4 sous WOW. Dans cet environnement, SQL Server 2000 n'est pas une application 64 bits native. S'exécutant sous WOW en mode 32 bits, SQL Server 2000 n'a même pas connaissance de l'existence de l'univers 64 bits.
Comme si cela n'était pas déjà assez complexe, ia64 WOW et x64 WOW sont deux sous-systèmes totalement différents. Sur ia64, WOW doit à la fois émuler un espace d'adressage virtuel restreint et procéder à l'émulation réelle d'instructions machine. Les instructions machine d'ia64 sont totalement différentes de x86. Il s'agit d'un tout autre ordinateur. SQL Server ne prend en charge aucun de ses composants (SQL Server 2000 ou SQL Server 2005) s'exécutant sous ia64 WOW.
Sous x64 WOW, le contexte est complètement différent. Les architectures d'ordinateurs x86 et x64 sont très proches l'une de l'autre. Le jeu d'instructions est identique (ou presque). Ainsi, vous ne devez émuler que l'espace d'adressage virtuel 32 bits et bénéficiez pour cela d'une assistance matérielle. Comme vous pouvez le voir, il existe de nombreux composants SQL Server prenant en charge x64 WOW tels que SQL Server 2000 SP4 et toutes les éditions de SQL Server 2005.
Mise à niveau vers SQL Server 2005
Q : Je souhaite effectuer une mise à niveau de SQL Server 2000 vers SQL Server 2005. Hormis la correction du code SQL, à quelles modifications liées aux applications dois-je procéder ? Par exemple, si je connecte SQL Server 2005 à l'aide d'un client Microsoft® .NET Framework, dois-je mettre mes clients à niveau vers Windows XP SP2 ?
J'exécute Windows XP SP1, Visual Basic® 6.0, ainsi que quelques applications utilisant .NET. SQL Server Native Client, .NET Framework 2.0 et Windows XP SP2 sont-ils requis ? En d'autres termes, quelles sont les configurations client minimales requises pour une mise à niveau ? Si je décide d'adopter la mise en miroir de SQL Server 2005, les configurations client requises sont-elles appelées à changer ?
R : Pour tirer pleinement parti de la mise en miroir, vous devez utiliser SQL Native Client (pour OleDb ou ODBC) ou le client SqlClient ADO.NET 2.0. De plus, vous devez modifier la chaîne de connexion pour référencer à la fois l'entité et le miroir.
Néanmoins, il vous suffit de disposer de SQL Native Client ou de .NET Framework 2.0 pour informer le client de la présence du miroir. Les anciens clients peuvent se connecter mais n'effectuent aucune tentative automatique sur le miroir si l'entité n'est plus disponible.
Sans intervenir sur les clients, vous pouvez ajouter un commutateur BIG-IP entre les clients et le serveur pour les déplacer manuellement d'un ordinateur à l'autre lors du basculement. En cas d'échec de la connexion à l'entité, vous pouvez également modifier le code de l'application pour tenter une connexion au serveur miroir.
La configuration système requise inclut les éléments suivants : Windows Installer 3.0, Microsoft Windows XP SP1 ou version ultérieure, Microsoft Windows 2000 SP4 ou version ultérieure ou encore Microsoft Windows Server 2003. Pour plus d'informations sur la configuration requise, consultez les sections Using ADO with SQL Native Client (« Utilisation d'ADO avec SQL Native Client »), Updating an Application to SQL Native Client from MDAC (« Mise à jour d'une application vers SQL Native Client à partir de MDAC ») et System Requirements for SQL Native Client( « Configuration système pour SQL Native Client »).
SQL Server 2000 - Mémoire insuffisante
Q : Je rencontre actuellement d'étranges exceptions de mémoire insuffisante avec ma version de production sous SQL Server 2000 Édition Entreprise SP4 sous Windows Server 2003 SP1. La figure 1 illustre l'erreur consignée dans les journaux.
Figure 1 Erreur de mémoire insuffisante
2006-06-23 14:41:40.72 spid77 WARNING: Failed to reserve contiguous memory of Size= 24641536.
2006-06-23 14:41:40.85 spid77 Buffer Distribution: Stolen=4800 Free=1744 Procedures=39391
Inram=0 Dirty=90621 Kept=0
I/O=0, Latched=99, Other=3063345
2006-06-23 14:41:40.85 spid77 Buffer Counts: Commited=3200000 Target=3200000 Hashed=3154065
InternalReservation=587 ExternalReservation=0 Min Free=1024 Visible= 173320
2006-06-23 14:41:40.85 spid77 Procedure Cache: TotalProcs=9555 TotalPages=39391 InUsePages=1031
2006-06-23 14:41:40.85 spid77 Dynamic Memory Manager: Stolen=44191 OS Reserved=7648
OS Committed=7619
OS In Use=7603
Query Plan=39088 Optimizer=0
General=4183
Utilities=140 Connection=7651
2006-06-23 14:41:40.85 spid77 Global Memory Objects: Resource=1617 Locks=313
SQLCache=1836 Replication=204
LockBytes=2 ServerGlobal=42
Xact=63
2006-06-23 14:41:40.85 spid77 Query Memory Manager: Grants=0 Waiting=0 Maximum=94533 Available=94533
2006-06-23 14:41:40.88 spid77 Error: 17803, Severity: 20, State: 12
2006-06-23 14:41:40.88 spid77 Insufficient memory available..
Mon serveur dispose d'une grande quantité de mémoire vive (32 Go). Étrangement, ce problème ne semble survenir qu'aléatoirement ; la procédure stockée ne génère cette erreur qu'une ou deux fois sur 20 environ.
Les compteurs de performances du serveur indiquent une grande quantité de mémoire disponible. L'écriture différée effectue-t-elle une pagination trop lente sur le disque ? Pourquoi le processus nécessite-t-il une quantité de mémoire si importante lors de l'exécution de ma procédure stockée ? Est-ce dû au nombre d'instructions Select ou à l'utilisation de tables Temp ?
R : La quantité de mémoire physique disponible sur l'ordinateur n'a pas d'importance dans ce cas. En fonction du mode de configuration de votre fichier boot.ini, les applications disposent d'un espace d'adressage virtuel compris entre 2 et 3 Go. L'espace d'adressage virtuel est un élément précieux des systèmes 32 bits. Par défaut, le pool de tampons consomme l'intégralité de l'espace d'adressage virtuel, à l'exception de 384 Mo. Ces 384 Mo sont réservés pour les piles et les allocations des threads à partir des composants trop volumineux pour être pris en charge par le pool de tampons, ou ignorant totalement le mode d'allocation à partir du pool de tampons (processus tiers xprocs, serveurs liés et composants COM).
L'avertissement indique qu'une allocation d'environ 23 Mo a échoué. La réussite d'allocations de cette taille dépend du nombre et de l'emplacement des autres allocations au sein du bloc de 384 Mo.
Vous pouvez envisager de supprimer certains des processus tiers xprocs chargés sur le système et ainsi déterminer s'il est possible d'utiliser des composants COM via sp_oacreate ou des serveurs liés.
Du fait de l'utilisation de la clause XML FOR dans votre procédure stockée (non réimprimée ici), il est tout à fait possible que le processus nécessite une quantité de mémoire aussi importante lors de l'exécution de votre procédure stockée SQL. Si vous souhaitez plus d'informations, Process\sqlservr\Virtual Bytes vous renseigne sur la quantité d'espace d'adressage virtuel disponible. De plus, l'outil VMStat (présent sur le CD livré avec l'ouvrage de Jeffrey Richter, « Programming Applications for Microsoft Windows » [« Programmation d'applications pour Microsoft Windows »]) vous permet de déterminer la taille de bloc d'adressage virtuel la plus importante. L'écriture différée n'entre pas dans le cadre de cette allocation ; la zone de mémoire utilisée pour l'allocation ne se situe-t-elle pas dans le pool de tampons ?
Merci aux informaticiens Microsoft nommés ci-dessous pour leur expertise technique : Ramon Arjona, Stephen Borg, Sandu Chirica, Robert Djabarov, Guillaume Fourrat, Osamu Hirayama, Alejandro Mihanovich, Maxwell Myrick, Uttam Parui, Shashi Ramaka, Gavin Sharpe, Vijay Sirohi, Jimmie Thompson, Madhusudhanan Vadlamaani, Jian Wang et Dave Wickert.
© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.