Extended Events SP Logging

------------------------------------
-- Create schema
------------------------------------

IF NOT EXISTS ( SELECT  *
                FROM    sys.schemas
                WHERE   name = N'xe' )
    EXEC('CREATE SCHEMA xe');
GO

------------------------------------
-- Create and start event session
------------------------------------
IF EXISTS(SELECT 1 FROM sys.dm_xe_sessions xe WHERE xe.[name] = 'monitor_sp_durations') DROP EVENT SESSION [monitor_sp_durations] ON SERVER;

CREATE EVENT SESSION [monitor_sp_durations] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.username)
    WHERE (
        [sqlserver].[is_system]=(0)
        AND sqlserver.database_name != N'SSISDB'
        AND sqlserver.database_name != N'msdb'
        AND sqlserver.database_name != N'master'
        AND duration >= 100000
        )),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.username)
    WHERE (
        [sqlserver].[is_system]=(0)
        AND object_type='P '
        AND sqlserver.database_name != N'SSISDB'
        AND sqlserver.database_name != N'msdb'
        AND sqlserver.database_name != N'master'
        AND duration >= 100000
        ))
ADD TARGET package0.ring_buffer(SET max_memory=(20480))
WITH (EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,STARTUP_STATE=OFF,MAX_DISPATCH_LATENCY=30 SECONDS)
GO

ALTER EVENT SESSION [monitor_sp_durations] ON SERVER
STATE = START;

------------------------------------
-- Create tables to log data and view to consume
------------------------------------
IF OBJECT_ID('xe.LoggedProcedures') IS NOT NULL DROP TABLE [xe].[LoggedProcedures];
CREATE TABLE [xe].[LoggedProcedures](
    [RowHash]                   [binary](16) NOT NULL
        CONSTRAINT [PK_LoggedProcedures] PRIMARY KEY CLUSTERED ([RowHash] ASC),
    [ObjectName]                [nvarchar](128) NULL,
    [ExecStatement]             [nvarchar](4000) NULL,
    [EventName]                 [varchar](64) NOT NULL,
    [DatabaseName]              [varchar](64) NOT NULL,
    [ClientAppName]             [varchar](255) NULL,
    [ClientHostName]            [varchar](255) NULL,
    [ServerPrincipalName]       [nvarchar](255) NULL,
    [UserName]                  [nvarchar](128) NULL,
    [ObjectId]                  [int] NULL,
);

IF OBJECT_ID('xe.ProcedureExecutions') IS NOT NULL DROP TABLE [xe].[ProcedureExecutions];
CREATE TABLE [xe].[ProcedureExecutions](
    [Id]                        [int] IDENTITY(1,1) NOT NULL
        CONSTRAINT [PK_ProcedureExecutions] PRIMARY KEY CLUSTERED ([Id] ASC),
    [LoggedProceduresRowHash]   [int] NOT NULL,
    [EventTimeUTC]              [datetime] NOT NULL,
    [Duration]                  [bigint] NOT NULL,
    [RowsAffected]              [bigint] NULL,
);

CREATE NONCLUSTERED INDEX NCIX_RowHash ON [xe].[ProcedureExecutions]([LoggedProceduresRowHash]);
GO


------------------------------------
-- Create procedure to write events to permanent tables
------------------------------------
IF OBJECT_ID('xe.ShredProcedureEvents') IS NOT NULL DROP PROCEDURE [xe].[ShredProcedureEvents];
GO

CREATE PROCEDURE xe.ShredProcedureEvents
    @MinuteLookback int
AS
BEGIN
    SET NOCOUNT ON;
    --Inspired by this SA question: https://dba.stackexchange.com/questions/121491/how-can-i-shred-this-extended-events-xml-as-fast-as-possible

    DECLARE @xml XML;
    DECLARE @FilterDate datetime2 = (SELECT DATEADD(minute, -@MinuteLookback, GETUTCDATE()));

    SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
    FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'monitor_sp_durations';

    IF OBJECT_ID('tempdb..#XmlResults') IS NOT NULL
    DROP TABLE #XmlResults;
    IF OBJECT_ID('tempdb..#TabularResults') IS NOT NULL
    DROP TABLE #TabularResults;


    CREATE TABLE #XmlResults
    (
        RowNum INT NOT NULL
            PRIMARY KEY CLUSTERED
            IDENTITY(1,1)
        , xeXML XML NOT NULL
    );

    INSERT INTO #XmlResults (xeXML)
    SELECT xm.s.query('.')
    FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
    OPTION (Optimize FOR (@xml = NULL)); --Allegedly useful for SQL 2008

    --SELECT *
    --FROM #XmlResults;
    WITH XmlParse AS (
    SELECT EventName =              xeXML.value('(event/@name)[1]','varchar(64)')
        , EventDateStamp =          xeXML.value('(event/@timestamp)[1]','datetime2')
        , DatabaseName =            xeXML.value('(event/action[(@name)[1] eq "database_name"]/value/text())[1]','varchar(64)')
        , ClientAppName =           xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
        , ClientHostName =          xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
        , ServerPrincipalName = xeXML.value('(event/action[(@name)[1] eq "server_principal_name"]/value/text())[1]','nvarchar(255)')
        , UserName =                xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','nvarchar(128)')
        , ObjectId =                xeXML.value('(event/data[(@name)[1] eq "object_id"]/value/text())[1]','int')
        , ObjectName =              xeXML.value('(event/data[(@name)[1] eq "object_name"]/value/text())[1]','nvarchar(128)')
        , Duration =                xeXML.value('(event/data[(@name)[1] eq "duration"]/value/text())[1]','bigint')
        , RowsAffected =            xeXML.value('(event/data[(@name)[1] eq "row_count"]/value/text())[1]','bigint')
        , ExecStatement =           xeXML.value('(event/data[(@name)[1] eq "statement"]/value/text())[1]','nvarchar(4000)')
    FROM #XmlResults xm
    )

    SELECT *
        , HASHBYTES('MD5',(SELECT EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement FROM (values(null))foo(bar) FOR XML AUTO)) AS [RowHash]
    INTO #TabularResults
    FROM XmlParse
    WHERE EventDateStamp>@FilterDate;

    --SELECT * FROM #TabularResults

    INSERT INTO xe.LoggedProcedures(RowHash, EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement)
    SELECT DISTINCT RowHash
        ,EventName, DatabaseName, ClientAppName, ClientHostName, ServerPrincipalName, UserName, ObjectId, ObjectName, ExecStatement
    FROM #TabularResults t
    WHERE RowHash NOT IN (SELECT RowHash FROM xe.LoggedProcedures)

    INSERT INTO xe.ProcedureExecutions([LoggedProceduresRowHash], [EventTimeUTC], Duration, RowsAffected)
    SELECT RowHash, EventDateStamp, Duration, RowsAffected
    FROM #TabularResults
END
GO

------------------------------------
-- Create job to schedule persisting events from ringbuffer to tables
------------------------------------

IF EXISTS (SELECT job_id
            FROM msdb.dbo.sysjobs_view
            WHERE name = N'ShredExtendedEvents') EXEC msdb.dbo.sp_delete_job @job_name=N'ShredExtendedEvents', @delete_unused_schedule=1;
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ShredExtendedEvents',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Shreds extended event xml from ring buffer into tables',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Shred past 10 minutes of events',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXEC xe.ShredProcedureEvents @MinuteLookback=10',
        @database_name=N'DBAdmin',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Shred_Every_10min',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=10,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20200703,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO