728x90
제 1강 : Update 되는 Top ~ Order by 뷰 및 CTE를 이용한 접속로그 테이블 관리

이름 : 최석준
Email : beatchoi@gmail.com
CJIG ANIMA 개발실 DBA

로그인 로그아웃 시간을 저장하는 방법은 많이 있다. 기능을 소개하기 위해서 간단한 접속로그 테이블의 예를 통해서 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 한다.

유저의 로그인 로그아웃 시간을 저장하는 다음과 같은 구조의 접속로그 테이블이 있다.

if object_id('ConnectLog','U') is not null
drop table ConnectLog
create table ConnectLog (
id varchar(10),
login datetime,
logout datetime
)

리스트 1 접속로그 테이블 만들기

이때 조건은 유저는 중간에 어플리케이션등의 오류로 인해 로그아웃이 기록되지 않은 채 로그아웃이 될 수도 있다. 가장 최근에 접속한 행을 업데이트 하는 문제이다.

declare @i int
set @i = 0
while @i < 365
begin
insert into ConnectLog
values('beatchoi',
        dateadd(ss,rand()* 1440,dateadd(d,@i,'2006-01-01')),
        case when @i = 364 then null
                when convert(int,rand() * 100) % 100 = 1 then null
            else dateadd(ss,(rand()+10) * 1440,
                    dateadd(d,@i,'2006-01-01'))
        end)
set @i = @i + 1
end

리스트 2 접속로그 테이블에 더미 데이터 입력

위와 같이 테이블에 1년간의 더미 데이터를 입력한다.

다음과 같이 많은 개발자들이 MSSQL2000 에서 정렬을 갖거나 행수 제한을 하기위한 뷰를 구현하기 위해서 Top ~ Order By 를 가진 정렬된 뷰를 생성을 할 수 있었다.

create view vw_ConnectLog
as
select top 1 Id,Logout
    from ConnectLog
  order by Login desc

리스트 3 Top ~ Order By 를 가진 정렬된 뷰만들기

MSSQL2000에서는 Top ~ Order By 포함하는 가진 뷰를 생성할 수는 있지만 업데이트시 다음과 같은 4427 에러를 발생시키고 업데이트는 할 수 없다.

update vw_ConnectLog
   set Logout = getdate()
  where Id = 'beatchoi'

서버: 메시지4427, 수준16, 상태1, 줄1
뷰또는함수'vw_ConnectLog' 정의에TOP 절이있어서업데이트할수없습니다.

리스트 4 Top ~ Order By를 가진 정렬된 뷰 업데이트 시 오류


UPDATE 가능한 MSSQL 2000 의 TOP ~ ORDER BY 를 포함하는 VIEW

Microsoft Certified Trainer (MCT)인 Zoltan Kovacs가 발견한 업데이트 가능한 정렬된 뷰를 생성할 수 있도록 해 주는 백도어가 있다. 이 기법은 Openquery() 함수내의 Select 쿼리에 Order By 절을 지정하는 것이다.

백도어를 이용하는 방법은 다음과 같다. Openquery () 를 호출하여 자기 자신 서버에 대하여 쿼리를 실행하기 위해서는 우선 Data Access 서버 옵션을 설정하는 것이 필요하다.

exec sp_serveroption '[원격서버명]','data access', true

create view vw_ConnectLog_Backdoor as
SELECT a
FROM OPENROWSET('SQLOLEDB','[원격서버명]';'[로그인]';'[암호]'
    'SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login') AS a

update vw_ConnectLog_Backdoor
    set Logout = getdate()
  where Id = 'beatchoi'

서버: 메시지7320, 수준16, 상태2, 줄1
OLE DB 공급자'SQLOLEDB'에대해쿼리를실행할수없습니다. 공급자가필요한행조회인터페이스를지원할수없습니다. 공급자가다른속성또는요구사항과의충돌이발생했음을나타냅니다.
[OLE/DB provider returned message: 여러단계OLE DB 작업을하는동안오류가발생했습니다. 각OLE DB 상태값이있으면확인해보십시오. 완료된작업이없습니다.]
OLE DB 오류추적[OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e21: SELECT top 1 * FROM tempdb.dbo.ConnectLog ORDER BY login[PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING]].

리스트 5 MSSQL2000 백도어를 이용한 업데이트 되는 Top ~ Order By를 가진 뷰

업데이트를 실행하면 위와 같은 오류가 발생하는데, 이는 SQL Server OLE DB 공급자는 UPDATE 또는 DELETE 작업을 위한 기본 테이블에 고유한 색인을 필요로 하기 때문이다. 고유한 색인이 원격 테이블에 없을 경우 Update나 Delete를 시도하면 다음 오류가 발생하며, OpenQuery와 이름이 4부분으로 된 Update와 Delete작업 모두에 적용된다. 원격 테이블에 다음과 같이 고유한 색인을 추가하면 문제가 해결된다.

--고유한 색인 추가
alter table ConnectLog add idx int identity primary key

update vw_ConnectLog_Backdoor
     set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

리스트 6 MSSQL2000 백도어를 이용한 업데이트 되는 뷰를 위해 색인 추가

고유한 색인을 추가면 정렬도 되고 업데이트도 가능하지만 다음과 같은 단점이 있다. ANSI 호환이 아니라는 점 외에 로컬 쿼리에 비해 더 많은 비용이 드는 분산 쿼리를 사용한다는 점이다.

따라서 MSSQL2000에서 가장 최근의 행을 업데이트 하기 위해서는 다음과 같은 쿼리를 해야 했다.

update a
    set Logout = getdate()
  from ConnectLog a
    join ( select id, max( login ) as login
              from ConnectLog
          group by id) b
     on a.id = b.id and a.login = b.login

(1개행적용됨)

리스트 7 MSSQL2000에서 가장 최근의 행을 업데이트 하는 쿼리

Openquery를 이용한 View와 위의 쿼리의 예상실행계획을 보면, Openquery를 이용한 View가 99.15% 일반 쿼리가 0.85% 로 성능과 제약조건으로 인해 Openquery를 이용한 View 는 실제 서비스에서 쓰기는 어려워 보인다.

리스트 8 백도어를 이용한 뷰와 일반쿼리 실행계획


UPDATE 가능한 MSSQL 2005의 TOP ~ ORDER BY 를 포함하는 VIEW

하지만 MSSQL2005 에서는 Top ~ Order By 를 가진 뷰를 만들고 업데이트 할수 있다.

create view vw_ConnectLog
as
select top 1 Id,Logout
   from ConnectLog
   order by Login desc

update vw_ConnectLog
    set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

리스트 9 MSSQL2005에서 업데이트 되는 Top ~ Order By를 가진 뷰

다음은 MSSQL2000 방식과 MSSQL2005 두 쿼리의 예상 실행계획 이다. TOP ~ ORDER BY 포함하는 가진 뷰가 실행계획이 좀 더 간결하다.

리스트 10 MSSQL2005 업데이트 되는 Top ~ Order by를 가진 뷰와 일반쿼리 실행계획


MSSQL 2005의 공통테이블식 CTE (COMMON_TABLE_EXPRESSION)

MSSQL2005에서 구현할수 있는 또 다른 방법은 공통테이블식 CTE (Common_Table_Expression) 를 이용하는 방법이다.

CTE 구문

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
          expression_name [ ( column_name [ ,...n ] ) ]
       AS
          ( CTE_query_definition )

리스트 11 CTE 구문

일반적으로 Select 문과 재귀적인 쿼리문에만 CTE를 사용하는 경우가 있지만 CTE는 Update,Insert,Delete 모두 지원한다. 코드도 간결하며, 뷰 같은 물리적인 오브젝트를 관리하지 않아도 된다는 장점이 있으며 다음 실행에서 보면, 실행시간 및 I/O등에서도 별반 차이가 없다.

뷰를 사용한 업데이트 문을 CTE구문에 맞게 작성을 해보면 다음과 같다.

;with cte_ConnectLog (id,logout) as (
select top 1 id,logout
    from ConnectLog
  order by login desc
)
update cte_ConnectLog
    set Logout = getdate()
  where Id = 'beatchoi'

(1개행적용됨)

리스트 12 CTE를 이용한 가장 최근의 행을 업데이트 하는 쿼리

리스트 13 MSSQL2005 업데이트 되는 Top ~ Order by를 가진 뷰와 CTE실행계획

뷰를 사용한 업데이트 문을 CTE를 실행하여보면 둘의 실행계획은 정확하게 일치한다. 다음은 위의 세가지 식을 테스트한 결과이다. 테스트 결과를 보면 TOP ~ ORDER BY 뷰와 CTE는 차이가 없으며, 기존 MSSQL2000방식보다 미세하게 성능이 나은 것을 볼수 있다.

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
update a
     set Logout = getdate()
   from ConnectLog a
     join ( select id,max(login) as login
               from ConnectLog
            group by id) b
      on a.id = b.id and a.login = b.login

SQL Server 구문분석및컴파일시간:
      CPU 시간 = 16ms, 경과시간 = 16ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
      CPU 시간 = 0ms, 경과시간 = 4ms.

(1개행적용됨)

