Waarom zou SET ARITHABORT ON een zoekopdracht drastisch versnellen?

Een beetje verouderd, maar voor iedereen die hier terechtkomt met een soortgelijk probleem …

Ik had hetzelfde probleem. Voor mij bleek het parameter snuiven te zijn, wat ik eerst niet genoeg begreep om me zorgen te maken. Ik voegde een “set arithabort on” toe die het probleem oploste, maar toen kwam het terug. Toen las ik:

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

Het loste alles op. Omdat ik Linq gebruikte om SQL en had beperkte opties om het probleem op te lossen, ik gebruikte uiteindelijk een queryplan-gids (zie einde van de link) om het queryplan te forceren dat ik wilde.

Opmerkingen

• Meer dan zes jaar later is de link in dit antwoord nog steeds ” vereist lezen ” … en nog steeds ook actueel, de laatste revisie is december ‘ 17.

.NET-toepassingen maken verbinding met de optie die standaard is uitgeschakeld, maar deze is standaard ingeschakeld in Management Studio. Het resultaat is dat de server in feite 2 afzonderlijke uitvoeringsplannen in de cache opslaat voor de meeste / alle procedures. Dit heeft invloed op hoe de server numerieke berekeningen uitvoert en als zodanig kunt u enorm verschillende resultaten krijgen, afhankelijk van de procedure. Dit is eigenlijk maar een van de 2 veelvoorkomende manieren waarop een proces een vreselijk uitvoeringsplan kan krijgen, de andere is parameter snuiven.

Kijk eens naar https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx voor een beetje meer discussie erover.

Reacties

• Ik ben het eens met de helft van dit antwoord. Ik ben echter erg sceptisch over de bewering over numerieke berekeningen!
• @Martin: Ik denk dat ik onduidelijk was. Ik zei net dat de ARITHABORT ON maakt dat SQL Server een fout zal vertonen bij elke div / 0 of rekenkundige overloopfout. Als het uit is, blijft het doorgaan en kan het om wat voor reden dan ook allerlei vreselijke problemen veroorzaken.
• @Ben – Ja sorry, ik heb ‘ niet in het bijzonder willen aanvallen uw antwoord. Ik wees er net op dat het heel gemakkelijk zou zijn om een SET optie te veranderen, een beter plan te krijgen en dit verkeerd te diagnosticeren als de optie zelf die de schuld heeft. Ik ‘ ben er niet van overtuigd dat de man in je link dit niet ‘ heeft gedaan.
• @Martin – Geen probleem, ik dacht niet ‘ niet dat je me aanviel. De andere discussie die ik heb gelinkt, kan een beetje onduidelijk zijn. Ik probeerde alleen ondersteunend bewijs te leveren.
• @Martin Achteraf denk ik dat je gelijk hebt.

Ik zou zeggen dat dit vrijwel zeker parameter snuiven was.

Er wordt vaak beweerd dat SET OPTIONS de prestaties op deze manier kan beïnvloeden, maar ik heb nog geen enkele gezaghebbende bron voor deze bewering gevonden, behalve in het geval waarin u zich bevindt met geïndexeerde weergaven / persistente berekende kolommen.

In dit geval (voor SQL2005 + en tenzij uw database zich in de SQL2000-compatibiliteitsmodus bevindt ). Als je zowel ARITHABORT en ANSI_WARNINGS OFF hebt, zul je zien dat de index niet wordt gebruikt dus misschien een scan in plaats van de gewenste zoekactie (en wat overhead omdat het aanhoudende berekeningsresultaat niet kan worden gebruikt). ADO.NET lijkt standaard ANSI_WARNINGS ON te hebben uit een snelle test die ik net heb gedaan.

De claim in Het antwoord van Ben dat “de manier waarop de server numerieke berekeningen uitvoert” minuten kan toevoegen aan een resultaat dat anders minder dan een seconde zou duren, lijkt me gewoon niet geloofwaardig. Ik denk dat wat meestal gebeurt, is dat bij het onderzoeken van een prestatieprestatieprobleem Profiler wordt gebruikt om de aanstootgevende vraag te identificeren. Dit wordt in de managementstudio geplakt en uitgevoerd en geeft direct resultaten weer. Het enige duidelijke verschil tussen verbindingen is de optie ARITH_ABORT.

Een snelle test in een management studio-venster laat zien dat wanneer SET ARITHABORT OFF wordt aangezet en de query wordt uitgevoerd, het prestatieprobleem terugkeert, dus dat is blijkbaar gesloten. Dit lijkt inderdaad de methodologie voor probleemoplossing te zijn die wordt gebruikt in de link Gregg Stark .

Dat negeert echter het feit dat je met die optieset krijgen uiteindelijk exact hetzelfde slechte plan uit de cache .

Het hergebruik van dit plan kan zelfs gebeuren als u bent aangemeld als een andere gebruiker dan de applicatieverbinding gebruikt.

Ik heb dit getest door eerst een testquery uit te voeren vanuit een webapplicatie en vervolgens vanuit de managementstudio met SET ARITHABORT OFF en kon de gebruikscijfers zien stijgen uit de onderstaande query.

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) 

Om ervoor te zorgen dat deze pf-plannen voor delen daadwerkelijk plaatsvinden, moeten alle plancache-sleutels hetzelfde zijn. Evenals arithabort zelf zijn enkele andere voorbeelden dat de uitvoerende gebruikers hetzelfde standaardschema nodig hebben (als de query gebaseerd is op impliciete naamomzetting) en dat de verbindingen hetzelfde set.

Een vollediger lijst met plancache-sleutels hier

Ik weet dat ik “laat ben op dit feest, maar voor toekomstige bezoekers heeft Martin precies gelijk. We kwamen hetzelfde probleem tegen – een SP liep erg langzaam voor .NET-clients, terwijl het razendsnel was voor SSMS. Bij het onderzoeken en oplossen van het probleem hebben we de systematische tests uitgevoerd waar Kenny Evitt naar vraagt in zijn commentaar op Martins vraag.

Een variant van gebruiken van Martins vraag, ik zocht de SP in de procedure cache en vond er twee. Als ik naar de plannen keek, was het in feite zo dat de ene ARITHABORT ON had en de andere ARITHABORT OFF. De ARITHABORT OFF-versie had een index-zoekactie terwijl de ARITHABORT ON-versie een indexscan gebruikte voor diezelfde output. Gezien de betrokken parameters, zou het zoeken naar index tientallen miljoenen records nodig hebben gehad voor de uitvoer.

Ik heb de twee procedures uit de cache gewist en heb de .NET-client de SP opnieuw laten uitvoeren met dezelfde parameters (met een breed datumbereik voor een klant met veel activiteit). De SP kwam onmiddellijk terug. Het cacheplan gebruikte dezelfde indexscan die eerder in het ARITHABORT ON-plan stond, maar deze keer was het plan voor ARITHABORT OFF. We hebben de SP met dezelfde parameters in SSMS uitgevoerd en kregen meteen weer resultaten. Nu zagen we dat een tweede plan in de cache was opgeslagen, voor ARITHABORT ON, met de indexscan.

We hebben toen de cache gewist, de SP in SSMS met een smal datumbereik uitgevoerd en kregen direct een resultaat. We ontdekten dat het resulterende plan in de cache een zoekactie in de index had, want dezelfde uitvoer werd eerder afgehandeld met een scan (wat ook een zoekactie was in het oorspronkelijke plan met ARITHABORT OFF). Opnieuw van SSMS, we draaiden de SP, dit keer met hetzelfde brede datumbereik, en zagen dezelfde vreselijke prestatie die we hadden in het oorspronkelijke .NET-verzoek.

Kortom, de ongelijkheid had niets te maken met de werkelijke waarde van ARITHABORT – met het aan of uit, van beide clients, konden we acceptabele of vreselijke prestaties krijgen: het enige dat telde waren de parameterwaarden die werden gebruikt bij het compileren en cachen van het plan.

While MSDN geeft aan dat ARITHABORT OFF zelf een negatieve invloed kan hebben op de query-optimalisatie, onze tests bevestigen dat Martin gelijk heeft – de oorzaak was parameter snuiven en het resulterende plan was niet optimaal voor alle parameters.

Reacties

• Vraag je af wat die zin Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. betekent. Of ze het nu hebben over het onvermogen om indexen te gebruiken op berekende kolommen en views (als ANSI_WARNINGS ook uit staat) of dat het inderdaad een ander effect heeft.
• Ik ‘ ben niet zeker. Ik vraag me af of het ‘ gewoon het geval is dat iemand bij MSDN in een vergelijkbare situatie kwam, ARTIHABORT op AAN zette, de prestatieverbetering zag en tot dezelfde conclusies sprong als anderen. Wat betreft geïndexeerde weergaven en berekende kolommen, ik ‘ m onduidelijk. Op een bepaald punt stelt het dat de SET-opties specifieke waarden moeten hebben als een INSERT-, UPDATE- of DELETE-bewerking de daarin opgeslagen gegevenswaarden wijzigt. Elders stellen ze dat de optimizer indexen negeert voor ” elke zoekopdracht ” die verwijst naar genoemde geïndexeerde weergave of berekende kolom. Zijn beide waar, of is het echt ” een zoekopdracht die gegevens wijzigt “?

Had net dit probleem. Zoals mensen hier zeiden, is de hoofdoorzaak meerdere queryplannen, waarvan er één niet optimaal is. Ik wilde alleen verifiëren dat ARITHABORT inderdaad het probleem zelf kan veroorzaken (aangezien de query waarmee ik problemen had geen parameters had, waardoor het snuiven van parameters uit de vergelijking wordt gehaald).

Dit herinnert me aan precies hetzelfde probleem dat ik ondervond in SQL Server 2008 dagen. In ons geval vonden we plotseling een sql-taak plotseling vertraagd (meestal een paar seconden en nu 9+ minuten), de taak moet toegang krijgen tot een gekoppelde server, we hebben ARITHABORT toegevoegd in de stap van de taak en het leek het probleem was een paar dagen opgelost en kwam toen terug.

We openden later een ticket met MS-ondersteuning, en aanvankelijk konden ze er ook niet achter komen, en het ticket werd geëscaleerd naar een zeer senior PFE-team, en twee ondersteuning PFEs hebben geprobeerd dit probleem op te lossen.

De laatste reden is dat de gebruikersreferentie (om de taakstap uit te voeren) geen toegang heeft tot de statistieken van de onderliggende tabellen (aan de gekoppelde serverzijde), en dat het uitvoeringsplan dus niet is geoptimaliseerd.

In detail heeft de gebruiker geen toestemming voor DBCC SHOW_STATISTICS (hoewel de gebruiker kan SELECTEREN uit de tafel). Volgens MSDN is deze toestemmingsregel gewijzigd na sql 2012 SP1

Machtigingen voor SQL Server en SQL Database

Om het statistiekobject te kunnen bekijken, moet de gebruiker eigenaar zijn van de tabel of de gebruiker moet lid zijn van de vaste serverrol sysadmin, de vaste databaserol db_owner of de vaste databaserol db_ddladmin.

SQL Server 2012 SP1 wijzigt de machtigingsbeperkingen en staat gebruikers met SELECT-toestemming toe om deze opdracht te gebruiken. Merk op dat de volgende vereisten bestaan voor SELECT-machtigingen om voldoende te zijn om de opdracht uit te voeren:

Om dit probleem te verifiëren, hoeven we alleen de profiler op de gekoppelde serverzijde uit te voeren en enkele gebeurtenissen in “Fouten en waarschuwingen “zoals hieronder weergegeven.

Ik hoop dat deze ervaring de gemeenschap op de een of andere manier kan helpen.