Forum aux questions sur SQLGoulots d'étranglement du processeur, restauration et déplacement de bases de données, et bien plus encore.

Par Nancy Michell

Q : L'utilisation du processeur de SQL Server™ a bondi tout d'un coup de façon excessive, mais rien n'a été modifié. Aucun nouvel utilisateur n'a été ajouté, aucun matériel n'est tombé en panne et aucune nouvelle table n'a été créée. Alors que se passe-t-il ?

Conseil : Accès pendant la création d'index

Dans certaines circonstances, vous avez besoin de créer des index sur des tables de grande taille (ce qui peut prendre énormément de temps), tout en conservant l'accès aux données pendant ce processus. Comment atteindre ces deux objectifs ?

Lors de la création, de la suppression ou de la reconstruction d'un index en cluster, SQL Server place un verrou de modification du schéma (SCH-M) sur la table, ce qui empêche les utilisateurs d'accéder à toutes les données sous-jacentes pendant l'opération. C'est le cas lorsque vous créez un index en cluster sur une table. En revanche, lorsque vous créez un index non mis en cluster sur une colonne, SQL Server met un verrou partagé (Shared, S) sur la table, ce qui empêche également la mise à jour des données dans la table sous-jacente, tout en permettant d'exécuter des instructions SELECT pour lire les données.

Si vous avez de réels besoins de lecture de la table pendant la création de l'index en cluster, vous pouvez créer un index sur une table et le transformer en opération en ligne. Voici la commande :

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

Lors de l'utilisation des opérations d'indexage en ligne sur les tables, SQL Server placera toujours un verrou SCH-M pour un index en cluster ou un verrou partagé pour un index non mis en cluster sur la table sous-jacente, mais seulement pour une période très courte : pendant les phases de début et de fin de l'opération d'indexage. Cette option fournit donc un meilleur accès pour l'interrogation et la mise à jour de la table sous-jacente durant le processus de création d'index. Notez cependant que la création d'index et les opérations d'indexage en ligne sont uniquement disponibles avec SQL Server 2005 Enterprise Edition.

R : Un goulot d'étranglement du processeur qui survient soudainement et subitement sans aucune modification ou charge supplémentaire sur le serveur peut avoir plusieurs causes, mais les plus fréquentes sont un plan de requêtes non optimisé, une mauvaise configuration de SQL Server, une conception inappropriée d'application ou de base de données, ou encore des ressources matérielles insuffisantes.

Dans une situation comme celle-ci, la première chose à faire est d'identifier si votre serveur fait un usage intensif du processeur, et si c'est le cas, d'identifier les instructions qui sont les plus gourmandes en processeur sur votre système SQL Server local. Vous pouvez utiliser l'Analyseur de performances pour déterminer si le serveur fait un usage intensif de votre processeur en consultant le compteur PROCESSOR:% PROCESSOR TIME. Si vous trouvez une valeur de temps utilisé par processeur supérieure à 75 %, vous avez un goulot d'étranglement de processeur.

Vous devez également contrôler les planificateurs de SQL Server en interrogant le script DMV (vue de gestion dynamique) du système appelé SYS.DM_OS_SCHEDULERS pour voir la valeur des tâches exécutables. Une valeur différente de zéro indique que les tâches doivent attendre leur tour pour s'exécuter. Les valeurs élevées dans ce compteur constituent également un symptôme de goulot d'étranglement de processeur.

Vous pouvez utiliser la requête suivante pour lister tous les planificateurs et consulter le nombre de tâches exécutables :

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Pour obtenir les 50 instructions SQL qui consomment le plus de processeur, utilisez la requête reproduits à la figure 1.

Figure 1 Les 50 plus grands consommateurs de processeur

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

Q : J'ai dû récupérer une base de données SQL Server 2005 à partir d'une bande. Après la restauration, la plupart des autorisations accordées aux utilisateurs de la base de données étaient perdues. Pouvez-vous me dire où je me suis trompé pendant la restauration ? Il n'y a aucun problème avec les données, mais les autorisations sont sens dessus dessous.

R : Vous n'avez probablement pas restauré la base de données MASTER au même point dans le temps, si bien que les ID de connexion de la base de données d'utilisateurs ne correspondent pas à la base de données MASTER actuelle. Vous devez les synchroniser. L'encadré « Où trouver de l'aide lors du déplacement d'une base de données » répertorie les ressources les plus utiles pour déterminer la source des problèmes que vous rencontrez lors de la restauration ou du déplacement d'une base de données.