update vw_ConnectLog
      set logout = getdate()
   where id = 'beatchoi'

SQL Server 구문분석및컴파일시간:
     CPU 시간 = 5ms, 경과시간 = 5ms.
SQL Server 구문분석및컴파일시간:
     CPU 시간 = 0ms, 경과시간 = 1ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
     CPU 시간 = 0ms, 경과시간 = 2ms.

(1개행적용됨)

;with cte_ConnectLog (id,Logout) as (
select top 1 id,Logout
      from ConnectLog
   order by login desc
)
update cte_ConnectLog
      set logout = getdate()
   where id = 'beatchoi'

SQL Server 구문분석및컴파일시간:
     CPU 시간 = 0ms, 경과시간 = 4ms.
테이블'ConnectLog'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
     CPU 시간 = 15ms, 경과시간 = 2ms.

(1개행적용됨)

리스트 14 일반쿼리와 되는 Top ~ Order by를 가진 뷰와 CTE 실행통계

'데이터베이스 > SQL Server' 카테고리의 다른 글

김민석 : SQL Server 2005의 Partitioned table 강좌(1)  (0) 2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(2)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1)  (0) 2008.05.07
테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리  (0) 2008.05.07
728x90
제 2강 : MSSQL2005에 등장한 각종 순위 함수

이름 : 최석준
Email : beatchoi@gmail.com
CJIG ANIMA 개발실 DBA

순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

RANK NTILE
DENSE_RANK ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

create table TEST1 (user_id char(3),num int)

insert TEST1 values('aaa',3)
insert TEST1 values('aaa',2)
insert TEST1 values('bbb',15)
insert TEST1 values('bbb',2)
insert TEST1 values('aaa',6)
insert TEST1 values('aaa',4)
insert TEST1 values('aaa',3)
리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

user_id   num1        num2        num3        num4        num5       
------- ----------- ----------- ----------- ----------- ----------
aaa         3             2              6              4               3
bbb        15            2             NULL          NULL           NULL
리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

--------------------------------------------------------
-- 일련번호생성하여 #temp_1 만들기

select identity(int,1,1) id,* into #temp_1 from TEST1
리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count(*)
from #temp_1
where user_id = a. user_id
and id <= a.id) id,a.user_id,num
from #temp_1 a) t1
group by user_id
리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

/* user_id     num1    num2    num3    num4     num5
------- ----------- ----------- ----------- ----------- ----------
aaa             3           2          6           4           3
bbb           15          2        NULL       NULL       NULL

(2개행적용됨)
*/
리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

ROW_NUMBER ( ) OVER
( [ <partition_by_clause> ] <order_by_clause> )
리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.

<order_by_clause>
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number()
over (partition by user_id order by user_id) id,* 
from TEST1) t1
group by user_id
리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

/*
user_id                                                
------- ----------- ----------- ----------- ----------- --------
aaa     3           2           6           4           3
bbb    15         2         NULL      NULL      NULL
경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다.

(2개행적용됨)
*/
리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과

리스트 9 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과
리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select identity(int,1,1) id,* into #temp_1 from TEST1

SQL Server 구문분석및컴파일시간:
CPU 시간= 13ms, 경과시간= 13ms.
테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 13ms.

(7개행적용됨)

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count (*)
from #temp_1
where user_id = a.user_id
and id <= a.id) id,a. user_id ,num
from #temp_1 a) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb    15         2         NULL       NULL       NULL

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 1ms.

select user_id,
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number( )
over (partition by user_id order by user_id) id,*  from TEST1) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb     15          2           null        null        null

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
CPU 시간= 0ms, 경과시간= 1ms.
리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

김민석 : SQL Server 2005의 Partitioned table 강좌(2)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(1)  (0) 2008.05.07
테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2)  (0) 2008.05.07
728x90
제 1강 : SQL Server 재 컴파일

이름: 송혁
Email : hyok81@nate.com
넥슨 DSM팀 DBA로 근무


1. SQL Server의 재 컴파일이란?

SQL Server 는 비용 기반의 최적화(cost based optimizer)를 기반으로 쿼리의 실행 계획을 생성하며, 생성시 통계 정보, 테이블 스키마 정보 및 쿼리에 대한 내용을 바탕으로 실행 계획을 생성합니다. 이 작업은 대부분 CPU 리소스를 사용하고 있습니다.

