Questions tagged [extended-events]
SQL Server Extended Events (Extended Events) is a general event-handling system for server systems.
176
questions
0
votes
1answer
45 views
How to capture the exact SQL text involved in deadlock (locks) using extended events?
I have setup extended event session with the following event:
xml_deadlock_report
When I look at the deadlock graph, it shows me the processes asking or requesting for the resource, and the type of ...
0
votes
1answer
35 views
Do trace flags 1204 and 1222 give any more information than available via the xml_deadlock_report event in Extended Events?
I am exploring deadlock detection approaches.
Trace flags 1204 and 1222
Extended events (xml_deadlock_report)
Do the trace flags contain any more data than what is available via the Extended Events ...
1
vote
1answer
41 views
How to easily copy SQL Statements from multiple extended events like I used to do in SQL Profiler?
One thing I miss from using SQL Profiler is selecting multiple rows in profiler (looking at Batch Completed and RPC Completed events) and seeing all the SQL in the below panel, i.e. as one combined ...
0
votes
1answer
29 views
XE process_login_finish
Can anybody explain the purpose for process_login_finish extended event?
I trying to catch some events for XE session with this event, but no one messages arrives to the session.
However event "...
0
votes
1answer
60 views
Azure SQL Database - Error Starting Extended Events Session
I'm using workloadtools to try and capture a production extended events "replay trace" and have hit a problem. The tool is creating the extended events session but is unable to start it for ...
1
vote
1answer
20 views
how to find the error description for the error codes found in the extended events file XEL generated by SSAS Tabular query end event
how can I translate the error codes logged into XEL files to the corresponding description?
I'm importing the XEL files to a SQL Table using the sys.fn_xe_file_target_read_file function, but the ...
0
votes
1answer
156 views
Updating a SQL Server Extended Event session to add data storage - is it possible?
I have an extended event session to track deadlocks, and have the data storage set up for Event_File, max file size of 20 MB, max number of files is 5, enable file rollover is true.
It looks like I ...
0
votes
1answer
31 views
MSSQL - How to log every rpc_completed event into a table?
I want to log the Execution Time and Parameters for every stored procedure call on my database.
What I reached by searching is to use extended events as the latest method with minimum performance ...
0
votes
0answers
27 views
Compile Memory - Relationship between XE query_optimizer_memory_gateway and DMV sys.dm_exec_query_optimizer_memory_gateways
I’m studying and testing what happens during the compilation process of a query and how the Compile Memory is treated. You can have a look here (https://techcommunity.microsoft.com/t5/sql-server/multi-...
1
vote
1answer
139 views
Extended Events Login audit - Reduce login noise
We are looking for the optimal sql login audit solution, with usable filtering.
Thus far we have looked into Server Audits and profiler but have concluded that extended events
look most promising, ...
0
votes
1answer
101 views
Sql_text Data Missing When Querying Extended Event File Target Using TSQL
I set up an Extended Events session to capture SQL Server error events.
Below is the TSQL I used to create the XE session:
CREATE EVENT SESSION [ErrorCapture] ON SERVER
ADD EVENT sqlserver....
0
votes
1answer
138 views
Performance impact of Extended Events sessions looking at the exact same event type
I have created an extended events session that watches the module_start event type and filters down based on the object_name: equal_i_sql_unicode_string]([object_name])
The purpose of this session is ...
0
votes
1answer
9 views
What Name from Event Library?
I'm new in SQL Server's extended events and you can create your own session and select your own name in Event library.
From your experience, what name from event library have you selected in order to ...
0
votes
2answers
172 views
extended events rpc_completed duration does not matching with rpc_starting
Does rpc_completed duration (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp?
In my case, I saw a big difference between them. Duration is only few millsec, where rpc_starting.timestamp -...
0
votes
1answer
66 views
SQL Server Extended Events to Capture Objects access by a User [duplicate]
I am in for a requirement where I need to use EE to capture the Objects Accessed by a particular SQL User.
I want to avoid the action sqlserver.sql_text, if I use this, I will need to manually scan ...
6
votes
1answer
621 views
How to avoid Table Lock Escalation?
I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time
So, I used approach that helped me before many times - updating top (N) rows at a ...
0
votes
1answer
51 views
Where is the documentation for SQL Server's blocked process report?
I'm trying to work with the blocked process reports captured using Extended Events and, while I can find numerous blog articles and Q&A about specific details (e.g.1 2 3), I can't find any ...
7
votes
2answers
298 views
Automatically reject certain expensive query statements
I have some clients which sometimes run some "not allowed" very expensive queries.
I've tracked them down using Extended Events.
Is it possible to reject these queries automatically? I know ...
1
vote
1answer
131 views
Extended Events XEL file reader; Is there a FileName property avaialble in QueryTableXEventData or SqlBulkCopy
Using the QueryTableXEventData class in Powershell I am able to parse 100's of XEL files very quickly and export their content to a table in SQL Server using SQLBulkCopy
Example:
$events = new-object ...
3
votes
1answer
180 views
How to capture runtime parameters with extended event?
I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way:
right-click extended event -> Watch Live Data and ...
0
votes
1answer
271 views
How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL?
I'm setting up an Extended Events trace in a Managed Instance (NOT Azure SQL DB) for the first time.
These are the steps I've carefully followed:
Created a Master key on my Database.
Created a ...
0
votes
2answers
62 views
Backup Restore, CPU usage and Extended Events
I'm working on measuring the performance impact of Backups on CPU. I would like to capture all the backup operations and CPU usage. I'm running a weekly full backup, daily diff, and 15 min log backup. ...
0
votes
1answer
156 views
SQL Server Extended event filters: can you use an IN predicate with a subquery?
I have an extended event that tracks and filters login events from specific users. The filter is something like this:
WHERE (([sqlserver].[nt_user]=N'fredJohnson') OR ([sqlserver].[nt_user]=N'...
1
vote
2answers
193 views
Problem tracing Error: 8623 with extended events
From time to time we are getting SQL Server error:
Error: 8623, Severity: 16, State: 1
The query processor ran out of
internal resources and could not produce a query plan. This is a rare
event and ...
0
votes
1answer
50 views
SQL extended event captured only the estimated execution plan but no actual execution plan
My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288)
I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 ...
1
vote
1answer
147 views
Improve querying of Extended Events target file
As part of our server estate monitoring, I am adding extended events to pick up warnings, blocking etc and I'd like to periodically (Every couple of minutes) query the event file to collect the data. ...
2
votes
2answers
177 views
Performance of Query where time captured from SP statement completed differs by large from RPC completed
I am unable to understand the performance of below query when collected data from XE events:
Query
DECLARE @Importdate DATETIME = ISNULL ((
SELECT ImportDatetime
FROM
Customertable
where CustomerID ...
-1
votes
1answer
237 views
General Question as best practice to collect data from extended events
Hope ya all doing good and safe:-
I am collecting query level metrics from extended events using below events for -
SP statement completed
SQL Statement completed
SQL Batch Completed
RPC Completed
...
0
votes
1answer
124 views
Specify file target filename dynamically
I need to create an Extended Event session to capture all the executed queries while my server is running and save them in a file under a folder with the database name.
DECLARE @file varchar(50);
...
1
vote
0answers
128 views
SQL Server 2017, XEvent: sql_text IS NOT NULL
I'm attempting to use XEvents to find out which queries are causing my templog.ldf file to grow in size. I'm getting events, but I see the sizes of the files haven't changed, and that the sql_text ...
2
votes
1answer
60 views
Error while creating Extended Events on 2008 SQL Server
I am trying to create an extended event to capture the login information of my server. While creating an extended event on SQL 2008 using the below query
CREATE EVENT SESSION [SA_Monitor] ON SERVER
...
0
votes
0answers
206 views
SQL Server 2008: The target name, "package0.event_file", is invalid, or the object could not be found
What can cause the following error?
Msg 25623, Level 15, State 2, Line 1
The target name, "package0.event_file", is invalid, or the object could not be found
Given the following TSQL:
CREATE EVENT ...
1
vote
0answers
1k views
Troubleshoot MS DTC error
I've been getting these two errors on SQL Server log:
Error 1:
DESCRIPTION: Import of Microsoft Distributed Transaction
Coordinator (MS DTC) transaction failed:
0x8004d00e(...
1
vote
2answers
50 views
Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement?
My problem
I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following
MERGE INTO table
USING
(SELECT ? AS Search_Col) AS SRC
ON table....
2
votes
1answer
81 views
Extended Events Collation resolution error
When trying to expand the sessions under extended events I am getting a Collation Resolution error in SSMS 18.2 (15.0.18142):
Looking at a trace I have found that this query is what is giving me the ...
0
votes
0answers
53 views
create_time in sys.dm_xe_sessions is in the future
I have run the following query which shows the running XE sessions on the server:
SELECT name,
create_time
FROM sys.dm_xe_sessions
and I get the results
+-------------------------------+...
1
vote
1answer
105 views
What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT?
By default, MAX_DISPATCH_LATENCY is set to 30 seconds. I chose FILE as a target and write all captures there. I thought that if I write all captures into file, why would then I lower down ...
3
votes
1answer
162 views
MSSQL Server XE column client_connection_id accessible through system view/table for auditing?
In our .NET application we create a SqlConnection to talk to the database.
After the connection is opened, we get the ClientConnectionId, a GUID.
Within the extended events I can manage to trace the ...
1
vote
1answer
421 views
SQL Server Extended Events purging
How do we purge events older than a day in Extended Events in SQL Server 2012. Stopping and Starting the session is not helping.
Target : asynchronous_file_target
Trying to create a job which will ...
1
vote
2answers
139 views
Querying system health data becomes slow as event volume increases
We get the data from system health XE from SQL server and load into table for later querying.
Part of that statements which gets pretty slow is
INSERT INTO TABLE1
(
[object_name]
...
1
vote
1answer
196 views
Trying to trace error in Stored Procedure using XEvents
Here is the situation:
I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table. The table has 1 INT column (it is an identity column and there ...
1
vote
0answers
23 views
Read all Captured Extended Events for a Given Extended Event Session Name
I want to be able to read all XE files for a dynamic XE session name. My XE session stores files to disk.
I have the query below
DECLARE @XeName SYSNAME = 'MyXE'
DECLARE @Path NVARCHAR(255)
SELECT @...
1
vote
2answers
280 views
Trying to identify what process is making a connection to a specific DB in SQL Server
I am attempting to track down... something... that is making a connection to a specific database without (apparently) doing anything with the connection. However, so far I have had no luck.
The data ...
2
votes
1answer
167 views
Can Extended Events be used instead of Auditing?
In BOL it is mentioned:
Does this mean Extended Events can capture all the events that are traced by auditing? Can we get all the functionalities of auditing just by using XEvents?
0
votes
1answer
131 views
How to convert Extended Event action mdmget_TimeStampUTC to datetime?
SQL Server Extended Events can include an action of mdmtargetpkg.mdmget_TimeStampUTC. It returns a value such as 132085458320550473. How can that be converted to an actual date?
It doesn't appear to ...
4
votes
2answers
577 views
Way to find when system health file is rolling over
Is there a way to find the events when system health extended event files are rolling over rather than manually monitoring for the events?
For my medium load server they stay upto 2-3 days. But for ...
6
votes
1answer
3k views
What is the proper event in Extended Events to track stored procedure executions?
My goal is to track executions of a single stored procedure using Extended Events
In the event selection list, I can find following:
sql_statement_completed
sql_batch_completed
...
12
votes
1answer
322 views
SQL Extended Events Session for deadlock detection
Is there a way to increase the size of the <inputbuf> element in the deadlock XML captured by the deadlock extended events session?
We want to see the complete query to help pinpoint the issue ...
3
votes
0answers
85 views
Why execution plan could be skipped from XE session?
Got an issue, similar to this one: Why does SQL Server Profiler trace not capture SELECT operator in execution plans?. There was an advice to use XE instead of Trace, but it does not help.
Thing is, ...
0
votes
1answer
32 views
Resources used by ExtendedEvents
Does anyone know how to tell how many resources (CPU/MEM/Reads etc) have been used by SQL Server Extended Events?
I've went through the available DMVs here
But I dont see where this is tracked ...
