쿼리 사이즈(길이) 에 따른 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
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34885
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17240
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1641
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 4028
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2323
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5460
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 4272
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1641
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 2026
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1532
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1364
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1703
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1294
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4915
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1297
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1121
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1245
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1132
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1205
» 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1362
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1750
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1303





XE Login