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

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

쿼리 사이즈(길이) 에 따른 CPU 사용량 증가

 

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

 

쿼리의 사이즈(길이)에 따라 CPU 소요량이 증가 할까?

정답은 증가한다. 물론 SP를 사용하거나 쿼리 플랜을 재사용 할 수 있다면 처음 컴파일 시간을 제외한 나머지는 순수 처리 시간에 따라 달라지겠지만 Ad-hoc 쿼리를 사용한다면 쿼리를 컴파일 하는데 걸리는 시간으로 인하여 쿼리가 길어 질수록 CPU 사용량이 증가하며 최종 반환 속도 또한 컴파일 시간만큼 느려진다.

 

다음 테스트를 통하여 쿼리 길이에 따라 CPU 사용량이 증가 하는 것을 확인해 보자.

[테스트 사양]

  • OS : Windows Server 2008STD (VM)
  • SQL : SQL Server 2008R2 STD
  • RAM : 8G
  • CPU : I5 750 4Core
  • DISK : RAID 0 ( 1TB * 5 , 7200RPM)

 

테스트 테이블을 생성

create table t1000 (

c1 int not null constraint test_pk primary key,

c2 varchar(10) not null,

c3 char(1000),

c4 int not null,

c5 int not null,

c6 int not null,

c7 int not null,

c8 int not null

)

go

 

 

 

20만건의 데이터를 생성

set nocount on

 

declare @i as int

 

set @i = 0

 

while @i<200000

 

begin

 

set @i = @i + 1

 

insert into t1000 (c1, c2, c3, c4, c5, c6, c7, c8)

values (@i,

cast (@i as varchar (10)),

'...simulating additional 1k data...',

@i, @i, @i, @i, @i)

 

end

 

set nocount off

go

 

 

 

테스트 쿼리를 생성하기 위하여 다음의 스크립트를 실행 한다. 아래 스크립트는 테스트 코드를 자동으로 생성해 주는 기능을 한다.

create PROCEDURE spWriteStringToFile

(@String Varchar(max), --8000 in SQL Server 2000

@Path VARCHAR(255),

@Filename VARCHAR(100)

)

AS

 

DECLARE @objFileSystem int

,@objTextStream int,

@objErrorObject int,

@strErrorMessage Varchar(1000),

@Command varchar(1000),

@hr int,

@fileAndPath varchar(80)

 

set nocount on

 

select @strErrorMessage='opening the File System Object'

 

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

 

Select @FileAndPath=@path+'\'+@filename

 

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

 

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'

 

, @objTextStream OUT, @FileAndPath,2,True

 

if @HR=0 Select @objErrorObject=@objTextStream,

 

@strErrorMessage='writing to the file "'+@FileAndPath+'"'

 

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

 

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'

 

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

 

if @hr<>0

 

begin

 

Declare

 

@Source varchar(255),

 

@Description Varchar(255),

 

@Helpfile Varchar(255),

 

@HelpID int

 

EXECUTE sp_OAGetErrorInfo @objErrorObject,

 

@source output,@Description output,@Helpfile output,@HelpID output

 

Select @strErrorMessage='Error whilst '

 

+coalesce(@strErrorMessage,'doing something')

 

+', '+coalesce(@Description,'')

 

raiserror (@strErrorMessage,16,1)

 

end

 

EXECUTE sp_OADestroy @objTextStream

 

EXECUTE sp_OADestroy @objTextStream

GO

 

 

 

위의 스크립트가 정상적으로 실행 되기 위해서는 OLE 자동화 사용을 활성화 해야 한다.

EXEC sp_configure 'Ole Automation Procedures', 1

 

RECONFIGURE WITH OVERRIDE

 

GO

 

 

 

테스트 테이블 및 데이터가 잘 생성되었는지 확인 하자. 쿼리의 Where절은 길지만 결과는 1개의 행만을 반환한다.

select top 1 c1

from t1000

where c1 > 0

or (c5 = 1)

or (c6 = 2)

or (c7 = 3)

or (c8 = 4)

or (c4 = 5)

or (c5 = 6)

or (c6 = 7)

or (c7 = 8)

or (c8 = 9)

or (c4 = 10)

GO

 

 

 

위에서 생성한 SP를 이용하여 테스트 코드를 생성하자. @num_of_ors 의 값에 따라 쿼리의 사이즈(길이)를 조절 할 수 있다.

set nocount on

 

declare @i as int

 

declare @sql_stmt as varchar(max)

 

declare @num_of_ors as int

 

set @num_of_ors = 5000

 

set @i = 0

 

set @sql_stmt = 'select top 1 c1 from t1000 where c1 > 0'

 

while @i<@num_of_ors

 

begin

 

set @i = @i + 1

 

set @sql_stmt = @sql_stmt +

 

' or (c'

 

+

 

cast

 

(

 

@i%5+4

 

as varchar(10)

 

)

 

+

 

' = '

 

+

 

cast

 

(

 

@i as varchar(10)

 

)

 

+

 

')'

 

end

 

set nocount off

 

execute spWriteStringToFile @sql_stmt, 'c:\temp\', 'query.sql'

 

print 'Done.'

 

go

 

 

 

다음과 같이 스크립트 파일이 생성되며 스크립트를 열어 보면 다음과 같이 Where절이 긴 쿼리문을 확인 할 수있다. 물론 결과는 1개의 행만을 반환 하도록 되어 있다.

 

 

다양한 크기의 쿼리를 생성 하였다. 쿼리는 첨부파일의 텍스트 파일을 참고 한다. 정확한 테스트를 위하여 쿼리를 실행 하기전 플랜캐시와 버퍼캐시를 초기화 하도록 한다.

dbcc dropcleanbuffers

dbcc freeproccache

go

 

SET STATISTICS IO ON

go

 

SET STATISTICS TIME ON

go

 

SET STATISTICS PROFILE ON

go

 

 

[Query Size : 145K, Where 절 : 5000개]

 

 

 

[Query Size : 292K, Where 절 : 10000개]

 

 

 

[Query Size : 448K, Where 절 : 15000개]

 

 

 

[Query Size : 604K, Where 절 : 20000개]

 

 

 

[쿼리 길이에 따른 테스트 결과]

 

 

동일한 결과를 반환함에도 쿼리 길이에 따라 CPU 사용량이 증가하는 것을 확인 할 수 있다. 쿼리를 작성 할 때 조건이 너무 복잡하지 않게 그리고 짧게 만드는 것이 CPU 시간을 줄이는데 큰 도움을 줄 수 있다는 것을 확인 할 수 있다. 가장 좋은 것은 최대한 컴파일이 일어나지 않도록 플랜을 재사용 하도록 유도하는 것이다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1500/lengthy-sql-server-queries-will-consume-your-cpu/

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp


No. Subject Author Date Views
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5164
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3721
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1584
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1714
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1587
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3710
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1075
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1134
» 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1304
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1669
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1427
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1067
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959





XE Login