Discussion :

[DeWaRs]

Bonjour à tous,

Je fais face a un problème que je n'arrive pas a comprendre...
J'ai trois machine tournant sous Windows 2012 R2 (16CPU / 32Go de RAM) configuré au niveau systeme exactement pareil (Configuration déployée en utilisant Opscode Chef)
Sur chaque machine, j'ai installé une version de SQL Server :
- SQL Server 2014 R2 Enterprise (seul SQL Engine et IS sont installés)
- SQL Server 2012 R2 Enterprise (seul SQL Engine et IS sont installés)
- SQL Server 2008 R2 Enterprise (seul SQL Engine et IS sont installés)

Les DATA / Transaction / Backup et pagefile sont sur des disques différents.

Sur chacun de ses servers, j'ai créer une database et importer une table contenant 26214400 lignes depuis un autre server SQL "de production"
J'effectue ensuite une requête SQL qui créée une nouvelle table comme ceci (Cette requête m'a été fournis par un developper)

https://gist.github.com/DeWaRs1206/0791c56eca8c4114bfa1 (je dois la mettre sur github car elle est trop longue pour ce forum..)

Sur mon server SQL 2014 R2, cette requête prend 3:30min environs, alors que sur 2008 et 2012, elle prend environ 1h !!
Je n'ai aucune idée d'ou vient le problème et de comment le résoudre. La seule différence que je vois entre les 3 serveurs est que lorsque je lance un "Display querry estimation time", sur 2014 il fait du parallélisme alors que sur 2012 et 2008 il ne le fait pas.

Je me doute bien que personne, avec ces informations, ne va pouvoir me trouver une solution, mais si l'un de vous a quelques pistes, je suis preneur !

Merci d'avance.

DeWaRs

[escartefigue]

Marche pas l'url du script

[DeWaRs]

Marche pas l'url du script

Etrange. Celle-ci : https://gist.githubusercontent.com/D...6/sqlquery.sql ?

[escartefigue]

Ca marche, mais vla la requête de cow-boy
Par contre, ce n'est pas la question, mais je ne comprends pas ce que vous attendez comme résultat : vous voulez charger une host variable avec une requete sans filtre et il n'y a pas de clause de limitation de la recherche

[DeWaRs]

Hello,

Malheureusement, je ne vais pas vraiment pouvoir répondre a cette question. Ce que je sais, c'est que cette requête est utilisée pour des tests (pas de perf ) pour le développement d'une application de mon entreprise.
Cette requête créé une nouvelle table avec un certain contenu, et cette table est ensuite utilisée pour d'autre tests.
Comme vous l'avez souligné, l'idée de ce thread n'est pas d'optimiser cette requête, mais plutôt de comprendre pourquoi il y a une telle différence de performance entre les différentes versions de SQL.

Cordialement

DeWaRs

[elsuket]

Bonjour,

Effectivement ces différences sont assez surprenantes, mais il faut noter que l'optimiseur en SQL Server 2014 est nouveau; comme le passage au niveau le plus haut de compatibilité ne se fait pas automatiquement, l'avez-vous fait pour la base de données concernée ?
Avez-vous aussi mis à jour toutes les statistiques à l'aide d'UPDATE STATISTICS ?
Avez-vous configuré la quantité de mémoire octroyée à SQL Server (option max server memory) ?
Notez-vous des différences importantes entre les plans d'exécution des mêmes requêtes sur les 3 instances ?

Les DATA / Transaction / Backup et pagefile sont sur des disques différents.

Pouvez-vous nous dire si les trois serveurs sont distincts et physiques, ou virtualisés sur le même serveur hôte ?
Quand vous parlez de disques différents, s'agit-il de LUN distincts ?

@++

[julien94320]

Bonjour,

Comme évoqué juste avant il faut noter imperativement que l'optimiseur à changer entre la version 2012 et 2014 notament le cardinality estimator.

Il est a noter pour votre information que les versions SQL SERVER 2012 R2 et SQL Server 2014 R2 n'existent pas ce sont éventuellement des Services pack que vous pouvez avoir, la version Release 2 éxiste que pour SQL Server 2008.

Dans votre cas je me pencherai sévèrement sur la mise a jour des statistiques de la base en full scan et je relancerai la requete pour voir le comportement ...

Pour ce qui est de la configuration si nous sommes sur 3 machines physiques identique avec des LUN séparé et un SAN non mutualisé derrière je ne vois que très peu d'hypothèse toutefois on va quand même essayer de creuser ...

Comme évoqué au dessus :

Comment avez vous paramétrer votre parrallélisme ?
Comment avez vous allouer la mémoire à chaque instance ?
Etes vous sur une machine physique ?
Comment sont configurer les LUN's ?
Quel est l'architecture de votre SAN ?

Mais je pense honnetement que le probleme vient d'une mauvaise estimation de plan lié à de mauvaises stats

[DeWaRs]

Merci pour vos questions.

Effectivement ces différences sont assez surprenantes, mais il faut noter que l'optimiseur en SQL Server 2014 est nouveau; comme le passage au niveau le plus haut de compatibilité ne se fait pas automatiquement, l'avez-vous fait pour la base de données concernée ?

Pour la creation de la base SQL, j'ai :

Code :

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

1
2
 ALTER DATABASE [MADATABASE] SET COMPATIBILITY_LEVEL = 100
  GO

Pour 2008 : 100
Pour 2012 : 110
Pour 2014 : 120

Avez-vous aussi mis à jour toutes les statistiques à l'aide d'UPDATE STATISTICS ?

Non, je n'ai jamais utilisé cela...

Avez-vous configuré la quantité de mémoire octroyée à SQL Server (option max server memory) ?

J'ai laissé ces valeurs par default pour toutes les installations je pense que SQL Server utilise le maximum, non ?

Notez-vous des différences importantes entre les plans d'exécution des mêmes requêtes sur les 3 instances ?

Le temps d'execution varie entre 00:00:00.8578735 et 00:00:00.9670574 je pense donc que la réponse est non

Pouvez-vous nous dire si les trois serveurs sont distincts et physiques, ou virtualisés sur le même serveur hôte ?
Quand vous parlez de disques différents, s'agit-il de LUN distincts ?

Les 3 instances sont créés sur le cloud interne de ma société, hébergé sur un ESXI dont il est difficile d'avoir des infos. Cependant, j'ai recréé cette environment 3 fois sur 3 datacenters différents et les performances sont toujours les meme.


Cordialement

DeWaRs

[julien94320]

Par défaut SQL Server utilise toute la mémoire disponible ce qui n'est pas bon car il faut en laisser pour l'OS ...

Dans votre cas il faudrait regler le max mémory a 28 go pour laisser 4 go a l'os ...

Pour le parrallélisme pareille il faut le regler en fonction du nombre de coeur physique et par rapport au nombre de disques physique disponible derrière ...

Pour moi la grosse différence de perf vient de l'optimiseur du a des stats erronées ...

Pour info il faut lancer 3 fois votre requete pour etre sur de la pertinence. (le temps d'avoir un cache correcte)

[escartefigue]

Il n'y a ni clause where, ni jointure, ni distinct, ni order by, ni group by... et ce sont les mêmes données sur les 3 serveurs
Dans ces conditions, la requête devrait effectuer un tablescan (sans s'occuper des index donc) et les stats n'auront pas d'importance

Du coup sauf config hardware différente, il reste mais à la marge, une possibilité que les conversions de format (beaucoup de cast dans la requête) aient été aussi optimisées avec la version 2014

[DeWaRs]

Merci pour les details, mes réponse si-dessous.

Il est a noter pour votre information que les versions SQL SERVER 2012 R2 et SQL Server 2014 R2 n'existent pas ce sont éventuellement des Services pack que vous pouvez avoir, la version Release 2 éxiste que pour SQL Server 2008.

Correct.
Les versions "réelles" :
2008 : 10.50.16001
2012 : 11.0.5058.0
2014 : 12.0.2000.8

Dans votre cas je me pencherai sévèrement sur la mise a jour des statistiques de la base en full scan et je relancerai la requête pour voir le comportement ...

Je viens de lancer

Code :

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

1
2
3
4
5
USE MADATABASE;
GO
UPDATE STATISTICS  MATABLE.TEST
    WITH FULLSCAN, NORECOMPUTE;
GO

Est-ce suffisant ? (Désolé je ne suis pas DBA :s ) L'execution prend moins d'une seconde, cela me parait peu !

Comment avez vous paramétrer votre parrallélisme ?
Comment avez vous allouer la mémoire à chaque instance ?
Etes vous sur une machine physique ?
Comment sont configurer les LUN's ?
Quel est l'architecture de votre SAN ?

Le parrallélisme est configuré de maniere identique sur tous les serveurs :
Cost Threshold for Parallelism : 5
Locks : 0
Max Degree of Parallelism : 0
Query Wait : -1

Pour la configuration de la mémoire, elle est la meme pour les 3 serveurs :
Minimum server memory : 0
Maximum server memory : 2147483647
Index creation memory : 0
Minimum memory pear query : 1024

Les machines sont toutes les trois virtuelles, avec les meme tailles de disque / mémoire / CPU
Concernant les LUN's et SAN, je n'ai malheureusement pas la réponse, mais comme tout fonctionne "correctement" avec 2014, je suis plus que septique sur le fait que cela puisse venir de l'infrastructure.

Mais je pense honnetement que le probleme vient d'une mauvaise estimation de plan lié à de mauvaises stats

[julien94320]

j'avais pas vu la dernière réponse ...

Pour l'update stat c'est bon.

Il faudra regler le parrallélisme plus tard en fonction de l'utilisation souhaitez mais aussi et surtout en fonction des conditions physiques ( ex : si 2 processeurs : il faut répartir tempdb en 2 et repartir sur 2 disques / Repartir les données sur 2 disques et mettre le niveau de parralellisme a 2 )


Pour la mémoire il faut modifier la valeur maximum a 28 Go si vous avez 32 go ... Sinon SQL Server peut geler la machine car il sera prioritaire sur l'OS !

Et pour la requete conso sur le cast, je ne vois pas ce qui a été modifié entre la version 2012 et 2014 a part des évol au niveau du cardinality estimator ...

[DeWaRs]

A tester quand même les stats ... ça coute pas chère

Que voulez vous dire ?

[julien94320]

Que voulez vous dire ?

Que la mise a jour des stats n'est pas une opération compliqué et elle permet de s'assurer que le probleme ne vient pas de la ... ce qui vient d'etre fait visiblement.

[escartefigue]

Il n'y a ni clause where, ni jointure, ni distinct, ni order by, ni group by... et ce sont les mêmes données sur les 3 serveurs
Dans ces conditions, la requête devrait effectuer un tablescan (sans s'occuper des index donc) et les stats n'auront pas d'importance
Du coup sauf config hardware différente, il reste mais à la marge, une possibilité que les conversions de format (beaucoup de cast dans la requête) aient été aussi optimisées avec la version 2014

Je pense que julien faisait référence à mon post ci-dessus

D'ailleurs je me demande si la requete que vous avez mise dans le lien 2 ne serait pas tronquée, car ca me semble très curieux cette absence de filtrage et de jointure. Comme le lien 1 ne fonctionne pas pour moi, je ne peux pas aller y vérifier

[DeWaRs]

Du coup sauf config hardware différente, il reste mais à la marge, une possibilité que les conversions de format (beaucoup de cast dans la requête) aient été aussi optimisées avec la version 2014

Cette possibilité me semble tout a fait légitime ! Je vais me renseigner avec les DEVs .

[DeWaRs]

Je pense que julien faisait référence à mon post ci-dessus

D'ailleurs je me demande si la requete que vous avez mise dans le lien 2 ne serait pas tronquée, car ca me semble très curieux cette absence de filtrage et de jointure, surtout pour charger une variable. Comme le lien 1 ne fonctionne pas pour moi, je ne peux pas aller y vérifier

Je confirme que la requete n'est pas tronqué, dans les deux liens et "chez moi", la requete se termine par

Code :

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

1
2
3
    END) AS FLOAT) ) AS "rr_TGT"
  FROM
    MATABLE.TEST) T

[julien94320]

A la fin de la requete vous pouvez essayer d'ajouter le hint suivant pour faire un essai (après le FROM):

Code :

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

OPTION (RECOMPILE)

Merci

Je pense que julien faisait référence à mon post ci-dessus

Yes

[aieeeuuuuu]

Bonjour,

Je doute qu'il s'agisse d'un problème de stats : la requete ne comporte ni jointure ni filtre.
Elle porte sur une seule table et traite visiblement la totalité des lignes.

Il faut trouver pourquoi le parallélisme n'est pas utilisé pour les deux autres serveurs, car vu la requête, ce serait très certainement gagnant.
Car vous avez 16 cpu, et sans parallélisme, ça mets environ 16 fois plus de temps (16 X 3,5 min = 1h).

S'agit-il pour tous les serveur d'édition entreprise, ou est-ce que certains sont en édition express ?

[SQLpro]

2 autres facteurs peuvent influencer énormément (outre l'optimiseur) :
1) le dimensionnement de vos espaces de stockage (base de prod et tempdb) sachant que l'algorithme de gestion des VLF du journal des transaction a été remanié pour la version 2014
2) le ballooning de la RAM des VM...

Pour effectuer des test isoforme, il faut des machines physiques configurées de manière strictement identiques.

Sinon, procéder à au moins 10 tests successifs et éliminer les deux valeurs extrêmes.

A +