Por que SET ARITHABORT ON acelera drasticamente uma consulta?

Um pouco desatualizado, mas para qualquer um que termine aqui com um problema semelhante …

Eu tive o mesmo problema. Para mim, acabou sendo uma detecção de parâmetro, que no começo eu não entendia o suficiente para me preocupar. Eu adicionei um “set arithabort on” que corrigiu o problema, mas ele voltou. Então eu li:

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

Ele limpou -tudo. Porque eu estava usando o Linq para SQL e tinha opções limitadas para corrigir o problema, acabei usando um guia de plano de consulta (veja o final do link) para forçar o plano de consulta que eu queria.

Comentários

• Mais de seis anos depois, o link fornecido nesta resposta ainda é ” leitura obrigatória ” … e ainda atual também, sendo a última revisão de dezembro ‘ 17.

Os aplicativos .NET se conectam com a opção desativada por padrão, mas está ativada por padrão no Management Studio. O resultado é que o servidor realmente armazena em cache 2 planos de execução separados para a maioria / todos os procedimentos. Isso afeta a forma como o servidor executa cálculos numéricos e, como tal, você pode obter resultados totalmente diferentes, dependendo do procedimento. Esta é realmente apenas uma das duas maneiras comuns pelas quais um proc pode ser alimentado com um plano de execução terrível, a outra sendo a detecção de parâmetros.

Dê uma olhada em https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx para um pouco mais de discussão sobre isso.

Comentários

• Eu concordo com metade de esta resposta. No entanto, sou muito cético quanto à afirmação do cálculo numérico!
• @Martin: Acho que não fui claro. Eu estava apenas dizendo que o ARITHABORT ON torna o SQL Server um erro em qualquer div / 0 ou erro de estouro aritmético. Quando está desligado, ele continua funcionando e por qualquer motivo pode causar todos os tipos de problemas horríveis.
• @Ben – Sim, desculpe, eu não ‘ não queria atacar especificamente sua resposta Eu estava apenas apontando que seria muito fácil mudar uma SET opção para obter um plano melhor e diagnosticar erroneamente isso como sendo a própria Opção que está com defeito. Eu ‘ não estou convencido de que o cara em seu link não ‘ fiz isso.
• @Martin – Não um problema, eu não ‘ achei que você estava me atacando. A outra discussão que vinculei pode não ser um pouco clara. Eu estava apenas tentando fornecer evidências de apoio.
• @Martin Em retrospecto, acredito que você está correto.

Eu diria que isso quase certamente foi uma detecção de parâmetro.

Costuma-se afirmar que SET OPTIONS pode afetar o desempenho dessa forma, mas ainda não vi uma única fonte autorizada para esta afirmação, exceto no caso de você usando Visualizações indexadas / colunas computadas persistentes.

Neste caso (para SQL2005 + e , a menos que seu banco de dados esteja no modo de compatibilidade SQL2000 ). Se você tiver ARITHABORT e ANSI_WARNINGS OFF, verá que o índice não está sendo usado portanto, pode haver uma varredura em vez da busca desejada (e alguma sobrecarga, pois o resultado do cálculo persistente não pode ser usado). O ADO.NET parece ter como padrão ANSI_WARNINGS ON de um teste rápido que acabei de fazer.

A declaração em A resposta de Ben que “a maneira como o servidor realiza cálculos numéricos” pode adicionar minutos a um resultado que, de outra forma, levaria menos de um segundo, simplesmente não parece crível para mim. Acho que o que tende a acontecer é que, ao investigar um problema de desempenho, o Profiler é usado para identificar a consulta ofensiva. Isso é colado no estúdio de gerenciamento e executado e retorna os resultados instantaneamente. A única diferença aparente entre as conexões é a opção ARITH_ABORT.

Um teste rápido em uma janela de estúdio de gerenciamento mostra que quando SET ARITHABORT OFF é ativado e a consulta é executada, o problema de desempenho ocorre novamente, de modo que o caso é aparentemente encerrado. Na verdade, essa parece ser a metodologia de solução de problemas usada no link Gregg Stark .

No entanto, isso ignora o fato de que, com essa opção definida, você pode acabam obtendo exatamente o mesmo plano ruim do cache .

Essa reutilização do plano pode acontecer mesmo se você estiver conectado como um usuário diferente do usado pela conexão do aplicativo.

Eu testei isso executando uma consulta de teste primeiro em um aplicativo da web e, em seguida, no Management Studio com SET ARITHABORT OFF e pude ver a contagem de usuários aumentando a partir da consulta abaixo.

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) 

Para que os planos de pf de compartilhamento realmente ocorram, todas as chaves de cache do plano devem ser iguais. Bem como arithabort em si, alguns outros exemplos mostram que os usuários em execução precisam do mesmo esquema padrão (se a consulta depende da resolução de nome implícita) e as conexões precisam do mesmo language set.

Uma lista mais completa de chaves de cache do plano aqui

Eu sei que estou atrasado para esta festa, mas para futuros visitantes, Martin está exatamente correto. Encontramos o mesmo problema – um SP estava funcionando muito lentamente para clientes .NET, enquanto era extremamente rápido para SSMS. Ao explorar e resolver o problema, fizemos os testes sistemáticos que Kenny Evitt perguntou em seu comentário à pergunta de Martin.

