Discussion :

[verbal34]

Bonjour tout le monde,

Sur une base avec des tables de grosses volumétries (plus de 400.000.000 de lignes par table pour +- 100Go de données index non compris) nous allons utiliser le partionnement des tables et index.

Pour la création de la fonction de partition, j'ai cru entendre, mais pas sûr, qu'avec SQL Server 2005 on est limité a 400 partitions. Or, pour le besoin de notre projet il me faut plus que 400 (434 pour être plus précis!)

Je vous sollicite afin de confirmer ou infirmer cette idée reçue !

Si tel est le cas, Est ce que SQL Server 2008 permet d'en créer plus que 400 ?

Merci d'avance.

[mikedavem]

Bonjour,

Le nombre de partitions maximum pour SQL Server 2005 et 2008 est 1000.

++

[verbal34]

Bonjour,

Le nombre de partitions maximum pour SQL Server 2005 et 2008 est 1000.

++

Nikel !

Merci pour l'info mikedavem.

[SQLpro]

Néanmoins, posez vous la question de l'intérêt de prévoir 434 partitions, soit 434 descripteurs de fichiers ouverts en exclusivité pour MS SQL Server....

Cela vous coutera cher en RAM et très cher en plan de requête.

Il n'y a pas d'intérêt en pratique, si vous voulez des performances, que le nombre de partitions soit aussi grand, ni que chacune soit aussi petite.
En effet, la bonne taille d'une partition c'est quelques dizaine de Go (grosso modo on peut dire que l'intérêt de partitionner c'est que chaque partition soit placé sur un disque PHYSIQUE indépendant !

A +

[verbal34]

Bonsoir SQLPro,

Néanmoins, posez vous la question de l'intérêt de prévoir 434 partitions, soit 434 descripteurs de fichiers ouverts en exclusivité pour MS SQL Server....

Dans notre base on doit garder les données d'une date J jusqu'à la date J+ 14 mois (+- 434 jours) et au jour J+435 ont doit supprimer les données de la date J. Le choix d'utiliser les partitions est historique au sein du projet et dû essentiellement à des problèmes de performances et de temps d'exécution (la suppression d'une partition est plus rapide que d'exécuter un DELETE sur des millions de lignes).

Cela vous coutera cher en RAM et très cher en plan de requête.

Serveurs target 4 x Quad Core avec 32Go de RAM et peu d'user.

Il n'y a pas d'intérêt en pratique, si vous voulez des performances, que le nombre de partitions soit aussi grand, ni que chacune soit aussi petite.
En effet, la bonne taille d'une partition c'est quelques dizaine de Go (grosso modo on peut dire que l'intérêt de partitionner c'est que chaque partition soit placé sur un disque PHYSIQUE indépendant !

A +

Dans la fonction de partition, les valeurs de partitionnement correspondent aux dates citées plus haut (converties en INT au format aaaammjj). Cette valeur est utilisée aussi dans nos requêtes (reporting par date) du coup on sait à l'avance quelle partition cibler.

[SQLpro]

Avez vous fait des essais en vrai grandeur ? Avec et sans vos 400 partitions ? Avec 10 partitions par exemple...
Quels sont vos conclusions ???

Supprimer une partition ne supprime pas les lignes de la table... Et heureusement !

Je crois que vous vous méprenez singulièrement sur l'usage du partitionnement.

A +

[verbal34]

Bonjour,

Avez vous fait des essais en vrai grandeur ? Avec et sans vos 400 partitions ? Avec 10 partitions par exemple...
Quels sont vos conclusions ???

Non pas du tout. J'ai pris le projet en cours de route. Je n'ai pas le poivoir de faire ce genre de choix, néanmoins je vais soumaitre cette idée surtout que :
- Ajourd'hui il y a 400 partitions (sur 400 dates) et dans chaque table, associée à un filgroup dédié, il n'y a que des données pour max 20 dates. Pourquoi les 400 partitions alors ? Aucune idée!

- Demain, l'idée est que l'on doit garder les données des 434 dates et les supprimer à J+434. Ce n'est pas encore implémenté et votre alerte va nous permettre surement d'anticiper des problemes de perf. Je vais proposer de partitionner par mois par exemple...

Supprimer une partition ne supprime pas les lignes de la table... Et heureusement !

Je crois que vous vous méprenez singulièrement sur l'usage du partitionnement.

A +

Erreur de formulation de ma part, on ne supprime pas les partions mais on s'en sert pour savoir quelles lignes on doit supprimer.

Je vais approfondir ce point et je reviendrais pour donner les résultats de tests.

[Ptit_Dje]

Bonjour,

Je pense que vous pourriez vous baser sur une clef de partitionnement annee-mois.
Ca vous ferait 14 partition - sur une base de 100GB, ca vous fera des partitions d'un peu moins de 10GB.
Se sera aussi plus facilement maintenable tout en ayant plus de sens que de compter en jours.

En cadeau, un exemple de fonction de partitionnement pour 2009 jusque 2012:

Code :

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

1
2
 
CREATE PARTITION FUNCTION [Year_Monthly_partition_function](int) AS RANGE RIGHT FOR VALUES (20090101, 20090201, 20090301, 20090401, 20090501, 20090601, 20090701, 20090801, 20090901, 20091001, 20091101, 20091201, 20100101, 20100201, 20100301, 20100401, 20100501, 20100601, 20100701, 20100801, 20100901, 20101001, 20101101, 20101201, 20110101, 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201)

Il vous reste a creer les filegroups adequats et mapper le tout avec un schema de partitionnement puis selectionner les tables a partitionner.

[verbal34]

Bonjour,

Je pense que vous pourriez vous baser sur une clef de partitionnement annee-mois.
Ca vous ferait 14 partition - sur une base de 100GB, ca vous fera des partitions d'un peu moins de 10GB.
Se sera aussi plus facilement maintenable tout en ayant plus de sens que de compter en jours.

En cadeau, un exemple de fonction de partitionnement pour 2009 jusque 2012:

Code :

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

1
2
 
CREATE PARTITION FUNCTION [Year_Monthly_partition_function](int) AS RANGE RIGHT FOR VALUES (20090101, 20090201, 20090301, 20090401, 20090501, 20090601, 20090701, 20090801, 20090901, 20091001, 20091101, 20091201, 20100101, 20100201, 20100301, 20100401, 20100501, 20100601, 20100701, 20100801, 20100901, 20101001, 20101101, 20101201, 20110101, 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201)

Il vous reste a creer les filegroups adequats et mapper le tout avec un schema de partitionnement puis selectionner les tables a partitionner.

Merci Ptit_Dje pour le cadeau même si j'avais déjà créé le script qui me le génère ^^

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
20
21
22
23
24
25
 
-- Create Partition function Script
DECLARE @StartDate DATETIME
DECLARE @MaxIteration INT
DECLARE @PartitionFunctionName VARCHAR(50)
SET @PartitionFunctionName = 'PartitionFunction_Year_Month'
SET @MaxIteration = 50
SET @StartDate = '01-01-2009'
DECLARE @Toto VARCHAR(MAX)
SET @Toto = '('
 
;WITH CTE AS
(SELECT @StartDate AS MyDate
UNION ALL
SELECT DATEADD(MONTH,1, MyDate) AS MyDate
FROM CTE
WHERE MyDate < DATEADD(MONTH, @MaxIteration, @StartDate)
)
SELECT @Toto = @Toto + CONVERT(VARCHAR, MyDate, 112) + ', '
FROM CTE OPTION (MAXRECURSION 100);
 
SELECT @Toto = SUBSTRING(@TOTO, 1, LEN(@TOTO)-2) + ')'
SELECT '-- PartitionFunction ' + @PartitionFunctionName 
SELECT 'CREATE PARTITION FUNCTION [' + @PartitionFunctionName + '](int) AS RANGE RIGHT FOR VALUES ' + @Toto + 
' GO '

il ne me reste qu'à effectuer le test de comparaison proposé par SQLPro.