Maumauboy,
Sim é possível através do uso de Extended Events em alguns cenários combinando com views e dmvs.
Veja neste exemplo, como obter informações sobre o tempo médio de uso de CPU:
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
Neste outro como obter informações sobre transações em cache:
SELECT QT.text,
QP.query_plan,
QS.execution_count,
QS.total_elapsed_time,
QS.last_elapsed_time,
QS.total_logical_reads
FROM sys.dm_exec_query_stats as QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as QT
CROSS APPLY sys.dm_exec_query_plan (QS.plan_handle) as QP
ORDER BY QS.execution_count DESC
Este outro mesmo informações sobre as querys em execução:
SELECT
DES.SESSION_ID,
DES.CPU_TIME,
DES.READS,
DES.WRITES,
DES.LOGICAL_READS,
DES.ROW_COUNT,
DER.SESSION_ID,
DES.STATUS,
DES.HOST_NAME,
DES.PROGRAM_NAME,
DES.LOGIN_NAME,
DES.ORIGINAL_LOGIN_NAME,
DEC.CLIENT_NET_ADDRESS,
DEC.AUTH_SCHEME,
DEC.NET_TRANSPORT,
SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2,
COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], - 1) / 2, 2147483647)) AS COMANDO
FROM
SYS.DM_EXEC_SESSIONS AS DES
INNER JOIN SYS.DM_EXEC_REQUESTS DER
ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC
ON DEC.SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_REQUESTS DER2
ON DER2.SESSION_ID = DES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
GO
Por último, um exemplo de como obter informações sobre a ocorrência de backups:
-- When SQL Server Backups Occurred --
DECLARE @path NVARCHAR(260)
SELECT @path=path FROM sys.traces WHERE is_default = 1
--Security Audit: Audit Backup Event
SELECT DatabaseName, TextData,
Duration, StartTime,
EndTime, SPID,
ApplicationName, LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (115)
and EventSubClass=1
ORDER BY StartTime DESC
Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