Q : J'ai développé une application dans laquelle j'utilise une procédure stockée qui décompose des données XML dans des tables relationnelles en utilisant OpenXML dans SQL Server 2005 SP1. Le document XML le plus lourd pèse 5 Ko (la moyenne est de 2,5 Ko). La procédure stockée est appelée plusieurs fois en parallèle (jusqu'à 50 fois).

Je rencontre de sérieux problèmes de conflits de verrouillage et je pense qu'ils sont causés par OpenXML. Qu'en pensez-vous ?

R : Si OpenXML peut être plus rapide que la méthode des nœuds pour décomposer ou convertir des données sur un thread unique, la méthode des nœuds s'adapte normalement mieux, surtout lors d'une utilisation en parallèle. Cependant, si vous utilisez OpenXML, vous devriez suivre les recommandations suivantes pour améliorer les performances générales d'OpenXML.

Au lieu d'appeler OpenXML cinq fois avec le même modèle de ligne (comme vous dites le faire dans votre solution), vous devriez extraire toutes les données avec le même modèle de ligne dans une table temporaire et exécuter ensuite vos instructions SELECT à partir de la table temporaire. Essayez de libérer de la mémoire avec sp_xml_removedocument dès que possible. Essayez également d'éviter autant que possible d'utiliser des caractères génériques comme * et //. Fournissez le chemin explicite pour améliorer les performances de vos requêtes.

Q : DBCC SHRINKFILE s'exécute vraiment lentement sur mon serveur. Est-ce que j'obtiendrais de meilleures performances sur une machine multiprocesseur ? Que puis-je faire pour améliorer la situation ?

R : DBCC SHRINKFILE est une opération à un seul thread, qui ne tirera donc pas parti de plusieurs processeurs. Il déplace des pages de la fin du fichier au début du fichier, une page à la fois. Et la réduction, comme cette opération est souvent appelée, n'effectue pas de défragmentation ; à vrai dire, dans la plupart des cas, la réduction augmente la fragmentation logique.

Pour améliorer les performances de la réduction, vous pouvez par exemple déplacer les pages dans des index en clusters. Si vous avez énormément de pages et qu'elles possèdent de nombreux index non mis en clusters, la vitesse sera particulièrement lente (par rapport à une situation où les index sont en clusters).

Notez également que le déplacement de pages pour des données d'objet BLOB est lent, parce que les données en ligne doivent être lues pour trouver la racine de ces données.

Si la majorité du contenu d'un index ou d'une table réside à la fin du fichier, vous pouvez reconstruire les index pour les transférer au début du fichier. La reconstruction des index tirera parti de processeurs multiples et pourrait utiliser moins d'espace de journal en mode bulk_logged. Après cela, la réduction s'exécutera plus rapidement.

Pour plus d'informations sur les opérations de réduction, commencez par lire les articles suivants : blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx et blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Où trouver de l'aide lors du déplacement d'une base de données

• COMMENT FAIRE : Déplacer des bases de données entre des ordinateurs qui exécutent SQL Server
• Comment faire pour déplacer des bases de données SQL Server vers un nouvel emplacement à l'aide des fonctions Détacher et Attacher dans SQL Server
• INF : utilisation de l'option WITH MOVE avec l'instruction RESTORE
• Comment faire pour résoudre les problèmes d'autorisation lorsque vous déplacez une base de données entre des serveurs SQL Server
• INF : Comment faire pour transférer des noms d'accès et des mots de passe entre instances de SQL Server
• INF : Comprendre et dépanner l'Assistant Copie de base de données SQL Server 2000
• Les connexions et autorisations des utilisateurs sur une base de données peuvent être incorrectes après la restauration d'une base de données
• Vous ne pouvez pas restaurer des sauvegardes de base de données système à une version différente de SQL Server
• Comment faire pour extraire une table ou des lignes spécifiques à partir de sauvegardes de bases de données ou de journaux des transactions dans SQL Server

Conseil : Modification des ports

Tous ceux qui connaissent SQL Server savent que le numéro de port par défaut qu'écoute SQL Server est le 1433. Si des instances nommées de SQL Server sont configurées pour utiliser des Ports dynamiques (c'est-à-dire qu'elles peuvent sélectionner tout port disponible au démarrage d'une instance de SQL Server), l'instance de SQL Server par défaut écoute toujours le port 1433. Donc si votre serveur écoute le port par défaut et qu'il n'est pas correctement sécurisé, il court un risque. Mais vous pouvez contrecarrer les attaques en modifiant le port par défaut. Voici comment.

Ouvrez le Gestionnaire de configuration SQL Server et développez Configuration du réseau SQL Server 2005, puis Protocoles. Ensuite, double-cliquez sur TCP/IP. La liste des propriétés TCP/IP et de leurs fonctions est affichée dans le tableau ci-dessous : paramétrez-les en conséquence.

Notez que le moteur de base de données SQL Server peut écouter plusieurs des ports sur la même adresse IP. Vous devez donc créer une liste des ports que vous voulez utiliser en les séparant par une virgule, au format 1433,1500,1501. Si vous souhaitez configurer une seule adresse IP pour écouter plusieurs ports, vous devez également définir le paramètre « Ecouter tout » sur Non dans l'onglet Protocoles de la boîte de dialogue Propriétés TCP/IP.

Maintenant, cliquez avec le bouton droit sur chaque adresse et cliquez sur Propriétés pour identifier l'adresse IP que vous souhaitez configurer. Si la boîte de dialogue Ports TCP dynamiques contient 0, ce qui indique que le moteur de base de données écoute les ports dynamiques, supprimez le 0. Dans la zone Propriétés IP de la boîte de dialogue Port TCP, saisissez le numéro du port que vous voulez que cette adresse IP écoute, puis cliquez sur OK. Dans le volet de console, cliquez sur Services SQL Server 2005, et dans le volet de détails, cliquez avec le bouton droit sur SQL Server (<nom de l'instance>), puis cliquez sur Redémarrer pour arrêter et redémarrer SQL Server.

Une fois que vous avez configuré SQL Server pour qu'il écoute un port spécifique, un client pour se connecter au port de trois façons. Vous pouvez exécuter le service Explorateur SQL Server sur le serveur pour vous connecter à l'instance Moteur de base de données par son nom ; vous pouvez créer un alias sur le client, en spécifiant le numéro de port ; ou vous pouvez programmer le client pour qu'il se connecte en utilisant une chaîne de connexion personnalisée.

Merci aux professionnels de l'informatique de Microsoft suivants pour avoir répondu aux questions de ce mois : Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal et Wayne Yu.

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