Discussion :

[escartefigue]

Bonjour,

J'interviens chez un client dont une application annexe au coeur de métier utilise une base de données MS SQL server.
Cette application possède des tables relativement volumineuses, allant de quelques millions de lignes à plus d'un milliard pour celles que j'ai pu vérifier.
(je n'ai examiné qu'une dizaine de tables, celles décrites comme au coeur de l'application par le document à ma disposition).
Jusqu'ici rien de révolutionnaire.
Les temps de traitement sont considérablement longs (les batchs du WE débordent régulièrement sur la journée du lundi, voire jusqu'au mercredi )

A temps perdu, bien que je ne sois pas missionné pour ce sujet, j'ai donc entrouvert le capot du bousin pour voir ce que se passait la dessous.

Et là... c'est le drame !

Voici le constat :
La version SQL server est antédiluvienne : select @@version ==> Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64).

Le serveur dispose 14 giga de RAM

Aucune table ne possède de PK : select * from sys.indexes where is_primary_key=1 ==> vide.

Cohérence dans l'effort, aucune intégrité référentielle n'est trouvée : select * from sys.foreign_keys ==> vide.

Droit dans mes bottes, je m'enquiers de ce qu'il en est des index :

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
   select substring(TAB.name, 01, 25)            as TabName 
        , substring(IND.name, 01, 25)            as NdxName 
        , substring(IND.type_desc, 01, 25)       as Descr   
        , IND.is_unique       as UNQ                        
        , IND.is_primary_key  as PK                         
        , IXC.key_ordinal     as KeySeq                     
        , substring(COL.name, 01, 25)            as ColName 
        , substring(TYP.name, 01, 12)            as Coltype 
        , COL.max_length      as Lng                        
   from  sys.tables  TAB                                    
   inner join sys.indexes IND                               
      on IND.object_id = TAB.object_id                      
   inner join sys.index_columns IXC                         
      on IXC.object_id = IND.object_id                      
     and IXC.index_id  = IND.index_id                       
   inner join sys.columns COL                               
      on COL.object_id = IXC.object_id                      
     and COL.column_id = IXC.column_id                      
   inner join sys.types TYP                                 
      on TYP.user_type_id=COL.user_type_id                  
   where  TAB.name in  [...]                                     
   order by TAB.name                                        
          , IND.name                                        
          , IXC.key_ordinal

Le résultat est probant : tous les index, aucune exception, sont multicolonnes et utilisent à minima deux colonnes varchar (jusqu'à varchar(72) tant qu'à faire)

N'ayant plus rien à craindre, je me dis, mignonne, allons voir si la rose ou plutôt les tables sont obèses :

Code :

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

1
2
3
4
5
6
7
   select table_name as TNAME                                  
        , count(COLUMN_NAME)                                   
   from information_schema.columns                             
   where TABLE_CATALOG=[...]                          
     and TABLE_SCHEMA =[...]                                
     and TABLE_NAME in [...]                  
   group by TABLE_NAME

Bingo, on dépasse les 170 colonnes...

Bref, bilan tellement monstrueux que je me demande si j'ai loupé un truc ?

Bien évidemment, pas question de refondre tout ça, il s'agit d'un progiciel, facturé comme il se doit par son prestataire, mais auquel on ne touche pas.

Question à 595 francs (soit 15 francs+15 francs+15 francs) : pensez-vous que faute de mieux, une montée de version SQL server pourrait au moins apporter un petit quelque chose d'un point de vue performances.

[Invité]

Perso, même si c'est un logiciel sous licence, je ne me gène pas pour ajouter / désactiver des indexes suivant les besoins.
Il me semble que c'est une partie de maintenance qui va avec une bd.
Mettons que je ne sais pas si je suis vraiment dans le cadre légal mais face à ce genre de produit de m*rd*, on fait ce qu'on peut

[escartefigue]

Il y a des index multicolonnes très larges (au bas mot 40 octets) car basés sur des varchars
En l'absence d'identifiant technique type identity, impossible de faire un truc propre.

Du coup je ne vois que des actions à la marge telles que la RAM et la version SQL server

[mikedavem]

Bonjour,
Question à 595 francs (soit 15 francs+15 francs+15 francs) : pensez-vous que faute de mieux, une montée de version SQL server pourrait au moins apporter un petit quelque chose d'un point de vue performances.

Honnêtement, ca ne mange pas de pain d'essayer mais ne t'attend pas à des miracles non plus avec le contexte que tu as.

Néanmois quand tu parles de performances désastreuses tu peux toujours essayer d'identifier les points les plus évidents pour lesquels tu pourrais apporter des quick wins (le fameux 20% d'efforts pur 80% de résultats).
Les leviers les plus simples à activer sont effectivements l'ajout de ressource (mais peut-etre solution à court-terme) ou essayer de prototyper des index qui vont bien. Pour ce dernier tu pourras dire au client de faire remonter à l'éditeur tes recommandations.

