Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Extended events  [new]
Любопытный гость
Guest
Серверы 2008R2 DE 10.50.4263 и 2012 DE 11.0.3339.0
Запускаем код основанный на этой статье
IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents ON SERVER
GO
-- Create our Event Session for current Session_ID
DECLARE @SqlCmd NVARCHAR(MAX) = N'
CREATE EVENT SESSION SQLStmtEvents
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    ),
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE (sqlserver.session_id = '+CAST(@@SPID AS NVARCHAR(4))+')    )
ADD target package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON)';
EXEC(@SqlCmd);
GO
-- Start the Event Session
ALTER EVENT SESSION SQLStmtEvents
ON SERVER
STATE=START;
GO
SELECT 1
GO
SELECT cast(t.target_data as xml)
FROM
 sys.dm_xe_session_targets t JOIN sys.dm_xe_sessions s ON t.event_session_address = s.Address
WHERE t.target_name = 'ring_buffer' AND s.Name = 'SQLStmtEvents'
GO
IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'SQLStmtEvents')
    DROP EVENT SESSION SQLStmtEvents ON SERVER
GO
+ результат на 2008R2
<RingBufferTarget truncated="0" eventsPerSec="0" processingTime="0" totalEventsProcessed="0" eventCount="0" droppedCount="0" memoryUsed="0" />
+ результат на 2012
<RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="3" eventCount="3" droppedCount="0" memoryUsed="2432">
  <event name="sql_statement_starting" package="sqlserver" timestamp="2013-04-03T08:19:32.093Z">
    <data name="state">
      <type name="statement_starting_state" package="sqlserver" />
      <value>0</value>
      <text>Normal</text>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>1</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>0</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>-1</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>SELECT 1
</value>
    </data>
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>SELECT 1
</value>
    </action>
    <action name="tsql_stack" package="sqlserver">
      <type name="xml" package="package0" />
      <value>
        <frames>
          <frame level="1" handle="0x010001001D3F0602008229F50C00000000000000000000000000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
        </frames>
      </value>
    </action>
    <action name="attach_activity_id_xfer" package="package0">
      <type name="activity_id_xfer" package="package0" />
      <value>19D2EC3B-BC11-4BAF-A2AB-C1EDF9743EAC-0</value>
    </action>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0" />
      <value>2942D486-E40D-4717-8B16-7CA85D15D7D6-1</value>
    </action>
  </event>
  <event name="sql_statement_completed" package="sqlserver" timestamp="2013-04-03T08:19:32.093Z">
    <data name="duration">
      <type name="int64" package="package0" />
      <value>36</value>
    </data>
    <data name="cpu_time">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="physical_reads">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="logical_reads">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="writes">
      <type name="uint64" package="package0" />
      <value>0</value>
    </data>
    <data name="row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="last_row_count">
      <type name="uint64" package="package0" />
      <value>1</value>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>1</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>0</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>-1</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>SELECT 1
</value>
    </data>
    <data name="parameterized_plan_handle">
      <type name="binary_data" package="package0" />
      <value />
    </data>
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>SELECT 1
</value>
    </action>
    <action name="tsql_stack" package="sqlserver">
      <type name="xml" package="package0" />
      <value>
        <frames>
          <frame level="1" handle="0x010001001D3F0602008229F50C00000000000000000000000000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
        </frames>
      </value>
    </action>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0" />
      <value>2942D486-E40D-4717-8B16-7CA85D15D7D6-2</value>
    </action>
  </event>
  <event name="sql_statement_starting" package="sqlserver" timestamp="2013-04-03T08:19:32.144Z">
    <data name="state">
      <type name="statement_starting_state" package="sqlserver" />
      <value>0</value>
      <text>Normal</text>
    </data>
    <data name="line_number">
      <type name="int32" package="package0" />
      <value>1</value>
    </data>
    <data name="offset">
      <type name="int32" package="package0" />
      <value>0</value>
    </data>
    <data name="offset_end">
      <type name="int32" package="package0" />
      <value>-1</value>
    </data>
    <data name="statement">
      <type name="unicode_string" package="package0" />
      <value>SELECT cast(t.target_data as xml)
FROM
 sys.dm_xe_session_targets t JOIN sys.dm_xe_sessions s ON t.event_session_address = s.Address
WHERE t.target_name = 'ring_buffer' AND s.Name = 'SQLStmtEvents'
</value>
    </data>
    <action name="sql_text" package="sqlserver">
      <type name="unicode_string" package="package0" />
      <value>SELECT cast(t.target_data as xml)
FROM
 sys.dm_xe_session_targets t JOIN sys.dm_xe_sessions s ON t.event_session_address = s.Address
WHERE t.target_name = 'ring_buffer' AND s.Name = 'SQLStmtEvents'
</value>
    </action>
    <action name="tsql_stack" package="sqlserver">
      <type name="xml" package="package0" />
      <value>
        <frames>
          <frame level="1" handle="0x02000000C1BD871BD2585ABE5365F1E751E3FCD5EE7232FF0000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
        </frames>
      </value>
    </action>
    <action name="attach_activity_id_xfer" package="package0">
      <type name="activity_id_xfer" package="package0" />
      <value>19D2EC3B-BC11-4BAF-A2AB-C1EDF9743EAC-0</value>
    </action>
    <action name="attach_activity_id" package="package0">
      <type name="activity_id" package="package0" />
      <value>4C67842D-C443-4101-BA1F-CE8D1E3E7C95-1</value>
    </action>
  </event>
</RingBufferTarget>


Почему на Р2 получили фигвам? Как бороть?
3 апр 13, 12:26    [14130382]     Ответить | Цитировать Сообщить модератору
 Re: Extended events  [new]
Любопытный гость
Guest
Никто не знает? Или я опять глупость сморозил?
8 апр 13, 09:36    [14149621]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить