안녕하세요.
쿼리속도 이상증상 관련하여 질문 드릴까 합니다.
현재 데이터베이스는 MS Sql 을 사용중입니다.
아래 사용중인 테이블들은 데이터갯수 합쳐봤자 백만개도 못미치는 몇십만개 수준입니다.
어느 날, 5초에서 10초 안팎으로 결과가 나오던 쿼리가 어느날 갑자기 5분이 걸리는 일이 발생했습니다.
담당 개발자가 수정을 하였나 싶어서 확인 요청 했었으나
소스 쿼리 둘다 바꾸지 않았으며, 느려진 원인을 확인하다 결국 당일 해결을 못하였습니다.
다음날 아침에 오니 느리던 쿼리가 다시 정상 속도로 돌아왔습니다.
그러다 2일 뒤에 똑같은 현상이 몇시간 나타났다가 또 다시 사라졌습니다.
그 뒤 2~3주 지난 오늘,
또 다른 쿼리가 이상증상이 나타났습니다
몇초 안에 나오던 쿼리가 10분 기다려도 결과가 안나오길래 kill 하였고
해당 쿼리 일부분 짤라서 돌려봐도 0.1초만에 나오던 쿼리가 40초 만에 돌아가는 등의 증상이 있습니다.
이상 증상이 있을 당시에 디비나 네트웍 트래픽이 심했다거나 하는 이상은 없었습니다. (평소와 같은 환경)
DB문제인지 네트웍 문제인지... 어디부터 확인해봐야 할지 감을 못잡아서 질문을 올립니다.
저도 개발자로 근무중 이지만, 어쩌다 보니 디비관리, 네트웍관리도 하게 되었습니다. ㅠ.ㅠ
다른 DBA 분들만큼 아는 용어나 스킬이 부족하겠지만, 짐작 가시는 부분 힌트라도 주시면 감사하겠습니다.
'느려지는 경우가 발생한다' 외의 정보는 없으므로
1. 평상시의 실행계획을 기록하고 느려진 상황에서의 실행계획을 비교해 봅니다.
https://docs.microsoft.com/ko-kr/sql/relational-databases/performance/display-and-save-execution-plans?view=sql-server-2017
평소 빠른 경우의 실행계획을 xml로 저장해 두시면 나중에 꺼내 볼 수 있습니다.
2. 느려진 상황에서 lock 상태를 확인합니다.
https://docs.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017
3. xevent를 이용하여 성능 모니터링을 수행합니다.
https://docs.microsoft.com/ko-kr/sql/relational-databases/extended-events/use-the-ssms-xe-profiler?view=sql-server-2017
4. xevent는 비교적 나중에 나온 기능이라 참조할 문서가 훨씬 많은 sql profiler를 이용하여 모니터링 합니다.
https://docs.microsoft.com/ko-kr/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017
LOCK이 가장 의심 됩니다.
MS-SQL는 기본 설정에서
select에서도 LOCK이 발생합니다.
select 범위에 insert나 update가 발생하면 select 도 lock이 해제될때 까지 기다리게 됩니다.
(오라클과 동작 방식이 다름)
프로그램을 DB특성을 고려하지 않고 개발했다면
deadlock 상황이 발생할 가능성이 높습니다.
목록 출력등 트랜잭션이 필요 없는
select 쿼리에 with(nolock) 힌트를 주게
프로그램을 수정해 보세요.
답변 감사합니다. 관련된 정보 찾아본 후 조금 더 공부해 보겠습니다.