Si pas de chance, il faudra aller plus profondemment dans l'analyse et les recommandations bien entendu.

Bon courage

++

[StringBuilder]

Bonsoir,

Ca me fait plaisir de ne pas être le seul à galérer avec des bases de données d'éditeurs merdiques !

Quelques interrogations :

SQL Server 2008 SP1 n'est plus maintenu depuis de nombreuses années
=> Qu'en est-il de la version du logiciel, dont la maintenance est facturée ? C'est la première fois que je vois un éditeur maintenir une application tournant sur un système (SGBD, OS, etc.) n'est plus maintenu ! N'auriez-vous pas quelques dizaines de versions de retard dans l'outil ? Quitte à migrer SQL Server, autant migrer tout le reste... à commencer par l'applicatif ! Vous n'êtes pas à l'abris d'un sursaut encéphalique des développeurs qui ont peut-être optimisés leur base de données ! (même s'il est probable qu'en fait ça ait empiré, mais restons optimiste)

Les index explicitement documentés ne doivent effectivement pas être touchés
=> Mais à mon sens, rien n'empêche un DBA d'en rajouter pour ses besoins, ou de modifier ceux qui ne sont pas documentés (et donc par extension, ne faisant pas partie du produit )

Outre l'ignominie de la modélisation, quels sont les réels problèmes ?
=> As-tu analysé les traitements qui prennent des dizaines d'heures à tourner ? Je suis prêt à parier que la mauvaise modélisation n'est au final pas pour grand chose à tes problèmes de performances. Je table sur une utilisation ultra abusive de curseurs et autres objets temporaires.

[escartefigue]

Bonsoir et merci pour vos réponses,

SQL Server 2008 SP1 n'est plus maintenu depuis de nombreuses années
=> Qu'en est-il de la version du logiciel, dont la maintenance est facturée ? C'est la première fois que je vois un éditeur maintenir une application tournant sur un système (SGBD, OS, etc.) n'est plus maintenu ! N'auriez-vous pas quelques dizaines de versions de retard dans l'outil ? Quitte à migrer SQL Server, autant migrer tout le reste... à commencer par l'applicatif ! Vous n'êtes pas à l'abris d'un sursaut encéphalique des développeurs qui ont peut-être optimisés leur base de données ! (même s'il est probable qu'en fait ça ait empiré, mais restons optimiste)

C'est possible en effet, je vais essayer de creuser cette piste, mais encore une fois, j'ai mené cette analyse entre la poire et le fromage, ça n'entre pas dans le cadre de ma mission, du coup je n'ai pas toutes les clefs
EDIT : pas tant que ça. Cf. https://docs.microsoft.com/fr-fr/lif...server&skip=10

Les index explicitement documentés ne doivent effectivement pas être touchés
=> Mais à mon sens, rien n'empêche un DBA d'en rajouter pour ses besoins, ou de modifier ceux qui ne sont pas documentés (et donc par extension, ne faisant pas partie du produit )

Certes, mais en l'absence d'identifiants concis, ces nouveau index porteront eux aussi sur des combinaisons de colonnes varchar

Néanmois quand tu parles de performances désastreuses tu peux toujours essayer d'identifier les points les plus évidents pour lesquels tu pourrais apporter des quick wins (le fameux 20% d'efforts pur 80% de résultats).
Les leviers les plus simples à activer sont effectivements l'ajout de ressource (mais peut-etre solution à court-terme) ou essayer de prototyper des index qui vont bien. Pour ce dernier tu pourras dire au client de faire remonter à l'éditeur tes recommandations.

Même motif, mêmes inquiétudes quant aux éventuels index de perf

Outre l'ignominie de la modélisation, quels sont les réels problèmes ?
=> As-tu analysé les traitements qui prennent des dizaines d'heures à tourner ? Je suis prêt à parier que la mauvaise modélisation n'est au final pas pour grand chose à tes problèmes de performances. Je table sur une utilisation ultra abusive de curseurs et autres objets temporaires.

Plus probablement le cumul des deux : modèle de données déplorable ET architecture des traitements du même tonneau, mais ça, c'est la boite noire logicielle de l'éditeur

[Invité]

SQL Server 2008 SP1 n'est plus maintenu depuis de nombreuses années
=> Qu'en est-il de la version du logiciel, dont la maintenance est facturée ? C'est la première fois que je vois un éditeur maintenir une application tournant sur un système (SGBD, OS, etc.) n'est plus maintenu ! N'auriez-vous pas quelques dizaines de versions de retard dans l'outil ? Quitte à migrer SQL Server, autant migrer tout le reste... à commencer par l'applicatif ! Vous n'êtes pas à l'abris d'un sursaut encéphalique des développeurs qui ont peut-être optimisés leur base de données ! (même s'il est probable qu'en fait ça ait empiré, mais restons optimiste)

J'ai déjà vu ça plusieurs fois pour des applications très spécifiques et qui ont visiblement une équipe technique très réduite... Et sans doute une attitude « ça marche, on ne touche plus à rien ! »
J'ai cependant déjà fait des migrations vers des versions plus à jour avec le compatibility level qui va bien et je n'ai pas eu de problème.
Pour les perfs, vu qu'on en profitait pour changer de serveurs avec des meilleurs caractéristiques, c'était un peu mieux.

J'ai également remarqué que c'est souvent les éditeurs qui sont très réticent à changer de version qui ont un modèle de merde et qui n'utilisent pas de fonctions avancées.
Ceux qui ont des bds plus évoluées comprennent pourquoi il faut être à jour et comment gérer ça.

Évidemment, ça reste de grosses généralisations.

[SQLpro]

je suis en train d'écrire un article pour dénoncer les malfaçons de certains éditeurs de logiciels.... En voici un extrait :

Intro

"
Un nombre non négligeable d’éditeurs informatiques, utilisent de manière totalement inadaptée les systèmes de gestion de bases de données relationnelles (SGBDR) ce qui a des conséquences dramatiques pour leurs clients (blocages, volumétrie anormalement levée, performances médiocre...).
Ces anomalies relèvent du défaut de vice caché et le client peut parfaitement et légalement obliger l’éditeur à corriger ces défauts afin de rétablir une situation de production normale.

Alertés régulièrement par leurs clients, certains éditeurs continuent à faire la sourde oreille à ces problématiques prégnantes, dans une éternelle fuite en avant dont les conséquences sont, à termes désastreuses, principalement pour les clients, mais aussi pour l’éditeur, qui, s’il ne se corrige pas, pourrait ne pas pouvoir franchir le gap du passage au cloud...
"

Un peu plus loin...

"
Une autre conséquence des malfaçons des bases de données, est la difficulté d’aller dans le cloud... En effet, ces malfaçons entrainent systématiquement des lectures et écritures de données surnuméraires dont la quantité peut vite s’avérer astronomique et nous expliquerons pourquoi. Or le modèle commercial de facturation des bases de données dans le cloud est en grande partie basé que la quantité d’E/S que produit le SGBDR !
"

Sans devoir remanier complétement le modèle il existe néanmoins certaines remèdes...
1) doter toutes les tables d'un PK autoincrémentée NONCLUSTERED
2) indexer toutes les tables sous forme d'un index clustered columnstore
3) activer la compression des données pour les tables et les index présentant beaucoup de NULL (au niveau ROW)
4) activer la compression des données pour les tables avec beaucoup de données redondnates (au niveau PAGE)
5) partitionner les tables à forte cardinalité par unité de temps
6) rajouter certaines vues indexées pour les calculs agrégés
7) ajouter de la RAM et un disque MVNe pour utiliser le "buffer poool extention"
8) effectuer les traitements lourd en mode de journalisation asynchrone (delayed durability)
...

A +

[escartefigue]

Bonsoir Fréderic,

Que veux tu dire par "table à forte cardinalité" ?

5) partitionner les tables à forte cardinalité par unité de temps

[SQLpro]

Bonsoir Fréderic,

Que veux tu dire par "table à forte cardinalité" ?

La cardinalité c'est le nombre de lignes... Disons qu'il faut réfléchir au partitionnement à partir du million de lignes.

A +

[escartefigue]

ok, j'utilise "cardinalité" dans le contexte MCD: 0,n 1,1 1,n...
et plutôt "effectif" ou "population" ou tout simplement "nombre de lignes" dans ce cas

[SQLpro]

Cardinalité est le terme exact consacré à la fois dans la littérature concernant les SGBDR, comme par les éditeurs de SGBDR.

Quelques extraits (en image) :




Franck Edgar Codd - The Relational Model for Database Management - version 2 (Addison Wesley page 20 - 1990)




Détails sous forme XML d'un plan de requête SQL Server

A +

[escartefigue]

L'annexe 1.3 mentionne bien que "cardinalité" est le terme de l'univers mathématique (d'où sont utilisation au niveau conceptuel) alors qu'on parle de nombre de lignes au niveau tabulaire

Bref peu importe.

Au sujet de la colonne index_id de la table sys.indexes, faut il comprendre que la valeur zéro signifie qu'il n'y a aucun index cluster pour cette table ?

[SQLpro]

Oui.... Dans SQL Server toute ce qui stocke logiquement de l'information se retrouve dans sys.indexes.

• index_id = 0 => table
• index_id = 1 => index clustered
• index_id >1 et <1000 => index non clustered
• index_id >=1000 => index particulier (XML, spatial, columnstore, hash).

A +

[escartefigue]

J'ai trouvé 10 tables dépourvue d'index cluster, dont deux relativement volumineuses (1 et 6 millions de lignes)
Décidément, plus je creuse, plus je trouve de la m#### !

Question subsidiaire : est-ce que l'ajout de RAM (je rappelle que je suis en 2008R2) nécessite un redémarrage du serveur ?
J'ai bien noté que le redémarrage serveur n'était pas conseillé (notamment ce post : https://www.developpez.net/forums/d2.../#post11655631)
Mais s'il n'y a pas le choix autant le faire au moment le plus opportun.

Si ce redémarrage était incontournable pour prendre en compte la nouvelle RAM, peut on recharger les plans d'exécution, les données du cache...?

[SQLpro]

...Question subsidiaire : est-ce que l'ajout de RAM (je rappelle que je suis en 2008R2) nécessite un redémarrage du serveur ?...

Si c'est juste pour dire à l'instance de prendre plus de RAM avec un

Code :

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

EXEC sp_configure

Non, cela ne nécessite aucun redémarrage, juste la prise en compte logique avec la commande RECONFIGURE.

Si c'est pour rajouter des barrettes de RAM sur un serveur physique, tout dépend de l'édition (Enterprise obligatoirement) et de la machine. Certaines machines (HP gamme ProLiant DL380 par exemple) supporte le rajout de composants hardware sans extinction de la machine.

A +

[escartefigue]

OK, merci

Et peut on retrouver la date du dernier démarrage du serveur SQL server (voire, mieux, les dernières dates)

[Invité]

Et peut on retrouver la date du dernier démarrage du serveur SQL server (voire, mieux, les dernières dates)

C'est dans les premières lignes des Errorlog.
Sauf si tu les recycles.

[SQLpro]

C'est dans les premières lignes des Errorlog.
Sauf si tu les recycles.

je t'ai mis un -1 !

Code :

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

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

A +

[Invité]

je t'ai mis un -1 !

Merci !
Mais pourquoi ?