SQL Server Extended Events Sessions - DBA Pack

gistfile1.txt

-- Blocked Process Report
IF
(
    SELECT value_in_use
    FROM sys.configurations
    WHERE name = N'show advanced options'
) <> 1
BEGIN
    EXEC ('EXEC sp_configure ''show advanced options'', 1;');
    EXEC ('RECONFIGURE');
END;

EXEC sp_configure 'blocked process threshold (s)', 5;
GO
RECONFIGURE;
GO

CREATE EVENT SESSION [Blocked Process Report]
ON SERVER
    ADD EVENT sqlserver.blocked_process_report
    ADD TARGET package0.event_file
    (SET filename = N'd:\Traces\Blocked-Process-Report.xel', max_file_size = (1024), max_rollover_files = (4))
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 120 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO


-- Deadlocks
CREATE EVENT SESSION [deadlock_capture]
ON SERVER
    ADD EVENT sqlserver.database_xml_deadlock_report
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_name,
         sqlserver.server_principal_name,
         sqlserver.session_nt_username,
         sqlserver.sql_text
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'd:\traces\deadlock_capture')
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO


--- Failed Logins
CREATE EVENT SESSION [Failed Logins]
ON SERVER
    ADD EVENT sqlserver.error_reported
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.server_instance_name,
         sqlserver.session_nt_username,
         sqlserver.username
     )
     WHERE (
               [package0].[equal_int64]([Severity], (14))
               AND [error_number] = (18456)
               OR [error_number] = (18452)
               OR [error_number] = (17806)
           )
    )
    ADD TARGET package0.event_file
    (SET filename = N'd:\traces\XE_Failed_Logins.xel', max_file_size = (100), max_rollover_files = (100))
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

--- Good Logins
CREATE EVENT SESSION [Logins]
ON SERVER
    ADD EVENT sqlserver.login
    (SET collect_database_name = (1)
     ACTION
     (
         package0.collect_system_time,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.server_instance_name,
         sqlserver.session_nt_username,
         sqlserver.username
     )
     WHERE (
               [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name], N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
               AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name], N'SQLAgent%')
           )
    )
    ADD TARGET package0.event_file
    (SET filename = N'd:\traces\XE_Logins.xel', max_file_size = (100), max_rollover_files = (100))
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO


--- File Growths to Ring Buffer
CREATE EVENT SESSION [File_Growths_Ring_Buffer]
ON SERVER
    ADD EVENT sqlserver.database_file_size_change
    (ACTION
     (
         package0.collect_system_time,
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.nt_username,
         sqlserver.session_nt_username,
         sqlserver.sql_text
     )
    )
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.database_file_size_change', source = N'sqlserver.database_name'),
    ADD TARGET package0.ring_buffer
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

--- Excessive Memory Grants
CREATE EVENT SESSION [Excessive Memory Grants]
ON SERVER
    ADD EVENT sqlserver.query_memory_grant_usage
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_name,
		 sqlserver.database_id,
         sqlserver.nt_username,
         sqlserver.session_nt_username,
		 sqlserver.username,
         sqlserver.sql_text         
     )
     WHERE ([granted_memory_kb] > (50000))
    )
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.query_memory_grant_usage', source = N'sqlserver.database_name'),
    ADD TARGET package0.ring_buffer
    (SET max_events_limit = (10000))
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    STARTUP_STATE = ON
);
GO

-- Start the event session  
ALTER EVENT SESSION [Excessive Memory Grants]  
ON SERVER  
STATE = start;  
GO  

--- Page Splits from Transaction Log
CREATE EVENT SESSION [PageSplitsFromTLog]
ON SERVER
    ADD EVENT sqlserver.transaction_log
    (ACTION
     (
         package0.collect_system_time,
         sqlserver.database_id,
         sqlserver.database_name
     )
     WHERE ([operation] = 'LOP_DELETE_SPLIT')
    )
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.transaction_log', source = N'alloc_unit_id', source_type = (0)),
    ADD TARGET package0.ring_buffer
    (SET max_events_limit = (10000))
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

-- Start the event session  
ALTER EVENT SESSION [PageSplitsFromTLog] 
ON SERVER STATE = START;
GO