Come visualizzare la cronologia delle query in SQL Server Management Studio

La cronologia delle query è memorizzata in alcuni file di registro? Se sì, puoi dirmi come trovare la loro posizione? Se no, puoi darmi qualche consiglio su come vederlo?

[Poiché questa domanda sarà probabilmente chiusa come duplicato.]

Se SQL Server non è stato riavviato (e il piano non è stato rimosso, ecc.), È ansible trovare la query nella cache del piano.

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

Se hai perso il file a causa di un arresto anomalo di Management Studio, potresti trovare qui i file di ripristino:

 C:\Users\\Documents\SQL Server Management Studio\Backup Files\ 

Altrimenti dovrai utilizzare qualcos’altro in avanti per aiutarti a salvare la cronologia delle tue query, come il pacchetto di strumenti SSMS menzionato nella risposta di Ed Harper, anche se non è gratuito in SQL Server 2012+. Oppure puoi impostare alcune tracce leggere filtrate sul tuo login o nome host (ma per favore usa una traccia lato server, non Profiler, per questo).


Come ha commentato @ Nenad-Zivkovic, potrebbe essere utile unirsi a sys.dm_exec_query_stats e ordinare da 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; 

Uno in ritardo, ma si spera utile poiché aggiunge ulteriori dettagli …

Per impostazione predefinita, non è ansible visualizzare query eseguite in SSMS. Ci sono diverse opzioni però.

Leggere il log delle transazioni – questa non è una cosa facile da fare perché è in un formato proprietario. Tuttavia, se hai bisogno di vedere le query che sono state eseguite storicamente (tranne SELECT) questo è l’unico modo.

È ansible utilizzare strumenti di terze parti come ApexSQL Log e SQL Log Rescue (gratuito ma solo SQL 2000). Dai un’occhiata a questo thread per maggiori dettagli qui Explorer Transaction Log Explorer / Analyzer

Profiler di SQL Server: ideale se si desidera avviare il controllo e non si è interessati a ciò che è accaduto in precedenza. Assicurati di utilizzare i filtri per selezionare solo le transazioni di cui hai bisogno. Altrimenti finirai con una tonnellata di dati molto velocemente.

Traccia SQL Server: ideale se si desidera acquisire tutti o quasi tutti i comandi e conservarli nel file di traccia che può essere analizzato in un secondo momento.

Trigger: ideale se si desidera acquisire DML (tranne selezionare) e memorizzarli da qualche parte nel database

Il pacchetto di strumenti SSMS aggiunge funzionalità per registrare la cronologia di esecuzione, tra le altre cose.

Il sistema non registra le query in questo modo. Se si sa che si desidera farlo anticipatamente, è ansible utilizzare SQL Profiler per registrare ciò che sta arrivando e tenere traccia delle query durante il periodo in cui Profiler è in esecuzione.

Come altri hanno notato, puoi usare SQL Profiler, ma puoi anche sfruttare le sue funzionalità attraverso le stored procedure di sistema sp_trace_ *. Ad esempio, questo snippet SQL (almeno nel 2000, penso che sia lo stesso per SQL 2008, ma dovrai ricontrollare) catch RPC:Completed e SQL:BatchCompleted per tutte le query che richiedono più di 10 secondi per l’esecuzione e salvare l’output in un file di traccia che è ansible aprire in SQL Profiler in un secondo momento:

 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 

Puoi trovare l’ID per ogni evento di traccia, colonne, ecc. Dalla documentazione online; cercate solo sp_trace_create , sp_trace_setevent e sp_trace_setfiler sprocs. È quindi ansible controllare la traccia come segue:

 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 

… dove ’15’ è l’ID di traccia (come riportato da sp_trace_create, che il primo script esegue il kick out, sopra).

Puoi controllare per vedere quali tracce sono in esecuzione con:

 select * from ::fn_trace_getinfo(default) 

L’unica cosa che dirò con caucanvas – non so quanto carico questo metterà sul tuo sistema; ne aggiungerà un po ‘, ma quanto è grande il fatto che “alcuni” dipenda probabilmente da quanto è occupato il tuo server.

È ansible monitorare le query SQL di SQL Profiler se necessario

Uso la query seguente per tracciare l’attività dell’applicazione su un server SQL su cui non è abilitato il trace profiler. Il metodo utilizza Query Store (SQL Server 2016+) anziché i DMV. Ciò offre una migliore capacità di esaminare dati storici e ricerche più rapide. È molto efficiente catturare query a esecuzione breve che non possono essere acquisite da sp_who / sp_whoistriggersto.

 /* 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  SET QUERY_STORE = ON ALTER DATABASE  SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 100000) */ USE  /* 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 ('') 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()) 

puoi usare “Genera automaticamente script su ogni salvataggio”, se stai usando lo studio di gestione. Questo non è certamente il logging. Controlla se è utile per te ..;)

Se le query a cui si è interessati sono query dinamiche che si verificano in modo discontinuo, è ansible registrare l’SQL e il datetime e l’utente in una tabella nel momento in cui viene creata l’istruzione dynamic. Lo farebbe caso per caso, poiché richiede una programmazione specifica e richiede un po ‘di tempo di elaborazione extra, quindi fallo solo per quelle poche domande che ti interessano di più. Ma avere un registro delle dichiarazioni specifiche eseguite può davvero aiutare quando si sta cercando di scoprire perché non riesce solo una volta al mese. Le query dinamiche sono difficili da testare a fondo e talvolta si ottiene un valore di input specifico che non funzionerà e fare questa registrazione nel momento in cui viene creato l’SQL è spesso il modo migliore per vedere cosa specificamente wasn nello sql che è stato creato.