Discussion :

[gouffe]

Bonjour,

Je sollicite votre aide sur un problème peut être simple, mais là, j'avoue que je ne vois pas.

J'ai une table sollicitée uniquement en écriture. Que des INSERT. Appelons cette table A.
J'ai une table identique sur un autre serveur d'archive. Appelons cette table ArchiveA

J'ajoute environ 150000 lignes par jour dans la table A, ce qui n'est pas énorme encore, mais bon ça commence à faire ! Du coup, pour optimiser le temps de réponse à l'écriture, j'archive les données tous les jours dans ArchiveA.
L'archivage consiste à prendre les données du jour, les transférer dans ArchiveA, et supprimer ce qui a été copié.

Mon problème vient du fait que lorsque je fais cet archivage, la table A n'est plus accessible en écriture et plus j'ai de données par jour, plus le temps d'indisponibilité de la table A est long.
Pourtant, je n'ai absolument pas à locker la table A puisque je prends des données antérieures, et que ces données n'ont aucun risque d'être modifiées.

Est-ce qu'il existe une solution à ce problème ?

J'ai beau chercher, je ne trouve aucune info se rapportant à ce cas d'utilisation.

Merci par avance,
Guillaume

[Invité]

Mon problème vient du fait que lorsque je fais cet archivage, la table A n'est plus accessible en écriture et plus j'ai de données par jour, plus le temps d'indisponibilité de la table A est long.

Serait-ce trop demander que de connaitre comment vous procéder ? Version?
Ce genre de détails qui ont une certaine importance...

[gouffe]

En effet, je suis désolé ne pas avoir précisé tout ça.

Donc je suis sous SQL Server 2008 R2.
Pour le process d'archivage, il me semblait l'avoir décrit, je peux ajouter la structure de la procédure stockée pour avoir un exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
BEGIN TRANSACTION Archivage
 
BEGIN TRY
 
INSERT INTO ArchiveA (...) SELECT ... FROM A WHERE F_Date < @Date
 
DELETE FROM A WHERE F_Date < @Date
 
COMMIT TRANSACTION Archivage
 
END TRY
BEGIN CATCH
 
ROLLBACK TRANSACTION Archivage
 
-- Reporting d'erreurs
 
END CATCH

S'il y a besoin d'autre chose, n'hésitez pas !

Guillaume

[Christianbt]

bonsoir,

je pense que ta tableA est lockée par le begin trans. Tant qu'elle n'est pas finie (commit), les ressources sur cette table ne sont pas libérées.
Est-il possible de tester sans la transaction ? comme ça vous verrez si le problème vient de là.

Christian

[gouffe]

Bonjour,

merci pour votre réponse.
En effet, sans le "Begin Transaction", le lock n'est pas présent, mais justement, j'ai besoin de cette transaction, car je ne parle que de la table A ici, mais il y a une table B qui dépend de A sur laquelle je fais la même opération.
Donc s'il y a un problème pendant le process, il faut que je puisse faire un Rollback.

C'est justement tout mon problème, comment faire en sorte pour que dans une transaction, on ne lock pas les tables concernées par cette transaction ?

Guillaume

[Christianbt]

