2018-01-03(MSSQL 쿼리 이력 조회)

MSSQL 쿼리 이력 조회 방법

select
    db_name(st.dbid) DBNanme,
    object_schema_name(objectid, st.dbid) SchemaName,
    object_name(objectid, st.dbid) SPName,
    qs.total_elapsed_time,
    creation_time,
    last_execution_time,
    text
from
    sys.dm_exec_query_stats qs
cross apply
    sys.dm_exec_sql_text(qs.plan_handle) st
join 
    sys.dm_exec_cached_plans cp
on
    qs.plan_handle = cp.plan_handle
where
    last_execution_time < getdate() -1 -- 조건 : 수행일자 (1일전)
--and
--    db_name(st.dbid) is not null and cp.objtype = 'proc' -- 조건 : 종류
order by
    last_execution_time e desc;

 

이 구문의 특징은 sql management studio 라는 프로그램을 통해 실행한 쿼리 내역을 조사한다.
보통 스키마의 변동 내역을 SVN들 처럼 MSSQL 자체에서 변경 내역을 관리하고 있다.

이게 ODBC[게임서버] , ADODB[웹서버] 를 통해 실행된 쿼리는 기록으로 남기질 않는다.
보통 SQL Server 프로파일러를 통해 현재 실행중인 명령들에 대해 로깅을 하는 기능이 있는데,
이 기능은 어떤 클라이언트로 실행된 명령이더라도 로깅을 해줍니다. 대신 느려지는 단점이 있다.

즉, 어떤 쿼리가 실행되었는지 sql server 프로파일러를 쓰지 않고 그때그때 남기려면, 테이블마다
select , update , delete 등에 대해 트리거를 만들어서 쓰는게 좋다.
아니면 게임서버등에서 실행 로그를 남기던지, 근데 이방법은 외부 검증되지 않은 프로그램에서
실행하거나 명령에 대해선 남길수 없다.