Discussion :

[Pfeffer]

Bonjour à tous,

Je souhaiterai partitionné une table selon une fonction de partition sur des dates.
Cette table contient aujourd'hui 160 millions de lignes.

Voici la structure de ma table :

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
 
CREATE TABLE [gtm_crb_bgc30mn] (
[conso_id] bigint IDENTITY(1, 1) NOT NULL,
[datepoint] datetime NOT NULL,
[dateday] date NOT NULL,
[yearno] smallint NOT NULL,
[monthno] tinyint NOT NULL,
[compteur_id] int NOT NULL,
[type_pt_id] tinyint NOT NULL,
[version] tinyint NOT NULL,
[puissance] float NOT NULL,
[dateversion] datetime NOT NULL,
[validation] varchar(1) NOT NULL,
[echeance] varchar(4) NOT NULL,
CONSTRAINT [pk_gtm_crb_bgc30mn]
PRIMARY KEY CLUSTERED ([conso_id] ASC)

Question :
1. Est ce que je peux partitionner cette table sur le champs [datepoint] ?
2. Qu'en est il est de la PK [conso_id] ? Est ce que je dois la supprimer ou bien je peux la conserver ?
3. Est ce c'est bien comme cela qu'il faut créer la PK ?
4. Qu'en est il des index NON CLUSTERED ? Comment faire pour les aligner ?
5. Est ce que COLUMN STORE INDEX pourrait être plus performant ?

Code :

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

1
2
3
4
5
6
7
 
 
CREATE UNIQUE CLUSTERED INDEX [ix_gtm_crb_bgc30mn_datepoint] ON [gtm_crb_bgc30mn]
(
        [conso_id] ASC,
	[datepoint] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [ps_bgc_arch_datepoint]([datepoint])

[SQLpro]

NON et NON... soit votre table est organisée en index clustered soit votre table est partitionné, vous ne pouvez avoir les deux. Si vous ajouter la colonne "datepoint" dans votre clé ayant une colonne autoincrémentée "conso_id" alors cete dernière peut ne pas être unique...
De plus tout index partitionné doit avoir comme première colonne de la clé d'index le nom de la colonne de partitionnement...

Reprenons depuis le début...
1) Il faut créer une fonction de partitionnement à l'aide de points temporels, par exemple années :

Code :

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

1
2
3
CREATE PARTITION FUNCTION PF_DATE (DATETIME2(3))
AS RANGE RIGHT
FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01')

2) Créer autant d'espace de stockage que d’intervalle (si 3 "piquets" = > 4 intervalles :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
ALTER DATABASE CURRENT 
   ADD FILEGROUP FG_OLD;
GO
ALTER DATABASE CURRENT 
   ADD FILEGROUP FG_2022;
GO
ALTER DATABASE CURRENT 
   ADD FILEGROUP FG_2023;
GO
ALTER DATABASE CURRENT 
   ADD FILEGROUP FG_2024;
GO
 
ALTER DATABASE CURRENT 
   ADD FILE (NAME       = 'F_OLD',
             FILENAME   = 'D:\DATA\SQL\F_old.ndf',
             SIZE       = 10 GB,
             FILEGROWTH = 64 MB)
   TO FILEGROUP FG_OLD;
GO
ALTER DATABASE CURRENT 
   ADD FILE (NAME       = 'F_2022',
             FILENAME   = 'D:\DATA\SQL\F_2022.ndf',
             SIZE       = 10 GB,
             FILEGROWTH = 64 MB)
   TO FILEGROUP FG_2022;
GO
ALTER DATABASE CURRENT 
   ADD FILE (NAME       = 'F_2023',
             FILENAME   = 'D:\DATA\SQL\F_2023.ndf',
             SIZE       = 10 GB,
             FILEGROWTH = 64 MB)
   TO FILEGROUP FG_2023;
GO
ALTER DATABASE CURRENT 
   ADD FILE (NAME       = 'F_2024',
             FILENAME   = 'D:\DATA\SQL\F_2024.ndf',
             SIZE       = 10 GB,
             FILEGROWTH = 64 MB)
   TO FILEGROUP FG_2024;
GO

3) Créer votre schéma de partitionnement :

Code :

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

1
2
3
CREATE PARTITION SCHEME PS_DATE
   AS PARTITION PF_DATE
   TO (FG_OLD, FG_2022, FG_2023, FG_2024);

4) Maintenant créons la table :

Au passage le type DATETIME est fortement déconseillé ! Il faut utiliser du DATETIME2 !
Le VARCHAR1 est stupide ! Il occupe 2 octets si vide, 3 si plein !!!

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE gtm_crb_bgc30mn (
conso_id        bigint IDENTITY(1, 1) NOT NULL,
datepoint       datetime2(0) NOT NULL,
dateday         date NOT NULL,      --> si c'est le jour de la date "datepoint", c'est stupide.... Faire une colonne calculéé persistante !  
monthno         tinyint NOT NULL,   --> si c'est le mois de la date "datepoint", c'est stupide.... Faire une colonne calculéé persistante !
compteur_id     int NOT NULL,
type_pt_id      tinyint NOT NULL,
version         tinyint NOT NULL,   --> version étant un mot clé évitez le !
puissance       float NOT NULL,
dateversion     datetime2 NOT NULL,  
validation      char(1) NOT NULL,   --> validation étant un mot clé évitez le !
echeance        char(4) NOT NULL)
ON PF_DATE(datepoint); --> tabe partitionée

et sa clé primaire non partitionnée :

Code :

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

1
2
3
ALTER TABLE gtm_crb_bgc30mn 
   ADD CONSTRAINT pk_gtm_crb_bgc30mn 
       PRIMARY KEY NONCLUSTERED (conso_id ASC) ON [PRIMARY] --> clé primaire indépendante non partitionnée

5) Vous pouvez maintenant créer des index partitionnés :

Code :

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

1
2
CREATE INDEX X001 ON gtm_crb_bgc30mn (datepoint, conso_id) ON PF_DATE(datepoint);
CREATE INDEX X002 ON gtm_crb_bgc30mn (datepoint, conso_id) INCLUDE (type_pt_id) ON PF_DATE(datepoint, compteur_id) ;

6) ...Ou non partitionnés :

Code :

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

CREATE INDEX X003 ON gtm_crb_bgc30mn (dateday, monthno, dateversion) INCLUDE (conso_id, puissance) ON [PRIMARY];

Quand à savoir ce qui est plus performant (partitionnement vs index verticaux), c'est une question fonctionnelle. Sans savoir le contexte et ce que vous faite c'est comme demander si l'on doit prendre le bateau ou l'avion pour aller on ne sait ou... !

A +


PS : exemple fait de tête !

[Pfeffer]

Je suis sur une Instance Managée SQL dans Azure.

Et je vois ceci dans la msdn de Microsoft :

Le partitionnement n’est pas entièrement pris en charge dans Azure SQL Database. Étant donné que seul le PRIMARY groupe de fichiers est pris en charge dans Azure SQL Database, toutes les partitions doivent être placées sur le PRIMARY groupe de fichiers.

https://learn.microsoft.com/fr-fr/sq...ldb-mi-current

Du coup ça signifie quoi exactement ? Parce que je ne comprends pas l'intérêt de faire de la ventilation par partition si tout va dans PRIMARY, ou bien j'ai pas compris quelque chose ?

[Pfeffer]

Arf autant pour moi, dans la MSDN il parle bien de "Azure SQL Database".
Donc pas de soucis sur IM je peux faire de la partition sur plusieurs FILEGROUP.

[SQLpro]

Azure SQL c'est du pur cloud SQL Server sur Azure c'est une VM avec un SQL Server comme les "on premise"....

Le fait de tout mettre dans le même storage n'est pas forcément anti performant....

A +

[Pfeffer]

Alors j'ai partitionné par "Datepoint" parce que cela me parait pertinent, cela me garanti un équilibre en terme de lignes sur chaque partition.

Par contre j'ai 1 index aligné sur "Datepoint" et 5 index non cluster non alignés, notamment pour faire des GROUP BY.

Du coup dans certains scénarios je n'utiliserai pas l'index aligné.

Est ce que le fait de partitionner la table réduira le nombre de ligne à interroger par mes requêtes ? Et par ricochet améliorera les performances ?

J'espère que oui, parce que sinon je ne vois pas l'intérêt du partitionnement.

A+