Perguntas[extended-events](dba)

Criamos um evento estendido para capturar consultas de longa duração, o objetivo é obter o plano de execução juntando-se à coluna de manipulação do plano, alguns dados do evento têm um identificador de plano com valor de id, mas alguns têm apenas "00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 e incapaz de buscar o plano de execução com base neste ZERO e produzir Null.

aqui está 1 exemplo, por favor, guie o que está errado aqui e como recuperar o plano de execução para tal identificador de plano?

Muito obrigado pelo seu tempo.

<event name="sql_batch_completed" package="sqlserver" timestamp="2022-08-19T16:13:10.711Z">
  <data name="cpu_time">
    <value>34797000</value>
  </data>
  <data name="duration">
    <value>42259048</value>
  </data>
  <data name="physical_reads">
    <value>640</value>
  </data>
  <data name="logical_reads">
    <value>17204111</value>
  </data>
  <data name="writes">
    <value>79477</value>
  </data>
  <data name="row_count">
    <value>8180</value>
  </data>
  <data name="result">
    <value>0</value>
    <text>OK</text>
  </data>
  <data name="batch_text">
    <value>select  SYSDATETIMEOFFSET() AS [Current_System_DateTime_Offset],  event_data,  
  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) AS [ServerDateTime] 
   , (select top 1 query_plan from sys.dm_exec_query_plan(CONVERT(VARBINARY(64), '0x' + 
   CAST((CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)'))  AS varchar(4000)),1))) Query_plan  ,
   CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)') plan_handle
   
 FROM sys.fn_xe_file_target_read_file('C:\folder1\long_queries_long_queries_36c0ad49cf4e462fb7419312d0*xel', NULL, NULL, NULL) 
 where  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) &gt;  '2022-08-18 17:45:11.3280000'
  order by DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) desc</value>
  </data>
  <action name="collect_system_time" package="package0">
    <value>2022-08-19T16:13:10.711Z</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft SQL Server Management Studio - Query</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>CHANDAN-PC</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>SQLPlanner</value>
  </action>
  <action name="plan_handle" package="sqlserver">
    <value>0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>53</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>CHANDAN-PC\Chandan Kumar</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>select  SYSDATETIMEOFFSET() AS [Current_System_DateTime_Offset],  event_data,  
  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) AS [ServerDateTime] 
   , (select top 1 query_plan from sys.dm_exec_query_plan(CONVERT(VARBINARY(64), '0x' + 
   CAST((CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)'))  AS varchar(4000)),1))) Query_plan  ,
   CONVERT(XML, event_Data).value(N'(event/action[@name="plan_handle"]/value)[1]', N'nvarchar(max)') plan_handle
   
 FROM sys.fn_xe_file_target_read_file('C:\folder1\long_queries_36c0ad49cf4e462fb7419312d0*xel', NULL, NULL, NULL) 
 where  DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) &gt;  '2022-08-18 17:45:11.3280000'
  order by DATEADD(SS, DATEDIFF(SS, GETUTCDATE(), CURRENT_TIMESTAMP), CONVERT(XML, event_Data).value('(event/@timestamp)[1]', 'datetime2')) desc</value>
  </action>
  <action name="transaction_id" package="sqlserver">
    <value>0</value>
  </action>
  <action name="transaction_sequence" package="sqlserver">
    <value>0</value>
  </action>
  <action name="username" package="sqlserver">
    <value>CHANDAN-PC\Chandan Kumar</value>
  </action>
</event>

 

Estou tentando rastrear todas as execuções de um procedimento armazenado (incluindo de dentro de outros procedimentos armazenados) e as instruções desse procedimento - isso é possível com eventos estendidos? Meu evento estendido atual é:

CREATE EVENT SESSION [EVENT_NAME] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%'))),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%'))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id)
WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MY_STORED_PROCEDURE_NAME%')))
ADD TARGET package0.event_file(SET filename=N'TraceData')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Mas isso não parece funcionar a menos que eu execute o procedimento manualmente. Algum método de boas práticas para fazer isso?

Como faço para converter a string de hash de consulta "0x9F37D9B585242D49" em um uint64 necessário para meu filtro de evento estendido? A conversão para bigint não funciona, pois o valor fica negativo.

event
sql_statement_completed

filter
field : sqlserver.query_hash
operator : equal_uint64
value ???

Eu tenho uma sessão de evento estendida para rastrear deadlocks e tenho o armazenamento de dados configurado para Event_File, tamanho máximo de arquivo de 20 MB, número máximo de arquivos é 5, habilitar rollover de arquivo é true.

Parece que não consigo alterar o armazenamento de dados, mesmo depois de interromper a sessão. Está tudo acinzentado. Eu preciso ser capaz de aumentar o número de arquivos, porque por algum motivo não está realmente indo para o tamanho máximo de arquivo de 20 MB. Já tenho 4 arquivos para esta sessão, e o maior tamanho é 47 KB.

Como posso aumentar isso? Só preciso recriar a sessão? Já tenho uma história nele que quero manter.

Eu criei uma sessão de eventos estendida que observa o tipo de evento module_start e filtra com base no object_name: equal_i_sql_unicode_string]([object_name])

O objetivo desta sessão é simplesmente registrar informações básicas sempre que um proc na lista de filtros for chamado para que eu possa responder às perguntas dos desenvolvedores com 99,9% de garantia se um proc ainda é chamado em produção. A ideia é executar isso por ~ 1 mês 24 horas por dia, 7 dias por semana (sim, não leva em conta as coisas que são executadas anualmente, mas é o que é).

O problema que estou enfrentando é que a lista de procs que o desenvolvedor me deu tem cerca de 90 ou mais e a lista de filtros de uma sessão EE é limitada a 3.000 caracteres. A única ideia que tive para aumentar a taxa na qual podemos rastrear os procs é ter 2 sessões de EE separadas que são idênticas, exceto que os predicados de filtro são diferentes.

Não estou perguntando "qual será o impacto da CPU", mas mais ou menos é a preocupação deles em executar 2 das mesmas sessões de EE com diferentes predicados de filtro? É estranho para mim que a Microsoft limite a lista de filtros a 3.000 caracteres quando 'mais filtragem == melhor desempenho' porque a maneira como o EE é incorporado ao mecanismo é muito otimizado, ao contrário de um rastreamento que age mais como um proxy do que um "gatilho baseado em um evento".

É seguro assumir que, seja qual for o impacto no desempenho de executar 1 sessão, posso multiplicá-lo por 2 ou não estou considerando outras preocupações?

