시간 경과에 따른 일관된 쿼리 성능
상당한 양의 데이터가 포함 된 SQL Server 데이터베이스에 대해 집중적 인 애플리케이션로드 (초당 수천 건의 작업)를 실행하고 있습니다. 일부 테이블에는 수십억 개의 행이 있으며 그중 일부에는 많은 삽입 및 업데이트가 있습니다.
DB 성능은 일반적으로 꽤 괜찮지 만 주기적으로 쿼리 성능 문제가 발생합니다. 이전에는 잘 작동했던 다소 간단한 쿼리가 갑자기 10-100 배의 시간이 걸릴 수 있습니다.
이것은 테이블 / 인덱스 통계 및 쿼리 옵티 마이저와 관련된 것으로 보입니다. 대부분의 경우 통계 업데이트로 문제가 해결되고 다른 경우에는 통계 업데이트로 상황이 더 악화됩니다 (통계 업데이트를 다시 실행하면 일반적으로 해결됩니다. 결국 문제).
어떤 일이 일어나고있는 것처럼 보이는 것은 옵티마이 저가 일부 쿼리에 대해 객관적으로 잘못된 인덱스를 사용하기로 결정한 것입니다. 며칠과 몇 주 동안 올바른 것을 사용한 후 갑자기.
제 질문은 다음과 같습니다. 왜 이런 일이 발생하며 이에 대해 무엇을 할 수 있습니까?
이 데이터베이스는 기본적으로 동일한로드, 거의 동일한 쿼리 및 동일한 양의 업데이트로 수년 동안 실행되었습니다. 99.995 %의 쿼리에 대해 입력에 관계없이 시간이 지남에 따라 다른 인덱스 전략을 결정할 이유가 없어야합니다 (실제로 그렇게하면 쿼리 성능이 완전히 파괴됨).
위에서 언급했듯이 일정에 따라 자동으로 통계를 업데이트하면 종종 끔찍한 문제가 발생합니다. 통계 샘플이 왜곡되면 (최소 5 % 이상 발생하는 것으로 나타남) 고통의 세계에 빠집니다.
통계 히스토그램과 밀도가 시간이 지남에 따라 변경되지 않을 것이라고 SQL Server (특정 테이블에서)에 알리는 방법이 있습니까?이 테이블과 관련된 쿼리에 대해 동일한 쿼리 계획을 계속 사용하십시오. 그렇지 않다면 시간이 지남에 따라 통계 업데이트의 예측 가능한 결과를 보장 할 수 있습니까 (위에 설명 된 왜곡 된 통계 문제 방지)?
저장 프로 시저가 없습니다. 우리는 SQL을 제어 할 수 있으므로 잠재적으로 변경 될 수 있지만 코드가 많아서 모든 단일 쿼리를 변경해야하는 경우 (예 : 추가 절 추가) 불행 할 것입니다.
후속 질문 : 매개 변수 스니핑은 저장 프로 시저와 관련된 것으로 보입니다. 맞습니까?
답변
먼저 통계인지 또는 매개 변수가 당신을 해치는 지 확인하는 것이 좋습니다.
위 의 내용 과 상관없이 주제에 대한 Erland의 기사 를 읽어 보시기 바랍니다 .
그것에 대해 무엇을 해야할지 말하기 어렵습니다. 우리는 그것이 통계인지 스니핑인지 모릅니다.
그러나 추가 OPTIMIZE FOR는 "해결책"이 될 수 있습니다. RECOMPILE각 실행에 대해 계획 제작 히트를 치를 필요가 없기 때문에 더 저렴 합니다. 그리고 예측 가능성을 제공합니다. 물론 이것은 통계가 너무 많이 다른 경우가 없기 때문에 동일한 매개 변수 입력이 통계 이유 때문에 다른 계획을 산출한다고 가정합니다.
하나의 쿼리를 식별하십시오. 쿼리에 대한 계획이 하나 이상 있는지 확인하십시오. 테스트와 OPTIMIZE FOR및 / 또는 RECOMPILE. 데이터베이스 규모에서 하나의 "글로벌"옵션은 데이터베이스에 대한 매개 변수 스니핑을 비활성화하는 것입니다. 이는 옵티마이 저가 값에 대한 단서가 없기 때문에 최적화를 의미합니다. 이 모든 것이 Erland의 기사에 있습니다.
매개 변수 스니핑은 저장 프로 시저에만 적용되는 것은 아닙니다. 또한 매개 변수가있는 SQL (일반적으로를 사용하여 실행 됨 sp_executesql) 에도 적용되며 오늘날 저장 프로 시저보다 훨씬 더 일반적입니다.
댓글에서 생성 된 답변
통계 업데이트 후 얻은 잘못된 통계로 인해 잘못된 쿼리 계획을 얻을 수 있습니다. 그러나 통계 업데이트 후 쿼리가 가져온 첫 번째 매개 변수가 평소와 같지 않은 경우 매개 변수 스니핑으로 인해 잘못된 쿼리 계획을 얻을 수도 있습니다. 어떤 문제가 제시되는지 질문에서 이해하는 것은 불가능합니다. 통계를 업데이트하는 대신 쿼리가 나빠지면 쿼리 계획을 다시 컴파일하여 두 가지 문제를 분리하십시오. – Denis Rubashkin
실행 계획의 "재 구축"을 유발할 수있는 많은 요소가 있습니다. 그래서 잠시 동안 잘 작동하고 갑자기 느리게 작동하는 이유를 설명합니다. 통계를 업데이트하면이 개체와 관련된 모든 실행 계획이 무효화되고 이로 인해 다음 실행에서 새 계획이 작성됩니다. 사용 된 값에 따라 문제가 해결되거나 해결되지 않을 수 있습니다 (대부분의 값은 문제를 해결하고 다른 값은 해결하지 않습니다. 이로 인해 언젠가는 작동하는 이유를 설명하고 때로는 그렇지 않은 경우도 있음).
실행 계획을 "수정"하는 또 다른 방법은 쿼리 저장소 (SQL Server 2016에서 시작된 것으로 생각됨)를 사용하고 사용할 계획을 "수정"하는 것입니다. 데이터가 많이 변경되면 (SQL Server가 더 나은 계획을 생성 할 수 없기 때문에) 몇 가지 단점이있을 수 있지만 그런 종류의 문제를 해결할 수 있습니다 (현재 2 년 이후 수정 실행 계획으로 실행중인 쿼리가 있고 이후 매개 변수 스니핑 문제가 없습니다.) – 도미니크 부쉐