sql-server - tools - view history sql server management studio




Como ver o histórico de consultas no SQL Server Management Studio (7)

O histórico de consultas é armazenado em alguns arquivos de log? Se sim, você pode me dizer como encontrar a localização deles? Se não, você pode me dar algum conselho sobre como vê-lo?


Como outros notaram, você pode usar o SQL Profiler, mas também pode aproveitar sua funcionalidade por meio dos procedimentos armazenados do sistema sp_trace_ *. Por exemplo, este snippet SQL (em 2000, pelo menos; acho que é o mesmo para o SQL 2008, mas você terá que verificar SQL:BatchCompleted eventos RPC:Completed e SQL:BatchCompleted para todas as consultas que levam mais de 10 segundos para serem executadas e salve a saída em um arquivo de rastreio que você pode abrir no SQL Profiler em uma data posterior:

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

Você pode encontrar o ID para cada evento de rastreamento, colunas, etc do Books Online; Basta procurar os sp_trace_create , sp_trace_setevent e sp_trace_setfiler . Você pode então controlar o rastreio da seguinte maneira:

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

... onde '15' é o ID de rastreio (conforme relatado por sp_trace_create, que o primeiro script inicia, acima).

Você pode verificar quais rastreamentos estão sendo executados:

select * from ::fn_trace_getinfo(default)

A única coisa que vou dizer com cautela - eu não sei quanta carga isso vai colocar no seu sistema; ele adicionará alguns, mas o tamanho de "alguns" provavelmente depende da ocupação do seu servidor.


Eu uso a consulta abaixo para rastrear a atividade do aplicativo em um servidor SQL que não possui o perfil de rastreamento habilitado. O método usa o Query Store (SQL Server 2016+) em vez do DMV. Isso permite que você analise dados históricos, bem como pesquisas mais rápidas. É muito eficiente capturar consultas de execução curta que não podem ser capturadas por 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()) 

Se as consultas em que você está interessado forem consultas dinâmicas que falham de maneira intermitente, você poderá registrar o SQL e o datetime e o usuário em uma tabela no momento em que a instrução dinâmica for criada. Isso seria feito caso a caso, já que requer que uma programação específica aconteça e leva um pouco mais de tempo de processamento, por isso, apenas para as poucas perguntas com as quais você está mais preocupado. Mas ter um registro das declarações específicas executadas pode realmente ajudar quando você está tentando descobrir por que ele falha apenas uma vez por mês. Consultas dinâmicas são difíceis de testar completamente e, às vezes, você obtém um valor de entrada específico que simplesmente não funciona e fazer esse registro no momento em que o SQL é criado é geralmente a melhor maneira de ver especificamente o que foi criado.


Tarde um, mas espero útil, pois acrescenta mais detalhes ...

Não há como ver as consultas executadas no SSMS por padrão. Existem várias opções embora.

Lendo o log de transações - isso não é uma coisa fácil de fazer porque está em formato proprietário. No entanto, se você precisar ver as consultas que foram executadas historicamente (exceto SELECT), essa é a única maneira.

Você pode usar ferramentas de terceiros para isso, como ApexSQL Log e SQL Log Rescue (gratuito, mas somente SQL 2000). Confira este tópico para obter mais detalhes aqui Explorador / Analisador de Log de Transações do SQL Server

O SQL Server Profiler - é mais adequado se você deseja apenas iniciar a auditoria e não está interessado no que aconteceu anteriormente. Certifique-se de usar filtros para selecionar apenas as transações necessárias. Caso contrário, você terminará com muitos dados rapidamente.

Rastreamento do SQL Server - mais adequado se você deseja capturar todos ou a maioria dos comandos e mantê-los no arquivo de rastreamento que pode ser analisado posteriormente.

Triggers - mais adequado se você deseja capturar DML (exceto selecionar) e armazená-los em algum lugar no banco de dados



[Uma vez que esta questão provavelmente será encerrada como duplicata.]

Se o SQL Server não tiver sido reiniciado (e o plano não tiver sido despejado, etc.), você poderá encontrar a consulta no cache do plano.

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 você perdeu o arquivo porque o Management Studio travou, você pode encontrar arquivos de recuperação aqui:

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

Caso contrário, você precisará usar outra coisa para ajudá-lo a salvar seu histórico de consultas, como o SSMS Tools Pack, conforme mencionado na resposta de Ed Harper - embora não seja gratuito no SQL Server 2012+. Ou você pode configurar um rastreio leve filtrado em seu login ou nome de host (mas, por favor, use um rastreamento do lado do servidor, não o Profiler, para isso).

Como @ Nenad-Zivkovic comentou, pode ser útil juntar-se a sys.dm_exec_query_stats e ordenar por 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