XKCD 클래식의 DBA 버전.

경우를 식별하는 매개변수 검색,문제해야하는지 여부를 테스트를 구현하’다시 컴파일’는 데 도움이됩니다 힌 계획 품질입니다. (매개 변수 스니핑이 무엇인지 확실하지 않습니까? 이 블로그 게시물 또는이 30 분 무료 비디오에서 배우십시오.)당신은 결정해야합니다:어떤 힌트 나 명령을 사용합니까,그리고 당신은 그것을 어디에 둡니까? 미래에 sql Server 를 성능 조정 할 수있는 것에 관해서 당신은 어떤 절충안을 만들고 있습니까?

이 게시물은 sql Server 를 새 실행 계획을 생성하도록 유도(또는 구타)해야하는 일반적인 옵션을 통해 실행됩니다. 나는 각 방법에 대해 몇 가지 장단점을 제시하고 무엇이 유용하고 피할 가치가있는 것을 설명 할 것입니다.

유지하는 것이 상대적으로 간단하다,나는 방법을 논의 이용을 저장 프로시저는 오늘 이 게시물을 커버하지 않는 다른 형태의 매개 변수화(또는 비 매개 변수가 있는)쿼리를 처리합니다. 당신이 그 분야에서 큰 질문을 가지고 있다면,코멘트에 미래의 게시물을 위해 그것을 제안 주시기 바랍니다.

면책 조항:재 컴파일 힌트는 잘못 사용될 때 화재에 Cpu 를 조명하여 성능을 죽일 수 있습니다. 주의해서 다루십시오!

유용한 동적 관리 보기

이야기할 때에 미치는 영향에 대해 실행 계획 캐시 I 참조 두 Dmv:

• sys.가져오기–이 DMV 도움이시 상단에 문의 SQL 서버는지 여부에 관계없이 그 절차의 일부 또는하지 않습니다. 여기에서 샘플 쿼리를 확인하십시오.
• sys.dm_exec_procedure_stats-이 DMV 는 sql Server2008 이상에서만 사용할 수 있습니다. 저장 프로 시저에 대한 실행 메트릭을 추적합니다. 온라인 책에서 샘플 쿼리를 확인하십시오.

이 두 Dmv 모두에 대해 캐시에 실행 계획이 있으면 총 실행 수,총 및 평균 CPU,논리 읽기 등의 실행 메트릭을 볼 수 있습니다. 을 때 실행 계획에서 제거 캐시으로 인해 다시 컴파일을 메모리에 압력,재시작,또는 다른 행동,관련된 실행을 측정뿐만 아니라 제거됩니다.

실행 절차와 런타임을 다시 컴파일한 힌트

중 하나 옵션을 사랑하는 것에 대한 빠르고 쉽게 테스트를 호출 할 수있는 기능을 사용하여 저장 프로시저를 다시 컴파일에서 힌트를 실행한다. 컴파일 된 코드를 변경하지 않아도되므로 좋습니다. 당신의 힌트는 또한 당신이 런타임에하고있는 일에만 적용됩니다.이렇게하려면 다음과 같은 구문을 사용하면됩니다:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. 당신은 여전히 얻을 수 있습니다 매개 변수는 냄새에서 모든 호출을 하는 절차에 저장된 절차 자체입니다.

하는 동안 그래서 나는 이처럼 때로는 테스트를 위해,당신은 어떤 중첩에 당신의 저장된 절차 그것은 도움이 되지 않을 수 있습니다 당신은 정말 신선한 실행 계획을 실행합니다.

를 사용하 sp_recompile 시스템에 저장된 절차

이것은 때때로 편리해야 하는 경우 개입하는 동안 생산 사건이 있는 경우 나쁜 계획에 사용되는 반복적으로 들어오는 세션이 있습니다. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

이행과 관련된 계획은 즉시 제거에서 SQL 서버의 실행 계획 캐시입니다. 대신 마법은 다음에 다시 컴파일 된 객체를 참조하는 쿼리가 실행될 때 발생합니다. 그 시점에서 sys.dm_exec_query_stats 의 기존 실행 통계는 계획의 문에 대해 재설정됩니다.

실행에서 계산합니다.sys.dm_exec_procedure_stats 지는 않을 것입니다 다시 설정에 다음을 사용하는 경우에 당신을 표시하는 데 사용되는 테이블에 저장되는 절차이다. 그러나 재 컴파일을 위해 저장 프로 시저 자체를 표시하면 다음 사용시 재설정됩니다. (이것을 외우려고 귀찮게하지 말고 다음 단락을 읽으십시오.)

이 하나에는 큰 단점이 있습니다. 이 명령은 높은 권한이 필요합니다.이 명령을 실행하는 사용자는 테이블에’alter’권한이 필요합니다. 또한 완료하기 위해 높은 수준의 잠금 장치가 필요합니다. 바쁜 시스템에서 나는이 명령이 불쾌한 차단 체인에 참여하는 것을 보았습니다. 그것에주의하십시오!이것이 때때로 문제 해결 및 테스트에 유용 할 수 있지만 프로덕션 코드의 일부로 만들지 마십시오.

다시 컴파일에서 힌트를 저장 프로시저 머리글

가지고 있을 때는 절차를 생성하고 싶은 신선한 계획,보고를 시작하는 방법에 힌트를 사용하여 내에서 절차입니다. 대부분의 사람들이 먼저 발견하는 옵션은 다음과 같이 저장 프로 시저의 헤더에’재 컴파일과 함께’를 사용하는 것입니다:

를 사용하여 다시 컴파일에 저장 프로시저는 헤더가 매우 급격한 절차지 않을 것 캐시 실행 계획을 때 실행됩니다. 이는 다음을 의미합니다.

• 실행 계획에 캐시을 검토
• 실행 통계 기록에 sys.가져오기
• 실행 통계 기록에 sys.dm_exec_procedure_stats

와우,다. 필요할 경우 영향도를 파악하기 위하여 이 절차는 서버에서,당신은 당신을 실행하는 어떤 종류의 흔적 또는 확장된 이벤트션 및 수확하고 해석하는 결과입니다. 즉,빠르지 않으며 실행중인 흔적이 성능에 영향을 줄 수 있습니다.

큰 음의 종류. 그런 이유로,나는이 선택을 전혀 좋아하지 않는다.

다시 컴파일에 대한 힌트 개별 문

이 옵션을 조금 더 일이지만,그것은 훨씬 더 좋은 보수를 통해 시간. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

도를 사용해야 하는 경우에 힌트에 있는 모든 문서의 절차를 보다는 오히려 머리글에서,이전의 혜택! Proc 의 문 수준에서 재 컴파일 힌트를 넣으면 마술처럼 얻을 수 있습니다:

• 제한 실행 계획을 캐시을 검토하(마지막으로 실행)
• 제한 실행은 통계 기록에 sys.가져오기. 당신은 통계를 얻을 위한 마지막 실행하지만,plan_generation_num 열가 증가,적어도 당신 통찰력하는 무언가이다.
• 실행을 통계 기록에 sys.dm_exec_procedure_stats

는 사실 당신은 몇 가지 정보에서 이러한 Dmv 할 수 있는 유용합니다. 을 적용하 힌트를 다시 컴파일만 문는 그들이 필요한 더 많은 책임–그것은 귀하의 위험을 낮추고 타는 자신이 시간이 지남에 따라 CPU 구울 수 있습니다.

DBCC FREEPROCCACHE–핵 옵션

이것은 엄격하게 재 컴파일 힌트가 아니지만 확실히 재 컴파일이 발생합니다. SQL Server 에는”신선한 실행 계획으로 새롭게 시작하십시오”라고 말하는 데 사용할 수있는 명령이 있습니다. 일반적으로 다음과 같이 사용됩니다:

This command makes everything start fresh– for every query:

• CPU 사용량 까지 갈 수 있으로 새로운 계획을 컴파일된
• 실행을 통계는 삭제에 sys.가져오기(바로)
• 실행을 통계는 삭제에 sys.dm_exec_procedure_stats(바로)

이 명령하지 않 악 거기에 시간과 장소에서 문제 해결에 그것을 사용할 때 얻을 수 있습의 근본 원인 성능 문제를 신속하게. 그러나 전체 캐시에 영향을 미치고 성능에도 영향을 줄 수 있으므로이를 사용하는 데 매우 신중해야합니다. 정규 작업,자동화 된 프로세스 또는 프로덕션 코드에서이 명령을 사용하지 않는 것이 좋습니다.

으로 SQL Server2008,이 명령을 사용할 수 있습니다 조금 더 부드럽게 제거할 수 있습니다 구체적인 계획에서 캐시를 사용하여 계획을”처리”또는”sql 처리”–지만,물론 당신은 무엇을 파악하는 손잡이가고 싶은 이유를 제거합니다. 이것은 일부 틈새 상황에서 유용하게 올 수 있지만 실제로는 많이 나오지 않습니다. 리소스 주지사 풀을 지울 수도 있지만 리소스 주지사를 사용해야 할 수도 있습니다.

모두 무슨 뜻입니까?

테스트 할 때 툴킷에’RECOMPILE 이있는 EXEC 프로 시저’와 sp_recompile 프로 시저 모두에 대한 장소가 있습니다. 주의해서 사용하고 특히 sp_recompile 과 함께 가능한 반향에주의하십시오.

때 당신을 구현하는 힌트 코드에서 없어 가리개를 다시 컴파일에 사용할 수 있는’최적화’에 대한 힌트를 때로는 성공적으로뿐만 아니라(지만 때로는 최적화하고 있는 평범한). 그리고 때때로,동적 sql 도 당신을 도울 수 있습니다.

그러나 재 컴파일 힌트를 사용하는 경우 재 컴파일을 명령문 수준으로 유지하고 프로 시저의 헤더에 보관하지 마십시오.

자세한 내용을 보려면 매개 변수 스니핑 클래스의 기본 사항을 확인하십시오.