Pourquoi SET ARITHABORT ON accélérerait-il considérablement une requête?

Un peu daté, mais pour quiconque se retrouve ici avec un problème similaire …

Jai eu le même problème. Pour moi, cela sest avéré être le reniflage de paramètres, ce que je ne comprenais pas assez au début pour men soucier. Jai ajouté un « set arithabort on » qui a résolu le problème, mais ensuite il est revenu. Puis jai lu:

http://www.sommarskog.se/query-plan-mysteries.html

Cela a tout effacé. Parce que jutilisais Linq pour SQL et ayant des options limitées pour résoudre le problème, jai fini par utiliser un guide de plan de requête (voir fin du lien) pour forcer le plan de requête que je voulais.

Commentaires

• Plus de six ans plus tard, le lien fourni dans cette réponse est toujours  » lecture obligatoire  » … et toujours actuel également, la dernière révision étant décembre ‘ 17.

Les applications .NET se connectent avec loption désactivée par défaut, mais elle est activée par défaut dans Management Studio. Le résultat est que le serveur met en cache 2 plans dexécution distincts pour la plupart / toutes les procédures. Cela affecte la façon dont le serveur effectue les calculs numériques et, en tant que tel, vous pouvez obtenir des résultats très différents en fonction de la procédure. Ce nest vraiment quune des 2 façons courantes pour un proc de se nourrir dun plan dexécution terrible, lautre étant le reniflage de paramètres.

Jetez un oeil à https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx pour un peu plus de discussion à ce sujet.

Commentaires

• Je suis daccord avec la moitié des cette réponse. Je suis cependant très sceptique quant à la prétention du calcul numérique!
• @Martin: Je pense que je nai pas été clair. Je disais simplement que lARITHABORT ON fait que SQL Server se trompe sur toute erreur de dépassement div / 0 ou arithmétique. Quand il est éteint, il continue et pour quelque raison que ce soit peut causer toutes sortes de problèmes horribles.
• @Ben – Oui, désolé, je nai ‘ pas envie dattaquer particulièrement votre réponse Je faisais juste remarquer quil serait très facile de changer une option SET obtenir un meilleur plan et de mal diagnostiquer cela comme étant loption elle-même qui est en faute. Je ‘ ne suis pas convaincu que le type de votre lien n’a ‘ pas fait cela.
• @Martin – Pas un problème, je ne ‘ pas que vous mattaquiez. Lautre discussion que jai liée pourrait être un peu floue. Jessayais juste de fournir des preuves à lappui.
• @Martin Rétrospectivement, je pense que vous avez raison.

Je dirais que cétait presque certainement du reniflement de paramètres.

Il est souvent dit que SET OPTIONS peut affecter les performances de cette manière, mais je nai pas encore vu une seule source faisant autorité pour cette réclamation, sauf dans le cas où vous êtes en utilisant des vues indexées / des colonnes calculées persistantes.

Dans ce cas (pour SQL2005 + et sauf si votre base de données est en mode de compatibilité SQL2000 ). Si vous avez à la fois ARITHABORT et ANSI_WARNINGS OFF, vous trouverez que lindex nest pas utilisé ainsi peut avoir un balayage plutôt que la recherche désirée (et une certaine surcharge car le résultat de calcul persistant ne peut pas être utilisé). ADO.NET semble avoir par défaut ANSI_WARNINGS ON dun test rapide que je viens de faire.

La revendication dans La réponse de Ben que « la façon dont le serveur effectue les calculs numériques » peut ajouter des minutes à un résultat qui autrement prendrait moins dune seconde ne me semble tout simplement pas crédible. Je pense que ce qui a tendance à se produire, cest que lors de lenquête sur un problème de performances, Profiler est utilisé pour identifier la requête incriminée. Ceci est collé dans le studio de gestion et exécuté et renvoie les résultats instantanément. La seule différence apparente entre les connexions est loption ARITH_ABORT.

Un test rapide dans une fenêtre de studio de gestion montre que lorsque SET ARITHABORT OFF est activé et que la requête est exécutée, le problème de performances se reproduit, ce qui est apparemment clos. En effet, cela semble être la méthodologie de dépannage utilisée dans le lien Gregg Stark .

Cependant, cela ignore le fait quavec cet ensemble doptions, vous pouvez finissent par obtenir exactement le même mauvais plan du cache .

Cette réutilisation du plan peut se produire même si vous êtes connecté en tant quutilisateur différent de celui utilisé par la connexion dapplication.

Jai testé cela en exécutant une requête de test dabord à partir dune application Web puis depuis un studio de gestion avec SET ARITHABORT OFF et jai pu voir les comptes dutilisation augmenter à partir de la requête ci-dessous.

SELECT usecounts, cacheobjtype, objtype, text ,query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

Pour que ce partage de plans pf se produise réellement, toutes les clés de cache de plan doivent être identiques. En plus de arithabort, dautres exemples sont que les utilisateurs exécutants ont besoin du même schéma par défaut (si la requête repose sur une résolution de nom implicite) et les connexions ont besoin du même language set.

Une liste plus complète des clés de cache de plan ici

Je sais que je suis en retard à cette fête, mais pour les futurs visiteurs, Martin a tout à fait raison. Nous avons rencontré le même problème – un SP fonctionnait très lentement pour les clients .NET, alors que cétait extrêmement rapide pour SSMS. Pour explorer et résoudre le problème, nous avons effectué les tests systématiques que Kenny Evitt pose dans son commentaire à la question de Martin.

Utilisation dune variante de Martin, jai cherché le SP dans le cache des procédures et jen ai trouvé deux. En regardant les plans, il était en fait que lun avait ARITHABORT ON et lautre ARITHABORT OFF. La version ARITHABORT OFF avait une recherche dindex tandis que la version ARITHABORT ON utilisait une recherche dindex pour cette même sortie. Compte tenu des paramètres impliqués, la recherche dindex aurait nécessité une recherche sur des dizaines de millions denregistrements pour la sortie.

Jai effacé les deux procédures du cache et demandé au client .NET dexécuter à nouveau le SP, en utilisant les mêmes paramètres (qui comportaient une large plage de dates pour un client avec beaucoup dactivité). Le SP est revenu instantanément. Le plan mis en cache utilisait le même scan dindex qui figurait auparavant dans le plan ARITHABORT ON – mais cette fois, le plan était pour ARITHABORT OFF. Nous avons exécuté le SP avec les mêmes paramètres dans SSMS, et à nouveau obtenu des résultats instantanément. Nous avons maintenant vu quun deuxième plan était mis en cache, pour ARITHABORT ON, avec lanalyse dindex.

Nous avons ensuite vidé le cache, exécuté le SP dans SSMS avec une plage de dates étroite et obtenu un résultat instantané. Nous avons constaté que le plan mis en cache résultant avait une recherche dindex, car la même sortie était auparavant traitée avec une analyse (qui était également une recherche dans le plan dorigine avec ARITHABORT OFF). Encore une fois à partir de SSMS, nous avons exécuté le SP, cette fois avec la même large plage de dates, et avons vu les mêmes performances terribles que nous avions dans la requête .NET dorigine.

En bref, la disparité navait rien à voir avec la valeur réelle dARITHABORT – avec elle ou non, de lun ou lautre client, nous pourrions obtenir des performances acceptables ou terribles: tout ce qui importait était les valeurs des paramètres utilisés dans la compilation et la mise en cache du plan.

Pendant que MSDN indique quARITHABORT OFF lui-même peut avoir un impact négatif sur loptimisation des requêtes, nos tests confirment que Martin est correct – la cause était le reniflage de paramètres et le plan résultant nétait pas optimal pour toutes les plages de paramètres.

Commentaires

• Je me demande ce que signifie cette phrase Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.. Quils parlent simplement de lincapacité dutiliser des index sur des colonnes et des vues calculées (si ANSI_WARNINGS est également désactivé) ou si cela a effectivement un autre effet.
• Je ‘ ne suis pas sûr. Je me demande si ‘ est simplement le cas où quelquun chez MSDN a rencontré une situation similaire, a réglé ARTIHABORT sur ON, vu lamélioration des performances et a sauté aux mêmes conclusions que dautres. En ce qui concerne les vues indexées et les colonnes calculées, je ‘ ne suis pas clair. À un moment donné, il indique que les options SET doivent avoir des valeurs spécifiques si une opération INSERT, UPDATE ou DELETE modifie les valeurs de données qui y sont stockées. Ailleurs, ils déclarent que loptimiseur ignorera les index pour  » toute requête  » faisant référence à ladite vue indexée ou à la colonne calculée. Les deux sont-ils vrais ou sagit-il vraiment  » dune requête modifiant des données « ?

Je viens davoir ce problème. Comme les gens lont dit ici, la cause principale est plusieurs plans de requête, dont lun est sous-optimal. Je voulais juste vérifier quARITHABORT peut effectivement causer le problème par lui-même (car la requête avec laquelle javais des problèmes navait pas de paramètres, ce qui supprime le reniflage des paramètres de léquation).

Cela me rappelle exactement le même problème que jai rencontré dans SQL Server 2008 jours. Dans notre cas, nous avons soudainement trouvé une tâche SQL ralentie soudainement (généralement quelques secondes, et maintenant plus de 9 minutes), la tâche doit accéder à un serveur lié, nous avons ajouté activer ARITHABORT dans létape de la tâche, et il semblait le problème a été résolu pendant quelques jours puis est revenu.

Nous avons ensuite ouvert un ticket avec le support MS, et au départ, ils ne peuvent pas non plus le savoir, et le ticket a été transmis à une équipe PFE très senior, et deux les PFE de soutien ont tenté de résoudre ce problème.

La dernière raison est que les informations didentification de lutilisateur (pour exécuter létape de travail) ne peuvent pas accéder aux statistiques des tables sous-jacentes (côté serveur lié), et donc le plan dexécution nest pas optimisé.

En détail, lutilisateur na pas dautorisation sur DBCC SHOW_STATISTICS (bien que lutilisateur puisse SELECT la table). Selon MSDN , cette règle dautorisation est modifiée après sql 2012 SP1

Autorisations pour SQL Server et SQL Database

Pour afficher lobjet de statistiques, lutilisateur doit posséder la table ou lutilisateur doit être membre du rôle serveur fixe sysadmin, du rôle de base de données fixe db_owner ou du rôle de base de données fixe db_ddladmin.

SQL Server 2012 SP1 modifie les restrictions dautorisation et permet aux utilisateurs avec lautorisation SELECT dutiliser cette commande. Notez que les conditions suivantes existent pour que les autorisations SELECT soient suffisantes pour exécuter la commande:

Pour vérifier ce problème, nous devons simplement exécuter le profileur sur linstance côté serveur lié et activer certains événements dans « Erreurs et Avertissements « comme indiqué ci-dessous.

Jespère que cette expérience pourra aider la communauté dune manière ou dune autre.