SQL server 가 느리면? procedure가 갑자기 느려지면? 잘못된 플랜으로 돈다면?

SQL server 가 느리면? procedure가 갑자기 느려지면? 잘못된 플랜으로 돈다면?

문제의 원인은? parameter sniffing 때문이다.. 오라클에 익숙한 관리자라면 bind peeking을 생각하시면 된다

Sql server 의 경우 where 절에 전달이 되는 parameter 값에 따라 플랜이 생성되고 그 생성된 플랜은 cost가 적절하다면 계속 유지된다

근데? 왜 갑자기 성능이 안나올까????? 플랜은 옵티마이져가 잘 짰을텐데?????

생각해보자 왜 옵티마이저가 바보도 아니고 잘못된 플랜을 만들거나 효율적이지 못한 플랜을 계속 재사용 할까?

이유는 잘못된 통계정보, 적절한 index가 생성되어 있지 않는경우 로 보면 될것 같다

그럼? 어떻게 해결하지? 

위의 쿼리를 보면

ProductID에 따라 다른 plan을 보여주고 그에 따른 cost역시 다르다

친절한 SQL server씨는 3번째 값을 넣은 쿼리에 대해서 인덱스가 없으니 생성하시라고 친절하게 생성 구문까지 준다

불친절한 Ora모씨와는 다른 반응이다.. ㅋㅋ

인덱스 생성 구문을 보자

플랜에서 오른쪽 버튼을 누르고 (누락된 인덱스 세부 정보(M)...)을 눌러준다

아래와 같이 생성 구문과 함께 쿼리 비용 향상비율을 %단위로 표시하고 있다

DB쪽 담당자라면!!! 위 인덱스를 생성해주시는것이 좋다

하지만 지금은 전부 개발이 끝나서 납품된 수정이 불가한 어플리케이션의 관점으로 바라보겠다

자!

그럼 프로시져로 만들어서 얼마나 개떡같이 작동하는지 보도록 하자

parameter sniffing test2 프로시져 PS_test2 를 아래와 같이 만들고

sniffing이 동작하도록 870조건을 먼저 조회해서 잘못된 플랜이 생성되게 한 이후

897을 줘서 실행을 한다

보이는가???

한결같이 위에서 본 3개의 플랜중 가장 좋지 않은 플랜으로 저장되어 지속적으로 돌아간다

플랜이 재생성 되기 전까지는 성능이 폭망한다

그러면 어떻게 해결할까?

처음 실행되는 parameter 값을 최적의 성능을 보여주는 값으로 해서

플랜이 최초로 생성될때 돌려준다!

자......... 가능한가?... 가능하다면 어떻게 해야하는가???

맞다... 저건 말도 안되는 일이다..

그럼 어떻게 해야하지???

1. 프로시져를 실행할때마다 플랜을 재생성 하도록 프로시져를 recompile 한다

2. 힌트을 줘서 죄적값으로 돌릴수 있도록 특정 값을 임의로 준다

3. 모든 플랜정보를 날려버리고 신선하게 새로 생성한다

1. recompile 하는경우

test3의 경우 with recompile 옵션을 주고 프로시져가 실행될때 마다 recompile을 해서 플랜을 새로 생성하게 조치하였다 (DBA 및 DB단에서 처리시)

난 개발자인데...... DB 담당자랑 싸워서 요청하기 싫은데;;;;;;;;; 하시는 개발자들은 아래와 같이 with recompile 을 붙여주도록 하자 (개발자 및 application단에서 처리시)

2. 힌트를 줘서 처리하는 경우

OPTION (OPTIMIZE FOR (@ProductID=897)); 라고 힌트를 주면?

897이 들어가면 생성되는 플랜으로 항상 동작 한다

3. 모든 플랜정보를 날리고 재생성

추천하지는 않지만 어디서 발생되는지 추적이 불가하고 추적따위 하고 싶지 않다면 모든 플랜을 날려버리고 재생성한다

단점은 처음 돌리는 파라메터가 최악의 성능을 보여주는 파라메터로 들어간다면 성능개선이 되지않기 때문에

여러번 수행하여야 할수 있다

그리고 모든 플랜이 재생성 되므로 CPU사용률이 올라간다

DBCC FREEPROCCACHE

GO

방법을 적다보니 정작 중요한 인덱스 생성이 빠졌다...

parameter sniffing이 뭐때문에 발생한다? 인덱스의 부재, 통계정보의 부정확함..

그래서 위에서 언급된 인덱스를 생성해서 아래와 같은 결과를 봤다

이렇게 멋진 cost를 볼수 있다

기존 Cost를 다시 보면?

비용이 엄청나게 줄어든다..

결론

parameter sniffing에 안빠지게 하려면 옵티마이저에게 정확한 정보를 전달하라 (통계 및 인덱스)

인덱스생성이 불가피 하다면 위에 언급된 방법을 사용한다..