SWITCHOFFSET 내장함수의 잘 못된 예측

  • 미리 계산한 값을 쿼리에 연결하여 최적화 하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

이번 사례는 SQL Server CSS Engineer 공식 팀블로그에 기재된 내용으로 SQL Server의 내장 함수인 SWITCHOFFSET 함수 사용으로 잘못된 예측으로 인한 성능 문제이다.

 

[SWITCHOFFSET 함수]

저장된 표준 시간대 오프셋에서 지정된 새 표준 시간대 오프셋으로 변경된 datetimeoffset 값을 반환.

CREATE TABLE dbo.test (

ColDatetimeoffset datetimeoffset

);

GO

 

INSERT INTO dbo.test VALUES ('1998-09-20 7:45:50.71345 -5:00');

GO

 

SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00') FROM dbo.test;

GO

--Returns: 1998-09-20 04:45:50.7134500 -08:00

 

SELECT ColDatetimeoffset FROM dbo.test;

GO

--Returns: 1998-09-20 07:45:50.7134500 -05:00

 

 

[잘못된 예측으로 인한 성능 저하]

블로그에 기재된 내용을 요약하면 기간 검색으로 데이터를 조회 하는데 쿼리의 실행 속도가 매우 느리다고 한다. 그래서 쿼리를 확인해 보니 다음과 같은 형식의 쿼리가 실행 되고 있었다,

select * from tbl_x where c1 > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

 

SQL Server는 많은 built=in/intrinsic 기능이 있다. 옵티마이저는 쿼리를 컴파일 하는 동안 엿보기(peek)를 사용하여 더 나은 추정치를 제공하는 기능을 실행에 사용 할 수 있다.

예를 들어 쿼리에서 select * from t where c1 > getdate() 같은 값을 사용하였다면 옵티마이저는 getdate() 값을 가져온 다음 히스토그램을 사용하여 최대한 정확한 비용 산출을 하려 할 것이다.

 

DATEADD 함수 또한 최적화 트릭을 할 수 있는 내장 함수 이다. 그러나 SWITCHOFFSET는 옵티마이저가 엿볼 수 없기 때문에 최적화 트릭을 사용할 수 없다.

 

이 문제를 해결 하기 위해서 GETDATE()와 SWITCHOFFSET를 사용하는 경우 선언문을 통해서 미리 값을 계산해 놓고 쿼리에 연결하면 좋다.

--before

select * from tbl_x where c1 > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

 

--after

declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

select * from tbl_x where c1 > @dt option (recompile)

 

 

[AdventureWorks 예제]

set statistics profile on

 

--before

select * from Production.ProductCostHistory where StartDate > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

 

--after

declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')

select * from Production.ProductCostHistory where StartDate > @dt option (recompile)

 

set statistics profile off

 

 

 

[참고자료]

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 23409
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 12412
» SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1419
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1306
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1708
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1838
1810 DMV를 이용한 CPU 사용량 높은 쿼리 찾기 jevida(강성욱) 2016.09.29 4792
1809 DMV를 이용한 인덱스 크기 및 조각화 정보 반환 jevida(강성욱) 2016.09.29 1211
1808 Checkpoint 추적하기 jevida(강성욱) 2016.09.29 1335
1807 중복 인덱스와 성능(Duplicate Indexes with Performance) jevida(강성욱) 2016.09.29 2433
1806 823, 824, 825, 832 오류 (DISK IO 오류) jevida(강성욱) 2016.09.29 2270
1805 DISK I/O 병목 확인 jevida(강성욱) 2016.09.29 4366
1804 SQL Server 2012에서 비상계정 생성하기 - 비밀번호를 잊어 버렸을 경우 대처하기 jevida(강성욱) 2016.09.29 1523
1803 SQL Server 차단 최소화 jevida(강성욱) 2016.09.29 1188
1802 자주 사용되는 System 함수 jevida(강성욱) 2016.09.29 1099
1801 프로시저와 임시테이블, 그리고 리컴파일 jevida(강성욱) 2016.09.29 2531
1800 access check cache 크기에 따른 성능 문제 jevida(강성욱) 2016.09.29 1128
1799 Hot Add CPU jevida(강성욱) 2016.09.29 889
1798 스레드 및 파이버 실행 jevida(강성욱) 2016.09.29 1084
1797 CPU에 스레드 할당 및 lightweight pooling 옵션 사용 jevida(강성욱) 2016.09.29 1813
1796 스레드 및 태스크 아키텍처 jevida(강성욱) 2016.09.29 1503
1795 메모리 관리 아키텍처 – NUMA 버퍼 풀 증가 및 축소 jevida(강성욱) 2016.09.29 1271





XE Login