Maintenance de base de données pour SharePoint Server 2010

 

Dernière rubrique modifiée : 2016-11-30

Résumé :  Découvrez comment maintenir les bases de données qui hébergent des données et des paramètres de configuration pour Produits Microsoft SharePoint 2010. Lisez des directives et étudiez les exemples des stratégies et des tâches de maintenance de bases de données recommandées.

S’applique à : Microsoft SharePoint Server 2010, Microsoft SharePoint Foundation 2010

Auteurs : Bill Baer et Bryan Porter

Relecteur technique : Paul S. Randal (SQLskills.com (éventuellement en anglais))

Sommaire

• Introduction

• Utilisation de DBCC (Database Console Command) CHECKDB pour vérifier les erreurs de cohérence

• À propos de DBCC CHECKDB

• DBCC CHECKDB et performance

• Mesurer et réduire la fragmentation d'index

• Reconstruction d’index en ligne et hors connexion

• Mesurer la fragmentation dans une base de données SQL Server 2008 ou 2005 (sys.dm_db_index_physical_stats)

• Réduction de la fragmentation d'une base de données

• Réduction de la fragmentation d'une table spécifique et de ses index

• Ajustement des performances d'index par la définition du facteur de remplissage

• Réduction des fichiers de données

• Création de plans de maintenance SQL Server 2008

• Conclusion

Introduction

La maintenance périodique des bases de données est essentielle pour garantir le bon fonctionnement des bases de données Microsoft SharePoint 2010.

Les tâches de maintenance recommandées pour les bases de données SharePoint 2010 incluent notamment :

• vérification de l’intégrité de la base de données ;

• défragmentation des index en les réorganisant ou en les reconstruisant ;

• définition du facteur de remplissage pour un serveur.

Bien que les versions précédentes des produits et technologies SharePoint nécessitaient une intervention manuelle pour effectuer la défragmentation d’index et la maintenance des statistiques, plusieurs règles de l’analyseur intégrité SharePoint automatisent ce processus dans SharePoint 2010. Ces règles évaluent quotidiennement l’intégrité des index et des statistiques de base de données, et corrigent automatiquement ces éléments pour les bases de données suivantes :

• Bases de données de configuration

• Bases de données de contenu

• Bases de données de profils de l’application de service Profil utilisateur

• Bases de données sociales de l’application de service Profil utilisateur

• Bases de données de création de rapports de l’application de service Web Analytics

• Bases de données de transit de l’application de service Web Analytics

• Bases de données des services d’automatisation Word

Vous pouvez effectuer des tâches de maintenance de base de données en exécutant des commandes Transact-SQL, ou en exécutant l’Assistant Maintenance de base de données. Cet article décrit les commandes Transact-SQL que vous pouvez utiliser, puis explique comment créer des plans de maintenance de base de données en utilisant l’Assistant Maintenance de base de données de Microsoft SQL Server. Les exemples détaillés concernent Microsoft SQL Server 2008 R2 et Microsoft SQL Server 2005.

Utilisation de DBCC (Database Console Command) CHECKDB pour vérifier les erreurs de cohérence

Démarrez vos opérations de maintenance périodique avec des contrôles de cohérence pour vérifier que vos données et index ne sont pas endommagés. Vous pouvez utiliser l’instruction DBCC (Database Console Command) CHECKDB pour effectuer un contrôle de cohérence interne des pages de données et d’index.

La plupart des problèmes de cohérence de base de données sont dûs à des erreurs du sous-système d’E/S. Cependant, d’autres facteurs et événements peuvent également compromettre la cohérence de la base de données ; par exemple, un arrêt inapproprié d’un serveur de bases de données ou une panne de lecteur. Des problèmes de performance et de disponibilité significatifs peuvent parfois être des symptômes de problèmes de cohérence de base de données sous-jacents. Effectuez des contrôles de cohérence au moins une fois par semaine sur vos bases de données SharePoint 2010, mais aussi lors de pannes de serveur de bases de données ou de sous-système d’E/S.

À propos de DBCC CHECKDB

DBCC CHECKDB vérifie l’intégrité logique et physique de tous les objets dans la base de données spécifiée en effectuant les opérations suivantes :

• Exécute l’équivalent de DBCC CHECKALLOC pour vérifier les structures d’affectation dans la base de données.

• Exécute l’équivalent de DBCC CHECKTABLE sur chaque table et vue dans la base de données pour vérifier leur intégrité logique et physique.

• Exécute l’équivalent de DBCC CHECKCATALOG sur la base de données pour vérifier la cohérence des métadonnées de la base de données.

Nous vous recommandons d’exécuter DBCC CHECKDB à la place d’opérations individuelles (commandes DBCC CHECKALLOC, DBCC CHECKTABLE et DBCC CHECKCATALOG) car DBCC CHECKDB identifie la plus large variété d’erreurs possibles et est donc plus sûr dans un environnement de production.

DBCC CHECKDB utilise une quantité importante de ressources de mémoire, d’E/S et de processeur. Au lieu d’exécuter DBCC CHECKDB sur votre système de production, vous pouvez l’exécuter sur une sauvegarde restaurée de vos bases de données SharePoint sur un autre serveur, puis retirez la charge de travail de vérification de cohérence du système de production.