A duração de rpc_completed (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp? No meu caso, vi uma grande diferença entre eles. A duração é de apenas alguns milissegundos, onde rpc_starting.timestamp - rpc_completed.timestamp é de 6 a 10 segundos.

Estou na empresa 2016 sp2-cu14, o campo de duração está em microssegundos. Eu fiz algo semelhante para capturar sp_statement_starting e sp_statement_completed, sp_batch_starting/sp_batch_completed dentro de rpc_starting e rpc_completed. mostra a mesma situação. Minha pergunta é "qual me diz quanto tempo a consulta realmente levou? É Duração em sp_statement_completed ou a diferença entre iniciar e concluir?"

Eu tenho alguns clientes que às vezes executam algumas consultas muito caras "não permitidas".

Eu os localizei usando Eventos Estendidos.

É possível rejeitar essas consultas automaticamente? Eu conheço a instrução SQL, então seria capaz de pegá-los usando o evento "sql_text" no EE.

Mas não encontrei uma maneira de executar nenhuma ação no EE quando, por exemplo, um determinado evento está acontecendo.

Ou existe outra maneira de contornar isso?

Usando a classe QueryTableXEventData no Powershell, sou capaz de analisar centenas de arquivos XEL muito rapidamente e exportar seu conteúdo para uma tabela no SQL Server usando SQLBulkCopy

Exemplo:

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData
("\\some_file_path\XELog_Files*.xel")

Eu não vejo uma propriedade File no array $fields.

$event | Select-Object -ExpandProperty Fields

is_cached     System.Boolean              True
is_recovered  System.Boolean             False
is_dac        System.Boolean             False
database_id   System.UInt32                 73
packet_size   System.UInt32               8000
options       System.Byte[]  {32, 0, 0, 40...}
options_text  System.String
database_name System.String

Ou em $eventos

Name      : login
UUID      : 13e22e12-3cb8-49bf-a3e1-131faa95601c
Package   : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropPackage
Metadata  : Microsoft.SqlServer.XEvent.Linq.Internal.XEventInteropEventMetadata
Timestamp : 18/08/2020 03:41:37 +00:00
Fields    : {is_cached, is_recovered, is_dac, database_id...}
Actions   : {server_instance_name, database_name, database_id, client_hostname...}
Location  : Microsoft.SqlServer.XEvent.Linq.EventLocator

Existe uma propriedade que eu possa usar em SQLBulkCopy ou QueryTableXEventData que tenha o nome do arquivo que está sendo lido ou exportado?

Eu uso o evento rpc_completed para capturar sprocs executados. Também devo visualizar a instrução e os parâmetros passados ​​usando a seguinte maneira:

clique com o botão direito do mouse em evento estendido -> Assistir a dados ao vivo e, finalmente, veja instrução + parâmetros

No entanto quero ver os dados capturados em formato de tabela, mas não consegui implementar. Quando executo a consulta abaixo, ele mostra a instrução sem parâmetros. Como posso ver os parâmetros no formulário da tabela? Precisa de suas sugestões.

IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL
DROP TABLE #capture_waits_data
SELECT CAST(target_data as xml) AS targetdata
INTO #capture_waits_data
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xes
ON xes.address = xet.event_session_address
WHERE xes.name = 'LongRunningQueries'
AND xet.target_name = 'ring_buffer';
--*/
/**********************************************************/
SELECT
CONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type,
xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS statement,
xed.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms,
xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads,
xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads
FROM #capture_waits_data
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data)
WHERE 1=1 and xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') <> 'NT SERVICE\SQLSERVERAGENT'

Aqui está o evento estendido usado real:

CREATE EVENT SESSION [LongRunningQueries] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([duration]>(4000000) AND [sqlserver].[database_name]='mydb')) 
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Estou configurando um rastreamento de Eventos Estendidos em uma Instância Gerenciada ( NÃO BD SQL do Azure) pela primeira vez.

Estes são os passos que eu segui cuidadosamente:

1. Criei uma chave mestra no meu banco de dados.
2. Criou uma credencial de escopo de banco de dados usando a assinatura de acesso compartilhado correta.
3. Criou e iniciou um rastreamento. Isso funciona e tudo é rastreado conforme o esperado.

Agora, estou tentando consultar o arquivo XEL usando T-SQL:

SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

No entanto, recebo dois comportamentos diferentes:

• Do SSMS: apenas recebo zero linhas e nenhum erro ou mensagens exibidas.

• Do Azure DataStudio: recebo a seguinte mensagem de erro

Msg 300, Level 14, State 1, Line 1 A permissão VIEW SERVER STATE foi negada no objeto 'servidor', banco de dados 'mestre'. Msg 297, Level 16, State 1, Line 1 O usuário não tem permissão para realizar esta ação.

Alguns fatos:

• Consigo baixar o arquivo XEL usando o Gerenciador de Armazenamento do Azure.
• Já verifiquei o caminho completo para o arquivo XEL e está correto. Posso baixar os arquivos diretamente usando um navegador.
• O nível de acesso do contêiner é Público.
• Já tentei com arquivos diferentes (executando e parando traces, todos com dados).
• Os arquivos XEL estão cheios de dados, pois eu os consulto localmente e posso abri-los usando o SSMS.
• Eu realmente não acho que haja uma chave ausente ou uma credencial com escopo definido. Caso contrário, o rastreamento não funcionaria. Enfim, larguei tudo e comecei de novo do zero e o comportamento não mudou.
• Todos os meus testes foram realizados usando a conta de administrador da instância gerenciada.

Então...

• A leitura de arquivos XEL é realmente suportada em instâncias gerenciadas?
• Existe alguma explicação sobre como acessar os arquivos usando a função sys.fn_xe_file_target_read_file?