블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (465)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total247,222
Today3
Yesterday49

달력

« » 2020.8
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

공지사항

sp_reset_connection 이 무엇을 하는가? 또 무엇을 하지 못하는가?

sp_reset_connection resets the following aspects of a connection:

It resets all error states and numbers (like @@error)
It stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query
It will wait for any outstanding I/O operations that is outstanding
It will free any held buffers on the server by the connection
It will unlock any buffer resources that are used by the connection
It will release all memory allocated owned by the connection
It will clear any work or temporary tables that are created by the connection
It will kill all global cursors owned by the connection
It will close any open SQL-XML handles that are open
It will delete any open SQL-XML related work tables
It will close all system tables
It will close all user tables
It will drop all temporary objects
It will abort open transactions
It will defect from a distributed transaction when enlisted
It will decrement the reference count for users in current database; which release shared database lock
It will free acquired locks
It will releases any handles that may have been acquired
It will reset all SET options to the default values
It will reset the @@rowcount value
It will reset the @@identity value
It will reset any session level trace options using dbcc traceon()

sp_reset_connection will NOT reset:
Security context, which is why connection pooling matches connections based on the exact connection string
If you entered an application role using sp_setapprole, since application roles can not be reverted

Note: Pasting the content as I do not want it to be lost in the ever transient web

logon trigger 의 발동

sp_reset_connection 이 발생되면, logon trigger 도 동작하게 된다 예전에는 sp_reset_connection 을 호출 하지 않을 방법이 있었지만, 해당 옵션은 버전이 올라가면서 제거 되었다. logon trigger 는 시간으로도 접근제어가 가능하도록 디자인 되어 있어 sp_reset_connection 이 발생 할때마다 체크 되도록 되어 있다. (by design)

linked server 연결에서 logon trigger 가 문제인데, sp_reset_connection 을 호출 하지 않게 할 수 없나?

있다. 다음과 같이 다른 방식으로 연결하면 사용하지 않는다.

-- 기존 전통적인 방식
EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sqler',@rmtpassword='!1234'


-- login logout 을 하지 않기 위해서 아래와 같이 odbc 를 사용합니다.
EXEC master.dbo.sp_addlinkedserver @server = N'test2', @srvproduct=N' ', @provider=N'MSDASQL', @provstr=N'Driver={SQL Server};Database=master;Server=10.1.1.1;UID=sqler;PWD=!1234;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'test2',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL


while (1=1) begin
 waitfor delay '00:00:00.500'
 select top 1  * from test1.master.dbo.sysprocesses -- 이넘은 login logout 을 계속 합니다.
 --select top 1  * from test2.master.dbo.sysprocesses -- 이건 login logout 을 하지 않습니다.
end


참고자료
http://msdn.microsoft.com/en-us/library/aa172690(v=SQL.80).aspx

다음은 같은 event 로 잡은 프로파일링 데이터 이다.

test1 의 프로파일링
sp_reset_connection 이 발생하고 eventsubclass 에 pooled 라고 찍혀 있다.
여담이지만, sp_reset_connection 은 connection pooling 이 되고 있을때만 발생한다. 이걸보고 login logout 이 생긴다고 pooling 을 사용하지 못한다라고 말하는이가 있거나 오해가 없길 바란다.


test2 의 프로파일링
sp_reset_connection 호출이 없다.


msdasql 을 쓸때 tempdb 나 기타 reset 해야 할 부분이 잘 되는가는 만세가 테스트 하기로 함....
test 할 때 set ansi_nulls default on off 같은걸로 테스트 하면 금방 할끼야~ 리셋 안되야 정상이지 안불렀는데...ㅡ.ㅡ
목적에 따라서 잘 쓰시길 바랍니다.

 

Posted by 보미아빠

댓글을 달아 주세요

  1. 2010.10.19 00:26 신고 후추맛  댓글주소  수정/삭제  댓글쓰기

    EVENTDATA() 에서 isPooled라는 항목을 테스트 해 봤습니다.
    native 연결된 서버의 경우 1회때는 0이 반환되고 그후는 1이 계속 반환되어 pooling이 되는것을 확인할 수 있습니다.
    MSDASQL을 이용할 경우 최초 수행할 때 login이 발생하고 0이 반환됩니다.
    이걸 이용하면 native를 이용할때도 0 또는 1을 보고 0일 경우만 로그를 남기면 원하는대로 로긴을 남길 수 있을것 같습니다.
    하지만 login이 발생할 때마다 체크가 발생하니 이에 대한 부하 테스트가 필요할거 같아요~
    reset_connection이 발생 안할때 생기는 문제 테스트 하는데 hyoksong이 isPooled 아이디어를 줘서 테스트 해봤는데 이것도 처음 본거라 기록해 둡니다~ ㅎㅎ


    -- 테이블 생성
    create table DBA.dbo.trg_test (col1 datetime, col2 varchar(500))

    -- 트리거 생성
    --drop trigger trg_test ON ALL SERVER
    create TRIGGER TRG_TEST
    ON ALL SERVER WITH EXECUTE AS 'feisia'
    FOR LOGON
    AS
    BEGIN
    IF ORIGINAL_LOGIN()= 'login_test'
    ROLLBACK;
    ELSE
    insert into DBA.dbo.trg_test select getdate()
    , EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'NVARCHAR(15)')
    END;


    -- 연결된 서버 수행
    select * from test1.master.dbo.sysprocesses
    select * from test2.master.dbo.sysprocesses


    -- 로그 확인
    --truncate table dba.dbo.trg_test
    select * from dba.dbo.trg_test

  2. 2010.10.19 21:41 신고 보미아빠  댓글주소  수정/삭제  댓글쓰기

    수고했다 프로파일러에도 찍히고 저기 있으면 당연히 니가 말한 곳에도 있지..... 그나저나 넌 다른 테스트를 하겠다고 한거 같은데......-_- ......계속 열심히 공부해서 좋은정보 알려줘~

  3. 2010.10.20 10:41 신고 후추맛  댓글주소  수정/삭제  댓글쓰기

    temp table 사용 과 관련해서 몇가지 테스트를 해 보았습니다.~~

    -- 연결된 서버 수행 (본문의 테스트랑 똑같이 만들어 봤습니다.)
    select * from test1.master.dbo.sysprocesses
    select * from test2.master.dbo.sysprocesses

    -- 아래 부터는 test2에서만 쿼리를 수행합니다.

    -- 1번 테스트. temp table 생성 테스트
    -- 1회 수행시 잘 수행 되지만 2회 부터는 이미 테이블이 만들어져 있으므로 오류가 발생함
    -- 음.. 생각했던대로 동작함
    select *
    from openquery(test2, '
    create table #t1 (col1 int)
    select * from master.dbo.sysprocesses
    ')

    -- 임시 테이블 제거
    select * from openquery(test2, '
    drop table #t1
    select 1')


    -- 2번 테스트. 저장 프로시저로 만든 다음 테스트
    create proc usp_t1 as
    create table #t1 (col1 int)
    select * from master.dbo.sysprocesses
    go

    -- 오류 없이 잘 수행 된다
    -- 저장 프로시저가 종료될 때 정리를 해 주는것 같음
    select * from openquery(test2, 'exec usp_t1')


    -- 3번 테스트. 이번에는 여러 세션에서 어떻게 되는지를 테스트 해 봄.
    -- 아래 쿼리를 1번 세션에서 수행 후 세션을 하나 더 만들어서 2번 세션에서 수행하면 2번 세션에서 오류가 발생 함.
    -- 오류가 안날꺼라 생각했는데 pooling하면서 세션을 공유하는가보다 라고 생각하기로 함.
    select * from openquery(test2, '
    drop table #t1
    select 1')


    -- 4번 테스트. 3번 테스트에서 여러 세션에서 공유하는 상태이므로 쿼리에서 temp table을 drop해 준다고 해도 문제가 생기지 않을까? 하는 의문이 듬.
    -- 아래처럼 drop table구문을 넣고 테스트
    -- 1번 세션에서 수행 후 10초가 지나기 전에 2번 세션에서 동일한 쿼리를 수행
    -- 오류가 발생하지 않음. 음.. 3번 테스트 대로라면 오류가 발생해야 할거 같은데 생각한대로 동작하지 않음
    -- 이 테스트 대로라면 temp table 정리만 잘해주면 문제가 없다는 의미인듯?
    select *
    from openquery(test2, '
    create table #t1 (col1 int)
    waitfor delay ''00:00:10''
    select * from master.dbo.sysprocesses
    drop table #t1
    ')

  4. 2011.02.09 14:24 엘리엘  댓글주소  수정/삭제  댓글쓰기

    링크 따라 들어 왔습니다. 좋은 정보 감사합니다.

최근에 달린 댓글

최근에 받은 트랙백

글 보관함