il faudrait essayer le select * from matable with (nolock) mais je ne suis pas certain que cela fonctionne dans ce contexte. Sinon, il faudrait trouver une autre façon de faire (que je n'ai pas comme ça de suite sous la main )

[gouffe]

Oui en effet, le NOLOCK est une solution, mais ne s'applique qu'au SELECT. Le DELETE, lui, va faire un LOCK.

Disons que si je ne trouve pas mieux, ce sera au moins un début de solution !

Merci encore,
Guillaume

[mikedavem]

Bonsoir,

Essayez d'insérer vos données par petits lots dans ce cas là pour éviter les transactions longues et les escalations de verrous.

Vérifiez également que votre procédure utilise les index et non des scans de table ...

++

[elsuket]

Bonjour,

Pour avoir implémenté cela chez mon précédent employeur, j'avais fait un job qui faisait cela dans les périodes de faible activité.

Et puis j'en suis venu à partitionner la table, en posant autant d'index filtrés que de mois stockés dans la table, puisque la plupart des statistiques que nous fournissions interrogeaient les tables par année + mois

@++

[gouffe]

Bonjour,

merci pour vos réponses !

En effet, je pense pouvoir améliorer le temps de traitement en ajoutant des index, même si je préfère éviter d'en ajouter autant que possible car comme je l'ai dit, cette table doit être hautement disponible en écriture.
Le partitionnement est aussi une solution, mais un peu lourde à mettre en place à mon sens. (On y viendra peut être lorsqu'il y aura des millions d'écritures par jour )

Mais donc dois-je en conclure qu'il n'est pas possible de ne locker qu'une partie de table, ou encore faire une transaction en précisant de ne pas locker les ressources "consciemment" ?

Merci encore pour vous être penché sur le problème.
Guillaume

[mikedavem]

Mais donc dois-je en conclure qu'il n'est pas possible de ne locker qu'une partie de table, ou encore faire une transaction en précisant de ne pas locker les ressources "consciemment" ?

Si en placant les index adéquats sur votre table et que ceux-ci peuvent être utilisés (il faut que les données concernées par votre archivage représentent qu'un pourcentage faible de la volumétrie globale de votre table)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO ArchiveA (...) SELECT ... FROM A WHERE F_Date < @Date
 
DELETE FROM A WHERE F_Date < @Date

Sans index vous avez ici 2 scans de table ..

Vous pouvez essayez de jouer avec les niveaux d'isolation pour vos transactions (READUNCOMMITED) mais je ne vous le conseille pas ..

++

[aieeeuuuuu]

Bonjour,

Je pense que dans votre cas, vous pourriez utiliser la clause OUTPUT

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
DELETE 
FROM A
WHERE F_Date < @Date
OUTPUT DELETED.X, DELETED.Y [...]
INTO ArchiveA

En ajoutant un TOP x à cette requête et en l'exécutant en boucle tant que @@ROWCOUNT > 1, vous devriez réduire vos locks, peut être même améliorer les perfs...


Pour ce qui est des index...
si j'ai bien compris, vous remplissez une table dans la journée, puis vous la videz la nuit (genre vers une heure du matin), en archivant vers une autre table. Votre filtre sur la date sert donc à éviter de prendre les premières données de la journées (entre minuit et une heure ) déjà présentent dans la table...
Donc si vous lancez votre job tôt le matin, cela représente un très faible pourcentage des données, et un index ne sera pas forcément utile...
Mais cela dépend de ce pourcentage justement, c'est à tester. S'il améliore les perfs pour votre opération, mais que vous voulez éviter les pertes de perfs en écriture tout au long de la journée, il peut aussi être avantageux de le créer avant votre archivage, et de le supprimer ensuite...

[elsuket]

Je ne vous le conseille pas non plus

Le fait de mettre des index ajoute du pouillème aux temps de modification si vous les insérez par petits lots.
Si vous faites des modifications massives des données de votre table, ce peut être pénalisant, quoiqu'on puisse jouer sur le FILLFACTOR des index pour limiter l'effet.

Encore une fois, vous pouvez placer des index filtrés sur une clé intéressante, de sorte que le SELECT de votre INSERT ne touche qu'un seul index à la fois.
Chaque index devrait ainsi porter sur des périodes strictement exclusives.
De la sorte, vous accéderez à une partie de la table, et lors des modifications de données, vous ne touchez qu'un index particulier

@++

[gouffe]

Je n'avais pas vu les choses comme ça, je ne savais pas que SQL Server se servait des index pour limiter un lock sur une table. C'est déjà une très bonne chose de le savoir !

Mais je viens de faire l'essai, en ajoutant l'index sur le champ date correspondant, et pourtant, ça me bloque toujours. Bizarre.
Je vote pour l'instant pour une erreur de mon côté, du coup, je vais développer un petit simulateur de cette situation pour avoir une analyse de plans fiable, et si ça ne marche toujours pas, je reviens vers vous !

Merci encore pour votre aide !

Guillaume

[elsuket]

C'est parce que la plage de données que vous spécifiez est trop large.
Donc SQL Server doit lire toute la table.
Comme vous l'a suggéré Mikedavem, procédez par "tranches" plus petites

Sinon pensez aux index filtrés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE INDEX monIndex
ON maTable (maColonneDate)
WHERE maColonneDate BETWEEN 'YYYYMMDD HH:MM:ss debut' AND 'YYYYMMDD HH:MM:ss fin'

@++