Problème de perf MDX entre 2005 et 2012

**Jinroh77** · 11/09/2013, 19h14

Bonjour à tous,
Je rencontre un très important écart de performance sur une requête MDX entre son execution sur une instance 2005 (9.00.5254) et 2012 (11.0.3000) : de 4s à 1min15.

Le serveur 2005 est physique, 40Go de RAM, 24 core sur 2*X5670.
Le serveur 2012 est virtuel, 8Go de RAM, 8 core, basés également sur du X5670.
La différence de config est très importante, mais pendant la requête seule 1 core est utilisé et la consommation de RAM n'augmente que très peu.
Je ne miserai donc pas énormément sur un problème d'infra.

La requête est malheureusement générée automatiquement à travers un outil sur lequel je n'ai pas la main

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
WITH 
  MEMBER [Date Création].[Jour].Total AS 
    Aggregate
    (
      (
        {[Date Création].[Jour].[All]}
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Qualification].[Activité].Total AS 
    Aggregate
    (
      (
        {[Qualification].[Activité].[Courrier / Fax / Mail]}
       ,[Date Création].[Jour].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Demande].[Statut].Total AS 
    Aggregate
    (
      (
        {[Demande].[Statut].[02 - A attribuer]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Ressource Rédacteur].[Ressource].Total AS 
    Aggregate
    (
      (
        {[Ressource Rédacteur].[Ressource].[Non Affecté]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Date Evènement].[Jour].Total AS 
    Aggregate
    (
      (
        {[Date Evènement].[Jour].[All]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
      )
    ) 
SELECT 
  NON EMPTY 
    {
      {[Date Création].[Jour].Children} * {[Measures].[Nb Demandes]}
     ,
      {[Date Création].[Jour].Total} * {[Measures].[Nb Demandes]}
    } ON COLUMNS
 ,NON EMPTY 
    {
        {[Qualification].[Activité].[Courrier / Fax / Mail]}*
        {[Demande].[Statut].[02 - A attribuer]}*
        {[Ressource Rédacteur].[Ressource].[Non Affecté]}*
        {[Date Evènement].[Jour].Children}
     ,
        {[Qualification].[Activité].Total}*
        {[Demande].[Statut].Total}*
        {[Ressource Rédacteur].[Ressource].Total}*
        {[Date Evènement].[Jour].Total}
    } ON ROWS
FROM [Demande]
WHERE 
  {
    [Activité Process Rédacteur].[Activité].[Courrier / Fax / Mail]
  }
CELL PROPERTIES 
  VALUE
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FORE_COLOR
 ,BACK_COLOR
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

==> 1min15

En supprimant simplement les lignes de total et en utilisant un ".Members" sur les dates, on retombe à 4s soit à peu près le temps du serveur 2005.

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
WITH 
  MEMBER [Date Création].[Jour].Total AS 
    Aggregate
    (
      (
        {[Date Création].[Jour].[All]}
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Qualification].[Activité].Total AS 
    Aggregate
    (
      (
        {[Qualification].[Activité].[Courrier / Fax / Mail]}
       ,[Date Création].[Jour].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Demande].[Statut].Total AS 
    Aggregate
    (
      (
        {[Demande].[Statut].[02 - A attribuer]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Ressource Rédacteur].[Ressource].Total AS 
    Aggregate
    (
      (
        {[Ressource Rédacteur].[Ressource].[Non Affecté]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Date Evènement].[Jour].CurrentMember
      )
    ) 
  MEMBER [Date Evènement].[Jour].Total AS 
    Aggregate
    (
      (
        {[Date Evènement].[Jour].[All]}
       ,[Date Création].[Jour].CurrentMember
       ,[Qualification].[Activité].CurrentMember
       ,[Demande].[Statut].CurrentMember
       ,[Ressource Rédacteur].[Ressource].CurrentMember
      )
    ) 
SELECT 
  NON EMPTY 
    {
      {[Date Création].[Jour].Children} * {[Measures].[Nb Demandes]}
     ,
      {[Date Création].[Jour].Total} * {[Measures].[Nb Demandes]}
    } ON COLUMNS
 ,NON EMPTY 
    {
        {[Qualification].[Activité].[Courrier / Fax / Mail]}*
        {[Demande].[Statut].[02 - A attribuer]}*
        {[Ressource Rédacteur].[Ressource].[Non Affecté]}*
        {[Date Evènement].[Jour].Members}
//     ,
//        {[Qualification].[Activité].Total}*
//        {[Demande].[Statut].Total}*
//        {[Ressource Rédacteur].[Ressource].Total}*
//        {[Date Evènement].[Jour].Total}
    } ON ROWS
FROM [Demande]
WHERE 
  {
    [Activité Process Rédacteur].[Activité].[Courrier / Fax / Mail]
  }
CELL PROPERTIES 
  VALUE
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FORE_COLOR
 ,BACK_COLOR
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;

Le moteur 2012 peut-il agir autant sur la dégradation de la performance d'une mauvaise requête ??

P.S. : La database SSAS a été restaurée ou redéployée depuis une solution migrée et le résultat est identique.
P.S.2 : Je n'exclu pas totalement le problème d'infra, mais la différence me parait tout de même très importante.

**LapinClair** · 12/09/2013, 09h27

Bjr,
Avez-vous pensé à une analyse de la requête via MDXStudio afin de vérifier le comportement des moteurs.

**FrancoisJehl** · 12/09/2013, 10h47

Alex le principal écart de perf c'est entre 2005 et 2008 ou je ne peux même pas te lister le nombre hallucinant d'optim du MDX (amélioration de l'autoexists et block computation entre autres). Difficile dans ton cas d'identifier le problème sans passer par de l'analyse de compteurs de perfs dans MDX Studio ou perfmon.

**Jinroh77** · 12/09/2013, 11h32

On la passe dans MDXStudio et on vérifie la différence de compteurs. Je vous transmets cela ensuite.

C'est clair qu'il y a eu énormement d'optimisations entre les versions, mais de la à croire qu'ells dégradent les mauvaises requêtes, c'est moins cool

**Jinroh77** · 12/09/2013, 12h39

Très bizarrement, MDXStudio ne retourne aucune stat pour l'éxecution ur le sevreur 2012...

Le nouveau serveur SSAS 2012
Perfmon :
Time : 1 min 16 sec 991 ms
Calc covers : 0
Cells calculated : 0
Sonar subcubes : 0
NON EMPTYs : 0
Autoexists : 0
EXISTINGs : 0
SE queries : 0
Flat cache insert : 0
Cache hits : 0
Cache misses : 0
Cache inserts : 0
Cache lookups : 0
Memory Usage KB : 0

L'ancien serveur SSAS 2005
Perfmon :
Time : 2 sec 723 ms
Calc covers : 44
Cells calculated : 82216
Sonar subcubes : 130
NON EMPTYs : 1
Autoexists : 1
EXISTINGs : 4
SE queries : 15
Flat cache insert : 41643
Cache hits : 24
Cache misses : 24
Cache inserts : 8
Cache lookups : 48
Memory Usage KB : 40152

Un serveur 2005, virtualisé.
Time : 3 sec 797 ms
Calc covers : 44
Cells calculated : 82216
Sonar subcubes : 130
NON EMPTYs : 1
Autoexists : 1
EXISTINGs : 4
SE queries : 15
Flat cache insert : 41643
Cache hits : 15
Cache misses : 42
Cache inserts : 17
Cache lookups : 57
Memory Usage KB : 17160

**Jinroh77** · 12/09/2013, 14h31

En complèment voici la trace de la requête avec le profiler.

**LapinClair** · 12/09/2013, 14h32

pour vos compteurs à 0, l'avez vous executer en Administrateur ?
Sur le fil de Marco Russo il donne cette piste.

http://sqlblog.com/blogs/marco_russo...-mdx-ssas.aspx

**Jinroh77** · 12/09/2013, 14h50

Les compteurs sont à 0 uniquement sur le cube 2012, non sur les autres. Pourtant MDXStudio est ouvert depuis mon poste, quelque soit le serveur interrogé.

**Jinroh77** · 12/09/2013, 15h18

... et le perfmon avec tous les compteurs SSAS.

**FrancoisJehl** · 12/09/2013, 15h30

Ouais t'as en gros 40K cells calculated et 40K sonar subcube au storage engine. La block computation ne marche pas en 2012 si je compte bien.

**Jinroh77** · 12/09/2013, 16h03

"ne marche pas en 2012", ça veut dire que c'est ce que provoque la requête et le moteur 2005 acceptait bien ce genre de chose alors que le 2012 ne laisse plus passer ?
C'est pas très très gentil ça...

**FrancoisJehl** · 12/09/2013, 17h12

Je ne faisais que constater, je n'ai pas l'explication. Mais un subcube/calc cover par cell c'est du block mode défoncé. Alors les pistes je ne sais pas. J'irais chercher vers une armée d'autoexists dus au crossjoin de tes membres calculés qui contiennent des currentmember avec des sets sur les mêmes attributs que tes currentmember.
En fait de vue comme ça j'aurais pas eu tendance à penser que 2005 peut gérer ça facile.

**Jinroh77** · 12/09/2013, 18h04

J'aurai effectivement plus parié sur le moteur 2012 que le 2005...

**Jinroh77** · 12/09/2013, 18h16

A tout hasard, j'ai branché une OlapQueryLog et je me retrouve avec 5-6 requêtes qui tournent en 13-16 ms...

**FrancoisJehl** · 13/09/2013, 11h10

Bah le truc, c'est que de ce que j'ai déjà vu, 2005 embarquait une armée de hacks non documentés pour régler les problèmes de block mode et de calculation pass value. En 2008 on avait eu plein de soucis de migration chez des clients qui avaient pris ces hacks pour un comportement lambda et se retrouvaient avec du 2008 qui performait moins bien que du 2005. Moralité il est possible qu'une query optimisée pour du 2005 (et ta query est quand même assez bizarre) soit moisie en version d'après.

**Jinroh77** · 16/09/2013, 10h28

J'ai l'impression que je me retrouve avec tes mêmes problèmes de migration de 2005 à 2008.
Je pense que l'on aura rien du côté de Microsoft ?
Je vais devoir aller du côté de l'éditeur pour lui faire retravailler ses requêtes MDX...

Tu vois une autre solution ?
Vous aviez fait autre chose que de ré-écrire les requêtes ?

**FrancoisJehl** · 17/09/2013, 13h50

On avait réécrit du scripting et chez certains changé de tool client (à l'époque Procla 6 avait la facheuse manie de balancer du MDX pourri).

Problème de perf MDX entre 2005 et 2012

SSAS

Discussions similaires

Partager

Partager