Extended Events – Page 2 – Reporting Services

August 7, 2019August 7, 2019

Get data to lab report

I started with series of post, I would like to show how to prepare simple testing environment, you can get the data when you test or compare queries performance for example, and you would like to save results and visualise them.

In this post we create simple table structure and SQL procedure which will be used in next post as Chart dataset.

I would like to save following information’s:

Test identification – identification of test – for example testing clustered vs nanocluster index, sorting group vs hash group, etc.
Task name – identification of task we made in test – for example task1 = table with clustered index, task2 heap table
Counter name (for example number of reads, writes, rows, etc.)
Counter value – measured results of tests

As you can see the structure is very simple, I believe we could design much sophisticated solution but for our purposes will be enough. Next time we can extend our solution.

Here is the final structure:

SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
 
CREATE TABLE [dbo].[LabTable](
    [WorkoutName] [sysname] NOT NULL,
    [TaskOwnerName] [sysname] NOT NULL,
    [CounterName] [sysname] NOT NULL,
    [CounterValue] [sql_variant] NULL,
    [InsertDateTime] [datetime] NULL
)
ON [PRIMARY]
GO
 
ALTER
TABLE [dbo].[LabTable] ADD
CONSTRAINT [df_InsertDateTime] DEFAULT (getdate())
FOR [InsertDateTime]
GO

Stored procedure returning data to dataset:

SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SSRSGetDatset]
@WorkoutName SYSNAME
AS
BEGIN
SELECT WorkoutName,
     TaskOwnerName,
     CounterName,
     TRY_CONVERT(BIGINT,TRY_CONVERT(DATETIME, CounterValue))
AS CounterValue,
     InsertDateTime
FROM dbo.LabTable
WHERE WorkoutName = @WorkoutName
END
GO

In next post we fill structures with tested data and create graph to visualise our results. Stay tuned.

August 3, 2019December 31, 2019

Extended events advanced

I would like to describe useful queries for extended events in more detail in this post Extended events get description. I will follow my previous post where I put few basic queries to get some extended events info.

Extended events are triggers occurring in SQL system. Each extended event has its own set of properties -data you get from result. Extended events are collected in packages, events can be combined from different packages. List of packages defined in SQL server instance

SELECT * FROM sys.dm_xe_packages

Beside of that each event can fire other actions synchronously int the same thread within event was invoked. Action extend extended events with own data in response.

For example, if you wold like to get sql text of a query it is defined by an action.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='action'

To filter event data, you can define Predicates. There is a list of objects you can use as predicate source.

 SELECT *
 FROM sys.dm_xe_objects o 
WHERE o.object_type='pred_source'

List of operators

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='pred_compare'

To start collecting data you must define session object which is set of actions, event data, predicates and target object.

Target object collects output event data by synchronous or asynchronous way. Data can be collected for example to buffer, file, Windows Events, etc.

SELECT * FROM sys.dm_xe_objects o WHERE o.object_type='target'

User – friendly description of internal key words which could be returned from events data

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='map'

Relationship between events implemented in created sessions and actions

SELECT * FROM sys.dm_xe_session_event_actions

Data types you can expect from data collection.

SELECT *
  FROM sys.dm_xe_objects o 
 WHERE o.object_type='type'

Next time I would like to share some solutions implemented using extended events. Stay tuned.

August 2, 2019March 21, 2020

Extended events query plan

I need to cover few scenarios connected to SQL statements or procedures and their Execution plans and IO statistics:

To monitor stored procedures performance running on server
To get execution plan from procedures not easily accessible from management studio
- Procedures called from service broker applications
- Huge nested level of SQL statement
Compare batches in testing labs

I decided to use Extended Events as lightweight solution for such a scenarios.

I found following tree evens very usefull to get requested data.

sqlserver.query_post_execution_showplan
- to get execution plan of running query
sqlserver.sql_statement_completed
- to get IO informations regarding executed query
sqlserver.sp_statement_completed
- to get IO informations regarding executed stored procedure¨

Bellow you can find simple script creating above mentioned events with resctriction to concreted session and database.

CREATE EVENT SESSION Test ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan -- ,
                         (
                         ACTION (sqlserver.database_name,
                                 sqlserver.plan_handle,
                                 sqlserver.query_plan_hash,
                                 sqlserver.session_id,
                                 sqlserver.sql_text,
                                 sqlserver.username)
                            WHERE
                                         sqlserver.session_id=58
                                         AND sqlserver.database_id = 8
                          )
,
ADD EVENT sqlserver.sql_statement_completed -- ,
                         (
                         ACTION (sqlserver.database_name,
                                 sqlserver.plan_handle,
                                 sqlserver.query_plan_hash,
                                 sqlserver.session_id,
                                 sqlserver.sql_text,
                                 sqlserver.username)
                            WHERE
                                         sqlserver.session_id=58
                                         AND sqlserver.database_id = 8
                          ),
ADD EVENT sqlserver.sp_statement_completed -- ,
                    (
                    ACTION (sqlserver.database_name,
                            sqlserver.plan_handle,
                            sqlserver.query_plan_hash,
                            sqlserver.session_id,
                            sqlserver.sql_text,
                            sqlserver.username)
                    WHERE
                                     sqlserver.session_id=58
                                     AND sqlserver.database_id = 8
                     )
ADD TARGET package0.ring_buffer (SET max_memory = 4096  ) WITH (max_dispatch_latency = 1 seconds)

Let’s start the session.

ALTER EVENT SESSION Test ON SERVER STATE = START

Here is an example to get data from the session.

SELECT CAST(LocksAcquired.TargetXml AS XML) AS XMLData
  INTO #LogData
  FROM (SELECT CAST(t.target_data AS XML) AS TargetXml
          FROM sys.dm_xe_session_targets AS t
          JOIN sys.dm_xe_sessions AS s ON s.address= t.event_session_address
         WHERE s.name= 'Test'   ) AS LocksAcquired;
 SELECT T.XMLData.value('(action[@name="sql_text"])[1]','varchar(1000)' ) AS [SqlText],
        T.XMLData.value('(@timestamp)[1]',    'datetime' )                AS [OccurredDtTm],
        T.XMLData.value('(data[@name="message"])[1]','varchar(60)')       AS [Message]
        ,T.XMLData.value('(action[@name="username"])[1]','SYSNAME')       AS [UserName]
        ,T.XMLData.value('(action[@name="session_id"])[1]','INT')         AS [SessionID]
        ,T.XMLData.value('(action[@name="query_plan_hash"])[1]','DECIMAL(38,0)') AS [QueryPLanHash]
        ,T.XMLData.value('(action[@name="plan_handle"])[1]','VARBINARY(MAX)') AS [QueryPLanHandle]
        ,T.XMLData.value('(data[@name="cpu_time"])[1]','INT') AS [CPUTime] 
        ,T.XMLData.value('(data[@name="duration"])[1]','BIGINT')  AS [Duration]
        ,NULL [PhysicalReads]
        ,NULL                                 [LogicalReads]
        ,NULL                                 [Writes]
        ,NULL                                 [RowCount]
       ,T.XMLData.value('(data[@name="object_type"])[1]','SYSNAME') AS [ObjectType]
       ,T.XMLData.value('(data[@name="object_id"])[1]','INT') AS [ObjectID]
       ,U.XMLData.query('.')                                                    AS [XML]
       ,T.XMLData.value('(data[@name="estimated_rows"])[1]','BIGINT') AS [EstimatedRow]
      ,T.XMLData.value('(data[@name="estimated_cost"])[1]','BIGINT')  AS [EstimatedCost]
       , 'QueryPlan' AS [type]
         INTO #finalset
         FROM #LogData x 
  CROSS APPLY x.XMLData.nodes('/RingBufferTarget/event[@name="query_post_execution_showplan"]') AS T(XMLData)
  CROSS APPLY t.XMLData.nodes('data[@name="showplan_xml"]/value/*') AS U(XMLData);
    SELECT  T.XMLData.value('(action[@name="sql_text"])[1]','varchar(1000)') AS [SqlText],
            T.XMLData.value('(@timestamp)[1]',    'datetime')                AS [OccurredDtTm],
            T.XMLData.value('(data[@name="message"])[1]','varchar(60)')      AS [Message]
           ,T.XMLData.value('(action[@name="username"])[1]','SYSNAME')       AS [UserName]
           ,T.XMLData.value('(action[@name="session_id"])[1]','INT') AS [SessionID]
           ,T.XMLData.value('(action[@name="query_plan_hash"])[1]', 'DECIMAL(38,0)' ) AS [QueryPLanHash]
           ,T.XMLData.value('(action[@name="plan_handle"])[1]', 'VARBINARY(MAX)' )    AS [QueryPLanHandle]
           ,T.XMLData.value('(data[@name="cpu_time"])[1]', 'INT')                     AS [CPUTime]
           ,T.XMLData.value('(data[@name="duration"])[1]','BIGINT')                   AS [Duration]
           ,T.XMLData.value('(data[@name="physical_reads"])[1]','BIGINT')                [PhysicalReads]
           ,T.XMLData.value('(data[@name="logical_reads"])[1]','BIGINT')                 [LogicalReads]
           ,T.XMLData.value('(data[@name="writes"])[1]', 'BIGINT' )                      [Writes]
           ,T.XMLData.value('(data[@name="row_count"])[1]','BIGINT')                     [RowCount]
           ,T.XMLData.value('(data[@name="object_type"])[1]', 'SYSNAME' )              AS [ObjectType]
           ,T.XMLData.value('(data[@name="object_id"])[1]',  'INT' )                   AS [ObjectID]
                -- ,U.XMLData.query('.')                                                AS [XML]
            ,CAST (NULL AS XML) AS [XML]
            ,T.XMLData.value('(data[@name="estimated_rows"])[1]','BIGINT')              AS [EstimatedRow]
           ,T.XMLData.value('(data[@name="estimated_cost"])[1]','BIGINT')               AS [EstimatedCost]
           , 'IOSql' AS [type]
         INTO #finalsetII
         FROM #LogData x
        CROSS APPLY x.XMLData.nodes('/RingBufferTarget/event[@name="sql_statement_completed"]') AS T(XMLData)

SELECT  T.XMLData.value('(action[@name="sql_text"])[1]','varchar(1000)') AS [SqlText],
        T.XMLData.value('(@timestamp)[1]',    'datetime' )               AS [OccurredDtTm],
        T.XMLData.value('(data[@name="message"])[1]','varchar(60)' )     AS [Message]
        ,T.XMLData.value('(action[@name="username"])[1]', 'SYSNAME')     AS [UserName]
        ,T.XMLData.value('(action[@name="session_id"])[1]','INT' )       AS [SessionID]
        ,T.XMLData.value('(action[@name="query_plan_hash"])[1]','DECIMAL(38,0)') AS [QueryPLanHash]
        ,T.XMLData.value('(action[@name="plan_handle"])[1]','VARBINARY(MAX)' ) AS [QueryPLanHandle]
        ,T.XMLData.value('(data[@name="cpu_time"])[1]','INT') AS [CPUTime]
        ,T.XMLData.value('(data[@name="duration"])[1]','BIGINT') AS [Duration]
        ,T.XMLData.value('(data[@name="physical_reads"])[1]', 'BIGINT' ) [PhysicalReads]
        ,T.XMLData.value('(data[@name="logical_reads"])[1]', 'BIGINT' )  [LogicalReads]
       ,T.XMLData.value('(data[@name="writes"])[1]', 'BIGINT' )          [Writes]
       ,T.XMLData.value('(data[@name="row_count"])[1]', 'BIGINT' )       [RowCount]
         ,T.XMLData.value('(data[@name="object_type"])[1]','SYSNAME') AS [ObjectType]
       ,T.XMLData.value('(data[@name="object_id"])[1]', 'INT' ) AS [ObjectID]
            -- ,U.XMLData.query('.')                                                    AS [XML]
            , CAST(NULL AS XML) AS [XML]
        ,T.XMLData.value('(data[@name="estimated_rows"])[1]','BIGINT')AS [EstimatedRow]
         ,T.XMLData.value('(data[@name="estimated_cost"])[1]', 'BIGINT' ) AS [EstimatedCost]
       , 'IOProcedure' AS [type]
         INTO #finalsetIII
            FROM  #LogData x
            CROSS APPLY x.XMLData.nodes('/RingBufferTarget/event[@name="sp_statement_completed"]') AS T(XMLData)
            --CROSS APPLY t.XMLData.nodes('data[@name="showplan_xml"]/value/*') AS U(XMLData)
            ;
SELECT * FROM #finalset UNION ALL SELECT * FROM #finalsetII UNION ALL SELECT * FROM #finalsetIII

Do not forget to clear your session

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'Test')
BEGIN
DROP EVENT SESSION Test ON SERVER;
END

In next post I will publish SQL routines in which above mentioned script will be encapsulated.