We have noticed that our nightly jobs take 2-3 times longer when the following extended event session is running. There is a clear deterioration in server performance across two separate servers running the same XE, even though there is not much data being written to the target files by the sessions (only a few MB). As soon as the sessions were stopped, the previous job runtime durations resumed.
Aren't extended events supposed to be lightweight? Is there something we could have done to make the below less burdensome? We're not writing to the ring_buffer, but to a text output file. The servers in question are very well equipped resource-wise (64 GB RAM, 8 x virtual cores @ 2.29 GHz)
-- Create new Extended Event session.
CREATE EVENT SESSION [StatusChanges] ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
SET collect_object_name = (1), collect_statement = (1)
ACTION (sqlserver.database_name, sqlserver.server_principal_name, sqlserver.sql_text)
WHERE
(
[sqlserver].[equal_i_sql_unicode_string] ([sqlserver].[database_name], N'MyDatabase') AND
[sqlserver].[like_i_sql_unicode_string] ([statement], N'%UPDATE%P%SET%Status%=%')
)
)
ADD TARGET package0.event_file (SET filename = N'C:\Temp\StatusChanges.xel', max_file_size=(100))
WITH
(
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = ON
);
GO
November 25, 2022 at 3:51 pm
Unless I am missing something - you are trying to use an extended event to scan all SQL text executed on the instance and filtering that down for code running in a specific database where the statement contains the words UPDATE, P, SET and the '=' sign.
Extended Events may be lightweight, but when you set it up to search every statement on the instance with that kind of wide-open matching criteria I can definitely see that as being a problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2022 at 8:35 pm
Fair point!
Is there something we could have done to make that sort of search more efficient? How could we have done this differently? We were trying to fault-find an intermittent issue that involved an update to a particular table column, but since there were multiple potential candidates (triggers and stored procedures) we needed some way to try to capture which objects were being called in which order and at which times.
If you knew there were specific objects that were candidates - you could have specified only those objects in the criteria. That would capture every time one of those objects was executed without having to filter through the SQL text.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2022 at 1:38 pm
One thing, toss, the action for SQL text. You'll get the statement values anyway in the event.
However, right there with Jeff, the nature of the session you have here is the issue, all by itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply