[Partition] / [CLUSTERED INDEX] / [NON CLUSTERED INDEX] / [COLUMN STORE INDEX]

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:

---

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:

---

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])

---

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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 !

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

Et je vois ceci dans la msdn de Microsoft :

Citation:

---

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.

---

Citation:

---

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 ?

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.

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 +

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+

Citation:

---

Envoyé par 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 ?

---

Le partitionnement apporte plusieurs choses :

• le fait de filtrer plus rapidement si vous passez la date dans un prédicat (WHERE, Jointure ON, HAVING ou encore ORDER BY LaDate OFFSET / FETCH...)
• la ventilation des threads sur les partitions (ed. Enterprise)
• l'escalade de verrou au niveau partition avant table
• des statistiques plus fines donc des plans de requêtes plus précis et plus performant si vous activez les statistiques incrémentales...
• la possibilité d'opérations au niveau de la partition (par exemple TRUNCATE PARTITION...) et de la maintenance à ce niveau aussi
• vous pouvez compresser les partitions anciennes (ce qui améliore les performances en lecture)
• si vos partitions sont stockées dans storage différent, vous pouvez rendre certaines partitions (les anciennes par exemple) READ ONLY...
• ...

Citation:

---

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

---

Si la majorité de vos requêtes ne passent pas la date en filtrage, cela n'aura pas trop d'impact... Ni positif, ni négatif... Mais vu les avantages autres, c'est souvent intéressant pour de grosses tables !

A +