Coucou tout le monde,
 
j'utilise une table qui commence à devenir lourde : 60.000.000 d'enregistrements ( 5.000.000 par mois ), et qui est consultée à longueur de journée par plusieurs utilisateurs.
 
Pour les recherches 'simples', çà ne pose pas encore de problème ; par contre, dès qu'il y a ajout ou suppression d'enregistrement, là çà commence à grincer, et toute requête un peu plus fine ( GROUP BY ) prend une éternité ...
 
Je me dis donc : pourquoi ne pas scinder cette table en plusieurs tables ? Je devrais y gagner en rapidité d'éxécution !
Mais la gestion de ces tables à la place d'une seule me parait un poil plus compliquée ...
 
Qq'un connait ce problème de volumétrie ?
Quels choix faire ?

Faire des index corrects.
 
A la limite, pour remplacer le découpage, tu peux faire une fonction du style :
 

 
Ce type de fonction (ou vue paramètrée) permet d'accélerer de façon conséquente la récupération d'un lot réduit de données. L'avantage, c'est que lorsque tu veux pas te limiter à 1 mois, tu peux toujours faire un select normal directement dans ta table.
De plus, si tu utilises SQL Server 2000, une telle fonction te permet de faire des mises à jour dedans (update, delete et insert sont authorisés tant que tu n'as pas de fonctions de regroupement ou de jointures)
Pour l'ajout/supression, tu peux aussi passer par des procédures stockées. Tu devrais y gagner énormément.
 
Mais pour moi, le plus important, c'est d'avoir des index.

La table contient déjà plusieurs index
Mais les index ne sont utiles que pour les select, non ?
Là mon pb du moment c'est l'insertion de données en masse ...

Pour l'insertion, en effet, les indexne jouent que très peu (et même pire, plus il y a d'index, et plus ça peut ralentir : mise à jour de toutes les index, et vérification de l'unicité des index uniques)
 
Je n'ai pas d'idée pour optimiser l'insertion. Il n'y a pas de SQL Loader avec SQL Server, et en effet, l'insert est assez lent. A part augmenter la rapidité du serveur (ajout de disques en RAID, et augmentation de la mémoire) je ne vois pas trop comment tu peux améliorer les performances de façon conséquente à ce niveau.
 
Juste un truc : si c'est un insert de masse, je te conseille de faire :

 
Le faite de faire un lock avec une portée sur toute la table durant ton insersion va éviter que des personnes fassent des select sur la table en même temps.
 
Il y a cependant deux points négatifs :
-> Le select avec lockxx va être mis en file d'attente jusqu'à ce que tous les locks (même d'une ligne) soient libérés sur la table.
-> Durant toute ta transaction, toutes les requêtes seront mises en file d'attente. Il convient donc de mettre à jour ta table à un moment de faible utilisation.
 
Pourquoi ne pas faire une table temporaire dans laquelle tu log les infos du jour, puis par job, tu les recopie durant la nuit dans la table en question ?
Si les données du jour ne représentent pas une information vitale, c'est ce qu'il y a de mieu à mon avis.

Ah, et lors de tes insert de masse, tu peux désactiver le autocommit. Eviter à la base de faire une transaction par ligne, c'est une bonne optimisation déjà (bien que d'après des tests que j'ai pu faire, c'est pas super concluant)

J'utilise un DTS pour faire mon insertion, je ne contrôle donc pas tout le processus ...
Pour ce qui est de la puissance de la bécane, bah on va difficilement pouvoir faire mieux
Bi Xeon 3.2G / 2G ram / Raid 5 SCSI / ...
 
Si je supprime temporairement tous les index avant l'insertion, et je les regénére après, çà peut aider ?

j'en doute grandement. et de toute façon, faut surtout pas faire
 
PS: logiquement, un DTS est transactionnel, donc pour le "select null from latable with lockxx" tu dois pouvoir le mettre en première étape de ton lot. Je ne garantis pas que ça énormément améliorer la chose, mais ça devrait déjà être un minimum.

Sous Oracle, t'as des options sur les tables pour améliorer un peu les perfs, genre pouvoir accéder à la table en parallèle (même en ne lançant qu'un requête).
 
Des infos ici :
 
http://www.oreilly.com/catalog/ora [...] /ch01.html
 
Il y a sûrement un équivalent SQL Server ..

Sinon, je te renvoie sur cette page de la documentation de SQL Server :
 
Optimisation des performances de base de données

Mais y'a pas grand chose de plus que ce que j'ai dit :
- Indexes
- Procédures stockées : totalement interdit de faire la moindre requête "en live"

Et le découpage en plusieurs fichiers sert à qqchose ?
Là le fichier de données fait 10G ...
En sachant que j'utilise un fichier pour les données et un pour les index

Si t'as plusieurs disques et qu'ils ne sont pas en RAID, oui, ça peut servir. Sinon, ça n'est pas spécialement utile. Les fichiers de données ne sont pas gérés dans SQL Server comme les TABLESPACE d'Oracle.
 
Par contre, tu peux dors et déjà mettre le fichier des index et celui des données sur deux disques distincts (pas sur la même chaîne RAID si tu es en RAID. Tu devrais déjà gagner considérablement.
 
Mais bon, le plus important, c'est surtout le journal des transactions.
M$ préconise (pendant l'installation de SQL Server) de découper comme suit :
 
C:\ (RAID mirroré) : Windows + Binaires de SQL Server
D:\ (RAID 5 ou 50) : Journal des transactions
E:\ (RAID 5 ou 50) : Fichier de données
 
Avec obligatoirement D et E sur deux canaux différents, voir deux contrôleurs si possible, car il y a des écritures/lectures intensives dans les deux fichiers en //, surtout lorsqu'on fait des mises à jour des données (c'est le cas de tes insert).
 
Chez moi j'ai pas de RAID, mais j'au tout de même fait attention à avoir chacun de ces 3 disques sur des disques physiques différents, et D: et E: sont sur deux canaux U3W séparés, ce qui me garanti des performances optimales pour mon matériel.

Ah... Et juste un truc...
 
Ton fichier de données, il est à croissance automatique ? Tu insères quel volume quand tu passes un lot d'insertions ? Vérifie que le fichier des données/index/transactions n'a pas besoin d'un redimensionnement en plein milieu...
 
Dans l'absolu, fait comme la gestion par défaut des tablespace d'Oracle : pas de croissance automatique : prévoit à l'avance l'occupation disque. Redimensionner à la voler un fichier de 10 Go, c'est généralement pas très sympa pour le disque.
 
PS: 10 Go ça me paraît énorme. Ormis ta table de 60 000 000 de lignes, y'a quoi dans ta base ? T'es sûr que c'est pas le log des transactions qui bouffe toute la place (j'ai eu le cas, chez moi j'ai saturé un disque de 160 Go avec un journal des transactions plein... En 3 heures (pas bien de faire des jobs qui font des actions sur l'ensemble des lignes de la base toutes les 30 secondes )

Config du serveur :
. C : raid 0
. D et E : raid 5 sur 3 disques
 
Fichier de données : 9.9G
Fichier des index : 2.3G
Fichier log : 33M
 
Les 3 fichiers sont sur le D, ds le même répertoire.
Les autres bases sont + ou - calquées sur ce même schéma.
 
Pour ce qui est des canaux et controleurs, j'en sais rien j'ai pas monté la machine ( j'aurais bien voulu )
 
Tous les fichiers sont en croissance automatique ...
Là je viens de voir que les fichiers grossissent par palier de 32M, pas terrible, je vais augmenter cette valeur tout de suite !
 
A part ma table de 60.000.000 de lignes y'a rien d'autre ds cette base
 
En basculant les logs sur le C, çà devrait déjà aller mieux puisque c'est un disque physiquement différent, nan ?

Si c'est possible, moi je te conseille grandement d'éclater ton RAID 5 en 3 disques :
D: LOG
E: INDEX
F: DATA
 
Et je te conseille de mettre E et F sur la même chaîne SCSI, et C et D sur l'autre.
 
Mine de rien, même si le RAID 5 est très performant, en changeant les fichiers de disques, tu vas énormément gagner. Notamment pour les raisons suivantes :
1) Ca va réduire les accès I/O sur le disque logique. Par conséquent, ça va réduire la file d'attente, et même si le RAID 5 est très rapide, tu devrais gagner à ce niveau. En effet, le serveur est actuellement incapable de sérialiser correctement plusieurs tâches dans les 3 fichiers. En temps normal, il faut permettre au serveur de lire dans un index pendant qu'il écrit dans les données et met à jour le ficher des transactions. Là, il est obligé de tout faire à la suite, impossible donc de sérialiser.
2) Ca va réduire la fragmentation de tes fichiers : en effet, imagine la config de base :
D : Fichier de données
I : Fichier d'index
L : Fichier des logs

 
Résultat, pour lire le contenu d'un index volumineux, dans ce cas, il va falloir faire 5 accès disque. Ensuite, une fois les données retrouvées, il va falloir faire 4 accès de données. Deplus, les fichiers n'étant pas contigüs, tu allonge les temps d'accès (plus de distance à parcourir pour retrouver les infos d'un bout de fichier à l'autre).
 
Seul problème, c'est que tu perds les capacités de récupération et d'agrandissement à chaud de l'espace disque du RAID 5. Il faudrait donc, dans l'absolu, racheter des disques pour au moins faire du RAID 0.
 
Dans un permier temps, tu peux tout de même améliorer les choses de la façon suivante :
 
LOG : sur C: => C'est un fichier qui ne fait que grossir (sauf lorsque tu le shrink à la fin d'un backup). Il n'y a jamais de lecture dessus. Les accès n'ont donc pas besoin d'être très rapide (ils sont juste nombreux). Il ne va donc pas spécialement alourdir les traîtements sur le disque système. Deplus, vu que ce fichier est extrêment sensible (combiné aux backups, il te permet de récupérer à la seconde près un état stable de la base de données, même si ton ficher de données est corrompu). Le RAID 0 est donc parfaitement approprié pour ce fichier.
Data sur le D:
Index sur le E:
Mettre les Index et les Data sur le même disque, c'est très moyen, à cause de la sérialisation. Cependant, les deux ont besoin d'un accès extrêment rapide, et font des lectures/écritures intensives, tout en ayant besoin d'un espace de stockage fiable. Le RAID 5 est donc le bienvenu (mais dans l'idéal il en faudrait deux). Le fait de les changer de disque logique (un sur D et l'autre sur E), c'est que lorsque les fichiers vont croître, ils font chacun le faire sur une partie du disque réservée à leur partition. Par conséquent, il n'y aura plus de phénomène de fragmentation (ce qui, d'après le paramètrage de 32 Mo, me semble est une source certaine des ralentissements que tu obtiens).
 
Tu peux déjà commencer par ce paramètrage, avant de tout péter. L'avantage de faire ça, c'est que tu ne perds pas la sécurisation du stockage du serveur actuel.
 
PS: Est-ce que tes données sont particulièrement sensibles ? Si ce n'est pas le cas, tu peux m'envoyer un backup de ta base ainsi que quelques requêtes types de consultation et d'alimentation en données, que je regarde sur mon serveur personnel (config que je t'ai indiqué). Il est bien moins puissant que le tiens (bi-piii 933 / 2 Go SDRAM / 4 HD U3W 10ktrm) mais c'est une bonne base pour faire des tests avec une architecture avec 4 disques différents, puisque je les ai déjà

Ouah !
En relisant tes explications, je vois que tu sais de quoi tu parles !
Merci pour toutes ces précisions
 
Je ne peux physiquement pas toucher aux machines, elles sont chez un hébergeur pro ; je ne peux donc pas faire de tests en modifiant / désactivant le raid ( j'aurais vraiment adoré pouvoir le faire, mais là çà va vraiment être difficile )
 
Vu le prix de ces machines, je ne pense pas qu'on va réinvestir ds des disques si mon argument c'est : "on va p'tête gagner en perf"
 
Donc pour l'instant, il faut utiliser la config matérielle actuelle
 
Ce qui est chiant c'est que tu ne peux pas modifier les fichiers d'origine ; j'ai voulu modifier le fichier de log pour le déplacer sur le C, mais imposible de supprimer celui d'origine sur le D ... Faut que je creuse un peu + ...
 
J'ai déjà fais une 1ère modif : désormais les fichiers s'agrandissent par palier de 512M, çà redimensionnera - souvent
 
Le pb, c'est que cette machine est celle de prod, elle est donc utilisée toute la journée et une bonne partie de la nuit, difficile donc de faire des tests dessus ... Le seul moment où je peux faire des tests à peu près sans géner personne, c'est entre 12h30 et 13h30, un peu light pour faire des manip sur des fichiers monstrueux ...
 
Je peux utiliser la machine de pré prod à ma guise, mais elle est un peu - puissante ( 1 seul Xéon / 1G ram / C, D et E sur 3 disques en raid 5 )
 
Notre serveur de dév est encore - puissant ( 2800+ / 1.5G ram / 80G raid 0 ), c'est le seul que j'ai physiquement à portée de main, mais pas forcément intéressant vu qu'il y a que 2 disques dedans ...
 
Oui nos données sont sensibles : cette table contient les appels téléphoniques de nos clients ( on est opérateur téléphonique alternatif )
Mais bon, çà ne sont que des chiffres
 
Je vais déjà essayer de mettre 1 fichier par partition, on verra bien ...
Mais demain çà va pas être possible, je suis pas dispo de la journée
 
La suite jeudi ...
 
Question bonus : défragmenter les disques peut vraiment aider sur ce type de machine, pour savoir si çà vaut le coup d'investir ds un VRAI soft de défragmentation ?

En cherchant un peu, je viens de découvrir 2 choses 'marrantes'
.Il y a 6 disques ds la bécane et pas 5 ( 2 en raid 1 pour le C et 4 en raid 5 pour le D, E )
.Il y a 18G d'espace non affecté sur le C et 36G d'espace non affecté sur le D, E ( on s'est déjà pris la tête plusieurs fois pour des pb d'espace disque ... )
 
Les gars qui ont monté cette bécane sont vraiment des boulets ...
 
Donc bonne nouvelle : avec 6 disques, je peux faire 3 raid 1    
 
Reste à voir çà avec mon responsable de projet pour pas péter le serveur

Bon, j'ai pas trop le tps de jouer avec la base en ce moment pour trouver la config idéale ; quels réglages dois je adopter avec la config actuelle du serveur en considérant que j'ai 2 disques physiquement différents ?
Y'a qd même pas mal de possibilités :
index + log sur C / data sur D
index sur C / log + data sur D
...

Même si c'est pas ce qu'il y a de mieu, je dirais log sur C et index + data sur D, pour diverses raisons liées à la config des disques.

OK, je vais essayer de refaire une base avec cette architecture
 
Merci