none
MS SQL Server 2000 에서 query text를 어떻게 추출하나요? RRS feed

  • 질문

  • MS SQL Server 2008은 sys.dm_exec_query_stats와 sys.dm_exec_sql_text(sql_handle)을 이용하여

    아래와 같은 쿼리로 Query Text를 모두 출력할수 있습니다.

     

    SELECT query_stats.query_hash AS "Query Hash",
        query_stats.total_worker_time  AS "CPU Time",
        query_stats.statement_text AS "Statement Text"
    FROM
        (SELECT QS.*,
        SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
        ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE QS.statement_end_offset END
                - QS.statement_start_offset)/2) + 1) AS statement_text
         FROM sys.dm_exec_query_stats AS QS
         CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
    GO

     

    하지만 MS SQL Server 2000에서는  sys.dm_exec_query_stats와 sys.dm_exec_sql_text(sql_handle)가

    제공 되지 않습니다. MS SQL Server 2008 온라인 도움말과 MS SQL Server 2000 온라인 도움말을

    이용하여 syscacheobjects 테이블에 query text가 있다는 것을 알게 되었습니다.

    select objid, sql from syscacheobjects where objtype='Adhoc' or objtype='Prepared'

    위와 같이 실행하면 캐시된 오브젝트인 Adhoc Query와 Prepared Query의 Query Text가 출력됩니다.

    문제는 syscacheobjects 테이블의 sql 컬럼 데이터 타입이 nvarchar(256) 으로 정의되어 있다는데에 있습니다.

    제가 원하는 결과는 모든 Query Text(길이 256이상의 Query Text도 포함)를 출력 결과로 얻고

    싶은데 어떻게 하면 이 문제를 해결할수 있을까요? 많은 고수님들의 답변 부탁 드립니다.


    참고 : MS SQL Server 2000 SP3에서는 fn_get_sql 함수와 sysprocesses 시스템 테이블을 이용하여

    query text 를 뽑을수 있는데요. 이것도 제가 원하는 답이 아닌것 같습니다.

    현재 프로시저 캐쉬에 존재하는 모든 Query Text를 뽑을수 있는 SQL을 알고 싶습니다..

    2012년 6월 11일 월요일 오후 12:39

답변

  • 포럼에서도 뵙는군요^^

    네 안타깝게도 255까지만 지원이 됩니다.

    DBCC INPUTBUFFER(SPID)로 특정 SPID에서 실행한 최종 QUERY를 확인할 수 있습니다.

    하지만, 이도 역시 255 까지만 지원이 됩니다.

    FN_GET_SQL 도 역시 syscacheobjects에 있는 모든 쿼리에 대해서 지원하지는 않으며,

    sysprocesses에 있는 sqlhandle 값을 기준으로 최종 쿼리만 확인하실 수 있습니다.

    어떤 정보를 원하시는지를 알려주시면 다른 방법이 있는지 검토해 드리겠습니다.

    감사합니다.


    Best Regards, Daejoong Samuel Sung Microsoft SQL Server MVP,MCITP,MSTS, Senior Consultant @ SQLRoad.COM

    • 답변으로 표시됨 뭉게뭉게 2012년 6월 13일 수요일 오전 12:51
    2012년 6월 12일 화요일 오후 1:47
  • 이미 언급드린 바와 같이 해당 기능을 SQL Server 2000에서는 지원하지 않습니다.

    SQL Server 2000에서는 해당 작업은 SQL 프로파일러를 통해서 쿼리 부하를 추적한 다음,

    ReadTrace 등과 같은 도구를 통해 분석할 수 있었습니다.

    SQL Server 2005 이후에서는 이러한 부족한 부분을 보완하기 위해,

    질문에서 제시해 주신 관련 동적관리뷰(DMV)의 개념이 도입되었습니다.

    SQL Server 2008이후에는 XEvent를 통해 좀 더 다양한 정보를 모니터링할 수 있습니다.

    감사합니다.


    Best Regards, Daejoong Samuel Sung Microsoft SQL Server MVP,MCITP,MSTS, Senior Consultant @ SQLRoad.COM

    2012년 6월 13일 수요일 오전 2:52

모든 응답

  • 포럼에서도 뵙는군요^^

    네 안타깝게도 255까지만 지원이 됩니다.

    DBCC INPUTBUFFER(SPID)로 특정 SPID에서 실행한 최종 QUERY를 확인할 수 있습니다.

    하지만, 이도 역시 255 까지만 지원이 됩니다.

    FN_GET_SQL 도 역시 syscacheobjects에 있는 모든 쿼리에 대해서 지원하지는 않으며,

    sysprocesses에 있는 sqlhandle 값을 기준으로 최종 쿼리만 확인하실 수 있습니다.

    어떤 정보를 원하시는지를 알려주시면 다른 방법이 있는지 검토해 드리겠습니다.

    감사합니다.


    Best Regards, Daejoong Samuel Sung Microsoft SQL Server MVP,MCITP,MSTS, Senior Consultant @ SQLRoad.COM

    • 답변으로 표시됨 뭉게뭉게 2012년 6월 13일 수요일 오전 12:51
    2012년 6월 12일 화요일 오후 1:47
  • 친절한 답변 감사드립니다. MS SQL Server 2008은 sys.dm_exec_query_stats와 sys.dm_exec_sql_text(sql_handle)을 이용하여(제가 질문했들때 사용한 쿼리 참고) 프로시저 캐시에 있는 Ad-hoc Query와 Prepared Query에 대한 전체 Query 텍스트와 성능정보(CPU에서 실행한 시간, 실행횟수, 등등)을 뽑을수 있습니다. 제가 원하는 것은 MS SQL Server 2000에서 이와 동일한 결과을 얻고 싶습니다.  
    만약 방법이 없다면 MS SQL Server 2000에서 제공하는 API를 이용하여 프로그래밍을 하여 프로시저 캐쉬에 있는 SQL Text와 성능정보를 뽑아낼수 있는지도 궁금합니다.
    2012년 6월 13일 수요일 오전 12:52
  • 이미 언급드린 바와 같이 해당 기능을 SQL Server 2000에서는 지원하지 않습니다.

    SQL Server 2000에서는 해당 작업은 SQL 프로파일러를 통해서 쿼리 부하를 추적한 다음,

    ReadTrace 등과 같은 도구를 통해 분석할 수 있었습니다.

    SQL Server 2005 이후에서는 이러한 부족한 부분을 보완하기 위해,

    질문에서 제시해 주신 관련 동적관리뷰(DMV)의 개념이 도입되었습니다.

    SQL Server 2008이후에는 XEvent를 통해 좀 더 다양한 정보를 모니터링할 수 있습니다.

    감사합니다.


    Best Regards, Daejoong Samuel Sung Microsoft SQL Server MVP,MCITP,MSTS, Senior Consultant @ SQLRoad.COM

    2012년 6월 13일 수요일 오전 2:52