Perché SET ARITHABORT ON velocizza notevolmente una query?

Un po datato, ma per chiunque finisca qui con un problema simile …

Ho avuto lo stesso problema. Per me si è rivelato essere lo sniffing dei parametri, che allinizio non capivo abbastanza da preoccuparmene. Ho aggiunto un “set arithabort on” che ha risolto il problema ma poi è tornato. Poi ho letto:

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

Ha cancellato tutto. Perché stavo usando Linq per SQL e disponendo di opzioni limitate per risolvere il problema, ho finito per utilizzare una guida al piano di query (vedere la fine del collegamento) per forzare il piano di query che volevo.

Commenti

• Più di sei anni dopo, il link fornito in questa risposta è ancora ” lettura obbligatoria ” … e ancora anche attuale, lultima revisione è di dicembre ‘ 17.

Le applicazioni .NET si connettono con lopzione disabilitata per impostazione predefinita, ma è abilitata per impostazione predefinita in Management Studio. Il risultato è che il server memorizza effettivamente 2 piani di esecuzione separati per la maggior parte / tutte le procedure. Ciò influisce sul modo in cui il server esegue i calcoli numerici e come tale è possibile ottenere risultati molto diversi a seconda della procedura. Questo è davvero solo uno dei 2 modi comuni in cui un proc può essere alimentato da un pessimo piano di esecuzione, laltro è lo sniffing dei parametri.

Dai unocchiata a https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx per ulteriori discussioni su di esso.

Commenti

• Sono daccordo con metà questa risposta. Tuttavia, sono molto scettico sullaffermazione del calcolo numerico!
• @Martin: Penso di essere stato poco chiaro. Stavo solo dicendo che ARITHABORT ON fa sì che SQL Server vada in errore su qualsiasi div / 0 o errore di overflow aritmetico. Quando è spento, continua a funzionare e per qualsiasi motivo può causare tutti i tipi di problemi orribili.
• @Ben – Sì, scusa non ‘ volevo attaccare particolarmente la tua risposta ti stavo solo facendo notare che sarebbe molto facile cambiare unopzione SET per ottenere un piano migliore e diagnosticare erroneamente che si tratta dellopzione stessa che è colpevole. ‘ non sono convinto che il tizio nel tuo link non abbia ‘ fatto questo.
• @Martin – Non un problema, ‘ non pensavo che mi stessi attaccando. Laltra discussione che ho collegato potrebbe essere un po poco chiara. Stavo solo cercando di fornire prove a sostegno.
• @Martin In retrospettiva, credo che tu abbia ragione.

Direi che questo era quasi certamente lo sniffing dei parametri.

Si afferma spesso che SET OPTIONS può influenzare le prestazioni in questo modo, ma devo ancora vedere una singola fonte autorevole per questa affermazione tranne per il caso in cui ti trovi utilizzando viste indicizzate / colonne calcolate persistenti.

In questo caso (per SQL2005 + e a meno che il database non sia in modalità di compatibilità SQL2000 ). Se hai sia ARITHABORT e ANSI_WARNINGS OFF, troverai lindice non utilizzato quindi potrebbe avere una scansione piuttosto che la ricerca desiderata (e un po di overhead poiché il risultato del calcolo persistente non può essere utilizzato). ADO.NET sembra avere per impostazione predefinita ANSI_WARNINGS ON da un rapido test che ho appena fatto.

Laffermazione in La risposta di Ben che “il modo in cui il server esegue i calcoli numerici” può aggiungere minuti a un risultato che altrimenti richiederebbe meno di un secondo, ma non mi sembra credibile. Penso che ciò che tende ad accadere sia che durante lanalisi di un problema di prestazioni delle prestazioni Profiler viene utilizzato per identificare la query offensiva. Questo viene incollato nello studio di gestione ed eseguito e restituisce immediatamente i risultati. Lunica differenza apparente tra le connessioni è lopzione ARITH_ABORT.

Un rapido test in una finestra di studio di gestione mostra che quando SET ARITHABORT OFF è attivato e la query viene eseguita, il problema di prestazioni si ripresenta, quindi apparentemente il caso è chiuso. In effetti questa sembra essere la metodologia di risoluzione dei problemi utilizzata nel collegamento Gregg Stark .

Tuttavia ciò ignora il fatto che con quellopzione impostata puoi finire per ottenere lo stesso identico piano sbagliato dalla cache .

Il riutilizzo di questo piano può avvenire anche se hai effettuato laccesso come un utente diverso da quello utilizzato dalla connessione dellapplicazione.

Lho testato eseguendo una query di prova prima da unapplicazione web e poi dallo studio di gestione con SET ARITHABORT OFF e ho potuto vedere i conteggi degli utenti in aumento dalla query sottostante.

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) 

Affinché questa condivisione dei piani di pf avvenga effettivamente, tutte le chiavi della cache del piano devono essere le stesse. Oltre allo stesso arithabort, alcuni altri esempi sono gli utenti in esecuzione che richiedono lo stesso schema predefinito (se la query si basa sulla risoluzione dei nomi implicita) e le connessioni richiedono lo stesso language set.

Un elenco più completo di chiavi della cache del piano qui

So di essere in ritardo a questa festa, ma per i futuri visitatori Martin ha esattamente ragione. Abbiamo riscontrato lo stesso problema: un SP funzionava molto lentamente per i client .NET, mentre era velocissimo per SSMS. Nellesplorare e risolvere il problema, abbiamo eseguito i test sistematici che Kenny Evitt chiede nel suo commento alla domanda di Martin.

Utilizzando una variante di Martin, ho cercato lSP nella cache delle procedure e ne ho trovati due. Guardando i piani, era in effetti il caso che uno aveva ARITHABORT ON e uno aveva ARITHABORT OFF. La versione ARITHABORT OFF aveva una ricerca di indice mentre la versione ARITHABORT ON utilizzava una scansione dellindice per quella stessa uscita. Dati i parametri coinvolti, la ricerca dellindice avrebbe richiesto una ricerca su decine di milioni di record per loutput.

Ho cancellato le due procedure dalla cache e ho fatto eseguire nuovamente al client .NET lSP, utilizzando gli stessi parametri (che presentavano un ampio intervallo di date per un cliente con molte attività). LSP è tornato immediatamente. Il piano memorizzato nella cache utilizzava la stessa scansione dellindice precedentemente inclusa nel piano ARITHABORT ON, ma questa volta il piano era per ARITHABORT OFF. Abbiamo eseguito lSP con gli stessi parametri in SSMS e di nuovo abbiamo ottenuto risultati istantaneamente. Ora abbiamo visto che un secondo piano è stato memorizzato nella cache, per ARITHABORT ON, con la scansione dellindice.

Abbiamo quindi svuotato la cache, eseguito lSP in SSMS con un intervallo di date ristretto e ottenuto un risultato immediato. Abbiamo scoperto che il piano memorizzato nella cache risultante aveva una ricerca di indice, poiché lo stesso output era stato precedentemente gestito con una scansione (che era anche una ricerca nel piano originale con ARITHABORT OFF). Sempre da SSMS, abbiamo eseguito lSP, questa volta con lo stesso ampio intervallo di date, e abbiamo visto le stesse terribili prestazioni che avevamo nella richiesta .NET originale.

In breve, la disparità non aveva nulla a che fare con il valore effettivo di ARITHABORT – con esso attivato o disattivato, da entrambi i client, potevamo ottenere prestazioni accettabili o terribili: tutto ciò che importava erano i valori dei parametri utilizzati nella compilazione e nella memorizzazione nella cache del piano.

Mentre MSDN indica che ARITHABORT OFF stesso può avere un impatto negativo sullottimizzazione delle query, i nostri test confermano che Martin è corretto: la causa era lo sniffing dei parametri e il piano risultante non era ottimale per tutti gli intervalli di parametri.

Commenti

• Mi chiedo cosa significhi quella frase Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.. Sia che si stia solo parlando dellimpossibilità di utilizzare gli indici su colonne e viste calcolate (se ANSI_WARNINGS è anche disattivato) o se ha effettivamente qualche altro effetto.
• ‘ non ne sono sicuro. Mi chiedo se ‘ sia semplicemente il caso in cui qualcuno in MSDN si è imbattuto in una situazione simile, ha impostato ARTIHABORT su ON, ha visto il miglioramento delle prestazioni e è saltato alle stesse conclusioni di altri. Per quanto riguarda le viste indicizzate e le colonne calcolate, ‘ non sono chiaro. A un certo punto si afferma che le opzioni SET devono avere valori specifici se unoperazione INSERT, UPDATE o DELETE modifica i valori dei dati memorizzati in esse. Altrove affermano che lottimizzatore ignorerà gli indici per ” qualsiasi query ” che fa riferimento a detta vista indicizzata o colonna calcolata. Sono entrambe vere o è davvero ” query che modifica i dati “?

Ho appena avuto questo problema. Come si dice qui, la causa principale è costituita da più piani di query, uno dei quali non è ottimale. Volevo solo verificare che ARITHABORT possa effettivamente causare il problema da solo (poiché la query con cui stavo avendo problemi non aveva parametri, il che elimina i parametri dallequazione).

Questo mi ricorda esattamente lo stesso problema che ho riscontrato nei giorni 2008 di sql server. Nel nostro caso, abbiamo improvvisamente trovato un lavoro sql improvvisamente rallentato (di solito pochi secondi e ora più di 9 minuti), il lavoro deve accedere a un server collegato, abbiamo aggiunto limpostazione ARITHABORT su nel passaggio del lavoro e sembrava il problema è stato risolto per alcuni giorni e poi è stato restituito.

Successivamente abbiamo aperto un ticket con lassistenza di MS, e inizialmente non è stato possibile scoprirlo, e il ticket è stato inoltrato a un team PFE molto anziano, e due i PFE di supporto hanno cercato di risolvere questo problema.

Il motivo finale è che le credenziali dellutente (per eseguire la fase di lavoro) non possono accedere alle statistiche delle tabelle sottostanti (sul lato server collegato), e quindi il piano di esecuzione non è ottimizzato.

In dettaglio, lutente non dispone dellautorizzazione per DBCC SHOW_STATISTICS (sebbene lutente possa SELEZIONARE da la tavola). Secondo MSDN , questa regola di autorizzazione viene modificata dopo sql 2012 SP1

Autorizzazioni per SQL Server e database SQL

Per visualizzare loggetto statistiche, lutente deve possedere la tabella oppure lutente deve essere un membro del ruolo predefinito del server sysadmin, del ruolo predefinito del database db_owner o del ruolo predefinito del database db_ddladmin.

SQL Server 2012 SP1 modifica le limitazioni delle autorizzazioni e consente agli utenti con autorizzazione SELECT di utilizzare questo comando. Tieni presente che esistono i seguenti requisiti affinché le autorizzazioni SELECT siano sufficienti per eseguire il comando:

Per verificare questo problema, è sufficiente eseguire il profiler sullistanza lato server collegata e attivare alcuni eventi in “Errori e Avvisi “come mostrato di seguito.

Spero che questa esperienza possa aiutare la comunità in qualche modo.