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

쿼리 사이즈(길이) 에 따른 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/

 

2013-03-07 / 강성욱 / http://sqlmvp.kr