매번 쿼리 요청이 될 때 마다 쿼리의 실행 계획을 계속 생성한다는 작업한다면 많은 성능 적 문제를 야기 할 수 있어 SQL Server에는 프로시져 캐시라는 메모리 공간에 쿼리가 수행된 실행 계획을 메모리에 적재 후 추후 동일한 쿼리요청이 들어온다면 캐시에 있는 내용을 재사용을 하게 됩니다. 이러한 것을 실행 계획 재사용이라고 부르며, 기존의 실행계획을 사용하면 안될 경우 및 사용할 수 없는 경우에 새로운 실행 계획을 만들기 위해 재 컴파일이라는 것을 하게 됩니다.

2. OLTP 서비스 환경에서의 재 컴파일이 주는 장점 및 단점

OLTP환경 즉 많은 트랙잭션이 빠른 시간 안에 처리되어야 하는 환경에서는 OLAP환경 보다 재 컴파일에 대해서 보다 민감하고, 성능에 많은 영향을 줄 수 있습니다.

위에서 설명 하고 있듯이 SQL Server는 프로시져 캐시라는 곳에 실행계획을 저장하고 있어 재사용을 할 수 있습니다. 재사용으로 인해 매번 실행계획을 다시 생성하지 않아도 되어 보다 적은 리소스로 쿼리에 대한 응답을 할 수 있습니다.

그러나 예전에 생성된 실행 계획을 재 사용할 경우 문제가 발생 할 수도 있습니다.

기존에 실행계획은 최초 실행계획의 생성시 받는 매개변수에 대해서 실행 계획을 만드는 것이 일반적이며, 기존에 실행계획을 생성한 매개변수와 나중에 수행되는 매개변수에 대한 카디널리티 편차가 크다면 실행 계획 재사용으로 인해 적절한 실행 계획을 찾지 못하고 기존의 실행 계획을 이용하여 성능상 문제를 야기 할 수 있습니다.

실행계획의 재사용이란 항상 좋은 것만이 아닌 필요에 따라서는 재 컴파일을 통해 새로운 실행 계획을 생성 하는 것이 성능상 이점을 가질 수 있습니다.

아래의 링크에서 이러한 실행계획의 재사용으로 인해서 발생 될 수 있는 문제에 대해서 간략히 설명 하고 있습니다.

프로시져 parameter sniffing에 의한 실행계획의 차이점

3. 강제 매개변수화, 자동 매개변수화란.

SQL Server 2005에서는 자동 매개 변수화 만이 아닌 강제매개변수화 라는 기능이 추가 되었습니다. 강제 매개변수화의 경우 실행 계획을 생성할 때 사용자가 설정한 매개변수로 해당 실행계획을 생성할 수 있습니다.

이러한 강제 매개변수화를 잘 사용한다면 특정 환경에서 카디널리티 편차가 심한 데이터에 대해서 재 컴파일로 최적의 실행 계획을 만드는 것이 아닌 변수의 분포도에 따라 몇 가지 실행 계획을 생성 하여 보다 효율적으로 작성 할 수 도 있습니다.

SQL Server 2005 예전 버전에도 존재하던 기능 중에 하나인 자동매개변수화(단순매개변수화)는 실행 계획을 재사용하기 위해서 단순한 구문 및 아래의 조건에 매칭되지 않는 경우에 대해서 상수를 변수로 변환하여 실행계획을 생성 할 수 도 있습니다.

이러한 내용들은 보면 최대한 재 컴파일을 막기 위한 SQL Server 개발진들의 노력이 보입니다. 이러한 노력을 보면 재 컴파일이 성능에 얼만큼 영향을 주는 부분인지 단편적으로 생각 해 볼 수 있을 것 같습니다.

자동 매개변수화가 될 수 없는 조건

   1. BULK INSERT

   2. IN 구문 또는 OR

   3. UPDATE 절에 SET @변수 = col

   4. SELECT 절에 UNION구문

   5. SELECT 절에 INTO구문

   6. SELECT/UPDATE 절에 FOR BROWSE

   7. SELECT/UPDATE/DELETE 에 OPTION 쿼리 힌트

   8. SELECT 절에 DISTINCT 구문

   9. SELECT/UPDATE/DELETE/INSERT절에 TOP구문

   10. WAITFOR 구문

   11. SELECT절에 GROUP BY, HAVING, COMPUTE 구문

   12. DELETE/UPDATE 절에 FROM CLAUSE 구문

   13. 전체 검색, 연결된 서버, 테이블 변수 참조

   14. 서브쿼리

   15. 조건 절에 <> 상수

   16. 조인이 포함된 구문

   17. 두 개 이상의 테이블을 참조

   18. DELETE, UPDATE 에 FROM절

4. SQL Server 2005에서 변경된 구문단위 재 컴파일

