Discussion :

[camboui]

Bonjour,

J'ai une série de scripts sql qui tourne sous SQL Server 2008R2 via des batchs en utilisant l'utilitaire osql.exe.
Ça fonctionne comme cela depuis des années (au début c'était même sous SQL Server 2000).
La dernière révision des scripts date de 2011.

Maintenant j'essaye de migrer vers SQL Server 2014 et j'utilise sqlcmd.exe (on me dit qu'il est préférable à osql.exe).
Désormais certains scripts ont des performances catastrophiques.

J'ai essayé d'isoler les requêtes qui poseraient problème, et il semblerait que ce soit l'usage de curseurs qui ne plaisent pas à SQL Server 2014.
Par exemple, j'ai un curseur sur une requête qui renvoi 466000 rows à partir desquels j'en insère 2400000 autres dans une table.
Sous 2008R2 cette requête tourne en 15 minutes environ, soit 2700 insertions par seconde environ.
Sous 2014 cette même requête tourne en une douzaine d'heures , soit 55 insertions par seconde...
C'est incompréhensible.

J'ai bien sûr vérifié les paramétrages entre les deux instances 2008R2 et 2014 (qui tourne sur le même serveur, Windows Server 2008R2).
La mémoire est limitée à 8GB pour les deux (32GB physique sur la machine), la DB temp est déplacée vers le disque D:, le reste est par défaut.
La DB venant de 2008R8 est un backup restauré sous 2014 dont le niveau de compatibilité a été rehaussé à 2014. Modèle de recouvrement simple.
La DB est donc identique à celle d'origine en contenu. Il n'y a pas de store proc.

Une peu d'aide pour essayer de régler ce soucis serait bienvenue.
Merci.

[aieeeuuuuu]

Bonjour,

La première chose à faire après la restauration d'une 2008R2 sur une 2014 est de refaire une calcul complet des statistiques.

Est-ce que vous observez le même comportement si vous remettez le mode de compatibilité en 2008R2 ?

Mais, plutôt que de perdre du temps à chercher pourquoi c'est plus long, personnellement, j'utiliserais ce temps pour réécrire le script sans curseur et gagner sur tous les plans...

[camboui]

Oui, mais je ne suis pas seul sur mon coin de table de cuisine.
Une modification requiert un processus lourd de validation, test, etc.
Même une petite modification, simple, facile, du genre "je-suis-sûr-que-c'est-bon", "on-peut-en-production-sans-risque", "je-te-jure-que-c'est-bon"...

Ben non

Ma mission c'est: "tu migres de 2008R2 à 2014, sans toucher à rien d'autre"
Et quand le manager décide, le sous-fifre s'exécute...

[camboui]

Voici un autre exemple, très simple, de query hyper lent:

Code :

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

1
2
3
4
5
INSERT INTO @peoNoPrimary (PeopleID)
SELECT DISTINCT PeopleID FROM DSpos WHERE PeopleID NOT IN
(
SELECT DISTINCT PeopleID FROM DSpos WHERE isPrimary=1
)

Très rapide sous 2008R2 (quelques dizaines de secondes, et il n'y a pas d'index sur PeopleID pour la table DSpos qui contient entre 1 et 2 millions de records).
Elle est dans les choux sous 2014...

[aieeeuuuuu]

Ma mission c'est: "tu migres de 2008R2 à 2014, sans toucher à rien d'autre"

Du coup, bonne nouvelle, votre travail est terminé !

Si ça sous entendait que les perf devaient être maintenues, alors il va falloir toucher à quelque chose d'autres.
Le moteur à changé entre les deux versions, et il est donc normal que les plan d’exécution ne soient plus les mêmes.


Il y a quand même quelque chose que je ne comprend pas. Vous parlez d'un processus lourd de mise en production, mais à coté de ça, vous avez fait un migration de version de SQL Server en production sans faire de tests au préalable ? n'avez vous pas un serveur de test ?

[aieeeuuuuu]

Voici un autre exemple, très simple, de query hyper lent:

Code :

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

1
2
3
4
5
INSERT INTO @peoNoPrimary (PeopleID)
SELECT DISTINCT PeopleID FROM DSpos WHERE PeopleID NOT IN
(
SELECT DISTINCT PeopleID FROM DSpos WHERE isPrimary=1
)

Très rapide sous 2008R2 (quelques dizaines de secondes, et il n'y a pas d'index sur PeopleID pour la table DSpos qui contient entre 1 et 2 millions de records).
Elle est dans les choux sous 2014...

elle doit pouvoir se réecrire ainsi :

Code :

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

1
2
3
4
INSERT INTO @peoNoPrimary (PeopleID)
SELECT PeopleID FROM DSpos 
EXCEPT
SELECT PeopleID FROM DSpos WHERE isPrimary=1

ou bien

Code :

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

1
2
3
4
5
INSERT INTO @peoNoPrimary (PeopleID)
SELECT PeopleID 
FROM DSpos 
GROUP BY PeopleID
HAVING MAX( CASE WHEN isPrimary=1 THEN 1 ELSE 0 END) = 0

voire mieux si on connait le modèle

[julien94320]

Bonjour,

Avez vous les plans d'execution pour votre requete en 2008 R2 et en 2014 ?

Sauf erreur de ma part, je n'ai pas vu de réponses de votre part concernant les points suivants évoqués précédemment ?

• Avez vous mis a jour les statistiques en full sur votre nouvelle instance
• Avez vous essayer de changer le compatibility level ?
• Avez vous vérifier qu'un plan guide n'était pas utilisé dans votre ancienne version ?

[camboui]

La première chose à faire après la restauration d'une 2008R2 sur une 2014 est de refaire une calcul complet des statistiques.

Good point
Ça ne change pas grand chose en pratique.

Est-ce que vous observez le même comportement si vous remettez le mode de compatibilité en 2008R2 ?

Non, en effet Le comportement redevient comme avant.
Certaines requêtes ne sont visiblement plus optimisées en compatibilité 2014 alors qu'elles le sont toujours sous compatibilité 2008R2.
Quelle conclusion en tirer, à part que MS a retiré des (bonnes) choses de 2008R2 ?

Mais, plutôt que de perdre du temps à chercher pourquoi c'est plus long, personnellement, j'utiliserais ce temps pour réécrire le script sans curseur et gagner sur tous les plans...

Finalement, et après plusieurs essais, il faudra bien passer par la relecture d'un bon nombre de scripts, et donc du temps et une phase de validation. Ce sera ça ou rien, je suis blindé

[camboui]

Du coup, bonne nouvelle, votre travail est terminé !

Si ça sous entendait que les perf devaient être maintenues, alors il va falloir toucher à quelque chose d'autres.
Le moteur à changé entre les deux versions, et il est donc normal que les plan d’exécution ne soient plus les mêmes.


Il y a quand même quelque chose que je ne comprend pas. Vous parlez d'un processus lourd de mise en production, mais à coté de ça, vous avez fait un migration de version de SQL Server en production sans faire de tests au préalable ? n'avez vous pas un serveur de test ?

Si, je suis en phase de test, et c'est là que je vois que ça ne va pas. Mais le client s'attendait à une simple mise à jour de soft. 48h pour s'assurer que c'est bon en test, et hop, en production ! Je caricature mais c'est presque ça.

[camboui]

elle doit pouvoir se réecrire ainsi :

Code :

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

1
2
3
4
INSERT INTO @peoNoPrimary (PeopleID)
SELECT PeopleID FROM DSpos 
EXCEPT
SELECT PeopleID FROM DSpos WHERE isPrimary=1

ou bien

Code :

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

1
2
3
4
5
INSERT INTO @peoNoPrimary (PeopleID)
SELECT PeopleID 
FROM DSpos 
GROUP BY PeopleID
HAVING MAX( CASE WHEN isPrimary=1 THEN 1 ELSE 0 END) = 0

voire mieux si on connait le modèle

Super ! Merci !

J'étais justement dessus et j'obtiens ceci:

Code :

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

1
2
3
INSERT INTO @peoNoPrimary (PeopleID)
SELECT DISTINCT PeopleID FROM DSpos DP
 WHERE NOT EXISTS (SELECT * FROM DSpos T WHERE T.PeopleID=DP.PeopleID and T.IsPrimary=1)

Mais j'aime bien bien ta solution avec EXCEPT

Pour info, sous 2008R2 la requête originale tourne en 2 secondes, sous 2014 entre 10 et 15 heures...
La nouvelle requête est quasi instantanée.
Allez zou ! Au travail

[camboui]

• Avez vous mis a jour les statistiques en full sur votre nouvelle instance
• Avez vous essayer de changer le compatibility level ?
• Avez vous vérifier qu'un plan guide n'était pas utilisé dans votre ancienne version ?

Je pense avoir répondu, sauf la dernière que je n'ai pas vu.
Et d'ailleurs je ne la comprends pas.
Plan guide ? ckoa ?

[SQLpro]

En fait, l'estimateur de cardinalité de l'optimiseur à changé à partir de la version 2014. C'est peut être la raison de ce changement de performances. En effet, c'est le composant principal de l'optimiseur en ce qui concerne le choix du plan. Si vous avez beaucoup optimisé vos requêtes pour votre ancienne version, alors quelques unes de ces requêtes vont être "contrariées" par ce nouveau mode de calcul.
Dans ce cas, vous pouvez tenter d'activer l'indicateur de trace 9481 pour revenir à l'ancienne version du CE.

Néanmoins, pour les curseurs, cela n'a rien à voir. Mais osql et cqlcmd ne fonctionnent pas de la même manière et je pense qu'ils n'ouvrent pas les curseurs dans le même mode. Tentez de rectifier vos curseurs en les ouvrant avec le mode :
LOCAL
FORWARD_ONLY
STATIC
READ_ONLY

A +

[julien94320]

Un plan guide est un plan d’exécution forcé pour une requête

https://docs.microsoft.com/fr-fr/sql...ce/plan-guides

Pouvez vous essayer de reexecuter votre requete en ajoutant la commande suivante :

Code :

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

1
2
3
4
5
SELECT DISTINCT PeopleID FROM DSpos WHERE PeopleID NOT IN
(
SELECT DISTINCT PeopleID FROM DSpos WHERE isPrimary=1
)
OPTION (RECOMPILE)

+1 avec mon ami SQL PRO le moteur de cardinalité a changé en 2014 ce qui explique sans doute le changement de plan.

Pour les autres questions concernant les statistiques votre réponse n'ait pas claire !

Good point
Ça ne change pas grand chose en pratique.

Vous avez réalisé l'opération ou non ?

Contrairement a votre réponse :

Cela peut changer énormément car le plan d’exécution va se baser sur des statistiques erronés et le moteur risque de ce tromper de plan d'autant plus que le moteur de cardinalité à changé comme cité précédemment ...

ex : réalisé un table scan sur table en pensant avoir 100 lignes des lors que la table a été remplie et elle comporte 100 Millions de lignes ...

Non, en effet Le comportement redevient comme avant.
Certaines requêtes ne sont visiblement plus optimisées en compatibilité 2014 alors qu'elles le sont toujours sous compatibilité 2008R2.
Quelle conclusion en tirer, à part que MS a retiré des (bonnes) choses de 2008R2 ?

Cela confirme donc ce que le moteur de cardinalité change le plan de vos requetes car il ne les interprete pas de la même manière.
L'algebriseur qui est derrière devrait interpréter de la même manière le not in du Expect car transformé en algèbre relationnel nous avons le même besoin.
Mais la question reste la même, vos statistiques ont elle été mis a jour ? Oui ou Non ?

Si oui, essayer le Trace flag conseillé par SQL PRO pour voir.

Pouvez vous nous fournir les plans d'execution en 2008 R2 et en 2014 ?

Si vous ne voulez pas activer le Trace Flag, vous pouvez toujours essayer de fixer le plan en reprenant celui de 2008 R2 avec un plan guide mais je le déconseille fortement car l'idéal c'est de laisser le moteur choisir son propre chemin en fonction de son contexte.

[camboui]

J'ai dit Good point car en effet je n'avais pas fait l'update des statistiques, c'est désormais fait.
Et le résultat est que ça ne change pas grand chose, je voulais dire qu'il n'y avait pas de différence flagrante de performance entre avant et après.

Au total j'ai 4 DB sur le serveur.
Sur une des DB l'update des statistiques me renvoie cette erreur au bout de quelques minutes:
System.OutOfMemoryException...

Plus tard pour la suite, là il fait tard

[SQLpro]

Sur une des DB l'update des statistiques me renvoie cette erreur au bout de quelques minutes:
System.OutOfMemoryException

Pas très bon.... Quel est la RAM du serveur ? Est-ce un serveur Physique ? Quelle est la RAM allouée pour SQL Server ?

Pouvez-vous nous renvoyer le résultat des requêtes suivantes :

Code :

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

1
2
3
4
5
EXEC sp_configure;
SELECT * FROM sys.configurations;
EXEC xp_msver;
EXEC xp_readerrorlog 0, 1, N'System Manufacturer:';
EXEC xp_readerrorlog 0, 1, N'SQL Server detected';

A +

[aieeeuuuuu]

Sur une des DB l'update des statistiques me renvoie cette erreur au bout de quelques minutes:
System.OutOfMemoryException

Cette erreur provient souvent de SSMS. fermez le et rouvrez-le pour voir si l'erreur persiste.

[camboui]

Serveur physique de juillet 2009, HP ProLiant DL380p Gen8, 2 CPUs de 8 coeurs chacun, 32GB, Windows Server 2008R2 Standard, 8GB alloués pour SQL Server (et si je pouvais donner des ordres dynamiquement à SQL Server pour lui dire combien de mémoire utiliser en fonction des autres processus, ce serait mieux).

En relançant SSMS (version 2016 installée sur ma machine) ça passe, bien vu

Pour en revenir aux requêtes, j'en ai modifié quelques unes.
3 cursors dont deux remplacés par des queries plus classiques, et puis l'exemple donné plus haut que aieeeuuuuu m'a corrigé (en fait c'est cette requête qui était vraiment critique ).
Il y a sans doute bcp de choses à améliorer, mais avec ça j'en suis revenu aux performances que j'avais sous 2008R2.
Et améliorer quelque chose qui fonctionne est souvent source de problèmes...
La preuve avec cette mise-à-jour de 2008R2 vers 2014

Je n'ai pas encore testé les OPTION, j'y reviendrai plus tard quand j'aurais plus de temps.
Merci

[SQLpro]

Serveur physique de juillet 2009, HP ProLiant DL380p Gen8, 2 CPUs de 8 coeurs chacun, 32GB, Windows Server 2008R2 Standard, 8GB alloués pour SQL Server

Pourquoi seulement 8 Go ??? Mettez au moins 28 !

Répondez aux autres requête, cela ne prendra que quelques secondes

A