Discussion :

[grinder59]

Bonjour,

je travaille sur des bases SQL Server 2005 dans lesquelles on trouve plusieurs années de données et tout cet historique commence à altérer les performances de mes applications.

L'idée serait de faire la chose suivante :

1. Sauvegarde complète des données jusqu'à (Mois - 2) sur un server SQL d'archivage (pour que les données restent consultables) (tâche réalisée une seule fois pour la reprise d'historique)

2. Conservation des données de production des 2 derniers mois (Mois en cours et M - 1)

3. Tous les 1er du mois (mois M) faire une sauvegarde des données M-2 et aller archiver ces données sur le serveur d'archivage pour qu'elles soient disponibles

4. Purger le serveur de données M-2 que l'on vient d'archiver...

En gros, garder en production les données des 2 derniers mois (mois en cours inclus) et archiver le reste...

Vers quelles type de sauvegardes dois-je m'orienter ? Sauvegarde partielle ?
Et comment ajouter le critère M-2 ?

Merci de votre aide / liens / idées...

[dbaffaleuf]

Hello,

En édition Enterprise, il y a le partitionnement de tables aussi plutôt que des backups, qui est fait pour résoudre le problème de glissement de données. Tu ne peux pas dire à SQL Server "Fais-moi un backup de tous les mois à partir de M-2". Un backup physique prend forcément tout.

David B.

[mikedavem]

Les sauvegardes partielles ont été introduites avec SQL Server 2005. Pour cela il faut implémenté une architecture physique différente concernant les fichiers de votre base de données.

Il faut diviser votre base en groupes de fichiers (et fichiers) dans lequels vous archiverez vos données et d'autres où vous garderez vos données de production. Les groupes de fichiers dont les données sont archiver peuvent être en lecture seule. (Une archive par année ou autre par exemple ...).

La sauvegarde partielle vous permettra d'exclure les groupes de fichiers en lecture seule de la sauvegarde .. ce qui peut vous faire gagner en temps de sauvegarde et de maintenance également ...

++

[SQLpro]

Oui, mais pour que la sauvegarde partielle marche, il faut que chaque groupe de fichiers ou fichiers soit autonome : par de partitionnement, pas d'intégrité référentielle déclarative...

A mon sens, conserver un large historique en ligne ne doit pas poser des problèmes de performance si l'indexation est correcte, ce qui est rarement la cas.

Par exemple :

• les tables les plus grandes sont-elles organisées sous forme d'index clustered monotone (IDENTITY ou horodatage) ?
• Les index sont-ils bien couvrants ?
• Y a t-il suffisamment d'index ?

En effet tout ceci réduit drastiquement la fenêtre des données et économise la RAM...

A +

[grinder59]

Merci pour vos réponses...

Concernant le partitionnement des tables, nous avons trop de données et il y a trop d'impact pour pouvoir se permettre de modifier la structure dans un délai bref...

au niveau des index, ils sont placés sur :
- les clés primaires
- les champs recherchés dans les applications qui utilisent ces tables

Par contre je ne comprends pas cette phrase :

"les tables les plus grandes sont-elles organisées sous forme d'index clustered monotone (IDENTITY ou horodatage) "

[elsuket]

Bonjour,

Concernant le partitionnement des tables, nous avons trop de données

Non, une base de données est faite pour stocker beaucoup de données, d'où son nom
Pour information, je gère actuellement des bases de données de plusieurs téra-octets et je n'ai pas beaucoup de problèmes de performances, même sur des tables qui font plusieurs centaines de Go.
Demandez à SQLPro et Mikedavem, ils sont approximativement dans le même cas que moi, et on n'est pas les seuls ...
Pensez aux hôpitaux, aux banques & assurances, ...

au niveau des index, ils sont placés sur :
- les clés primaires
- les champs recherchés dans les applications qui utilisent ces tables

Pour le premier c'est normal, puisque toute spécification de clé primaire entraîne la création d'un index cluster sur la table.
A partir de ce moment là, les lignes de la tables sont ordonnées suivant l'ordre physique des valeurs de la clé.

Pour le deuxième, qu'est-ce qui vous permet de l'affirmer ?
Avez-vous étudié les requêtes les plus recompilées, les plus consommatrices de CPU, les plus sujettes aux attentes ?
Avez-vous regardez s'il existe des index que SQL Server considère comme manquants ?
Avez-vous regardé si tous vos index sont utiles ?
Avez-vous des utilisateurs se plaignant de ralentissements ?
Comment varie votre Buffer Cache Hit Ratio ?
Comment varie votre Page Life Expectancy ?
Nombreuses sont les questions qui peuvent suivre ...

Par contre je ne comprends pas cette phrase :

"les tables les plus grandes sont-elles organisées sous forme d'index clustered monotone (IDENTITY ou horodatage) "

SQLPro vous demande en fait si les clés primaires ou les index uniques clusterisés de vos tables ayant les volumétries les plus élevées sont de type :

- entier (INT ou BIGINT), avec la propriété d'auto-incrémentation (IDENTITY)
- date et heure : type DATETIME avec des valeurs toujours croissantes.

Placer des index cluster sur une colonne d'un de ces deux types procure de très bonnes performances :
- parce que les valeurs de la clé pour une plage de valeurs sont consécutives, ce qui minimise la fenêtre de données qui doit être présente dans le cache, puisqu'on lit plus souvent les lignes que l'on vient d'insérer que les lignes que l'on a inséré il y a 3 mois
- parce que cela évite les splits de page

@++

[grinder59]

merci de votre réponse... quand je disais qu'il y avait trop de données, je voulais dire qu'il ne nous est pas possible dans l'immédiat de modifier la structure des bases pour les partitionner...

pour les index placés sur les champs utilisés dans les recherche, c'est parce que nous les avons définis comme tels. Nous connaissons nos applications donc, nous savons que les recherches sont faites sur tel ou tel champs ! et tous nos index clusturisés (clé primaires) sont de type entier en auto increment.

Avez-vous étudié les requêtes les plus recompilées, les plus consommatrices de CPU, les plus sujettes aux attentes ?

- non, il existe des outils qui permettent de le faire ?

Avez-vous regardez s'il existe des index que SQL Server considère comme manquants ?

- non plus, là encore comment interroger sql server pour qu'il me le dise ?

Avez-vous regardé si tous vos index sont utiles ?

- oui, justement avec nos soucis de perf, nous nous sommes penchés sur ce sujet. Nous avons regardé si tous les champs qui sont utilisés comme des critères de recherche dans les applis faisaient bien l'objet d'un index

Avez-vous des utilisateurs se plaignant de ralentissements ?

- malheuresement oui,

Comment varie votre Buffer Cache Hit Ratio ?

- gloups, je ne sais pas ce que c'est, je vais chercher...

Comment varie votre Page Life Expectancy ?

- pareil, je ne sais pas ce que c'est => google

[zinzineti]

Bonjour,

4. Purger le serveur de données M-2 que l'on vient d'archiver...

En gros, garder en production les données des 2 derniers mois (mois en cours inclus) et archiver le reste...

Vers quelles type de sauvegardes dois-je m'orienter ? Sauvegarde partielle ?
Et comment ajouter le critère M-2 ?

Merci de votre aide / liens / idées...

Une idée :
1. Identifier les principaux tables (celles qui contiennent beaucoup de lignes)
donc faire une requête qui te renvoie pour chaque BD utilisateur le nombre de lignes de chaque table....

2. Partir d'une sauvegarde complète des bases de Prod

3. Restaurer les bases sauvegardées sur le serveur d'archivage

4. Mettre en place un ETL (tu peux utiliser SSIS) qui va se déclencher automatiquement (JOB) chaque 1er du mois en Heure Non Ouvrable (HNO) pour faire :

* Export des données des principales tables du serveur de PROD vers
les tables du serveur d'ARCHIVAGE
WHERE données plus vieille que 2 mois
AND données NOT IN Table_Archive

* Delete des données des principales tables du
serveur de PROD vers les tables du serveur d'ARCHIVAGE
WHERE données plus vieille que 2 mois:c'est la purge

NB : Faut bien tester l'appli d'Export/Import avant
la mise en prod et faire des tests du style si nb de lignes plus vieilles
que 2 mois à importer est DIFFERENT du nb de lignes
Réellement INSERT INTO Table_Archive Alors
ROLLBACK et envoie de message d'erreur ...

Du courage !

Etienne ZINZINDOHOUE

[grinder59]

Merci à tous pour vos réponses...
Alors à la lecture de celle-ci, j'ai notamment cherché les index que SQL Sevrer considérait comme manquant et cela a considérablement amélioré les performances des mes applications...

Plus de plaintes des clients et utilisateurs... Y'a un potentiel certain d'administration et d'amélioration que je n'ai pas exploité au travers des vues systèmes...

Merci encore !