보통 SQL Server을 사용할 때 주로 프로시져 단위로 SQL구문을 작성 하여 개발 하는 경우가 많습니다. SQL Server 2000에서는 프로시져 내부에 재 컴파일이 해야 하는 경우 프로시져 내부에 존재 하는 모든 구문에 대해서 재 컴파일을 하였습니다.

만약 수만 줄의 쿼리 가 있었다면 이것을 재 컴파일 한다는 것은 그 작업만으로도 상당한 리소스를 소비 하게 됩니다. 그래서 이러한 경우를 대처 하기 위해서 자주 재 컴파일 되는 부분을 다른 프로시져로 만들고 원본 프로시져에서 호출 하는 방식을 사용하였습니다.

이러한 방법으로 전체 재 컴파일이 되는 것을 막을 수 는 있었지만 많이 번거로운 작업 이었습니다.

그러나 SQL Server 2005에서는 구문 단위의 재 컴파일이 도입 되어 SQL Serve 2000에서 사용하던 것처럼 따로 프로시져를 만들어 호출 하지 않아도 구문단위로 재 컴파일을 할 수 있습니다.

테스트!!

5. 프로시져 캐시 에서의 지연기록기의 역할

SQL Server에서는 프로시져 캐시라는 곳에 예전에 실행된 실행 계획을 가지고 있어 재사용한다고 하였습니다. 그렇다면 이 프로시져 캐시에 존재 하는 것들에 대해서 사용되지 않거나 사용빈도가 낮은 것에 대해서는 메모리에서 제거를 해야 하는 작업이 필요합니다.

만약 이러한 작업이 없다면 계속 프로시져 캐시의 증가로 인해 서버 메모리에 문제가 발생 할 수 있습니다. 그러나 SQL Server에서는 이러한 문제가 거의 발생 되지 않습니다.

지연기록기(LazyWriter)라는 백그라운드 프로세스가 존재 하기 때문입니다. 지연기록기 는 free page를 확보하기 위해 주기적으로 버퍼캐시를 검색 합니다.

이러한 작업을 수행 하면서 해당 조건에 보다 낮거나 같은 수치를 가지는 것을 캐시영역에서 제거 하여 free page를 확보 하게 됩니다.

그리고 지연기록기가 하는 일중 다른 하나는 운영체제의 메모리가 부족 하다면 지연기록기에서 SQL Server에서 사용되는 버퍼 풀 메모리의 일부분을 free하여 운영체제에게 반환하는 역할을 합니다.

SQL Server 2005에서 지원이 보다 강화된 NUMA(Non-Uniform Memory Access )에 대해서는 각 노드에 대해 지연기록기 프로세스가 존재 하게 됩니다.

NUMA(Non-Uniform Memory Access) 이해

(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/2a77699c-e06b-4377-8acf-4d565d225f3c.htm)

아래의 DMV를 이용하여 현재 재사용 되는 실행계획의 개수 및 재사용되는 상위 50개의 구문이 확인이 가능 합니다.

--Compare Single-Use and Re-Used Plans
declare @single int, @reused int, @total int
select @single=sum(case(usecounts) when 1 then 1 else 0 end), @reused= sum(case(usecounts) when 1 then 0 else 1 end),
     @total=count(usecounts)
from sys.dm_exec_cached_plans

select
'Single use plans (usecounts=1)'= @single,
'Re-used plans (usecounts>1),'= @reused,
're-use %'=cast(100.0*@reused / @total as dec(5,2)),
'total usecounts'=@total

select 'single use plan size'=sum(cast(size_in_bytes as bigint))
from sys.dm_exec_cached_plans
where usecounts = 1

--List Statements By Plan Re-Use Count
SELECT TOP 50
         qs.sql_handle
                 ,qs.plan_handle
                 ,cp.cacheobjtype
                 ,cp.usecounts
                 ,cp.size_in_bytes
                 ,qs.statement_start_offset
                 ,qs.statement_end_offset
                 ,qt.dbid
                 ,qt.objectid
                 ,qt.text
                 ,SUBSTRING(qt.text,qs.statement_start_offset/2,
                         (case when qs.statement_end_offset = -1
                         then len(convert(nvarchar(max), qt.text)) * 2
                         else qs.statement_end_offset end -qs.statement_start_offset)/2)
                 as statement
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle
--and qt.dbid = db_id()
ORDER BY [Usecounts] DESC


6. 재 컴파일의 원인 및 프로시져 캐시 사라지는 경우

위에서 보듯이 쿼리의 자주 실행 되는 것이 아니라면 지연기록기에 의해 프로시져 캐시에서 실행계획이 제거 될 수 있습니다. 이렇게 되면 다시 쿼리가 호출 되면 다시 컴파일을 수행 하여야 합니다.

여기서는 지연기록기에 프로시져 캐시가 사라지는 경우가 아닌 다른 작업으로 사라지는 경우와, 기존의 실행계획이 있지만 다시 재 컴파일을 해야 하는 경우 대해서 알아보도록 하겠습니다.

재 컴파일의 원인은 아래와 같이 스키마 변경, 통계정보 업데이트 등으로 발생 될 수 있으며, ALTER DATABASE구문이나 DBCC CHECKDB구문 등으로 프로시져 캐시의 내용이 모두 사라져 모든 쿼리에 대해서 다시 컴파일을 하여야 합니다.

하나의 테이블에 통계정보가 변경되어 해당 테이블과 연관된 프로시져를 재 컴파일을 하는 것은 오히려 좋을 수 있습니다. 보다 최신의 통계정보를 바탕으로 오히려 예전 보다 더욱 효율적인 실행계획을 가지고 처리 할 수 있기 때문입니다.

그러나 ALTER DATABASE, RESTORE DATEBASE같은 경우는 서비스 운영 중에 많이 사용하고 있지 않는 구문이지만 만약 이 구문들이 사용된다면 전체 프로시져 캐시에 존재하는 실행계획이 사라져서 다시 모든 쿼리에 대해서 컴파일을 하여야 합니다.

많은 트랜잭션을 가지고 있는 사이트라면 순간적으로 CPU 리소스가 높이 사용 될 것입니다. 그러기에 이러한 동작을 유발 하는 구문에 대해서 서비스 중 작업이 필요하다면 충분한 주의가 필요 합니다.

재 컴파일 원인

  • 스키마 변경(ALTER TABLE, ALTER VIEW, Alter Index)

  • 통계정보 변경

  • 지연된 컴파일

  • SET 옵션 변경

  • 임시 테이블 변경

  • OPTION(RECOMPILE) 쿼리 힌트

  • sp_recompile 호출

프로시져 캐쉬가 사라지는 작업

  • ALTER DATABASE [dbName] SET ONLINE

  • ALTER DATABASE [dbName] SET OFFLINE

  • ALTER DATABASE [dbName] SET READ_ONLY

  • ALTER DATABASE [dbName] SET READ_WRITE

  • ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name]

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE

  • ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY

  • ALTER DATABASE [dbName] COLLATE Collation_Name

  • DROP DATABASE [db_Snapshot_Name]

  • Restore database

  • Detach database

  • DBCC FREEPROCCACHE

  • DBCC FREESYSTEMCACHE

  • DBCC CHECKDB (참고 : SQL Server 2005 SP2 이전에는 DBCC CHECKDB를 수행하면 프로시져의 캐시가 사라지게 되나 SP2에서는 제거되지 않음.)

SQL Server 2005 SP2에서는 프로시져 캐시가 사라지는 작업 시 SQL 에러로그에 아래와 같은 메시지가 추가 되었습니다.

2007-01-19 14:29:07.39 spid54       Starting up database 'R'.
2007-01-19 14:29:23.84 spid54       Setting database option OFFLINE to ON for database R.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.06 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 13 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2007-01-19 14:29:24.08 spid54       SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.


7. 재 컴파일 발생 원인 및 빈도 찾기

이러한 재 컴파일이 문제가 될 수 있다는 것을 위에서 알아 보았습니다. 그러면 이제는 현재 운영하고 있는 사이트에서 얼마나 많은 재 컴파일이 발생하는지 그리고 이유는 무엇인지 확인할 필요가 있습니다.

많은 트랜잭션이 발생하지 않는다면, 요즘은 워낙 CPU사양도 좋고 해서 재 컴파일에 의한 문제는 그리 크게 사람에게 다가오지 않을 수도 있습니다. 그러나 많은 트랜잭션이 발생 하는 환경이라면 분명 확인 할 가치는 있을 겁니다.

보통 재 컴파일에 대한 문제로 원인 및 빈도를 확인 하기 위해서는 프로파일러와 성능 모니터를 통해 확인 합니다.

프로파일러의 경우 모든 SQL Server로 들어오는 쿼리에 대해서 재 컴파일이 발생 되었다면 SP: Recompile, SQL: StmtRecompile 이벤트 및 EventSubClass컬럼을 추가 하여 원인 및 빈도를 확인 할 수 있습니다.

그렇지만 프로파일러를 서비스중인 서버에 사용하는 것도 힘든 곳도 많을 것입니다. 그리고 구문단위까지 이벤트를 걸게 되면 추적 파일 크기는 금방 커질 것 입니다.