Nous recommandons de d’abord exécuter DBCC CHECKDB, puis, s’il révèle des erreurs, de restaurer la base de données concernée en utilisant vos sauvegardes les plus récentes.

L’exemple de sortie suivant provient de DBCC CHECKDB.

DBCC results for 'Contoso_Content_1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 2663 rows in 21 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 309 rows in 4 pages for object "sys.sysrowsets".

...more

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Contoso_Content_1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Pour plus d’informations sur l’utilisation de DBCC CHECKDB avec SQL Server 2008, voir DBCC CHECKDB (Transact-SQL).

DBCC CHECKDB et performance

Nous recommandons d’exécuter des contrôles de cohérence pendant les heures hors production car DBCC CHECKDB effectue une utilisation intensive des ressources (E/S, processeur, mémoire et espace tempdb). On croit souvent par erreur que DBCC CHECKDB acquiert des verrouillages de blocage, mais cela est faux depuis SQL Server 2000. Pour plus d’informations, voir Un mythe de base de données SQL Server par jour : (2/30) DBCC CHECKDB provoque un blocage (éventuellement en anglais).

Vous pourriez remarquer que l’exécution de DBCC CHECKDB utilise trop de ressources pour votre système de production. Dans ce cas, ne tentez pas d’exécuter des contrôles de cohérence une table à la fois. La meilleure manière de réduire la charge d’un contrôle d’intégrité sur le système de production consiste à utiliser l’une des options suivantes :

• Utilisez l’option WITH PHYSICAL_ONLY pour réduire l’utilisation du processeur et la mémoire.

• Restaurez une sauvegarde de base de données sur un SQL Server séparé et exécutez des contrôles de cohérence sur la copie restaurée de la base de données.

Pour plus d’informations sur ces options, voir le billet de blog de Paul S. Randal, CHECKDB à tout angle : Options de contrôle de cohérence pour un VLDB (éventuellement en anglais).

Mesurer et réduire la fragmentation d’index

Une fragmentation d’index se produit lorsque l’ordre logique des pages dans une table ou un index (tel que défini par la clé d’index) diffère de l’ordre physique des pages dans les fichiers de données. Cela peut également signifier que la densité des données sur les pages de fichiers de données est faible, ce qui entraîne un gaspillage d’espace disque, de mémoire et d’E/S. La fragmentation d’index peut être due à de nombreuses insertions, mises à jour ou suppressions dans une table. Les illustrations suivantes mettent en opposition un nouvel index récemment créé non fragmenté et un index fragmenté par de nombreuses insertions, mises à jour et suppressions. La flèche rouge montre l’ordre physique de l’index ; les flèches noires montrent l’ordre logique des pages d’index.

Figure 1. Index non fragmenté (source de l’image : Paul S. Randal)

 

Figure 2. Index fragmenté (source de l’image : Paul S. Randal)

 

Comme les insertions, les mises à jour et les suppressions ne sont pas distribuées équitablement entre les lignes de la table et les index, l’intégrité (ou la densité des données) de chaque page peut être variable. Pour les requêtes qui analysent une partie ou la totalité des index d’une table, la fragmentation peut entraîner des lectures de pages supplémentaires qui entravent l’analyse des données et peuvent compromettre significativement les performances de recherche.

Une fragmentation d’index peut entraîner une diminution des performances et une utilisation inefficace de l’espace, et les index risquent de devenir rapidement fragmentés sur les bases de données ne présentant qu’une utilisation modérée.

Avant d’implémenter un plan de maintenance de fragmentation d’index, déterminez les tables et index les plus fragmentés. Créez ensuite un plan de maintenance pour reconstruire ou réorganiser ces index.

Par exemple, dans SharePoint 2010, AllDocs est une table qui devient souvent fragmentée, elle contient des bibliothèques de documents, leurs documents associés, ainsi que des listes et des éléments de liste, et leurs métadonnées respectives.

Le niveau de fragmentation d’un index est le pourcentage de pages d’index qui ne sont pas dans le même ordre logique et physique.

Reconstruction d’index en ligne et hors connexion

La reconstruction d’index en ligne est disponible uniquement dans les éditions Enterprise, Developer et Evaluation de SQL Server. Les méthodes décrites dans cet article tiennent compte de ces restrictions. Les procédures dans cet article utilisent une reconstruction d’index hors connexion si l’édition de SQL Server qui héberge une base de données spécifique ne prend pas en charge les reconstructions d’index en ligne, ou si l’index qui est reconstruit n’est pas éligible pour une reconstruction d’index en ligne. Un index pourrait ne pas être éligible pour une reconstruction en ligne en raison de la présence de colonnes de grands objets (LOB), par exemple les colonnes ayant un type de données NVARCHAR(MAX), IMAGE, etc.

Pour obtenir des informations sur les reconstructions d’index en ligne, voir Fonctionnement des opérations d’index en ligne. Lorsqu’une reconstruction index en ligne est effectuée, des verrouillages de niveau table sont effectués pendant le processus de reconstruction, ce qui pourrait empêcher toute écriture dans la table, voire tout accès à celle-ci. De nombreux index dans les bases de données SharePoint sont toujours reconstruits en utilisant une reconstruction d’index hors connexion en raison de la présence de colonnes LOB.

