Capture long transact query using SQL Server performance condition alert feature

Question

Post reply

Capture long transact query using SQL Server performance condition alert feature

pcq0125

SSCrazy

Points: 2890
More actions
September 10, 2024 at 8:41 pm

#4456275

I created a SQL Alert trying to capture long query transaction under the SQL Server Agent\Agent with the options below. However, it does not have the feature to capture what query is causing the long transaction. I was thinking of adding the sp_WhoisActive to capture the long transaction query but it did not have SQL option to place my query. Any ideas how can I capture the long transaction query syntax and trigger an email to me? Thanks.
Type: SQL Server performance condition alert
Object: Transaction
Counter: Longest Transaction Running Time
Alert if counter value > 300

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

I would go for eXtended Events to capture such information.

could be something like this:

-- Create the event session
CREATE EVENT SESSION DBA_LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE sqlserver.sql_statement_completed.duration > 1000 -- Duration in milliseconds
)
ADD TARGET package0.asynchronous_file_target (
    SET filename = 'your_XE_LogPath\DBA_LongRunningQueries.xel'
),
ADD TARGET package0.ring_buffer (
    SET max_memory = 4096
)
WITH (max_dispatch_latency = 1 SECONDS);

-- Start the event session
ALTER EVENT SESSION DBA_LongRunningQueries ON SERVER STATE = START;

-----

-- Query the data 
CREATE TABLE #XeEventData
([event_data_XML]    [XML] NULL, 
[file_name]     [NVARCHAR](260)  NOT NULL, 
[file_offset]   [BIGINT] NOT NULL, 
[timestamp_utc] DATETIME2(7) NOT NULL
);
Insert into #XeEventData
SELECT CONVERT(XML, event_data) AS event_data_XML
, file_name
, file_offset
, timestamp_utc
FROM sys.fn_xe_file_target_read_file(
  'your_XE_LogPath\DBA_LongRunningQueries.xel'
, NULL, NULL, NULL);


SELECT 
event_data_XML.value('(event/action[@name="database_id"]/value)[1]', 'INT') AS database_id,
--event_data_XML.value('(event/action[@name="database_name"]/value)[1]', 'SYSNAME') AS database_name,
event_data_XML.value('(event/@name)[1]', 'VARCHAR(50)') As event_name,
event_data_XML.value('(event/action[@name="session_id"]/value)[1]', 'SMALLINT') AS session_id,
event_data_XML.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') AS duration,
event_data_XML.value('(event/data[@name="cpu_time"]/value)[1]', 'BIGINT') AS cpu_time,
event_data_XML.value('(event/data[@name="physical_reads"]/value)[1]', 'BIGINT') AS physical_reads,
event_data_XML.value('(event/data[@name="logical_reads"]/value)[1]', 'BIGINT') AS logical_reads,
event_data_XML.value('(event/data[@name="writes"]/value)[1]', 'BIGINT') AS writes,
event_data_XML.value('(event/data[@name="row_count"]/value)[1]', 'BIGINT') AS row_count,
event_data_XML.value('(event/data[@name="error_number"]/value)[1]', 'BIGINT') AS error_number,
event_data_XML.value('(event/data[@name="severity"]/value)[1]', 'BIGINT') AS error_severity,
event_data_XML.value('(event/data[@name="state"]/value)[1]', 'BIGINT') AS error_state,
event_data_XML.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(max)') AS error_message,
event_data_XML.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(128)') AS client_app_name,
event_data_XML.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(128)') AS client_host_name,
isnull(event_data_XML.value('(event/action[@name="username"]/value)[1]', 'SYSNAME'), '??') AS username,
event_data_XML.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(max)') AS sql_text,
event_data_XML.value('(event/data[@name="statement"]/value)[1]', 'VARCHAR(max)') AS statement,
event_data_XML
FROM #XeEventData
;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 2

Also keep in mind you can use QueryStore to have query followup.

Interesting with this is you already have reports at hand to help you dig into it all