비쥬얼하게 보여지는 프로파일러 대신 SQLDIAG나 저장 프로시져를 사용하는 것이 서버의 리소스 소비에 대해서 보다 효과 적입니다.

SQLDIAG는 기존 SQL Server 2000에도 존재 하였지만 일반적인 에러로그 및 수행 당시의 시스템 테이블 정보 등 이었지만 SQL Server 2005의 SQLDIAG는 기존에 Microsoft PSS팀에서 사용하던 PSSDIAG의 기능을 포함 하고 있어 운영체제 이벤트 로그, 추적, 성능 카운터 등을 한번에 수집이 가능 하여 보다 쉽게 관련 데이터를 수집 할 수 있습니다. 그러나 이러한 이벤트나 카운터를 수정 해야 한다면 XML파일을 직접 수정 해야 하는 번거로움은 있습니다.

단순히 재 컴파일에 대해서 어떠한 원인으로 발생 되었는지 확인 하려면 아래와 같이 열 필터의 EventSubClass의 “값이 없는 행 제외”를 추가하여 추적하여 성능 모니터의 수치와 비교해 본다면 보다 쉽게 재 컴파일의 원인을 확인 할 수 있습니다.

성능 모니터를 통해 재 컴파일의 빈도를 찾기 위해서는 아래에 있는 카운터 중 SQL Re-Compilations/sec, Batch Requests/sec 을 추가하여 빈도를 확인 할 수 있습니다.

SQLDIAG, 저장 프로시져를 이용하여 추적 하는 방법은 온라인 설명서를 참조하시면 됩니다.

SQLdiag 유틸리티(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/sqlcmpt9/html/45ba1307-33d1-431e-872c-a6e4556f5ff2.htm)

SQL Server 프로파일러 저장 프로시저(Transact-SQL)(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/8c99c3db-0b04-46c3-aa59-d6f680522fdd.htm)

<그림 : 열 필터 설정>

<그림: 열 필터 설정 후 프로파일러 모습>

EventSubClass 값

설명

1

스키마가 변경되었습니다.

2

통계가 변경되었습니다.

3

지연된 컴파일입니다.

4

SET 옵션이 변경되었습니다.

5

임시 테이블이 변경되었습니다.

6

원격 행 집합이 변경되었습니다.

7

FOR BROWSE 권한이 변경되었습니다.

8

쿼리 알림 환경이 변경되었습니다.

9

분할된 뷰가 변경되었습니다.

10

커서 옵션이 변경되었습니다.

11

OPTION (RECOMPILE)이 요청되었습니다.

<EventSubClass 컬럼 설명>

성능 모니터 (perfmon)의 SQL Statistics 카운터를 통해 분석 및 빈도를 확인 할 수 있습니다.

SQL Server SQL Statistics 카운터

설명

Auto-Param Attempts/sec

초당 자동 매개 변수화 시도 수입니다. 합계는 자동 매개 변수화의 실패한 횟수, 안전한 횟수 그리고 안전하지 않은 횟수의 합과 같습니다. 자동 매개 변수화는 유사한 여러 요청을 처리할 때 캐시된 결과 실행 계획을 다시 사용하기 위해 SQL Server 인스턴스가 일부 리터럴을 매개 변수로 바꾸어 Transact-SQL 요청을 매개 변수화할 때 일어납니다. Microsoft SQL Server 2000 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 이 카운터에는 강제 매개 변수화는 포함되지 않습니다.

Batch Requests/sec

초당 받는 Transact-SQL 명령 일괄 처리 수입니다. 이 통계는 모든 제약 조건(I/O, 사용자 수, 캐시 크기, 요청의 복잡도 등)의 영향을 받습니다. 높은 일괄 처리 수치는 높은 처리 효율을 의미합니다.

Failed Auto-Params/sec

실패한 자동 매개 변수화의 초당 시도 횟수입니다. 이 값은 작을수록 좋습니다.

Forced Parameterizations/sec

초당 성공한 강제 매개 변수화 수입니다.

Safe Auto-Params/sec

안전한 자동 매개 변수화의 초당 시도 횟수입니다. 안전한 자동 매개 변수화는 여러 유사한 Transact-SQL 문 간에 캐시된 실행 계획을 공유할 수 있도록 하는 결정을 참조하며 SQL Server 가 시도하는 많은 자동 매개 변수화 중 일부는 성공하고 나머지는 실패합니다. SQL Server 2005 에서는 자동 매개 변수화를 단순 매개 변수화라고도 합니다. 강제 매개 변수화는 포함되지 않습니다.

SQL Attention rate

초당 주의 수입니다. 주의는 현재 실행 중인 요청을 종료하기 위한 클라이언트의 요청입니다.

SQL Compilations/sec

초당 SQL 컴파일 수입니다. 컴파일 코드 경로를 입력한 횟수를 나타내며 SQL Server 2005 에서 문 수준의 다시 컴파일에 의한 컴파일을 포함합니다. SQL Server 사용자 작업이 안정되면 이 값은 일정한 수준에 이릅니다.

SQL Re-Compilations/sec

초당 문 다시 컴파일 수입니다. 문 다시 컴파일이 트리거된 횟수를 나타냅니다. 일반적으로 다시 컴파일하는 횟수는 적을수록 좋습니다. SQL Server 2000 에서 다시 컴파일은 Microsoft SQL Server 2005 의 일괄 처리 범위가 아니라 문 범위 다시 컴파일입니다. 따라서 SQL Server 2005 와 이전 버전 간에 이 카운터 값을 직접 비교할 수 없습니다.

Unsafe Auto-Params/sec

안전하지 않은 자동 매개 변수화의 초당 시도 횟수입니다. 예를 들어 쿼리는 캐시된 계획을 공유하지 않고 자동 매개변수화하는 특성이 있습니다. 이러한 자동 매개변수화는 안전하지 않은 것으로 지정됩니다. 강제 매개 변수화 수는 여기에 포함되지 않습니다.


드디어 “SQL Server의 재 컴파일이란?” 아티클에 마지막입니다.

여기서 제가 가장 하고 싶은 말은 지연기록기가 어떠한 연산을 하여 실행계획을 제거되고 서버의 메모리 중 얼마만큼 프로시져 캐시로 사용할 수 있는 그러한 내용이 아닌 “환경에 따라 독이 될 수도 약이 될 수도 있는 것이 재 컴파일이다.” 입니다.

그러나 여기서는 재 컴파일이 발생 하지 않게 모니터링 방법 등만을 기술 하였습니다. 이것은 대부분의 환경에서는 재 컴파일이라는 것이 약 이 되는 경우가 많다는 것이며 항상 재 컴파일이 나쁘다는 것은 아닙니다.

그렇지만 재 컴파일이라는 것을 약 또는 독을 만드는 것은 사람이라고 생각합니다. 만약 독이 라면 어떻게 찾고 해결할까? 그렇지만 찾는 방법만 있지 이것을 여기서 해결 하는 방법은 기술 되지 않았습니다.

이 부분은 직접 테스트를 해보면서 배우고 느끼는 것이 가장 좋다고 생각하는 개인적인 의견입니다. ^^

저는 SQL Server라는 DBMS를 접하게 된지 그리 오래 되지 않았습니다. 그래서 아직은 배워야 할 것 도 많다고 생각합니다. 그래서 더욱 재미있다고 생각합니다. DBMS라는 것에 대해서는 무엇보다 사람이 중요성이 다른 무엇보다 크다고 생각하기 때문입니다.

나중에 SQL Server 3000이 나와서 아무리 옵티마이져가 똑똑해졌다고 한다고 하더라도 사람이 해야 할 부분까지 SQL Server단에서 처리 하기는 힘들 거라고 생각합니다. 환경 및 비즈니스 로직에 따라서 사람의 단 하나의 판단으로 많은 차이가 날 수 있을 수 있기 때문입니다.

길지 않은 아티클을 작성하는데 생각보다 오랜 시간이 걸린 듯합니다. 지금 다시 보면 별 내용이 없는 것 같아 괜히 부끄러워지지만, 그래도 짧은 시간 동안 많은 공부를 할 수 있었고 정리를 하는 시간을 가져서 참 좋았습니다.

솔직히 이 재 컴파일이 주제가 정하기 전 여러 주제에 대해서 생각을 해보고 작성도 해보았지만 마음에 들지 않아 주제를 변경하기도 여러 번..아마 그 주제 들은 어딘가의 폴더 안에서 곤히 잠들고 있을 겁니다.

만약 다음에도 아티클로 다시 뵐 수 있는 기회가 온다면 더욱 열심히 준비하고 공부해서 좋은 아니 괜찮은 아티클로 다시 찾아 뵙겠습니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

최석준 : Update되는 Top 뷰와 Common table expression(CTE)(1)  (0) 2008.05.07
최석준 : Update되는 Top 뷰와 Common table expression(CTE)(2)  (0) 2008.05.07
테크니컬 칼럼 - DB별 대용량 데이터의 파티셔닝과 병렬 처리  (0) 2008.05.07
송혁 : SQL Server 2005 Query optimizer의 변경된 내용(2)  (0) 2008.05.07
master db 복구 SQL 2005  (0) 2008.05.07

+ Recent posts

티스토리툴바