• Article

Forum aux questions sur SQLExpansion des bases de données, utilisation d'IFilters et connexion à distance

Édité par Matthew Graven

Conseil : Effacez le cache

Vous êtes vous jamais demandé pourquoi une procédure stockée peut fonctionner correctement dans l'environnement de test mais fonctionne mal lorsqu'elle est déployée sur le serveur SQL de production ? Il peut s'agir d'un problème lié au cache. Avant de déployer des procédures stockées sur votre environnement de production, testez les procédures dans l'environnement de test après avoir effacé les plans de requête mis en mémoire cache pour savoir comment votre procédure stockée s'exécutera dans un environnement de cache « à froid ». Voici quelques points pratiques à garder à l'esprit.

Pour effacer le cache de procédure sur SQL Server :

DBCC FREEPROCCACHE
Go

Effectuez une requête pour répertorier tous les plans mis en mémoire cache :

Select * from sys.dm_exec_cached_plans
Go

Q J'ai une base de données qui a un trafic important pendant la journée et je ne veux pas utiliser la croissance automatique car cela peut potentiellement entraîner des délais d'expiration si SQL Server® décide d'effectuer cette opération pendant les heures de pointe. J'aimerais implémenter une tâche périodique pour étendre le fichier de base de données d'un pourcentage spécifique de l'espace utilisé. Comment faire ?

R La croissance de fichier est une opération E/S consommant beaucoup d'espace disque et si SQL Server doit attendre qu'un fichier de données ou un fichier journal se développe, vous pourriez certainement constater que cela nuit aux performances et aux temps de réponse. L'incrément de croissance par défaut est 1 Mo pour les fichiers de données et 10 % pour les fichiers journaux, qui peuvent être des incréments de croissance insuffisants pour les systèmes très actifs. En outre, l'utilisation de la croissance automatique peut entraîner la fragmentation du disque parce que les fichiers de données ou les fichiers journaux ne sont pas contigus sur le disque ; cela signifie que les temps de réponse peuvent être plus longs que nécessaires dans la mesure où les données sont physiquement dispersées sur le disque.

La clé des bonnes performances est d'allouer de façon proactive un espace de fichier suffisant pour les fichiers de données et les fichiers journaux. Cela nécessite souvent une analyse de tendance et une prévision de croissance mais cela se traduit généralement par de meilleures performances parce que les fichiers seront contigus sur le disque et évitent le coût d'E/S requis par la croissance automatique pendant les heures de pointe. La croissance automatique doit généralement rester activée puisqu'un fichier de données ou un fichier journal complets empêcheront totalement l'accès à la base de données. Mais gardez à l'esprit que la croissance automatique doit être considérée comme un filet de sécurité au lieu d'une fonctionnalité de gestion des bases de données.

La planification régulière d'expansion des fichiers de base de données doit être évitée puisque celle-ci peut également entraîner la présence de fichiers non contigus sur le disque et des performances réduites. Une surveillance proactive peut être effectuée en exécutant régulièrement un script pour déterminer le pourcentage d'espace libre pour chaque base de données (exécutée par une tâche de l'Agent SQL) puis en prenant une mesure (telle que l'envoi d'une alerte par courrier électronique via le la messagerie de base de données). Le code de la figure offre un exemple de script qui montre comment rassembler le pourcentage d'espace libre pour la base de données actuelle.

Une fois qu'une alerte a été générée, vous pouvez écrire le script de la croissance de fichier unique avec la commande ALTER DATABASE et vous pouvez utiliser une tâche de l'Agent SQL pour planifier cette action afin qu'elle ait lieu en dehors des heures de pointe. Essayez d'étendre le fichier à une taille qui sera suffisante dans l'avenir prévisible pour éviter les futures petites expansions incrémentielles des fichiers. Il est également important de s'assurer que la réduction automatique n'est pas activée dans la base de données car cela peut réduire inutilement la base de données ou accroître le nombre de cycles.

—Justin Langford

Déterminer l'espace disponible dans une base de données

-- Script to gather size, free space and 
-- calculate % free space for current 
-- USER database
DECLARE @size DEC(15,2)
DECLARE @free DEC(15,2)
DECLARE @result DEC(15,2)

SELECT @size = SUM(size)*1.0/128
FROM sys.database_files

SELECT @free = 
(SUM(unallocated_extent_page_count)*1.0/128)
FROM sys.dm_db_file_space_usage

PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)
PRINT 'Free Space ' + 
CONVERT(VARCHAR(15), @free)

SELECT @result = (@free/@size)*100

PRINT '% Free Space ' + 
CONVERT(VARCHAR(15), @result)

