SQL Server Management Studio에서 쿼리 기록을 보는 방법

SQL Server Management Studio에서 쿼리 기록을 보는 방법

---

쿼리 기록이 일부 로그 파일에 저장됩니까? 그렇다면 위치를 찾는 방법을 말씀해 주시겠습니까? 그렇지 않다면 어떻게 볼 수 있는지 조언 해 주시겠습니까?

---

[ 이 질문 은 중복으로 종료 될 수 있습니다.]

SQL Server가 다시 시작되지 않았고 계획이 제거되지 않은 경우 계획 캐시에서 쿼리를 찾을 수 있습니다.

SELECT t.[text]
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%';

Management Studio가 중단되어 파일을 잃어버린 경우 여기에서 복구 파일을 찾을 수 있습니다.

C:\Users\<you>\Documents\SQL Server Management Studio\Backup Files\

그렇지 않으면 Ed Harper의 답변에 언급 된 SSMS 도구 팩과 같은 쿼리 기록을 저장하는 데 도움이되는 다른 것을 사용해야합니다 .SQL Server 2012 이상에서는 무료가 아닙니다. 또는 로그인 또는 호스트 이름으로 필터링 된 경량 추적을 설정할 수 있습니다 (단, 프로파일 러가 아닌 서버 측 추적을 사용하십시오).

---

네나드-Zivkovic은 주석 @,에 가입하는 것이 도움이 될 수 sys.dm_exec_query_stats및 순서 last_execution_time:

SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s
   ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE N'%something unique about your query%'
ORDER BY s.last_execution_time DESC;

---

늦었지만 더 자세한 내용을 추가하므로 유용하게 사용됩니다…

기본적으로 SSMS에서 실행 된 쿼리를 볼 수있는 방법은 없습니다. 그래도 몇 가지 옵션이 있습니다.

트랜잭션 로그 읽기 – 독점 형식이기 때문에 쉬운 일이 아닙니다. 그러나 역사적으로 실행 된 쿼리를 보려면 (SELECT 제외) 이것이 유일한 방법입니다.

ApexSQL Log 및 SQL Log Rescue 와 같은 타사 도구를 사용할 수 있습니다 (무료이지만 SQL 2000 만 해당). 자세한 내용은이 스레드를 확인하십시오. SQL Server 트랜잭션 로그 탐색기 / 분석기

SQL Server 프로파일 러 – 감사를 시작하고 이전에 발생한 일에 관심이없는 경우에 가장 적합합니다. 필터를 사용하여 필요한 트랜잭션 만 선택하십시오. 그렇지 않으면 많은 양의 데이터가 매우 빨리 끝납니다.

SQL Server 추적-모든 명령 또는 대부분의 명령을 캡처하고 나중에 구문 분석 할 수있는 추적 파일에 보관하려는 경우 가장 적합합니다.

트리거 – DML (선택 제외)을 캡처하여 데이터베이스 어딘가에 저장하려는 경우 가장 적합합니다.

---

SSMS 도구 팩은 무엇보다도 실행 기록을 기록하는 기능을 추가합니다.

---

