none
Can not find 'duation' event under SQL Extended Events for Azure SQL DB

    Întrebare

  • I have been effectively using extended events on 'on prem' MS SQL Server DBs however now trying to add extended events on Azure MS SQL DBs and under event selection I can not find 'duration' as the option. I am looking for queries taking more than 2 secs to execute for application performance improvement. Kindly help. Thank you!
    10 iulie 2018 07:09

Toate mesajele

  • Hello,


    The duration value does exist as shown below:

    XEWaitInfoReader (@file NVARCHAR(255))

    RETURNS TABLE

        RETURN WITH CTE_WaitInfoXE (WaitInfo) AS

        (

            SELECT CAST(C.query('.') AS XML) AS WaitInfo

            FROM (SELECT

                    CAST(event_data AS XML) AS XMLDATA

                FROM

                    sys.fn_xe_file_target_read_file(   

                    @file, null, null, null)) a

            CROSS APPLY a.XMLDATA.nodes('/event') as T(C)

            WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'wait_info'

        )

         SELECT     

            WaitInfo.value('(//event/@name)[1]','varchar(50)') as Name

            ,WaitInfo.value('(//event/@timestamp)[1]','datetime') as [TimeStamp]

            ,WaitInfo.value('(//event/@package)[1]','varchar(128)') as Package

            ,WaitInfo.value('(//event/data[@name="wait_type"]/text)[1]', 'varchar(50)') as WaitType

            ,WaitInfo.value('(//event/data[@name="opcode"]/text)[1]', 'varchar(50)') as OpCode

            ,WaitInfo.value('(//event/data[@name="duration"]/value)[1]', 'int') as Duration

            ,WaitInfo.value('(//event/data[@name="signal_duration"]/value)[1]', 'int') as SignalDuration

            ,WaitInfo.value('(//event/data[@name="wait_resource"]/value)[1]', 'varchar(50)') as WaitResource

            ,WaitInfo.value('(//event/action[@name="sql_text"]/value)[1]', 'varchar(MAX)') as SQLText

            ,WaitInfo.value('(//event/action[@name="username"]/value)[1]', 'varchar(50)') as UserName

            ,WaitInfo.value('(//event/action[@name="client_app_name"]/value)[1]', 'varchar(100)') as Client

    For more information. Read the following article:

    http://www.mssqlgirl.com/using-extended-events-on-azure-sql-database.html


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    10 iulie 2018 11:21