SET ARITHABORT ON이 쿼리 속도를 크게 향상시키는 이유는 무엇입니까?

약간 구식이지만 여기에서 비슷한 문제 …

같은 문제가있었습니다. 나를 위해 그것은 매개 변수 스니핑으로 밝혀 졌는데, 처음에는 “이해할만큼 충분히 이해하지 못했습니다.”set arithabort on “을 추가하여 문제를 해결했지만 다시 돌아 왔습니다. 그런 다음 읽었습니다.

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

모든 것이 삭제되었습니다. Linq를 사용하여 SQL을 사용하고 문제를 해결할 수있는 옵션이 제한되어 있었기 때문에 결국 쿼리 계획 가이드 (링크 끝 참조)를 사용하여 원하는 쿼리 계획을 실행했습니다.

댓글

• 6 년이 지난 후에도이 답변에 제공된 링크는 여전히 ” 필요한 읽기 ” … 최신 버전도 12 월 ‘ 17입니다.

.NET 응용 프로그램은 기본적으로 비활성화 된 옵션으로 연결되지만 Management Studio에서는 기본적으로 활성화되어 있습니다. 그 결과 서버는 실제로 대부분 / 모든 프로 시저에 대해 2 개의 별도 실행 계획을 캐시합니다. 이것은 서버가 수치 계산을 수행하는 방법에 영향을 미치므로 절차에 따라 매우 다른 결과를 얻을 수 있습니다. 이것은 실제로 proc이 끔찍한 실행 계획을 제공받을 수있는 두 가지 일반적인 방법 중 하나 일 뿐이며 다른 하나는 매개 변수 스니핑입니다.

에 대해 좀 더 논의 해보세요.

댓글

• 이 대답. 하지만 수치 계산 주장에 대해 회의적입니다!
• @Martin : 분명하지 않은 것 같습니다. ARITHABORT ON으로 인해 SQL Server가 div / 0 또는 산술 오버플로 오류에서 오류가 발생한다고 말하고 있습니다. 꺼져있을 때는 계속 진행되며 어떤 이유로 든 모든 종류의 끔찍한 문제를 일으킬 수 있습니다.
• @Ben-예, 특별히 공격하고 싶지 않았습니다. ‘ 귀하의 대답은 SET 옵션을 변경하는 것이 매우 쉬울 것이라는 점을 지적했습니다. 더 나은 계획을 세우고이를 옵션 자체에 잘못이있는 것으로 잘못 진단했습니다. ‘ 링크에있는 사람이 ‘이 작업을 수행하지 않았다고 확신하지 않습니다.
• @Martin-아님 문제입니다. ‘ 당신이 나를 공격하고 있다고 생각하지 않았습니다. 내가 연결 한 다른 토론은 약간 불분명 할 수 있습니다. 뒷받침하는 증거를 제공하려고 한 것뿐입니다.
• @Martin 돌이켜 보면 당신이 맞다고 생각합니다.

나는 이것이 거의 확실하게 매개 변수 스니핑이라고 주장합니다.

SET OPTIONS가 이러한 방식으로 성능에 영향을 미칠 수 있다고 종종 언급되지만 귀하가 다음과 같은 경우를 제외하고는이 주장에 대한 신뢰할 수있는 단일 출처를 아직 보지 못했습니다. 인덱스 된 뷰 / 지속 된 계산 열을 사용합니다.

이 경우 (SQL2005 + 및 데이터베이스가 SQL2000 호환 모드에 있지 않은 경우 ). ARITHABORT 및 ANSI_WARNINGS OFF가 모두있는 경우 사용되지 않는 색인을 찾을 수 있습니다. 따라서 원하는 검색이 아닌 스캔이있을 수 있습니다 (지속 된 계산 결과를 사용할 수 없기 때문에 약간의 오버 헤드). ADO.NET은 방금 수행 한 빠른 테스트에서 ANSI_WARNINGS ON를 기본으로하는 것 같습니다.

in Ben의 대답 은 “서버가 수치 계산을 수행하는 방식”이 그렇지 않으면 1 초도 채 걸리지 않는 결과에 몇 분을 더할 수 있다는 것입니다. 성능 성능 문제를 조사 할 때 프로파일 러가 문제가되는 쿼리를 식별하는 데 사용되는 경향이 있다고 생각합니다. 이것은 관리 스튜디오에 붙여넣고 즉시 실행되고 결과를 반환합니다. 연결 간의 명백한 차이점은 ARITH_ABORT 옵션뿐입니다.

관리 스튜디오 창에서의 빠른 테스트는 SET ARITHABORT OFF가 켜져 있고 쿼리가 실행될 때 성능 문제가 재발하여 명백하게 케이스가 닫 혔음을 보여줍니다. 실제로 이것은 Gregg Stark 링크에서 사용되는 문제 해결 방법 인 것 같습니다.

그러나 이는 해당 옵션 세트를 사용하여 할 수 있다는 사실을 무시합니다. 결국 캐시에서 똑같은 잘못된 계획을 가져옵니다.

이 계획 재사용은 애플리케이션 연결이 사용하는 것과 다른 사용자로 로그인 한 경우에도 발생할 수 있습니다.

먼저 웹 애플리케이션에서 테스트 쿼리를 실행 한 다음 SET ARITHABORT OFF를 사용하여 관리 스튜디오에서 테스트 쿼리를 실행하여 테스트했으며 아래 쿼리에서 사용 횟수가 증가하는 것을 확인할 수있었습니다.

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) 

이 공유 pf 계획이 실제로 발생하려면 모든 계획 캐시 키가 동일해야합니다. arithabort 자체뿐만 아니라 실행중인 사용자에게는 동일한 기본 스키마 (쿼리가 암시 적 이름 확인에 의존하는 경우)가 필요하고 연결에는 동일한 설정.

여기에 계획 캐시 키의 전체 목록

이 파티에 늦었다는 것을 알고 있지만 향후 방문자에게는 Martin이 정확히 맞습니다. 동일한 문제가 발생했습니다. SP가 매우 느리게 실행되었습니다. .NET 클라이언트의 경우 SSMS의 경우 매우 빠르지 만 문제를 조사하고 해결하는 과정에서 Kenny Evitt가 Martin의 질문에 대한 의견에서 묻는 체계적인 테스트를 수행했습니다.

Martin의 쿼리에서 프로 시저 캐시에서 SP를 찾아 두 개를 찾았습니다. 계획을 살펴보면 실제로 하나는 ARITHABORT ON이고 다른 하나는 ARITHABORT OFF입니다. ARITHABORT OFF 버전에는 인덱스 검색이 있습니다. ARITHABORT ON 버전은 인덱스 스캔을 사용했습니다. 동일한 출력. 관련된 매개 변수를 고려할 때 인덱스 검색은 출력을 위해 수천만 개의 레코드를 조회해야했습니다.

캐시에서 두 프로 시저를 지우고 .NET 클라이언트가 SP를 다시 실행하도록했습니다. 동일한 매개 변수 (활동이 많은 고객에게 넓은 날짜 범위를 특징으로 함). SP가 즉시 돌아 왔습니다. 캐시 된 계획은 이전에 ARITHABORT ON 계획에 포함 된 것과 동일한 인덱스 스캔을 사용했지만 이번에는 계획이 ARITHABORT OFF에 대한 것이 었습니다. SSMS에서 동일한 매개 변수를 사용하여 SP를 실행하고 결과를 즉시 얻었습니다. 이제 인덱스 스캔으로 ARITHABORT ON에 대한 두 번째 계획이 캐시 된 것을 확인했습니다.

그런 다음 캐시를 지우고 좁은 날짜 범위로 SSMS에서 SP를 실행 한 후 즉각적인 결과를 얻었습니다. 동일한 출력이 이전에 스캔으로 처리 되었기 때문에 결과 캐시 된 계획에 인덱스 탐색이 있음을 발견했습니다 (ARITHABORT OFF로 원래 계획의 탐색이기도 함). 다시 SSMS에서 SP를 실행했는데 이번에는 동일한 넓은 날짜 범위로 원래 .NET 요청에서와 동일한 끔찍한 성능을 보았습니다.

요컨대, 차이는 아무 관련이 없습니다. ARITHABORT의 실제 값-이를 켜거나 끄면 어느 쪽의 클라이언트에서든 만족 스럽거나 끔찍한 성능을 얻을 수 있습니다. 중요한 것은 계획을 컴파일하고 캐싱하는 데 사용 된 매개 변수 값이었습니다.

While MSDN 은 ARITHABORT OFF 자체가 쿼리 최적화에 부정적인 영향을 미칠 수 있음을 나타냅니다. 테스트 결과 Martin이 올바른 것으로 확인되었습니다. 원인은 매개 변수 스니핑이며 결과 계획이 최적이 아닙니다. 모든 범위의 매개 변수에 대해.

댓글

• Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.의 의미가 무엇인지 궁금합니다. 계산 된 열과 뷰에서 인덱스를 사용할 수 없다는 얘기인지 (ANSI_WARNINGS도 꺼져있는 경우) 아니면 실제로 다른 효과가 있는지 여부입니다.
• 잘 모르겠습니다. ‘ ‘ MSDN의 누군가가 유사한 상황에 처해 ARTIHABORT를 ON으로 설정하고 성능이 개선 된 것을 확인한 다음 다른 사람들과 동일한 결론에 도달 한 경우인지 궁금합니다. 인덱싱 된 뷰와 계산 된 열에 관해서는 ‘ 명확하지 않습니다. 한 시점에서 INSERT, UPDATE 또는 DELETE 연산이 저장된 데이터 값을 수정하는 경우 SET 옵션에 특정 값이 있어야한다고 명시합니다. 다른 곳에서는 옵티마이 저가 인덱싱 된 뷰 또는 계산 된 열을 참조하는 ” 모든 쿼리 “에 대한 인덱스를 무시한다고 명시합니다. 둘 다 사실입니까, 아니면 실제로 ” 데이터를 수정하는 쿼리 “입니까?

이 문제가있었습니다. 사람들이 여기에서 말했듯이 근본 원인은 여러 쿼리 계획이며 그중 하나는 차선책입니다. 나는 ARITHABORT가 실제로 문제를 일으킬 수 있는지 확인하고 싶었습니다 (문제가있는 쿼리에는 매개 변수가 없으므로 매개 변수가 방정식에서 스니핑됩니다).

이것은 SQL Server 2008 일에 경험했던 것과 똑같은 문제를 상기시킵니다. 우리의 경우 갑자기 하나의 SQL 작업이 갑자기 느려지는 것을 발견하고 (보통 몇 초, 이제는 9 분 이상) 작업이 연결된 서버에 액세스해야합니다. 작업 단계에서 ARITHABORT on 설정을 추가했습니다. 문제는 며칠 동안 해결 된 후 반환되었습니다.

나중에 MS 지원으로 티켓을 열었지만 처음에는 둘 중 하나를 찾을 수 없었고 티켓은 매우 고위 PFE 팀으로 에스컬레이션되었습니다. 지원 PFE는이 문제를 파악하려고했습니다.

마지막 이유는 작업 단계를 실행하기위한 사용자 자격 증명이 연결된 서버 측에서 기본 테이블의 통계에 액세스 할 수 없으므로 실행 계획이 최적화되지 않기 때문입니다.

자세하게 사용자는 DBCC SHOW_STATISTICS 에 대한 권한이 없습니다 (사용자가 탁자). MSDN 에 따르면 SQL 2012 SP1 이후에이 권한 규칙이 변경되었습니다.

SQL Server 및 SQL 데이터베이스에 대한 권한

통계 개체를 보려면 사용자가 테이블을 소유해야합니다. 또는 사용자는 sysadmin 고정 서버 역할, db_owner 고정 데이터베이스 역할 또는 db_ddladmin 고정 데이터베이스 역할의 구성원이어야합니다.

SQL Server 2012 SP1은 권한 제한을 수정 하고 SELECT 권한이있는 사용자가이 명령을 사용할 수 있습니다. SELECT 권한이 명령을 실행하는 데 충분하려면 다음 요구 사항이 있습니다.

이 문제를 확인하려면 연결된 서버 측 인스턴스에서 프로파일 러를 실행하고 “오류 및 경고 섹션을 참조하세요.

이 경험이 커뮤니티에 도움이되기를 바랍니다.