Forum aux questions SQLReconstruction d'index, longueur de mise en attente du disque, etc.

Par Nancy Michell

Q : Comment les index de SQL Server sont-ils reconstruits ? Je voudrais savoir pourquoi DBCC DBREINDEX consomme tout mon espace disque et pourquoi l'espace n'est pas libéré lorsque la commande échoue. La taille de ma base de données est de 90 Go et la plus grande table fait 70 Go.

Lorsque j'exécute DBCC DBREINDEX, en laissant 10 % d'espace libre, il consomme tout l'espace disque disponible et la commande échoue. Au départ, le fichier de base de données fait 90 Go, mais lorsque la commande DBCC échoue, il atteint 160 Go, et ces 70 Go supplémentaires ne sont jamais libérés : je dois rétrécir manuellement la base de données à plusieurs reprises pour la récupérer.

Lorsque la commande fonctionne, la taille du fichier de base de données est également de 160 Go, mais l'espace est automatiquement libéré quelques heures plus tard par réduction automatique. Serait-il acceptable d'effectuer une défragmentation plutôt qu'un DBREINDEX pour consommer moins d'espace ? Je n'ai pas d'index en cluster, et j'utilise le mode de récupération simple.

R : Au niveau le plus simple, vous reconstruisez les index en créant une nouvelle copie de l'index et en supprimant ensuite l'ancien index. Cela signifie que, pendant une courte période, il existe deux copies de l'index. La construction du nouvel index peut nécessiter autant d'espace de fichier de base de données que l'index original, et si la reconstruction exige une opération de tri, il vous faut 20 % de la taille de l'index en plus pour le tri.

Donc, dans le pire des cas, la reconstruction d'un index peut nécessiter jusqu'à 1,2 fois l'espace de l'ancien index. Si le fichier de base de données ne dispose pas de suffisamment d'espace, il devra grandir au cours de l'opération. Si la fonction de croissance automatique n'est pas activée, ou s'il n'y a pas assez d'espace libre sur le volume de disque, il est possible que l'espace disponible soit insuffisant et que l'opération de reconstruction échoue.

Que l'opération échoue ou non, l'espace disque supplémentaire alloué au fichier de base de données n'est pas libéré une fois l'opération de reconstruction terminée. L'hypothèse est que l'espace sera utilisé pour les opérations régulières de base de données.

L'exécution d'une réduction (que ce soit manuellement ou automatiquement) garantit pratiquement la fragmentation de l'index, en raison de la façon dont l'algorithme fonctionne. Pour plus de détails, voir le Moteur de stockage de SQL Server. La réduction automatique peut être particulièrement nuisible aux performances si la base de données a besoin de l'espace libre pour des opérations régulières, parce qu'il est possible qu'elle crée un cycle croissance automatique-réduction automatique-croissance automatique-réduction automatique qui peut faire des ravages sur la fragmentation et les performances.

L'utilisation de DBCC INDEXDEFRAG (ou ALTER INDEX ... REORGANIZE dans SQL Server™ 2005) a l'avantage de ne presque pas utiliser d'espace de fichier de base de données supplémentaire, mais cette opération peut être plus longue et risque de générer beaucoup plus d'enregistrements de transactions qu'une reconstruction d'index. DBCC INDEXDEFRAG est toujours entièrement enregistré, quel que soit le mode de récupération utilisé, tandis que dans le mode de récupération simple, une reconstruction d'index est enregistrée en bloc. Il existe de nombreux avantages et inconvénients pour chacune de ces deux méthodes ; ils sont expliqués plus en détails dans le livre blanc sur les méthodes recommandées pour la défragmentation d'index sous SQL Server.

Avant de prendre une décision sur la méthode de correction de la fragmentation, commencez par vous demander si vous souhaitez vraiment corriger la fragmentation. Selon le type d'opérations pour lesquelles l'index est utilisé, il se peut que la fragmentation n'ait aucun effet sur les performances, et sa correction pourrait constituer une perte de ressources. Le Livre blanc est très détaillé.

Conclusion : veillez à choisir la méthode de fragmentation la plus appropriée à votre environnement et à augmenter les performances des requêtes en effectuant une défragmentation.

Q : J'ai réussi à configurer la mise en miroir d'une base de données entre deux instances de SQL Server 2005. Mon application se connecte à SQL Server en utilisant une ouverture d'une session SQL Server, et elle est générée en utilisant ADO et le client natif de SQL. Ma chaîne de connexion et mes paramètres de connexion indiquent les bonnes informations, y compris le partenaire de basculement approprié. J'ai également créé sur le serveur miroir toutes les ouvertures de session présentes sur le serveur principal. Lors d'un test d'échec de la base de données, le miroir prend en charge le rôle principal avec succès et tout semble correct sur l'instance de SQL Server. (Je peux même me connecter au miroir en utilisant mon ouverture de session Windows®). Cependant, la reconnexion de l'application échoue avec l'erreur suivante :

Cannot open database "<db name>" requested by the login. The login failed. 

Il semble que la connexion n'est pas associée à un utilisateur dans la nouvelle base de données (qui était au départ le miroir). J'exécute la commande sp_change_users_login pour synchroniser les utilisateurs et les sessions pour la base de données, et j'obtiens un message disant qu'elle a réparé de multiples utilisateurs orphelins. Mon application se reconnecte ensuite au nouveau serveur principal. J'ai essayé de nombreux basculements, et je vois à chaque fois le même problème : l'association entre la session et l'utilisateur se perd.

Existe-t-il une façon de paramétrer la configuration du miroir pour que éviter ce problème ?

R : Oui. Le problème vient du fait qu'il n'existe pas de correspondance entre les identificateurs de sécurité (les SID) pour les logins SQL Server sur chaque serveur, même si les logins possèdent le même nom. Ce problème ne concerne pas les logins utilisateur/groupe de Windows/domaine, parce que leurs SID sont créés en fonction du SID de domaine de chaque utilisateur/groupe, et ils sont donc identiques pour un utilisateur/groupe donné, quel que soit le serveur SQL auquel l'utilisateur ou le groupe est ajouté.

Pour pouvoir vous passer de l'étape de synchronisation sp_change_users_login, vous devez créer les logins SQL Server sur le serveur miroir avec non seulement le même nom, mais également le même SID que sur le serveur principal. Pour ce faire, utilisez la spécification SID dans la déclaration CREATE LOGIN, lors de la création des logins sur le serveur miroir, comme suit :

CREATE LOGIN <loginname> WITH PASSWORD = <password>, 
SID = <sid for 
same login on principal server>,...

Vous pouvez récupérer le SID de chaque login du serveur principal en interrogeant le catalogue sys.sql_logins. La figure 1 illustre un exemple de requête qui produira une vraie déclaration CREATE LOGIN pour chaque login SQL Server/Windows sur un serveur donné.

Figure 1 Générer une déclaration CREATE LOGIN

select 'create login [' + p.name + '] ' + 
case when p.type in('U','G') then 'from windows ' else '' end + 
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + 
' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + 
case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
from sys.server_principals p
left join sys.sql_logins l
on p.principal_id = l.principal_id
left join sys.credentials c
on l.credential_id = c.credential_id
where p.type in('S','U','G')
and p.name <> 'sa'

Q : Quelle doit être la longueur moyenne de la file d'attente de mon disque ? Par exemple, si j'ai 20 piles physiques de disques séparées provenant d'un réseau de stockage (SAN) avec une configuration RAID 01, comment est-ce que je calcule la longueur moyenne de la file d'attente du disque ? Est-ce Longueur moyenne de la file d'attente du disque/20 ou Longueur moyenne de la file d'attente du disque/2 ?

R : Avant tout, vous devriez consulter la latence du disque avant de perdre du temps sur la longueur moyenne de la file d'attente du disque dans un environnement SAN. Mais cela dépend vraiment de ce que vous recherchez. Vous verrez pourquoi dans un moment.

L'explication de ce compteur (de Perfmon) est : « La longueur moyenne de la file d'attente du disque est le nombre moyen de requêtes, à la fois de lecture et d'écriture, qui ont été mises en attente pour le disque sélectionné pendant l'intervalle considéré. » Il s'agit d'un compteur de disque physique ou d'un compteur de disque logique ; donc le nombre que vous obtenez dépend de la présentation du stockage sous-jacent au système d'exploitation.

Penchons-nous sur votre cas. Vous avez 20 piles de disques dans une configuration RAID 01, ce qui signifie qu'ils sont agrégés par bande et mis en miroir (ou mis en miroir et agrégés par bande selon la façon dont vous lisez 01 ou 10). Le point important concernant votre tableau de stockage est qu'il y a 10 piles dans le jeu d'agrégats par bandes.

Mais il me manque des informations essentielles, comme la taille de l'agrégat par bande et de vos écritures, ainsi que le genre d'E/S que vous émettez (lecture, écriture, séquentielle ou aléatoire).

En ignorant les informations manquantes pour le moment, si la Longueur moyenne de la file d'attente du disque renvoie 10, c'est que le système d'exploitation a mis 10 E/S en attente dans le tableau du disque. Théoriquement, cela pourrait être une E/S pour chaque 10 jeux mis en miroir dans l'agrégat par bande, tout comme cela pourrait être 10 E/S pour un seul disque. Il est impossible de le savoir.

C'est là que les informations manquantes entrent en ligne de compte. Supposons que la taille de votre agrégat est 64 Ko, que votre taille d'écriture est 8 Ko, et vous vous apprêtez à faire un gros morceau d'écritures séquentielles. C'est un scénario typique pour l'activité de stockage de SQL Server. Dans ce cas, il y a de bonnes chances que 8 des E/S soient toutes allées au premier disque, et que les 2 E/S suivantes soient allées au disque suivant. Donc, si vous essayez de déterminer la longueur de file d'attente du disque par disque dans ce scénario, le résultat est 8 pour le premier disque, 2 pour le deuxième disque et 0 pour les huit autres disques dans le tableau.

Modifions maintenant ce scénario, avec une taille théorique de 8 Ko pour l'agrégat, et une taille d'écriture de bloc de 64 Ko, tout en conservant une longueur de file d'attente de disque de 10. Dans ce cas, chaque bloc de 64 Ko est réparti sur 8 disques, donc une E/S est écrite sur 8 disques et les 10 E/S mises en file d'attente sont réparties sur 80 écritures de disque, sur l'ensemble des 10 disques dans le tableau. Si vous essayez de calculer la longueur de file d'attente du disque par disque dans le tableau, elle sera de 8.

Soyons réalistes et ajoutons un autre niveau d'incertitude. Dans la plupart des cas, votre stockage SAN sera connecté au serveur utilisant un ou plusieurs HBA dans le serveur, un certain nombre de fibres pour relier le HBA au SAN, un certain nombre de ports sur la face avant du SAN, et peut-être un commutateur de fibre dans la structure entre le serveur et le SAN. Il nous faut alors nous pencher sur l'architecture interne des bus dans le SAN, et sur la façon dont les disques sont connectés aux ports sur le devant du SAN.

Toute file d'attente qui apparaît dans Perfmon peut être un symptôme de latence élevée, ou d'une mise en file d'attente à l'un de ces points situés entre l'emplacement de la mesure de la file d'attente par le système d'exploitation et la surface des disques. C'est pourquoi vous devriez examiner la latence et baser vos décisions sur ce compteur, plutôt que sur la longueur moyenne de file d'attente du disque.

Q : J'utilise la réplication transactionnelle, et je sais que beaucoup de lignes ont été modifiées manuellement dans un tableau au niveau de l'abonné. J'obtiens donc des erreurs lorsque l'éditeur tente de mettre à jour une ligne qui n'existe plus dans l'abonné.

J'ai besoin de savoir s'il existe une façon, grâce à la réplication, de réinitialiser simplement ce tableau unique à partir de l'éditeur, au lieu d'appliquer une nouvelle fois un instantané tout entier. J'ai examiné la fonction tablediff qui semble pouvoir faire tout ce que je veux, mais je me demande comment elle interagit avec la réplication.

Par exemple, est-ce que tablediff prend un instantané du tableau de l'éditeur et le compare à l'équivalent de l'abonnement ? Ai-je besoin d'arrêter la réplication pour utiliser l'utilitaire tablediff et assurer la cohérence des données ? Il y a-t-il autre chose que je dois savoir ?

R : Tout d'abord, tablediff ne prend pas un instantané au sens littérale, ni du tableau de l'éditeur ni de celui de l'abonné. En ce qui concerne votre scénario, il y a quelques options que vous pourriez considérer.

La première est un arrêt temporaire de la réplication et l'exécution de l'utilitaire. Si vous êtes inquiet des tentatives de modification des données par des utilisateurs, vous pouvez utiliser les paramètres -sourcelocked et -destinationlocked, qui effectueront un verrouillage exclusif des deux tableaux pendant l'exécution de l'utilitaire. Si ce n'est pas possible, vous pourriez également consulter les paramètres -rc et -ri tout en laissant la réplication s'exécuter. Cela exécutera tablediff une fois, puis le lancera sur les erreurs qu'il détecte, en éliminant les erreurs qui pourraient être provoquées par des retards de propagation de réplication. Mais attention : avec cette option, selon votre retard de réplication, il est possible que vous ne trouviez pas toutes les lignes modifiées sur l'abonné.

Merci aux informaticiens Microsoft ci-dessous pour l'apport de leur expertise technique : Sunil Agarwal, Chad Boyd, David Browne, Gilles Comeau, Emmanuel Dreux, Amanda Foote, Matt Hollingsworth, Paul Mestemaker, Uttam Parui, Paul Randal, Dennis Tighe et Steven Wort.

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