Usando uma variante de Consulta do Martin, procurei o SP no cache de procedimento e encontrei dois deles. Olhando os planos, na verdade era o caso que um tinha ARITHABORT ON e outro ARITHABORT OFF. A versão do ARITHABORT OFF tinha busca de índice enquanto a versão ARITHABORT ON usava uma varredura de índice para essa mesma saída. Dados os parâmetros envolvidos, a busca de índice teria exigido uma pesquisa em dezenas de milhões de registros para a saída.

Limpei os dois procedimentos do cache e fiz o cliente .NET executar o SP novamente, usando os mesmos parâmetros (que apresentavam um amplo intervalo de datas para um cliente com muita atividade). O SP voltou instantaneamente. O plano em cache usava a mesma varredura de índice que era apresentada anteriormente no plano ARITHABORT ON – mas desta vez o plano era para ARITHABORT OFF. Executamos o SP com os mesmos parâmetros no SSMS e novamente obtivemos resultados instantaneamente. Agora vimos que um segundo plano foi armazenado em cache, para ARITHABORT ON, com a varredura de índice.

Em seguida, limpamos o cache, executamos o SP no SSMS com um intervalo de datas estreito e obtivemos um resultado instantâneo. Descobrimos que o plano em cache resultante tinha uma busca de índice, pois a mesma saída era tratada anteriormente com uma varredura (que também era uma busca no plano original com ARITHABORT OFF). Novamente do SSMS, executamos o SP, desta vez com o mesmo amplo intervalo de datas, e vimos o mesmo desempenho terrível que tínhamos na solicitação .NET original.

Em suma, a disparidade não tinha nada a ver com o valor real de ARITHABORT – com ele ativado ou desativado, de qualquer cliente, poderíamos obter um desempenho aceitável ou péssimo: tudo o que importava eram os valores dos parâmetros usados na compilação e armazenamento em cache do plano.

Enquanto MSDN indica que o próprio ARITHABORT OFF pode ter um impacto negativo na otimização da consulta, nossos testes confirmam que Martin está correto – a causa foi a detecção de parâmetros e o plano resultante não foi o ideal para todos os intervalos de parâmetros.

Comentários

• Imagine o que essa frase Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. significa. Quer estejam apenas falando sobre a incapacidade de usar índices em colunas e visualizações computadas (se ANSI_WARNINGS também estiver desativado) ou se realmente tiver algum outro efeito.
• Eu ‘ não tenho certeza. Eu me pergunto se ‘ é simplesmente o caso de alguém no MSDN ter passado por uma situação semelhante, definir ARTIHABORT como ON, ver a melhoria de desempenho e tirar as mesmas conclusões que os outros. No que diz respeito às visualizações indexadas e colunas computadas, eu ‘ m confuso. Em um ponto, ele afirma que as opções SET devem ter valores específicos se uma operação INSERT, UPDATE ou DELETE modificar os valores de dados nela armazenados. Em outro lugar, eles afirmam que o otimizador irá ignorar os índices para ” qualquer consulta ” que faça referência à referida exibição indexada ou coluna computada. Ambos são verdadeiros ou são realmente ” qualquer consulta modificando dados “?

Acabei de ter este problema. Como as pessoas disseram aqui, a causa raiz são vários planos de consulta, um dos quais é abaixo do ideal. Eu só queria verificar se ARITHABORT pode de fato causar o problema por si só (já que a consulta com a qual eu estava tendo problemas não tinha parâmetros, o que tira a detecção de parâmetros da equação).

Isso me lembra exatamente o mesmo problema que experimentei no sql server 2008 dias. Em nosso caso, de repente encontramos um trabalho sql lento repentinamente (geralmente alguns segundos, e agora 9+ minutos), o trabalho precisa acessar um servidor vinculado, adicionamos set ARITHABORT on na etapa do trabalho, e parecia o problema foi resolvido por alguns dias e depois voltou.

Mais tarde, abrimos um tíquete com o suporte da MS e, inicialmente, eles também não conseguiram descobrir, e o tíquete foi escalado para uma equipe PFE muito sênior, e dois Os PFEs de suporte tentaram resolver esse problema.

O motivo final é que a credencial do usuário (para executar a etapa do trabalho) não pode acessar as estatísticas das tabelas subjacentes (no lado do servidor vinculado) e, portanto, o plano de execução não é otimizado.

Em detalhes, o usuário não tem permissão em DBCC SHOW_STATISTICS (embora o usuário possa SELECIONAR em a mesa). De acordo com o MSDN , esta regra de permissão foi alterada após sql 2012 SP1

Permissões para SQL Server e banco de dados SQL

Para visualizar o objeto de estatísticas, o usuário deve possuir a tabela ou o usuário deve ser membro da função de servidor fixa sysadmin, da função de banco de dados fixa db_owner ou da função de banco de dados fixa db_ddladmin.

SQL Server 2012 SP1 modifica as restrições de permissão e permite que usuários com permissão SELECT usem este comando. Observe que os seguintes requisitos existem para que as permissões SELECT sejam suficientes para executar o comando:

Para verificar esse problema, precisamos apenas executar o criador de perfil na instância do lado do servidor vinculado e ativar alguns eventos em “Erros e Avisos “, conforme mostrado abaixo.

Espero que esta experiência possa ajudar a comunidade de alguma forma.