Q Mon entreprise stocke différents formats de fichiers dans notre base de données à l'aide des colonnes varbinary et image. J'ai entendu dire que SQL Server a intégré une fonctionnalité qui me permet de réaliser une recherche sur ces différents formats de fichier. Comment puis-je configurer SQL Server pour effectuer cette opération ?

R Cette fonctionnalité est intégrée dans le service d'indexation de texte intégral. Le service fournit la flexibilité nécessaire pour utiliser les interfaces IFilter, permettant ainsi de développer et de charger des filtres qui peuvent extraire des informations utiles à partir de données propriétaires. Ces IFilters sont également utilisées pour d'autres produits, tels que Microsoft® Office SharePoint® Server, pour recueillir des informations sur les fichiers analysés.

Un IFilter est fourni par le créateur du format de fichier ou par les fournisseurs tiers. SQL Server contient déjà des IFilters qui sont chargés lorsque FulltextService (FTS) est installé (ceux-ci incluent des filtres pour les fichiers HTML et DOC). Cependant, d'autres IFilters peuvent être ajoutés en fonction des besoins. Par exemple, les filtres pour Adobe PDF peuvent être téléchargés sur le site Web d'Adobe, et un nouveau pack de filtres pour les extensions Office System 2007 a été publié à la fin de 2007. Notez que vous devez savoir de quelle version d'IFilter vous avez besoin. Par exemple, un IFilter conçu pour les systèmes 32 bits ne fonctionnera pas avec les installations 64 bits de SQL Server.

Une fois que vous avez exécuté le package d'installation sur le client, l'IFilter sera généralement enregistré dans l'écosystème du système d'exploitation. Lorsque les bits sont enregistrés dans le système d'exploitation, vous devez exécuter quelques opérations pour que FTS soit capable de charger les filtres. Après avoir démarré votre outil d'exécution de requête, lancez les commandes suivantes :

  • sp_fulltext_service 'load_os_resources',1 (Cette instruction permettra à FTS de charger les bits enregistrés pour le traitement, notamment les composants tels que les séparateurs de mots et les analyseurs morphologiques).
  • sp_fulltext_service 'verify_signature',0. (Ceci contournera SQL Server pour vérifier si les filtres utilisés sont signés, étant donné que beaucoup de fournisseurs ne signent pas leurs filtres conformément à la norme).
  • Redémarrez l'instance de SQL Server et l'instance de FTS.
  • Créez votre index de texte intégral sur les colonnes ayant la colonne binaire comme contenu devant être analysé par l'IFilter et la colonne d'extension (c'est-à-dire la colonne avec le type d'extension, tel que DOCX) pour que SQL Server choisisse le filtre vers lequel rediriger le contenu.

Vous trouverez plus d'informations à l'adresse go.microsoft.com/?linkid=7912971.

—Jens Suessmeyer

Q Je ne parviens pas à me connecter à un serveur SQL distant. Dois-je configurer le pare-feu sur ma machine client ou serveur ?

Une connexion distante à SQL Server 2005 peut échouer pour de nombreuses raisons, mais la configuration de pare-feu est un des problèmes les plus courants. Le blog SQL Protocols (blogs.msdn.com/sql_protocols) est une ressource très utile qui regorge d'informations sur l'initiation d'une connexion SQL.

L'installation par défaut de SQL Server 2005 ne permet pas les connexions distantes. À partir de la machine exécutant SQL, dans le menu Démarrer, sélectionnez Microsoft SQL Server 2005 | Outils de configuration | Outil Configuration de la surface d'exposition SQL Server. Cliquez sur Configuration de la surface d'exposition pour les services et les connexions, sélectionnez Connexions distantes puis la case d'option « Utilisation à la fois de TCP/IP et de canaux nommés ». Vous devez ensuite redémarrer SQL pour que la modification entre en vigueur.

Par défaut, SQL Server utilise le port 1433. Pour vérifier si le port est ouvert, utilisez la commande telnet suivante, en remplaçant <ipaddress> par la véritable adresse IP de la machine exécutant SQL Server :

telnet <ipaddress> 1433

Si vous obtenez une réponse d'échec de connexion, ouvrez le Pare-feu Windows®, cliquez sur l'onglet Exceptions, sélectionnez Ajouter un port et ajoutez le port TCP 1433. Telnet doit réussir maintenant. (Notez que Telnet n'est pas installé par défaut sur Windows Vista®.)

—Rick Anderson

Merci aux experts SQL Server suivants d'avoir contribué à cet article :
Justin Langford qui travaille pour Coeo Ltd., est un intégrateur système et un partenaire Microsoft Certified Partner basé en Angleterre. Jens Suessmeyer est un consultant de base de données chez Microsoft en Allemagne. Rick Anderson travaille dans l'équipe Developer User Education chez Microsoft. Saleem Hakani est ingénieur de base de données senior et responsable de SQL Server Community chez Microsoft.

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