Jak wyświetlić historię zapytań w SQL Server Management Studio

Czy historia zapytań jest przechowywana w niektórych plikach dziennika? Jeśli tak, czy możesz mi powiedzieć, jak znaleźć ich lokalizację? Jeśli nie, czy możesz mi doradzić, jak to zobaczyć?

[Ponieważ to pytanie prawdopodobnie zostanie zamknięte jako duplikat.]

Jeśli SQL Server nie został ponownie uruchomiony (a plan nie został wykluczony itp.), Możesz znaleźć zapytanie w pamięci podręcznej planu.

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%';

Jeśli zgubiłeś plik z powodu awarii Management Studio, możesz znaleźć pliki odzyskiwania tutaj:

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

W przeciwnym razie będziesz musiał użyć czegoś innego, aby pomóc Ci zapisać historię zapytań, na przykład pakietu narzędzi SSMS, jak wspomniano w odpowiedzi Eda Harpera - chociaż nie jest to bezpłatne w SQL Server 2012+. Lub możesz skonfigurować lekkie śledzenie filtrowane na twoim loginie lub nazwie hosta (ale użyj do tego śledzenia po stronie serwera, a nie Profiler).

Jak skomentował @ Nenad-Zivkovic, pomocne może być dołączenie sys.dm_exec_query_statsi zamówienie przez 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;

Późne, ale miejmy nadzieję, przydatne, ponieważ zawiera więcej szczegółów…

Nie ma możliwości wyświetlenia zapytań wykonywanych domyślnie w programie SSMS. Istnieje jednak kilka opcji.

Czytanie dziennika transakcji - nie jest to łatwe, ponieważ jest w zastrzeżonym formacie. Jeśli jednak chcesz zobaczyć zapytania, które były wykonywane historycznie (z wyjątkiem SELECT), jest to jedyny sposób.

Możesz użyć do tego narzędzi innych firm, takich jak ApexSQL Log i SQL Log Rescue (bezpłatne, ale tylko SQL 2000). Sprawdź ten wątek, aby uzyskać więcej informacji tutaj. Eksplorator / analizator dzienników transakcji programu SQL Server

SQL Server profiler - najlepiej nadaje się, jeśli chcesz tylko rozpocząć inspekcję i nie interesuje Cię to, co wydarzyło się wcześniej. Upewnij się, że używasz filtrów, aby wybrać tylko potrzebne transakcje. W przeciwnym razie bardzo szybko otrzymasz mnóstwo danych.

Śledzenie SQL Server - najlepiej nadaje się, jeśli chcesz przechwycić wszystkie lub większość poleceń i przechowywać je w pliku śledzenia, który można później przeanalizować.

Wyzwalacze - najlepiej nadają się, jeśli chcesz przechwytywać DML (z wyjątkiem selekcji) i przechowywać je gdzieś w bazie danych

Pakiet narzędzi SSMS dodaje między innymi funkcję rejestrowania historii wykonywania.

Jak zauważyli inni, można użyć programu SQL Profiler, ale można również wykorzystać jego funkcjonalność za pomocą systemowych procedur składowanych sp_trace_ *. Na przykład ten fragment kodu SQL (przynajmniej w 2000 r .; myślę, że jest taki sam dla SQL 2008, ale będziesz musiał dwukrotnie sprawdzić) przechwytuje RPC:Completedi SQL:BatchCompletedzdarzenia dla wszystkich zapytań, których uruchomienie zajmuje ponad 10 sekund, i zapisze dane wyjściowe w plik śledzenia, który można później otworzyć w profilerze 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

Identyfikator każdego zdarzenia śledzenia, kolumn itp. Można znaleźć w Books Online; po prostu wyszukaj sp_trace_create , sp_trace_setevent i sp_trace_setfiler sprocs. Następnie możesz sterować śledzeniem w następujący sposób:

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

... gdzie „15” to identyfikator śledzenia (zgodnie z raportem sp_trace_create, który uruchamia pierwszy skrypt powyżej).

Możesz sprawdzić, jakie ślady działają z:

select * from ::fn_trace_getinfo(default)

Jedyne, co powiem z ostrożnością - nie wiem, jak bardzo obciąży to twój system; doda trochę, ale jak duża jest ta „część” prawdopodobnie zależy od tego, jak zajęty jest twój serwer.

System nie rejestruje w ten sposób zapytań. Jeśli jednak wiesz, że chcesz to zrobić z wyprzedzeniem, możesz użyć programu SQL Profiler do rejestrowania nadchodzących danych i śledzenia zapytań w czasie działania programu Profiler.

Używam poniższego zapytania do śledzenia aktywności aplikacji na serwerze SQL, który nie ma włączonego profilera śledzenia. Metoda używa magazynu zapytań (SQL Server 2016+) zamiast DMV. Daje to lepszą możliwość wglądu w dane historyczne, a także szybsze wyszukiwanie. Przechwytywanie krótko działających zapytań, których nie można przechwycić przez sp_who / sp_whoisactive, jest bardzo wydajne.

/* 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

Powinno to pokazać godzinę i datę uruchomienia zapytania

Jeśli potrzebujesz, możesz monitorować zapytania SQL za pomocą programu SQL Profiler

Historię zapytań można przeglądać za pomocą widoków systemowych:

1. sys.dm_exec_query_stats
2. sys.dm_exec_sql_text
3. sys.dm_exec_query_plan

Na przykład, używając następującego zapytania:

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'

Aktualne zapytania można zobaczyć za pomocą następującego skryptu:

select ES.[session_id]
      ,ER.[blocking_session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
      , COALESCE(
                    CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
                   ,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '')  <> 'running') 
                            THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
                    END
                ) as [total_time, sec]
      , CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
      , CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running') 
                THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
        END as [sleep_time, sec] --Время сна в сек
      , NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
      , CASE  ER.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommited'
        WHEN 2 THEN 'ReadCommited'
        WHEN 3 THEN 'Repetable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        END as [transaction_isolation_level_desc]
      ,ER.[status]
      ,ES.[status] as [status_session]
      ,ER.[command]
      ,ER.[percent_complete]
      ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      , SUBSTRING(
                    (select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
                  , ER.[statement_start_offset]/2+1
                  , (
                        CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
                                THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
                             ELSE ER.[statement_end_offset]
                        END
                        - ER.[statement_start_offset]
                    )/2 +1
                 ) as [CURRENT_REQUEST]
      ,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
      ,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
      ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
      ,ER.[wait_type]
      ,ES.[login_time]
      ,ES.[host_name]
      ,ES.[program_name]
      ,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
      ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
      ,ES.[host_process_id]
      ,ES.[client_version]
      ,ES.[client_interface_name]
      ,ES.[security_id]
      ,ES.[login_name]
      ,ES.[nt_domain]
      ,ES.[nt_user_name]
      ,ES.[memory_usage]
      ,ES.[total_scheduled_time]
      ,ES.[last_request_start_time]
      ,ES.[last_request_end_time]
      ,ES.[is_user_process]
      ,ES.[original_security_id]
      ,ES.[original_login_name]
      ,ES.[last_successful_logon]
      ,ES.[last_unsuccessful_logon]
      ,ES.[unsuccessful_logons]
      ,ES.[authenticating_database_id]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,NULL as [dop]--ER.[dop]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
      ,ER.[user_id]
      ,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])

To żądanie wyświetla wszystkie aktywne żądania i wszystkie żądania, które jawnie blokują aktywne żądania.

Wszystkie te i inne przydatne skrypty są implementowane jako reprezentacje w bazie danych SRV , która jest dystrybuowana bezpłatnie. Na przykład, pierwszy skrypt pochodzi z widoku [inf]. [VBigQuery] , a drugi z widoku [inf]. [VRequests] .

Istnieją również różne rozwiązania innych firm dotyczące historii zapytań. Używam Query Manager z Dbeaver : i Query Execution History z SQL Tools , który jest osadzony w SSMS :

Ta funkcja nie jest dostępna po wyjęciu z pudełka w programie SSMS.

Jeśli używasz SSMS 18 lub nowszego, możesz spróbować SSMSPlus.

Posiada funkcję historii zapytań.

https://github.com/akarzazi/SSMSPlus

Zastrzeżenie: jestem autorem.

jeśli korzystasz z Management Studio, możesz użyć opcji „Automatycznie generuj skrypt przy każdym zapisie”. To z pewnością nie jest rejestrowanie. Sprawdź, czy Ci się przyda ..;)

Jeśli zapytania, które Cię interesują, są zapytaniami dynamicznymi, które sporadycznie kończą się niepowodzeniem, możesz zarejestrować SQL, datę i godzinę oraz użytkownika w tabeli w momencie tworzenia instrukcji dynamicznej. Byłoby to jednak wykonywane na zasadzie indywidualnego przypadku, ponieważ wymaga specjalnego programowania i zajmuje trochę dodatkowego czasu przetwarzania, więc rób to tylko dla tych kilku zapytań, na których najbardziej Ci zależy. Jednak rejestrowanie określonych instrukcji może naprawdę pomóc, gdy próbujesz dowiedzieć się, dlaczego kończy się niepowodzeniem tylko raz w miesiącu. Dynamiczne zapytania są trudne do dokładnego przetestowania i czasami otrzymujesz jedną konkretną wartość wejściową, która po prostu nie zadziała, a rejestrowanie w czasie tworzenia kodu SQL jest często najlepszym sposobem, aby zobaczyć, co konkretnie znajdowało się w skompilowanym sql.

Nieco nieszablonową metodą byłoby utworzenie skryptu rozwiązania w AutoHotKey. Używam tego i nie jest to doskonałe, ale działa i jest bezpłatne. Zasadniczo ten skrypt przypisuje klawisz skrótu do CTRL+ SHIFT+, Rktóry skopiuje wybrany SQL w SSMS ( CTRL+ C), zapisze plik SQL z datownikiem, a następnie wykona podświetlone zapytanie ( F5). Jeśli nie jesteś przyzwyczajony do skryptów AHK, początkowy średnik jest komentarzem.

;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

Największym ograniczeniem jest to, że ten skrypt nie zadziała, jeśli klikniesz „Wykonaj” zamiast użyć skrótu klawiaturowego, a skrypt ten nie zapisze całego pliku - tylko zaznaczony tekst. Ale zawsze można zmodyfikować skrypt, aby wykonać zapytanie, a następnie zaznaczyć wszystko ( CTRL+ A) przed kopiowaniem / zapisaniem.

Korzystanie z nowoczesnego edytora z funkcjami „znajdowania w plikach” umożliwia przeszukiwanie historii SQL. Możesz nawet wymyślić i zeskrobać swoje pliki do bazy danych SQLite3, aby zapytać o swoje zapytania.