소개
비용 기반 쿼리 최적화(Cost-based Query Optimizing)를 가진 현대의 대부분의 RDBMS에서 쿼리 최적화의 절대적 영향력을 가진 열 단위 정보가 바로 통계(Column Statistics)일 겁니다 (아래 참고 자료의 MS 문서 참조).
열 단위 통계 정보를 저장하는 통계 개체 또한 만들고(Create), 내용을 최신 데이터로 갱신하고(Update), 필요 시 삭제(Drop)하는 과정들이 필요하고 그것이 쿼리 최적화 시 통계 정보를 참조하는 작업과 충돌을 일으키면서 쿼리 성능 문제나 DB 전체 안정성 문제를 유발하기도 합니다. 특히 대용량 테이블을 참조하거나 동시 대량 쿼리 호출을 가진 경우에 경험할 수 있습니다.
일반적인 상황에서는 SQL Server의 자동화된 처리로도 특별히 문제가 없지만 대용량 테이블 대량 호출과 같은 특수한 조건이나 상황을 가진 경우에 다른 접근이 필요할 수 있고 이 때 DBA나 전문가의 손길이 미치는 시점이 됩니다. 그 중 하나가 바로 여기서 소개하는 열 통계 정보를 갱신(Update)할 때 동기식으로 할 것인지 비동기식으로 할 것인지 결정하는 것입니다(아래 참고 자료의 MS 문서 참조).
참고 자료
비동기 통계 업데이트 작업의 “차단” 문제
통계 업데이트 작업은 명령문을 써서 수동으로 할 수도 있지만 여기서 다루는 주제는 쿼리 실행 과정에서 필요에 따라 자동으로 수행되는 작업이다. “동기식“은 쿼리가 실행되는 해당 스레드에서 업데이트가 수행되므로 쿼리 처리가 일시 중지된다(이것이 디폴트 동작), 반면에 “비동기식“은 별도 스레드에서 백그라운드로 처리되는 방식이며 이것이 필요하면 DB 단위 옵션으로 추가 설정해야한다(위 MS 문서 참조)
일반적인 환경에서는 디폴트인 동기식을 사용하면 되지만 만일 통계가 자동으로 업데이트되는 시간이 오래걸리는 것이 확인되고 이로 인해 관련 쿼리의 대기 시간이 길어지면서 문제가 된다면 그 때 비동기 방식으로 처리하도록 DB 옵션을 추가 설정할 수 있습니다.
[그림-1] 기존 문제 – 원본: 위 MS 문서
이에 대한 자세한 설명은 위 MS 문서(“Improving concurrency of asynchronous statistics upate”)에 소개되어 있으니 SQL Server를 잘 아는 분이라면 직접 읽어보시기 바랍니다. 여기서는 쉬운 이해를 위해 간단하게 소개만 하겠습니다.
비동기식을 사용하면 통계 업데이터하는 동안 쿼리(ex. SELECT) 실행을 방해하거나 차단하는 등의 성능 상 문제가 없을 것으로 기대하지만 실제론 그렇치 않다는 것입니다, 아래 그림에서 볼 수 있듯이 업데이트하는 대상 열 통계 개체에 동시에 허용되지 않는 Sch-S와 Sch-M 잠금이 동시에 요구되는 상황에서 챗 바퀴 물리듯 대기(Waiting)가 발생할 수 있습니다.
SQL Server 2022 새로운 DB 범위 옵션, “ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY”
이는 SQL Server 2019까지 발생하는 현상입니다. 그런데 사용자 입장에서 보면 비동기식을 사용한다는 것은 결국 통계 정보가 갱신되는 동안 줄을 서지 않고 과거 통계를 계속 사용해서 쿼리를 최적화하겠다는 의사 표시이므로 이러한 동작은 원하지 않는다는 것입니다.
USE Northwind;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON
GO
SELECT *
FROM sys.database_scoped_configurations
WHERE name='ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY'
이에 SQL Server 2022(혹은 Azure SQL)에서 DB 범위 옵션의 하나로 “ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY”를 지원합니다. 이름 그대로 해석하면 되겠습니다, 위 그림-1에서 비동기로 통계를 업데이트 하는 중에 Query가 요구되면 먼저 처리되도록 우선 순위를 조정해서 뒷 줄로 옮겨가겠다는 것입니다, 아래 그림-2가 옵션이 적용한 경우의 변경된 동작으로 표현하고 있습니다.
[그림-2] 개선된 방식 – 원본: 위 MS 문서
그럼 계속 양보만 하다 보면 재는 언제 처리하나? 이런 생각이 들 수도 있죠, 문서에 의하면 Timeout 이 설정된다고 합니다 그러면 취소가 될 수도 있겠죠? 그 외 아직 모르지만 미묘한 이슈가 발생할 수도 있을겁니다만 앞으로 실제 상황들을 지켜봐야겠습니다.
자세한 설명과 테스트 가능한 코드는 위 마지막 MS 문서를 참조해 보시고 가능하면 직접 테스트해 보시면 이해에 더 도움이 되실겁니다. 문서에 달린 댓글들도 유익하니 같이 보시면 좋습니다.
정리
기존의 비동기 통계 업데이트 DB 옵션을 사용하던 사용자에게는 좋은 선택지가 되리라 생각됩니다 또한 SQL Server 2019 이하 버전에서도 이러한 옵션을 지원해주기를 개인적으로 바랍니다.
모두 건강하세요.
김정선 드림
