Søket er et enkelt utvalg som inneholder mange grupperingsnivåer og aggragatoperasjoner. Med SET ARITHABORT ON tar det mindre enn et sekund, ellers tar det flere minutter. Vi har sett denne oppførselen på SQL Server 2000 og 2008.
Svar
Litt datert, men for alle som ender opp her med et lignende problem …
Jeg hadde det samme problemet. For meg viste det seg å være sniffing av parametere, som først ikke forstod nok å bry meg om. Jeg la til et «sett arithabort på» som løste problemet, men så kom det tilbake. Så leste jeg:
http://www.sommarskog.se/query-plan-mysteries.html
Det ryddet opp – alt. Fordi jeg brukte Linq til å SQL og hadde begrensede muligheter for å løse problemet, jeg endte opp med å bruke en spørreplanguide (se slutten av lenken) for å tvinge spørringsplanen jeg ønsket.
Kommentarer
- Mer enn seks år senere er lenken i dette svaret fortsatt » nødvendig lesing » … og fortsatt aktuell også, den siste revisjonen er desember ‘ 17.
Svar
.NET-applikasjoner kobles til med alternativet deaktivert som standard, men det er aktivert som standard i Management Studio. Resultatet er at serveren faktisk cacher 2 separate utførelsesplaner for de fleste / alle prosedyrer. Dette påvirker hvordan serveren utfører numeriske beregninger, og som sådan kan du få veldig forskjellige resultater avhengig av prosedyren. Dette er egentlig bare en av to vanlige måter en prosessor kan få matet på en forferdelig utførelsesplan, den andre er parameter sniffing.
Ta en titt på https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx for litt mer diskusjon om det.
Kommentarer
- Jeg er enig med halvparten av dette svaret. Er veldig skeptisk til det numeriske beregningskravet skjønt!
- @Martin: Jeg tror jeg var uklar. Jeg sa bare at ARITHABORT ON gjør at SQL Server vil feile på en hvilken som helst div / 0 eller aritmetisk overflow-feil. Når den er av, fortsetter den og av en eller annen grunn kan det føre til alle slags forferdelige problemer.
- @Ben – Ja beklager at jeg ikke ‘ ikke vil angripe spesielt svaret ditt. Jeg påpekte bare at det ville være veldig enkelt å endre et
SET-alternativ, få en bedre plan og feildiagnostiser dette som selve alternativet som er feil. Jeg ‘ er ikke overbevist om at fyren i linken din ikke har ‘ ikke gjort dette. - @Martin – Ikke en problem, jeg trodde ikke ‘ at du angrep meg. Den andre diskusjonen jeg koblet kunne være litt uklar. Jeg prøvde bare å gi bevis.
- @Martin I ettertid tror jeg at du har rett.
Svar
Jeg vil hevde at dette nesten helt sikkert var parameter sniffing.
Det blir ofte uttalt at SET OPTIONS kan påvirke ytelsen på denne måten, men jeg har ennå ikke sett en eneste autoritativ kilde for dette kravet, bortsett fra tilfellet der du er ved hjelp av indekserte visninger / vedvarende beregnede kolonner.
I dette tilfellet (for SQL2005 + og med mindre databasen din er i SQL2000-kompatibilitetsmodus ). Hvis du har både ARITHABORT og ANSI_WARNINGS OFF, vil du finne at indeksen ikke blir brukt så kan ha en skanning i stedet for ønsket søk (og noe overhead fordi det vedvarende beregningsresultatet ikke kan brukes). ADO.NET ser ut til å være standard som ANSI_WARNINGS ON fra en rask test jeg nettopp gjorde.
Påstanden i Bens svar at «måten serveren utfører numeriske beregninger på» kan legge minutter til et resultat som ellers ville tatt mindre enn et sekund, bare ikke virker troverdig for meg. Jeg tror det som har en tendens til å skje, er at Profiler blir brukt til å identifisere det fornærmende spørsmålet ved å undersøke et ytelsesproblem. Dette limes inn i lederstudioet og kjøres og gir resultater umiddelbart. Den eneste tilsynelatende forskjellen mellom tilkoblinger er alternativet ARITH_ABORT.
En rask test i et administrasjonsstudievindu viser at når SET ARITHABORT OFF er slått på og spørringen kjøres at ytelsesproblemet kommer tilbake slik at det tilsynelatende er lukket. Dette ser ut til å være feilsøkingsmetoden som brukes i Gregg Stark -lenken.
Men det ignorerer det faktum at du med dette alternativet kan ender opp med å få nøyaktig den samme dårlige planen fra hurtigbufferen .
Denne gjenbruken av planen kan skje selv om du er logget på som en annen bruker enn applikasjonstilkoblingen bruker.
Jeg testet dette ved å utføre en testspørring først fra en webapplikasjon og deretter fra management studio med SET ARITHABORT OFF og kunne se brukerregnskapet gå opp fra spørringen nedenfor.
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) For at denne delingen pf planer skal faktisk forekomme må alle plan cache nøkler være de samme. I tillegg til arithabort i seg selv, er noen andre eksempler de utførende brukerne trenger det samme standardskjemaet (hvis spørringen er avhengig av implisitt navneløsning) og tilkoblingene trenger det samme language sett.
Svar
Jeg vet at jeg er sen på dette festen, men for fremtidige besøkende er Martin helt korrekt. Vi kjørte inn i den samme saken – en SP kjørte veldig sakte for .NET-klienter, mens det strålte raskt for SSMS. Når vi utforsket og løste problemet, gjorde vi den systematiske testen som Kenny Evitt spør om i sin kommentar til Martins spørsmål.
Ved å bruke en variant av Martins spørsmål, jeg lette etter SP i prosedyrebufferen og fant to av dem. Ser vi på planene, var det faktisk slik at man hadde ARITHABORT ON og en hadde ARITHABORT OFF. ARITHABORT OFF-versjonen hadde en indekssøk mens ARITHABORT ON-versjonen brukte en indeksskanning for den samme utgangen. Med tanke på parametrene som er involvert, ville indekssøket ha krevd et oppslag i titalls millioner poster for utdataene.
Jeg fjernet de to prosedyrene fra hurtigbufferen og hadde .NET-klienten kjørt SP igjen, ved å bruke de samme parameterne (som inneholdt et bredt datoperiode for en kunde med mye aktivitet). SP kom tilbake umiddelbart. Den hurtigbufrede planen brukte den samme indeksskanningen som tidligere var omtalt i ARITHABORT ON-planen – men denne gangen var planen for ARITHABORT OFF. Vi kjørte SP med de samme parameterne i SSMS, og fikk igjen resultater umiddelbart. Nå så vi at en annen plan ble lagret for ARITHABORT ON, med indeksskanning.
Vi tømte deretter hurtigbufferen, kjørte SP i SSMS med et smalt datointervall og fikk et øyeblikkelig resultat. Vi fant ut at den resulterende hurtigbufrede planen hadde en indekssøking, for den samme utgangen ble tidligere håndtert med en skanning (som også var et søk i den opprinnelige planen med ARITHABORT OFF). Igjen fra SSMS kjørte vi SP, denne gangen med samme brede datoperiode, og så den samme forferdelige ytelsen vi hadde i den opprinnelige .NET-forespørselen.
Kort fortalt hadde ulikheten ingenting å gjøre med den faktiske verdien av ARITHABORT – med den på eller av, fra begge klienter, kunne vi få akseptabel eller forferdelig ytelse: Alt som gjaldt var parameterverdiene som ble brukt til å kompilere og cache planen.
Mens MSDN indikerer at ARITHABORT OFF i seg selv kan ha en negativ innvirkning på søkoptimalisering, testingen vår bekrefter at Martin er riktig – årsaken var parameter sniffing og den resulterende planen var ikke optimal for alle parametrene.
Kommentarer
- Lurer på hva den setningen
Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.betyr. Enten de bare snakker om manglende evne til å bruke indekser på beregnede kolonner og visninger (hvisANSI_WARNINGSogså er av) eller om det virkelig har en annen effekt. - Jeg ‘ er ikke sikker. Jeg lurer på om det ‘ bare er slik at noen på MSDN kom i en lignende situasjon, satte ARTIHABORT til ON, så ytelsesforbedringen og hoppet til de samme konklusjonene andre har. Så langt som indekserte visninger og beregnede kolonner, er jeg ‘ uklar. På et tidspunkt står det at SET-alternativene må ha spesifikke verdier hvis en INSERT, UPDATE eller DELETE-operasjon endrer dataverdiene som er lagret der. Andre steder sier de at optimalisereren vil ignorere indekser for » ethvert spørsmål » som refererer til indeksert visning eller beregnet kolonne. Er begge sanne, eller er det virkelig » noen spørsmål som endrer data «?
Svar
Bare hadde dette problemet. Som folk sa her, er årsaken til flere søkeplaner, hvorav den ene er suboptimal. Jeg ville bare bekrefte at ARITHABORT faktisk kan forårsake problemet av seg selv (ettersom spørringen jeg hadde problemer med ikke hadde noen parametere, noe som tar parameter sniffing ut av ligningen).
Svar
Dette minner meg om nøyaktig samme problem som jeg opplevde i SQL Server 2008 dager. I vårt tilfelle fant vi plutselig at en sql-jobb plutselig bremset opp (vanligvis noen få sekunder, og nå 9+ minutter), jobben trenger tilgang til en koblet server, vi la til at ARITHABORT satt på i trinnet til jobben, og det virket problemet ble løst i noen dager og deretter returnert.
Vi åpnet senere en billett med MS-støtte, og i utgangspunktet kan de ikke finne ut av det heller, og billetten ble eskalert til et veldig høyt PFE-team, og to støtte PFEs prøvde å finne ut av dette problemet.
Den endelige årsaken er at brukerlegitimasjonen (for å kjøre jobbtrinnet) ikke får tilgang til statistikken til de underliggende tabellene (på den koblede serversiden), og dermed er ikke planen for utførelse optimalisert.
I detalj har ikke brukeren tillatelse til DBCC SHOW_STATISTICS (selv om brukeren kan VELGE fra Bordet). I henhold til MSDN , endres denne tillatelsesregelen etter SQL 2012 SP1
Tillatelser for SQL Server og SQL Database
For å se statistikkobjektet, må brukeren eie tabellen eller brukeren må være medlem av rollen til den faste sysadmin-serveren, rollen til db_owner og den faste databasen eller rollen til den faste databasen db_ddladmin.
SQL Server 2012 SP1 endrer tillatelsesbegrensningene og tillater brukere med SELECT-tillatelse å bruke denne kommandoen. Merk at følgende krav eksisterer for at SELECT-tillatelser skal være tilstrekkelig til å kjøre kommandoen:
For å bekrefte dette problemet, trenger vi bare å kjøre profilen på den tilknyttede serversiden og slå på noen hendelser i «Feil» og advarsler «som vist nedenfor.
Håper denne opplevelsen kan hjelpe samfunnet på en eller annen måte.