De ce SET ARITHABORT ON ar accelera dramatic o interogare?

Un pic datat, dar pentru oricine se termină aici cu o problemă similară …

Am avut aceeași problemă. Pentru mine s-a dovedit a fi un sniffing de parametri, pe care la început nu l-am înțeles suficient pentru a-mi păsa. Am adăugat un „set arithabort on” care a rezolvat problema, dar apoi a revenit. Apoi am citit:

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

S-a clarificat – totul. Pentru că foloseam Linq pentru SQL și avea opțiuni limitate pentru a remedia problema, am ajuns să folosesc un ghid de plan de interogare (vezi sfârșitul linkului) pentru a forța planul de interogare dorit.

Comentarii

• Mai mult de șase ani mai târziu, linkul dat în acest răspuns este încă ” citit obligatoriu ” … și încă actuală și cea mai recentă revizuire fiind decembrie ‘ 17.

Aplicațiile .NET se conectează cu opțiunea dezactivată în mod implicit, dar este activată implicit în Management Studio. Rezultatul este că serverul cache de fapt 2 planuri de execuție separate pentru majoritatea / toate procedurile. Acest lucru afectează modul în care serverul efectuează calcule numerice și, ca atare, puteți obține rezultate extrem de diferite, în funcție de procedură. Acesta este cu adevărat doar unul dintre cele două moduri obișnuite prin care un proc poate fi alimentat cu un plan de execuție teribil, celălalt fiind un parametru de adulmecare.

Aruncați o privire la https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx pentru încă o discuție.

Comentarii

• Sunt de acord cu jumătate din acest răspuns. Totuși, sunt foarte sceptic în legătură cu afirmația de calcul numeric!
• @Martin: Cred că nu eram clar. Spuneam doar că ARITHABORT ON face ca SQL Server să eroare la orice eroare div / 0 sau de deversare aritmetică. Când este dezactivat, acesta continuă și, din orice motiv, poate provoca tot felul de probleme oribile.
• @Ben – Da, îmi pare rău, nu ‘ nu am vrut să atac în mod special răspunsul dvs. Tocmai am subliniat că ar fi foarte ușor să schimbați o opțiune SET pentru a obține un plan mai bun și a diagnostica greșit acest lucru ca fiind opțiunea în sine care este de vină. Nu ‘ nu sunt convins că tipul din linkul tău nu ‘ nu a făcut acest lucru.
• @Martin – Nu este problemă, nu ‘ credeam că mă ataci. Cealaltă discuție pe care am legat-o ar putea fi puțin neclară. Încercam doar să dau dovezi de susținere.
• @Martin Retrospectiv cred că aveți dreptate.

Aș argumenta că acest lucru a fost aproape sigur că a adulmecat parametrii.

Se spune adesea că SET OPTIONS poate afecta performanța în acest fel, dar încă nu am văzut o singură sursă autoritară pentru această afirmație, cu excepția cazului în care vă aflați folosind vizualizări indexate / coloane calculate persistente.

În acest caz (pentru SQL2005 + și dacă baza de date nu este în modul de compatibilitate SQL2000 ). Dacă aveți atât ARITHABORT, cât și ANSI_WARNINGS OFF, veți găsi indexul neutilizat deci poate avea o scanare, mai degrabă decât căutarea dorită (și o anumită cheltuieli generale, deoarece rezultatul persistent al calculului nu poate fi utilizat). ADO.NET pare să aibă implicit ANSI_WARNINGS ON dintr-un test rapid pe care tocmai l-am făcut.

Revendicarea în Răspunsul lui Ben că „modul în care serverul efectuează calcule numerice” poate adăuga minute la un rezultat care altfel ar dura mai puțin de o secundă, nu mi se pare credibil. Cred că ceea ce tinde să se întâmple este că, la investigarea unei probleme de performanță, Profiler este folosit pentru a identifica interogarea contravențională. Acesta este lipit în studioul de management și rulat și returnează rezultatele instantaneu. Singura diferență aparentă între conexiuni este opțiunea ARITH_ABORT.

Un test rapid într-o fereastră de studio de management arată că atunci când SET ARITHABORT OFF este activat și se execută interogarea, problema de performanță se repetă, astfel încât se pare că este cazul închis. Într-adevăr, aceasta pare a fi metodologia de depanare utilizată în link-ul Gregg Stark .

Cu toate acestea, acesta ignoră faptul că, cu acea setare de opțiuni, puteți ajungeți să obțineți exact același plan rău din cache .

Această reutilizare a planului se poate întâmpla chiar dacă sunteți conectat ca utilizator diferit de cel pe care îl folosește conexiunea aplicației.

Am testat acest lucru executând mai întâi o interogare de testare dintr-o aplicație web, apoi din studioul de management cu SET ARITHABORT OFF și am putut vedea numărul de utilizatori crescând din interogarea de mai jos.

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) 

Pentru ca acest plan de partajare pf să apară, toate cheile de cache ale planului trebuie să fie aceleași. Pe lângă arithabort, alte exemple sunt că utilizatorii care execută au nevoie de aceeași schemă implicită (dacă interogarea se bazează pe rezoluția implicită a numelui), iar conexiunile au nevoie de același language set.

O listă mai completă de chei cache de plan aici

Știu că am întârziat la această petrecere, dar pentru viitorii vizitatori, Martin este exact corect. Ne-am confruntat cu aceeași problemă – un SP funcționa foarte încet pentru clienții .NET, în timp ce SSMS ardea rapid. În explorarea și rezolvarea problemei, am făcut testarea sistematică despre care Kenny Evitt întreabă în comentariul său la întrebarea lui Martin.

Folosind o variantă de Întrebarea lui Martin, am căutat SP în cache-ul procedurii și am găsit două dintre ele. Privind planurile, a fost de fapt cazul în care unul avea ARITHABORT ON și unul avea ARITHABORT OFF. Versiunea ARITHABORT OFF avea o căutare a indexului în timp ce versiunea ARITHABORT ON folosea o scanare index pentru aceeași ieșire. Având în vedere parametrii implicați, căutarea indexului ar fi necesitat o căutare a zeci de milioane de înregistrări pentru ieșire.

Am șters cele două proceduri din cache și am solicitat clientului .NET să ruleze SP din nou, folosind aceiași parametri (care prezintă o gamă largă de date pentru un client cu multă activitate). SP a revenit instantaneu. Planul în cache a folosit aceeași scanare a indexului care a fost prezentată anterior în planul ARITHABORT ON – dar de data aceasta planul a fost pentru ARITHABORT OFF. Am rulat SP cu aceiași parametri în SSMS și am obținut din nou rezultate instantaneu. Acum am văzut că un al doilea plan a fost stocat în cache, pentru ARITHABORT ON, cu scanarea indexului.

Apoi am șters memoria cache, am rulat SP în SSMS cu un interval de date îngust și am obținut un rezultat instantaneu. Am constatat că planul cache rezultat a avut o căutare a indexului, deoarece aceeași ieșire a fost tratată anterior cu o scanare (care a fost, de asemenea, o căutare în planul original cu ARITHABORT OFF). Din nou de la SSMS, am rulat SP, de data aceasta cu același interval larg de date și am văzut aceeași performanță teribilă pe care am avut-o în solicitarea originală .NET.

Pe scurt, diferența nu a avut nicio legătură cu valoarea reală a ARITHABORT – dacă este activat sau dezactivat, de la oricare dintre clienți, am putea obține performanțe acceptabile sau teribile: Tot ce a contat au fost valorile parametrilor utilizați la compilarea și stocarea în cache a planului.

În timp ce MSDN indică faptul că ARITHABORT OFF în sine poate avea un impact negativ asupra optimizării interogărilor, testarea noastră confirmă faptul că Martin este corect – cauza a fost detectarea parametrilor și planul rezultat nefiind optim pentru toate gamele de parametri.

Comentarii

• Mă întreb ce înseamnă acea expresie Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.. Indiferent dacă vorbesc doar despre incapacitatea de a utiliza indexuri pe coloane și vizualizări calculate (dacă ANSI_WARNINGS este, de asemenea, dezactivat) sau dacă are într-adevăr un alt efect.
• Nu ‘ nu sunt sigur. Mă întreb dacă ‘ este pur și simplu cazul în care cineva de la MSDN s-a confruntat cu o situație similară, a setat ARTIHABORT la ON, a văzut îmbunătățirea performanței și a sărit la aceleași concluzii pe care le au și alții. În ceea ce privește vizualizările indexate și coloanele calculate, ‘ sunt neclar. La un moment dat, acesta afirmă că opțiunile SET trebuie să aibă valori specifice dacă o operațiune INSERT, UPDATE sau DELETE modifică valorile de date stocate acolo. În altă parte, aceștia afirmă că optimizatorul va ignora indexurile pentru ” orice interogare ” care face referire la vizualizarea indexată sau coloana calculată. Sunt ambele adevărate sau este într-adevăr ” vreo interogare care modifică datele „?

Tocmai am avut această problemă. După cum au spus oamenii aici, cauza principală sunt planurile de interogare multiple, dintre care unul nu este optim. Am vrut doar să verific dacă ARITHABORT poate provoca într-adevăr problema de la sine (întrucât interogarea cu care aveam probleme nu avea parametri, ceea ce scoate parametrii care mirosesc din ecuație).

Acest lucru îmi amintește exact aceeași problemă pe care am experimentat-o în SQL Server 2008 zile. În cazul nostru, am găsit brusc că un job sql a încetinit brusc (de obicei câteva secunde și acum 9+ minute), lucrarea trebuie să acceseze un server conectat, am adăugat setul ARITHABORT activat în pasul jobului și s-a părut problema a fost rezolvată câteva zile și apoi revenită.

Ulterior am deschis un bilet cu asistență MS și inițial nici ei nu pot afla, iar biletul a fost transferat către o echipă PFE foarte înaltă și sprijinul PFE a încercat să descopere această problemă.

Motivul final este că acreditarea utilizatorului (pentru a rula pasul jobului) nu poate accesa statisticile tabelelor subiacente (pe partea serverului legat) și, prin urmare, planul de execuție nu este optimizat.

În detaliu, utilizatorul nu are permisiunea pentru DBCC SHOW_STATISTICS (deși utilizatorul poate SELECTA din masa). Conform MSDN , această regulă de permisiune este modificată după sql 2012 SP1

Permisiuni pentru SQL Server și baza de date SQL

Pentru a vizualiza obiectul statistic, utilizatorul trebuie să dețină tabelul sau utilizatorul trebuie să fie membru al rolului de server fix sysadmin, rolul bazei de date fixe db_owner sau rolul bazei de date fixe db_ddladmin.

SQL Server 2012 SP1 modifică restricțiile de permisiune și permite utilizatorilor cu permisiunea SELECT să utilizeze această comandă. Rețineți că există următoarele cerințe pentru ca permisiunile SELECT să fie suficiente pentru a rula comanda:

Pentru a verifica această problemă, trebuie doar să rulăm profilerul pe instanța din partea serverului legat și să activăm unele evenimente în „Erori și avertismente „secțiunea”, așa cum se arată mai jos.

Sper că această experiență poate ajuta cumva comunitatea.