데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1578
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2220
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2502
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1716
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1542
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1870
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4476
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1271
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1189
» SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1327
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1636
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1761





XE Login