[2005] Partitionner table pour chaque valeurs de X

**Sergejack** · 08/09/2009, 15h33

Bonjour,

J'ai une table avec un champ entier qu'on n'a qu'à appeller X.
J'aimerais savoir s'il m'est possible de faire une partition des lignes de cette table pour chaque valeur de X.

Genre X = 156 => partition avec 13 lignes, X = 896 => parition avec 5 lignes, etc.

Merci.

**jero44** · 08/09/2009, 16h31

Bonjour,
tout d'abord le partitionnement de table ne sera efficace que dans le cas où votre table possède de très nombreuses lignes. Les exemples que vous citez sont très nettement insuffisant pour mettre en place un partitionnement.
La fonction de partitionnement travail par plage de valeur, il est donc tout à fait possible de définir des partitions en fonctions de la valeur contenue dans le colonne X.
+ d'infos sur la fonction de partitionnement http://msdn.microsoft.com/fr-fr/library/ms187802.aspx

**Sergejack** · 08/09/2009, 17h28

Dans la doc, il est indiqué qu'il faut fournir une liste de valeur que pourrait atteindre X afin de crée les partitions (de plus cette liste est limitée à 999 valeurs).

Moi, j'aurais une infinité de valeur.
Je songe à la partition parce que jamais je n'aurai à faire d'opération (insert/delete/update/select...) sur cette table pour des colonnes ayant des X distinct.

X représente vraiment l'isolation des groupes des lignes.

**Jinroh77** · 08/09/2009, 17h46

Même si vous n'avez pas d'opération de modification de données à faire sur ces parties de tables, le simple fait de les partitionner en autant de partition risque de vous faire perdre plus en lecture de métadonnées que le fait de modifier une table non partitionnée dont une grande partie n'est pas modifiée.

**Sergejack** · 09/09/2009, 08h57

Pourtant quand je fais une oppération de suppression de toutes les lignes pour un même X, celle-ci est très coûteuse en process (peut-être parce que j'ai 2 indexes sur cette table).

**mikedavem** · 09/09/2009, 13h33

Bonjour,

Le partitionnenement de tables ne vaut que pour les très grosses bases de données en général lorsque la RAM ne suffit plus à contenur l'ensemble des données que l'on désire avec de gros traitements et lorsque meme la mise en place des index ne permet plus d'obtenir des performances satisfaisantes ou bien encore lorsque la moindre opération sur la table prend énormément de ressources et / ou de temps.

Pourtant quand je fais une oppération de suppression de toutes les lignes pour un même X, celle-ci est très coûteuse en process (peut-être parce que j'ai 2 indexes sur cette table).

Qu'appelez vous coûteuse en progress ? Quelle quantité de données ?
Quelle est la DDL de votre table ? Sur quelle colonne et valeur voulez vous vraiment partitionner ?

++

**Sergejack** · 09/09/2009, 13h54

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tSubsetKey]') AND type in (N'U'))
CREATE TABLE tSubsetKey
(
	packID INT NOT NULL
	, memberID BIGINT NOT NULL
	, CONSTRAINT PK_tSubsetKey PRIMARY KEY (packID, memberID)
)
GO
 
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tSubsetKey]') AND name = N'IDX_memberID')
CREATE INDEX IDX_memberID ON tSubsetKey(memberID)
GO

J'aimerais partitionner pour chaque packID.
En général, groupées par packID j'ai environ 3000 lignes.

À la supression, le processeur est utilisé à 100% pendant 1 à plusieurs secondes.
Ce qui a pour conséquence, qu'en production notre serveur 4 cores est tout le temps à fond pendant les heures "de pointe".

**mikedavem** · 09/09/2009, 14h01

Est ce que memberID doit obligatoirement être en BIGINT et est ce que vos recherches n'utilisent que pour seul prédicat la colonne memberID ou utilisez un prédicat avec les colonnes PackID et memberID ensemble ?

++

**Sergejack** · 09/09/2009, 15h45

Hélas je n'ai pas le choix quant au type de memberID (qui est donc BIGINT).
J'utilise toujours packID comme principal critère de sélection et parfois memberID comme critère supplémentaire.

**Jinroh77** · 09/09/2009, 16h16

Si vous utilisez packID comme critère de recherche pour le delete, pourquoi n'existe-t-il pas d'index sur cette colonne ?

**Sergejack** · 09/09/2009, 17h05

Ma clé primaire (indexe clustered) commence par le packID, donc nul besoin de rajouter un indexe pseudo redodant.

**Jinroh77** · 09/09/2009, 17h26

Désolé, lecture trop rapide de votre code, je n'avais pas lu la contrainte PK.

**mikedavem** · 09/09/2009, 17h51

Envoyé par Sergejack

Hélas je n'ai pas le choix quant au type de memberID (qui est donc BIGINT).
J'utilise toujours packID comme principal critère de sélection et parfois memberID comme critère supplémentaire.

Donc vous n'avez pas besoin de l'index sur memberID. Vous pouvez le supprimer.

Est ce que les opérations de suppression sont moins gourmandes après cela ?

++

**Sergejack** · 10/09/2009, 09h34

Je n'avais sans doute pas bien compris le contexte de votre précédente question.

MemberID me sert dans bcp de jointures et l'index m'apporte à ce niveau un gain de vitesse appréciable.

**Ptit_Dje** · 10/09/2009, 09h45

Envoyé par Sergejack

À la supression, le processeur est utilisé à 100% pendant 1 à plusieurs secondes.
Ce qui a pour conséquence, qu'en production notre serveur 4 cores est tout le temps à fond pendant les heures "de pointe".

Quelle est la volumetrie de votre table?
En moyenne combien de lignes sont supprimées a la fois par transaction?
Que montre l'execution plan de votre requete?
Qu'est ce qu'il en est de la RAM libre?
Qu'en est il des disques?
Avez vous penser a monitorer les performances de votre serveur a "a plus long terme" afin de voir s'il ne faut pas le re-sizer?

**mikedavem** · 10/09/2009, 09h56

Je partage l'avis de Ptit_Dje.

Il faudrait dans ce cas pouvoir monitorer les performances de votre serveur lors que votre requête s'exécute.

La monopolisation des ressources processeurs est souvent liée à d'autres problèmes du serveur. Vous pouvez poser des compteurs sur la mémoire, l'activité des disques ,les process en jeu ainsi que l'activité processeur (activité en mode utilisateur et en mode noyau) pour comprendre ce qu'il se passe dans un 1er temps.

++