Même si une reconstruction d’index en ligne est utilisée, il y a toujours deux points où des verrouillages de table sont appliqués momentanément, et ceux-ci peuvent entraîner un blocage. Nous recommandons donc de toujours programmer les activités de reconstruction d’index pendant les périodes de faible activité.

Mesurer la fragmentation dans une base de données SQL Server 2008 ou 2005 (sys.dm_db_index_physical_stats)

Dans SQL Server 2008 ou SQL Server 2005, utilisez la vue de gestion dynamique sys.dm_db_index_physical_stats pour déterminer la fragmentation des index sur une table ou une vue spécifiée.

Pour le calcul de la fragmentation, nous recommandons de contrôler la colonne avg_fragmentation_in_percent. La valeur de avg_fragmentation_in_percent doit être aussi proche de zéro que possible pour des performances optimales. Cependant, les valeurs comprises entre 0 et 10 pour cent pourraient être acceptables. Pour plus d’informations, voir sys.dm_db_index_physical_stats.

Le tableau suivant montre des exemples de résultats de sys.dm_db_index_physical_stats, avec une valeur de 9 375 for avg_fragmentation_in_percent dans une ligne.

database_id	index_type_desc	alloc_unit_type_ desc	avg_fragmentation_ in_percent
10	CLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	CLUSTERED INDEX	IN_ROW_DATA	0
10	NONCLUSTERED INDEX	IN_ROW_DATA	0
10	CLUSTERED INDEX	IN_ROW_DATA	9.375

Pour utiliser la vue de gestion dynamique sys.dm_db_index_physical_stats

1. Sur la barre des tâches, cliquez sur Démarrer, pointez sur Tous les programmes, sur Microsoft SQL Server 2008, puis cliquez sur SQL Server Management Studio.

   Pour utiliser sys.dm_db_index_physical_stats avec un objet de base de données, vous devez connaître l’ID de base de données et l’ID d’objet.

2. Sélectionnez la base de données de contenu dans l’Explorateur d’objets, puis cliquez sur Nouvelle requête. Exécutez le script suivant.

   SELECT DB_ID() AS [Database ID];

   Notes

   Lorsque vous utilisez DB_ID sans spécifier de nom de base de données, le niveau de compatibilité de la base de données actuelle doit être 100 (une base de données SQL Server 2008) ou 90 (une base de données SQL Server 2005). Si vous effectuez une mise à niveau à partir d’une version antérieure de SQL Server, vous devez spécifier un nom de base de données dans l’instruction DB_ID. Pour plus d’informations sur les niveaux de compatibilité, voir sp_dbcmptlevel (Transact-SQL).

3. Exécutez sys.dm_db_index_physical_stats sur la base de données ou l’objet que vous sélectionnez. Vous pouvez spécifier la base de données, et également une table ou un index.

   Utilisez la syntaxe suivante lorsque vous exécutez sys.dm_db_index_physical_stats.

   sys.dm_db_index_physical_stats ( 
       { database_id | NULL | 0 | DEFAULT }
       , { object_id | NULL | 0 | DEFAULT }
       , { index_id | NULL | 0 | -1 | DEFAULT }
       , { partition_number | NULL | 0 | DEFAULT }
       , { mode | NULL | DEFAULT }
   )
   

   Soyez prudent lorsque vous utilisez la vue de gestion dynamique sys.dm_db_index_physical_stats car elle peut être gourmande en ressources. Voir Dans sys.dm_db_index_physical_stats (éventuellement en anglais) pour une description complète expliquant les divers modes d’utilisation.

Réduction de la fragmentation d’une base de données

Utilisez les instructions suivantes pour réduire le niveau de fragmentation de l’index.

Exécuter les règles de l’analyseur d’intégrité de base de données

SharePoint 2010 inclut l’infrastructure de règles de l’analyseur d’intégrité. L’infrastructure comporte de nombreuses règles qui contrôlent l’intégrité et le bon état d’un environnement SharePoint et dans certaines circonstances prend des mesures pour corriger certains types de problèmes.

SharePoint 2010 inclut plusieurs règles applicables à la maintenance de base de données de contenu. Des règles réduisent automatiquement la fragmentation d’index pour certaines bases de données SharePoint, d’autres règles vérifient la présence de statistiques non à jour, et les actualisent le cas échéant. Ces règles de l’analyseur d’intégrité remplacent le travail du minuteur Statistiques de la base de données mis à jour qui a été introduit dans le Service Pack 2 pour les produits et technologies SharePoint. Par défaut, ces règles sont configurées pour s’exécuter conformément à une planification variable, soit quotidiennement, hebdomadairement, à la demande, en fonction de la cible des règles.

Toutes les règles de l’analyseur d’intégrité qui sont configurées pour s’exécuter quotidiennement et qui sont associées à un service SharePoint particulier sont exécutées par le même travail du minuteur. L’ajustement de la planification de ce travail du minuteur définit le moment où les règles de l’analyseur d’intégrité configurées pour une exécution quotidienne et associées à ce service vont s’exécuter pendant la journée. Toutes les règles décrites dans cet article sont associées au service SharePoint Timer.

Les règles de l’analyseur d’intégrité qui sont configurées pour s’exécuter à un intervalle différent (par exemple hebdomadairement) ou associées à un service différent ont des travaux du minuteur distincts. Si vous configurez une règle d’analyseur d’intégrité pour prévoir une exécution hebdomadaire, cette règle s’exécute avec un travail du minuteur qui est configuré pour s’exécuter hebdomadairement pour le service spécifique auquel la règle de l’analyseur d’intégrité est associée. En d’autres mots, la règle s’exécute selon la planification qui est définie pour ce travail du minuteur.

Vous pouvez exécuter les règles de l’analyseur d’intégrité manuellement en cliquant sur Exécuter maintenant dans le Ruban sur la page des règles de l’analyseur d’intégrité dans l’Administration centrale. L’exécution de ces règles évalue l’intégrité des index et des statistiques ; elle reconstruit et recalcule également l’index le cas échéant.

Des bases de données utilisées par SharePoint comportent des index fragmentés. L’exécution de cette règle effectue des tâches suivantes :

• La règle signale les index comme étant fragmentés. Comme l’évaluation de l’intégrité des index est une opération coûteuse, après l’exécution de la règle de l’analyseur d’intégrité, cette règle signale toujours les index comme étant fragmentés pour déclencher la mesure corrective.

• Pour chaque base de données SharePoint, l’action de la règle recherche et exécute le cas échéant la procédure stockée proc_DefragmentIndices, qui construit une liste de tous les index dans la base de données. Le niveau actuel de fragmentation est évalué dans chaque index. La reconstruction est envisagée pour tout index fragmenté à plus de 30 pour cent.

• Si l’édition de SQL Server prend en charge les reconstructions d’index en ligne, une reconstruction d’index en ligne est tentée pour chaque index. Si cette tentative échoue (par exemple, l’index sous-jacent pourrait ne pas prendre en charge les reconstructions en ligne en raison de la présence de colonnes LOB) une reconstruction d’index hors connexion est effectuée.

Comme noté précédemment, cette règle ne s’applique pas à toutes les bases de données d’un environnement SharePoint. Certaines bases de données utilisent des règles différentes pour effectuer des activités de maintenance similaires.

Rechercher - Une ou plusieurs bases de données de propriétés contiennent des index fragmentés. Cette règle maintient les index dans les bases de données de propriétés de recherche de SharePoint 2010 Enterprise. Par défaut, cette règle est configurée pour s’exécuter hebdomadairement sur tout serveur dans la batterie. Tout le traitement de cette règle, notamment les mesures correctives, est effectué pendant la phase de la règle Check. Cela signifie que si vous souhaitez gérer des reconstructions d’index pour la base de données de propriétés de recherche Enterprise, il ne suffit pas de simplement configurer cette règle pour ne pas reconstruire les index automatiquement, vous devez désactiver la règle entièrement si vous ne souhaitez pas que SharePoint 2010 exécute automatiquement des opérations de maintenance d’index.

L’exécution de Search - One or more property databases have fragmented indices effectue les tâches suivantes :

• La règle confirme que l’environnement est dans un état sécurisé permettant d’effectuer une reconstruction d’index.

• Pour chaque base de données de propriétés qui est configurée pour des applications de recherche dans la batterie de serveurs locale, la règle exécute la procédure stockée proc_MSS_DefragSearchIndexes, qui reconstruit une liste de tous les index ayant une fragmentation moyenne supérieure à 10 %.

• Chaque index dans la liste qui altère les performances de la base de données de propriétés est reconstruit. Si l’édition de SQL Server prend en charge les reconstructions d’index en ligne, une reconstruction d’index en ligne est effectuée. Si une tentative de reconstruction d’index en ligne échoue, l’index est reconstruit hors connexion.

Rechercher - Une ou plusieurs bases de données d’analyse peuvent contenir des index fragmentés. Cette règle maintient les index dans les bases de données de propriétés de recherche de SharePoint 2010 Enterprise. Par défaut, cette règle est configurée pour s’exécuter hebdomadairement sur tout serveur dans la batterie. Tout le traitement de cette règle, notamment les mesures correctives, est effectué pendant la phase de la règle Check. Cela signifie que si vous souhaitez gérer des reconstructions d’index pour la base de données de propriétés de recherche Enterprise, il ne suffit pas de simplement configurer cette règle pour ne pas reconstruire les index automatiquement, vous devez désactiver la règle entièrement si vous ne souhaitez pas que SharePoint 2010 exécute automatiquement des opérations de maintenance d’index.

L’exécution de Search - One or more property databases have fragmented indices effectue les tâches suivantes :

• La règle confirme que l’environnement est dans un état sécurisé permettant d’effectuer une reconstruction d’index.

• Pour chaque base de données de propriétés qui est configurée pour des applications de recherche dans la batterie de serveurs locale, la règle exécute la procédure stockée proc_MSS_DefragSearchIndexes, qui reconstruit une liste de tous les index ayant une fragmentation moyenne supérieure à 10 %.

• Chaque index dans la liste qui altère les performances de la base de données de propriétés est reconstruit. Si l’édition de SQL Server prend en charge les reconstructions d’index en ligne, une reconstruction d’index en ligne est effectuée. Si une tentative de reconstruction d’index en ligne échoue, l’index est reconstruit hors connexion.

Rechercher - Une ou plusieurs bases de données d’analyse peuvent contenir des index fragmentés.. Cette règle maintient les index dans les bases de données d’analyse de recherche de SharePoint 2010 Enterprise. Par défaut, cette règle est configurée pour s’exécuter uniquement à la demande. Lorsqu’elle est exécutée, elle peut le faire depuis n’importe quel serveur de la batterie.

La règle signale les index dans la base de données d’analyse comme étant fragmentés car le contrôle de la fragmentation dans une base de données est une opération coûteuse. La simple désactivation de l’activité « Réparation » de cette règle entraîne la création d’un rapport indiquant que toutes les bases de données d’analyse sont en mauvais état, même lorsque les bases de données d’analyse ont bénéficié d’une récente reconstruction d’index.

Pour maintenir manuellement les index dans les bases de données d’analyse, désactivez entièrement la règle Search - One or more crawl databases may have fragmented indices.

L’exécution de Search - One or more crawl databases may have fragmented indices effectue les tâches suivantes :

• La règle confirme que l’environnement est dans un état sécurisé permettant d’effectuer une reconstruction d’index.

• Pour chaque base de données d’analyse qui est configurée pour des applications de recherche dans la batterie locale, la règle exécute la procédure stockée proc_MSS_DefragGathererIndexes.

• Chaque index dans la base de données d’analyse est reconstruit. Si l’édition de SQL Server prend en charge les reconstructions d’index en ligne, une reconstruction d’index en ligne est effectuée. Si une tentative de reconstruction d’index en ligne échoue, l’index est reconstruit hors connexion.

En raison de la nature de la base de données d’analyse, vous n’avez généralement pas à défragmenter cette base de données fréquemment. Exécutez cette règle après avoir d’abord exécuté une analyse complète sur votre contenu. Ensuite, surveillez l’éventuelle fragmentation des index dans la base de données, puis exécutez cette règle lorsque la fragmentation augmente. Une fragmentation des index pourrait se produire lors d’un soudain ajout ou d’une soudaine suppression d’une grande quantité de contenu analysé ; par exemple, pendant l’expulsion de contenu survenant à la suite d’un nettoyage environnemental, ou après l’introduction d’une nouvelle source de contenu, par exemple un partage du fichier ou une grande application Web SharePoint.

Les bases de données suivantes ne sont pas dotées d’un mécanisme de maintenance automatisé. Ces bases de données n’ont généralement pas beaucoup de fragmentation. Surveillez l’éventuelle fragmentation de ces bases de données, puis reconstruisez les index dans ces bases de données lorsque la fragmentation dépasse 30 %.

• Base de données d’administration de la recherche

• Base de données de banque d’informations sécurisée

• Base de données du service d’état

• Base de données de synchronisation de profil

• Base de données d’utilisation

• Base de données de métadonnées gérées

• Base de données de Business Connectivity Services

• Base de données de PerformancePoint Services

Pour plus d’informations sur les modifications qui sont prises en charge pour les bases de données SharePoint 2010, voir Prise en charge des modifications aux bases de données qui sont utilisés par les produits Office server et Windows SharePoint Services dans la Base de connaissances Microsoft.

Si les performances d’une base de données ou d’une table très fragmentée n’est pas améliorée de façon mesurable par une fréquente défragmentation, il convient de vérifier les performances du sous-système d’E/S.

Réduction de la fragmentation d’une table spécifique et de ses index

Si vous souhaitez défragmenter l’index associé à une table particulière, et non une base de données entière, vous pouvez réorganiser ou reconstruire l’index.

• La réorganisation de l’index spécifie que le niveau feuille de l’index est réorganisé. La réorganisation de l’index défragmente et compacte les index cluster et non cluster sur des tables et des vues, et peut améliorer de façon significative les performances d’analyse des index. La réorganisation d’un index utilise l’espace existant alloué à l’index. Elle est toujours effectuée en ligne afin que la table sous-jacente soit accessible aux utilisateurs.

• La reconstruction de l’index implique qu’une nouvelle copie de l’index est construite. Par conséquent, une opération de reconstruction nécessite suffisamment d’espace supplémentaire pour construire la nouvelle copie de l’index avant de supprimer l’ancien index fragmenté. La reconstruction améliore les performances des analyses et de recherches d’index. Vous pouvez reconstruire l’index à l’aide d’une table en ligne ou hors connexion.

Le niveau de fragmentation d’un index détermine la méthode à utiliser pour le défragmenter et si cela doit se faire en ligne ou hors connexion. Le tableau suivant décrit la méthode de défragmentation qui est recommandée pour différents niveaux de fragmentation.

Niveau de fragmentation	Méthode de défragmentation
Jusqu’à 10 %	Réorganiser (en ligne)
10-75 %	Reconstruire (en ligne)
75 %	Reconstruire (hors connexion)

Vous pouvez réorganiser et reconstruire des index en utilisant l’instruction ALTER INDEX de SQL Server 2008 ou SQL Server 2005, ou l’Assistant Plan de maintenance de SQL Server 2008 ou SQL Server 2005. Cet article présente les options de SQL Server 2008 ou SQL Server 2005 de façon détaillée.

Utilisation de l’instruction ALTER INDEX

ALTER INDEX permet à un administrateur de base de données d’effectuer des opérations de maintenance sur un index de table ou d’affichage existant. Vous pouvez l’utiliser pour désactiver, reconstruire et réorganiser des index. Vous pouvez également l’utiliser pour définir des options sur l’index. Dans la plupart des cas, vous pouvez reconstruire des index pendant que la base de données est en ligne, ce qui maintient les données plus disponibles pour les utilisateurs que lors d’une reconstruction d’un index hors connexion.

Vous pouvez personnaliser le script exemple suivant pour reconstruire tous les index sur une table.

USE Contoso_Content_1
GO
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO

Ajustement des performances d’index par la définition du facteur de remplissage

Pour améliorer le stockage et les performances des données d’index, utilisez un facteur de remplissage. Lorsque des index sont créés ou reconstruits, la valeur du facteur de remplissage (1-100) détermine le pourcentage d’espace qui est rempli avec des données sur chaque page de niveau feuille. L’espace restant est réservé pour une future croissance. Dans de nombreuses situations, le niveau du facteur de remplissage à l’échelle du serveur par défaut de 0 (remplir chaque page à 100 %) est optimal. Cependant, pour SharePoint 2010, un paramètre à l’échelle du serveur de 80 est optimal pour prendre en charge la croissance et minimiser la fragmentation.

Pour afficher la valeur du facteur de remplissage d’un ou de plusieurs index, interrogez l’affichage du catalogue sys.indexes. Pour plus d’informations sur l’affichage, voir sys.indexes (Transact-SQL).

Pour configurer la valeur du facteur de remplissage à l’échelle du serveur, utilisez la procédure stockée système sp_configure. Pour plus d’informations, voir spconfigure (Transact-SQL).

Réduction des fichiers de données

Dans SQL Server 2008 et SQL Server 2005, vous pouvez réduire chaque fichier dans une base de données (extensions de nom de fichier .mdf, .ldf, et .ndf) pour supprimer les pages inutilisées et récupérer de l’espace disque. Les bases de données SharePoint 2010 ne réduisent pas automatiquement les fichiers de données, bien que de nombreuses activités créent de l’espace inutilisé dans la base de données. Les activités qui créent de l’espace inutilisé incluent l’exécution de la commande Move-SPSite de Windows PowerShell, et la suppression de documents, de bibliothèques de documents, de listes, d’éléments de listes et de sites.

Figure 3. Allocation de base de données

 

L’espace libre est uniquement libéré à partir de la fin du fichier ; par exemple, un fichier de base de données de contenu de 60 Go avec une taille cible spécifiée de 40 Go libère autant d’espace que possible à partir des 20 Go de fin (de façon conceptuelle, l’extrémité droite) du fichier de la base de données. Si des pages utilisées sont incluses dans les 20 Go de fin, elles sont par la suite relocalisées dans les 40 Go de début du fichier qui sont conservés. Vous pouvez réduire les fichiers de base de données individuellement ou par groupe.

N’effectuez que rarement des opérations de réduction, et uniquement après avoir exécuté une opération qui supprime beaucoup de données d’une base de données, et ensuite uniquement lorsque vous ne prévoyez pas réutiliser cet espace libéré. Les opérations de réduction de fichiers de données peuvent provoquer une intense fragmentation des index et sont extrêmement gourmandes en ressources. Vous devrez notamment réduire des fichiers de base de données lorsque vous relocalisez un grand nombre de collections de sites d’une base de données de contenu à une autre base de données de contenu ou lorsque vous supprimez une grande liste. Ces deux opérations peuvent créer de grandes quantités d’espace inutilisé. Les fichiers de bases de données peuvent uniquement être réduits jusqu’au point où il ne reste plus d’espace libre. Par conséquent, une base de données de contenu dans laquelle vous supprimez rarement du contenu pourrait ne pas vraiement profiter d’une réduction, et pourrait subir une diminution de performance lorsqu’elle doit grandir pour recevoir des données supplémentaires sans ressources spécifiques. Pour plus d’informations, voir Initialisation des fichiers de base de données.

Comme la réduction entraîne une fragmentation des index, ne réduisez pas les fichiers de base de données régulièrement. Réduisez plutôt les fichiers de base de données uniquement en présence de grandes quantités d’espace inutilisé qui apparaissent à la suite d’opérations ayant un impact significatif sur la quantité relative d’espace utilisé dans une base de données. Dans la mesure du possible, évitez de réduire une base de données.

Cependant, si vous devez réduire une base de données, procédez comme suit :

• Évitez de réduire automatiquement les bases de données et de configurer un plan de maintenance qui réduit par programme vos bases de données.

• Réduisez uniquement une base de données lorsque les utilisateurs ou les administrateurs suppriment 50 % ou plus du contenu et que vous ne prévoyez pas réutiliser l’espace inutilisé.

• Réduisez uniquement des bases de données de contenu. Les utilisateurs et les administrateurs ne suppriment pas suffisamment de données de la base de données de configuration, de la base de données de contenu de l’Administration centrale et des diverses bases de données d’application de service pour contenir un espace libre significatif.

• La réduction de bases de données est une opération extrêmement gourmande en ressources. Par conséquent, si vous devez absolument réduire une base de données, planifiez avec précaution à quel moment il convient d’exécuter l’opération de réduction.

• Après la réduction d’une base de données, les index dans cette base de données sont fragmentés. Utilisez ALTER INDEX… REORGANIZE pour traiter la fragmentation. Si votre configuration ne permet pas l’initialisation de fichier instantanée, réduisez la base de données à une taille cible pouvant recevoir le volume requis pour la prise en charge de la croissance prévue à court terme. Pour plus d’informations, voir Initialisation des fichiers de base de données.

Vous pouvez réduire des bases de données et des fichiers de base de données manuellement pour récupérer de l’espace en exécutant les instructions DBCC SHRINKFILE et DBCC SHRINKDATABASE dans SQL Server 2008 ou SQL Server 2005 Management Studio.

Pour découvrir pourquoi la réduction d’une base de données compromet les performances et ne doit être effectuée qu’en cas d’absolue nécessité, voir Pourquoi vous ne devriez pas réduire vos fichiers de données (éventuellement en anglais).

Réduction d’une base de données au moyen de commandes Transact-SQL

DBCC SHRINKDATABASE réduit les fichiers de données et les fichiers journaux pour une base de données spécifique. Pour réduire des fichiers individuels, utilisez DBCC SHRINKFILE.

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE utilise la syntaxe suivante.

DBCC SHRINKDATABASE 
( 'database_name' | database_id | 0 
     [ ,target_percent ] 
     [ , { NOTRUNCATE | TRUNCATEONLY } ] 
)
[ WITH NO_INFOMSGS ]

database_name | database_id | 0 spécifie le nom ou l’ID de base de données. Pour sélectionner la base de données actuelle, utilisez 0.

target_percent est l’espace libre, en pourcentage, que vous souhaitez conserver après la réduction de la base de données.

NOTRUNCATE compacte les données dans des fichiers de données en déplaçant les pages allouées de la fin d’un fichier à des pages non allouées au début du fichier.

TRUNCATEONLY redonne au système d’exploitation tout l’espace libre disponible à la fin du fichier mais n’effectue pas de mouvements de pages dans le fichier.

Pour plus d’informations, voir DBCC SHRINKDATABASE (Transact-SQL).

DBCC SHRINKFILE

DBCC SHRINKFILE utilise la syntaxe suivante.

DBCC SHRINKFILE 
(
     { 'file_name' | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

file_name | file_id spécifie le nom ou l’ID du fichier.

EMPTYFILE migre toutes les données du fichier spécifié à d’autres fichiers dans le même groupe de fichiers.

target_size est la taille cible du fichier en méga-octets, exprimée sous forme d’entier.

NOTRUNCATE compacte les données dans des fichiers de données en déplaçant les pages allouées de la fin d’un fichier à des pages non allouées au début du fichier.

TRUNCATEONLY redonne au système d’exploitation tout l’espace libre disponible à la fin du fichier mais n’effectue pas de mouvements de pages dans le fichier.

Pour plus d’informations, voir DBCC SHRINKFILE (Transact-SQL).

Réduction d’une base de données à l’aide de SQL Server 2008 Management Studio

Pour cela, procédez comme suit.

Pour réduire une base de données à l’aide de SQL Server 2008 Management Studio

1. Dans la barre des tâches, choisissez Démarrer, Tous les programmes, Microsoft SQL Server 2008, SQL Server Management Studio.

2. Dans l’Explorateur d’objets, connectez-vous à une instance du moteur de base de données SQL Server 2008, puis développez cette instance.

3. Développez Bases de données, cliquez avec le bouton droit sur la base de données que vous souhaitez réduire, choisissez Tâches, Réduire, Fichiers.

4. Sélectionnez le type de fichier et le nom du fichier.

5. Sélectionnez Réorganiser les fichiers avant de libérer l’espace inutilisé. Vous devez également définir la valeur Réduire le fichier à. Sélectionnez cette option pour rendre au système d’exploitation tout espace inutilisé dans le fichier et pour relocaliser des lignes à des pages non allouées.

6. Cliquez sur OK.

Création de plans de maintenance SQL Server 2008

Vous pouvez appliquer par programme de nombreuses opérations de maintenance de base de données décrites dans cet article en implémentant des plans de maintenance SQL Server. Les plans de maintenance peuvent automatiser et planifier des tâches essentielles pour protéger vos données. En utilisant des plans de maintenance dans SQL Server 2008 ou SQL Server 2005, un administrateur peut planifier des opérations telles que l’exécution de contrôles de cohérence de base de données, la réorganisation d’index ou la reconstruction d’index. Pour plus d’informations, voir les ressources suivantes :

• Assistant Plan de maintenance pour SQL Server 2008.

• Assistant Plan de maintenance de base de données pour SQL Server 2005.

Pour configurer un plan de maintenance de base de données SQL Server 2008

1. Dans la barre des tâches, choisissez Démarrer, Tous les programmes, Microsoft SQL Server 2008, SQL Server Management Studio.

2. Dans l’Explorateur d’objets, connectez-vous à une instance du moteur de base de données SQL Server 2008, puis développez cette instance.

3. Choisissez Gestion, cliquez avec le bouton droit sur Plans de maintenance, puis choisissez Assistant Plan de maintenance.

4. Choisissez Suivant jusqu’à ce que vous accédiez à la page Sélectionner les propriétés de plan.

   Figure 4. Page Sélectionner les propriétés de plan

   

5. Dans les champs Nom et Description, spécifiez un nom et une description.

6. Décidez s’il convient de configurer un ou plusieurs plans de maintenance.

   • Pour configurer un plan de maintenance unique, sélectionnez Planification unique pour la totalité du plan ou pas de planification.

   • Pour configurer plusieurs plans de maintenance avec des tâches spécifiques, sélectionnez Planification distincte pour chaque tâche.

   Si votre environnement contient au moins 10 bases de données de contenu ou plus de 200 Go de données, il est recommandé de configurer des plans de maintenance différents pour répondre aux besoins spécifiques de chaque base et élargir au maximum la fenêtre de maintenance.

   Si vous configurez plusieurs plans de maintenance pour une base de données, spécifiez un nom ou une description qui vous permet de différencier les plans et leurs fonctions, notamment leur planification.

7. Cliquez sur Modifier pour définir une planification pour un ou plusieurs plans.

   La boîte de dialogue Propriétés de la planification du travail apparaît.

   Figure 5. Boîte de dialogue Propriété de la planification du travail

   

8. Remplissez la planification, cliquez sur OK, puis cliquez sur Suivant.

9. Dans la page Sélectionner des tâches de maintenance, sélectionnez les tâches de maintenance à inclure dans le plan, puis cliquez sur Suivant.

   Figure 6. Page Sélectionner des tâches de maintenance

   

   Tenez compte des remarques suivantes :

   • Un plan de maintenance peut comprendre une réorganisation d’index ou une reconstruction d’index, mais pas ces deux opérations à la fois.

   • Un plan de maintenance ne doit jamais inclure la réduction d’une base de données.

   • Pour déterminer la durée de chaque tâche, testez chaque tâche une à une avant de les regrouper dans un plan. Il peut être nécessaire de définir plusieurs plans de maintenance exécutés à différents moments, pour faire en sorte que les tâches soient réalisées entièrement pendant les heures creuses, afin de perturber le moins possible les opérations réalisées par les utilisateurs finaux.

   • La Tâche de nettoyage de maintenance supprime les fichiers qui subsistent après une maintenance planifiée.

10. Dans la page Sélectionner l’ordre des tâches de maintenance, changez l’ordre des tâches du plan de maintenance s’il le faut. Sélectionnez une tâche, puis cliquez sur Déplacer vers le haut ou Déplacer vers le bas. Après l’ordonnancement des tâches, cliquez sur Suivant.

    Notes

    Si vos bases de données sont très grandes, vous pouvez créer un plan de maintenance distinct qui vérifie l’intégrité de la base de données moins fréquemment que la maintenance d’index.

    Figure 7. Page Sélectionner l’ordre des pages de maintenance

    

11. Ensuite, l’Assistant vous guide dans la définition des détails de chaque tâche. Dans la page Définir la tâche de vérification de l’intégrité de la base de données, sélectionnez les bases de données dont il faut vérifier l’intégrité, puis cliquez sur Suivant.

    Notes

    Vous pouvez en toute sécurité vérifier l’intégrité de toutes les bases de données SharePoint 2010.

    Figure 8. Page Définir la tâche de vérification de l’intégrité de la base de données

    

12. Dans la page Définir la tâche Réorganiser l’index, dans la liste Bases de données, spécifier les bases de données pour lesquelles vous souhaitez réorganiser les index, activez la case à cocher Compacter les objets importants, puis cliquez sur Suivant.

    Figure 9. Page Définir la tâche Réorganiser l’index

    

13. Dans la page Définir la tâche Réorganiser l’index, si vous choisissez de reconstruire des index au lieu de réorganiser des index, spécifiez les bases de données dans la liste Bases de données.

14. Sélectionnez Modifier l’espace disponible par pourcentage de page de, tapez 80, puis cliquez sur Suivant.

    Modifier l’espace disponible par pourcentage de page définit le facteur de remplissage de la base de données.

    Figure 10. Page Définir la tâche Reconstruire l’index

    

15. Dans la page Définir la tâche de nettoyage de maintenance, spécifiez les valeurs demandées, puis cliquez sur Suivant.

    Conseil

    Nous recommandons de supprimer les Rapports de texte du plan de maintenance.

    Figure 11. Page Définir la tâche de nettoyage de maintenance

    

16. Dans la page Sélectionner des options de rapport, sélectionnez Enregistrer un rapport dans un fichier texte , sélectionnez un emplacement pour les fichiers, puis cliquez sur Suivant jusqu’à ce que vous terminiez l’Assistant.

    Figure 12. Sélectionner des options de rapport

    

Conclusion

La maintenance cohérente des bases de données qui hébergent SharePoint 2010 peut significativement améliorer l’état et les performances du système.

Vérifiez que vous disposez de sauvegardes fiables de toutes les bases de données avant de réaliser des opérations de maintenance et de mettre en place des plans de maintenance.

Avant d’implémenter un plan de maintenance ou des opérations de maintenance spécifiques qui s’exécutent de manière cohérente, testez l’impact des opérations sur votre système et le temps requis pour leur exécution.

Dans la mesure du possible, faites en sorte que les opérations et plans de maintenance soient exécutés pendant les heures creuses, pour éviter de ralentir les performances et de gêner les utilisateurs.

See Also

Other Resources

Cette rubrique est également disponible en téléchargement