다른 사람들이 지적했듯이 SQL Profiler를 사용할 수 있지만 sp_trace_ * 시스템 저장 프로 시저를 통해 해당 기능을 활용할 수도 있습니다. 예를 들어,이 SQL 스 니펫은 (2000 이상, SQL 2008에서는 동일하다고 생각하지만 10 초 이상 걸리는 모든 쿼리에 대한 캐치 RPC:Completed및 SQL:BatchCompleted이벤트를 잡아서 출력을 저장합니다. 나중에 SQL 프로파일 러에서 열 수있는 추적 파일 :

DECLARE @TraceID INT
DECLARE @ON BIT
DECLARE @RetVal INT
SET @ON = 1

exec @RetVal = sp_trace_create @TraceID OUTPUT, 2, N'Y:\TraceFile.trc'
print 'This trace is Trace ID = ' + CAST(@TraceID AS NVARCHAR)
print 'Return value = ' + CAST(@RetVal AS NVARCHAR)
-- 10 = RPC:Completed
exec sp_trace_setevent @TraceID, 10, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 10, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 10, 12, @ON        -- SPID
exec sp_trace_setevent @TraceID, 10, 13, @ON        -- Duration
exec sp_trace_setevent @TraceID, 10, 14, @ON        -- StartTime
exec sp_trace_setevent @TraceID, 10, 15, @ON        -- EndTime

-- 12 = SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 1, @ON     -- Textdata
exec sp_trace_setevent @TraceID, 12, 3, @ON     -- DatabaseID
exec sp_trace_setevent @TraceID, 12, 12, @ON        -- SPID
exec sp_trace_setevent @TraceID, 12, 13, @ON        -- Duration
exec sp_trace_setevent @TraceID, 12, 14, @ON        -- StartTime
exec sp_trace_setevent @TraceID, 12, 15, @ON        -- EndTime

-- Filter for duration [column 13] greater than [operation 2] 10 seconds (= 10,000ms)
declare @duration bigint
set @duration = 10000
exec sp_trace_setfilter @TraceID, 13, 0, 2, @duration

온라인 설명서에서 각 추적 이벤트, 열 등에 대한 ID를 찾을 수 있습니다. sp_trace_create , sp_trace_setevent 및 sp_trace_setfiler sprocs 만 검색하십시오 . 그런 다음 다음과 같이 추적을 제어 할 수 있습니다.

exec sp_trace_setstatus 15, 0       -- Stop the trace
exec sp_trace_setstatus 15, 1       -- Start the trace
exec sp_trace_setstatus 15, 2       -- Close the trace file and delete the trace settings

... 여기서 '15'는 추적 ID입니다 (sp_trace_create에 의해보고 됨). 위의 첫 번째 스크립트가 시작됩니다.

You can check to see what traces are running with:

select * from ::fn_trace_getinfo(default)

The only thing I will say in caution -- I do not know how much load this will put on your system; it will add some, but how big that "some" is probably depends how busy your server is.

---

The system doesn't record queries in that way. If you know you want to do that ahead of time though, you can use SQL Profiler to record what is coming in and track queries during the time Profiler is running.

---

You can Monitor SQL queries by SQL Profiler if you need it

---

I use the below query for tracing application activity on a SQL server that does not have trace profiler enabled. The method uses Query Store (SQL Server 2016+) instead of the DMV's. This gives better ability to look into historical data, as well as faster lookups. It is very efficient to capture short-running queries that can't be captured by sp_who/sp_whoisactive.

/* Adjust script to your needs.
    Run full script (F5) -> Interact with UI -> Run full script again (F5)
    Output will contain the queries completed in that timeframe.
*/

/* Requires Query Store to be enabled:
    ALTER DATABASE <db> SET QUERY_STORE = ON
    ALTER DATABASE <db> SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100000)
*/

USE <db> /* Select your DB */

IF OBJECT_ID('tempdb..#lastendtime') IS NULL
    SELECT GETUTCDATE() AS dt INTO #lastendtime
ELSE IF NOT EXISTS (SELECT * FROM #lastendtime)
    INSERT INTO #lastendtime VALUES (GETUTCDATE()) 

;WITH T AS (
SELECT 
    DB_NAME() AS DBName
    , s.name + '.' + o.name AS ObjectName
    , qt.query_sql_text
    , rs.runtime_stats_id
    , p.query_id
    , p.plan_id
    , CAST(p.last_execution_time AS DATETIME) AS last_execution_time
    , CASE WHEN p.last_execution_time > #lastendtime.dt THEN 'X' ELSE '' END AS New
    , CAST(rs.last_duration / 1.0e6 AS DECIMAL(9,3)) last_duration_s
    , rs.count_executions
    , rs.last_rowcount
    , rs.last_logical_io_reads
    , rs.last_physical_io_reads
    , q.query_parameterization_type_desc
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY plan_id, runtime_stats_id ORDER BY runtime_stats_id DESC) AS recent_stats_in_current_priod
    FROM sys.query_store_runtime_stats 
    ) AS rs
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_query AS q ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
LEFT OUTER JOIN sys.objects AS o ON o.object_id = q.object_id
LEFT OUTER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
CROSS APPLY #lastendtime
WHERE rsi.start_time <= GETUTCDATE() AND GETUTCDATE() < rsi.end_time
    AND recent_stats_in_current_priod = 1
    /* Adjust your filters: */
    -- AND (s.name IN ('<myschema>') OR s.name IS NULL)
UNION
SELECT NULL,NULL,NULL,NULL,NULL,NULL,dt,NULL,NULL,NULL,NULL,NULL,NULL, NULL
FROM #lastendtime
)
SELECT * FROM T
WHERE T.query_sql_text IS NULL OR T.query_sql_text NOT LIKE '%#lastendtime%' -- do not show myself
ORDER BY last_execution_time DESC

TRUNCATE TABLE #lastendtime
INSERT INTO #lastendtime VALUES (GETUTCDATE()) 

---

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('msdb')
ORDER BY deqs.last_execution_time DESC

This should show you the time and date of when a query was ran

---

you can use "Automatically generate script on every save", if you are using management studio. This is not certainly logging. Check if useful for you.. ;)

---

If the queries you are interested in are dynamic queries that fail intermittently, you could log the SQL and the datetime and user in a table at the time the dynamic statement is created. It would be done on a case-by case basis though as it requires specific programming to happen and it takes a littel extra processing time, so do it only for those few queries you are most concerned about. But having a log of the specific statements executed can really help when you are trying to find out why it fails once a month only. Dynamic queries are hard to thoroughly test and sometimes you get one specific input value that just won't work and doing this logging at the time the SQL is created is often the best way to see what specifically wasn in the sql that was built.

---

A slightly out-of-the-box method would be to script up a solution in AutoHotKey. I use this, and it's not perfect, but works and is free. Essentially, this script assigns a hotkey to CTRL+SHIFT+R which will copy the selected SQL in SSMS (CTRL+C), save off a datestamp SQL file, and then execute the highlighted query (F5). If you aren't used to AHK scripts, the leading semicolon is a comment.

;CTRL+SHIFT+R to run a query that is first saved off
^+r::
;Copy
Send, ^c
; Set variables
EnvGet, HomeDir, USERPROFILE
FormatTime, DateString,,yyyyMMdd
FormatTime, TimeString,,hhmmss
; Make a spot to save the clipboard
FileCreateDir %HomeDir%\Documents\sqlhist\%DateString%
FileAppend, %Clipboard%, %HomeDir%\Documents\sqlhist\%DateString%\%TimeString%.sql
; execute the query
Send, {f5}
Return

The biggest limitations are that this script won't work if you click "Execute" rather than use the keyboard shortcut, and this script won't save off the whole file - just the selected text. But, you could always modify the script to execute the query, and then select all (CTRL+A) before the copy/save.

Using a modern editor with "find in files" features will let you search your SQL history. You could even get fancy and scrape your files into a SQLite3 database to query your queries.

참고URL : https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio