Discussion :

[vinch999]

Salut,

J'aimerais faire un exercice, je ne vois pas vraiment comment m'y prendre:

Soit une table de logs contenant une colonne de type datetime.

Donc :

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE dbo.my_log_table
(
    id int identity(1,1),
    log_date datetime2(3),
    log_text varchar(255)
);

On souhaite garder 3 mois (90 jours) de données, à tout moment afin de ne pas faire exploser le volume . Sachant que l'on a un volume conséquent (du genre 500 go par mois)
J'aimerais partitionner la table par mois (donc 3 fichiers)

Je dois supprimer les vieilles données jour par jour , on va dire , si log_date < GETDATE() - 90 jours, et splitter la table sur 3 fichiers:

Je ne vois pas comment répartir les données selon la date.

Avez vous des idées de comment gérer cela?

[Invité]

On dirait une question de la certification MS !

Regarde ici pour commencer : https://msdn.microsoft.com/en-us/library/ms188730.aspx

[vinch999]

Non je n'ai pas vu ça dans une certif, c'est un besoin pour un client

J'ai fini par trouver quelque chose :

J'ai créé 3 filegroups, 3 fichiers .

Ensuite :

Code :

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

1
2
3
4
5
6
7
CREATE PARTITION FUNCTION PF_PartitionThis (int) 
	AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12) ;
GO
CREATE PARTITION SCHEME PS_PartitionThis
    AS PARTITION PF_PartitionThis
    TO (PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3,PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3, PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3,PartitionThis_FG_1, PartitionThis_FG_2, PartitionThis_FG_3, PartitionThis_FG_1) ;
GO

J'ai rajouté une colonne mois dans la 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 FUNCTION dbo.get_month (@date_value datetime) RETURNS int
WITH SCHEMABINDING AS
BEGIN
 RETURN MONTH(@date_value)
END
 
CREATE TABLE dbo.my_log_table
(
    id int identity(1,1),
    log_date datetime,
    log_text varchar(255),
	mois as dbo.get_month(log_date) PERSISTED,
	CONSTRAINT pk_my_log_table PRIMARY KEY (mois,id)
)
ON PS_PartitionThis (mois);

J'ai fait quelques insert :

Code :

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

INSERT INTO  dbo.my_log_table(log_date,log_text) VALUES (GETDATE()-20,'mon_log')

Pour vérifier le résultat :

Code :

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

1
2
3
4
5
6
7
8
SELECT
p.partition_number AS PartitionNumber,
f.name AS PartitionFilegroup,
p.rows AS NumberOfRows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'my_log_table'

Vous en pensez quoi?

[SQLpro]

J'en pense que c'est de la connerie ! En effet, si on vous avait demandé une partition par jour vous auriez fait 31 partitions ?

Réfléchissez un peu, un mois n'est pas indépendant d'une année, tout comme un jour est relatif à un mois et une année !

En sus comme vous avez des dates il faut mettre des date dans votre fonction de partitionnement et il faudra créer régulièrement de nouvelles entrées à chaque fin de mois, ceci pouvant être fait automatiquement par une tâche planifiée dans l'Agent SQL.

A +

[vinch999]

Sachant que j'aurai au max 3 mois de données (je dois supprimer chaque jour les données < j-90), quel est l'intérêt de faire comme vous dites?
En faisant comme je propose, je n'ai pas besoin de recréer de nouvelles entrées dans la fonction de partitionnement.

Ma méthode serait mauvaise d'un point de vue performance? (suppression des vieilles données, refresh des index,etc.) ?


Disons que j'ai un disque de 500 GB.
Par mois , 150 GB de data.
Comment feriez-vous? Combien de files?

[SQLpro]

Déjà le fait d'avoir :

Code :

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

mois as dbo.get_month(log_date) PERSISTED

est hautement stupide alors que :

Code :

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

MOIS MONTH(log_date) PERSISTED

suffit et utilise une fonction intégrée, bien plus rapide qu'une UDF ! Et puis quel intérêt de réinventer la roue ? Masochisme ou stupidité ???

En sus un partitionnement par DATE vous évite cette redondance qui augmente la volumétrie de votre table; Je suppose que si vous partitionnez c'est pour des raisons de volume ? Donc est-ce intelligent de rajouter du volume à votre table ?

Enfin, pour des raisons de performances liées à d'hypothétique requêtes, l'intérêt de partitionner est que vos utilisateurs qui utilise des requêtes puissent n'activer que certaines partitions et non pas toutes ! Or en choisissant un critère de partitionnement qui n'est jamais exprimé dans aucune requête l'optimiseur ne pourra pas n'exciter qu'une seule partition !

Bref, vous avez tout faux !

A +

[vinch999]

En effet , le calcul du mois via une fonction est débile, j'ai fait un copier coller d'un site en testant sans corriger.

D'un point de vue redondance ça me couterait 1 octet à la ligne en tinyint.... Soit 1,86 GB pour 2 milliards de lignes , pas de quoi en chier une pendule.

Soit, je vais réétudier la question d'une feuille blanche(je débute en partitioning)

[SQLpro]

Soit, je vais réétudier la question d'une feuille blanche(je débute en partitioning)

Oui, mais les performances seront toujours aussi pourries voir plus encore !
Or je suppose que vous voulez partitionner pour accélérer les recherches....
Votre méthode va les complexifier et donc faire exactement l'inverse du but recherché....

Et vous n'êtes pas au bout de vos surprises... Quid de la clef primaire, quid de l'index clustered...

Un partitionnement cela s'étudier sur une maquette ayant le volume réel de la base, avec les requêtes réelles de production...

A +