Questions tagged [extended-events]
SQL Server Extended Events (Extended Events) is a general event-handling system for server systems.
192
questions
1
vote
1
answer
18
views
What is the extended events equivalent of the default sys trace security audit events?
I am exploring the default sys trace and it contains various records for the security changes on the SQL server (event record examples are shown in screenshot below).
Since the trace feature can be ...
- 2,800
2
votes
1
answer
150
views
A way to filter out parseonly statements from extended events?
I'm currently using extended events to capture query statements executed in a production environment. But the actual session is also capturing the activity of 'query parsing' done by operators to ...
- 3,655
5
votes
1
answer
158
views
Use square brackets on extended events filter
I want create an extended events session and use the like_i_sql_unicode_string operator to filter the phrase [demo], with the square brackets.
I've started with:
CREATE EVENT SESSION [demo] ON SERVER
...
- 228
-2
votes
1
answer
74
views
Why only few SQL Server Extended event plan handle 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
We have created an extended event to capture long running queries , the objective is to get execution plan by joining on plan handle column
some event data has a plan handle with id value however some ...
0
votes
1
answer
68
views
Extended Events - How to capture executions for a given stored procedure along with the passed parameter values? [duplicate]
How do you capture executions for a given stored procedure along with the passed parameter values?
I tried using sqlserver.module_end but it only shows the store proc execution with no param values:
...
- 994
8
votes
1
answer
139
views
What can I add to an Extended Event to return correct row_counts?
I'm running an Extended Event session in SQL Server 2017 that's only recording module_end events for a specific database. The reason for this trace is two-fold:
I'm wanting to see the frequency (or ...
- 181
1
vote
1
answer
64
views
How to filter extended event session by sp execution time (seconds)?
Trying to view all executions of a named stored procedure where execution time exceeds a given number of seconds. How do you filter on execution time (seconds)? This is what I have cobbled together ...
- 994
0
votes
1
answer
60
views
SQL Server Extended Events odd behavior trying to capture statements coming from SAS
The problem: the Extended Events (EE) session we created to capture queries against a certain table is not working when the request comes from SAS.
The EE code:
CREATE EVENT SESSION [...
- 31
7
votes
1
answer
2k
views
Using an extended event to track a single stored procedure
I'm attempting to track all executions of a stored procedure (Including from inside other stored procedures) and the statements of that procedure - is this possible with extended events? My current ...
0
votes
1
answer
35
views
XE ucs package events
Can anybody explain for what purposes exists ucs package {C0AB75C5-B1EA-445B-B7DF-F897686F94E7} "Extended events for Unified Communications Stack"
I have tried to catch some events like ...
4
votes
1
answer
315
views
Extended events capture all calls by login/user and sql text containing a value
I'm using below extended events session to capture the data that contains a value 9999 by login xEventsTest. But I don't see any data captured with the filters added, but I could see this call when no ...
- 502
0
votes
1
answer
195
views
Loading Extended Events ring buffer to SQL table using powershell
I am running a RPC_Completed extended event which outputs to a ring buffer.
While trying to read the data from the ring buffer target data, xQuery is taking too much of time.
The faster route was to ...
- 11
-1
votes
1
answer
71
views
Best method for isolating long running locks?
On a SaaS production database with 7 users (5 human, 2 backend) many are experiencing SQL cliënt timeouts at 30 seconds, our standard .Net CommandTimeout.
This SQL Server instance (out of our ten) ...
- 59
1
vote
0
answers
41
views
Extended Event Failing To Capture Intent Locks
I have a SQL server with many unused databases and followed the linked article to set up an extended event to track intent locks and identify which DB's are actively being used, and which aren't and ...
- 141
0
votes
0
answers
51
views
sqlos.process_killed and sqlserver.lock_released Extended events not firing on SQL 2019 Enterprise
I have the following extended event sessions configured (among others). It seems that all events fire except these two. In the second session, the "lock_acquired" fires just fine, but the &...
1
vote
0
answers
125
views
Extended events error in Azure Managed instance
I have setup extended events on Managed instance. Below are the steps followed:
create a master key
Create a credential for Azure Blob storage with shared access token
Create the extended event and ...
- 33
3
votes
1
answer
311
views
How to convert a query hash to uint64?
How do I convert the query hash string "0x9F37D9B585242D49" to a uint64 needed by my extended event filter? Casting to bigint doesn't work as the value goes negative.
event
...
- 994
0
votes
1
answer
210
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 ...
- 2,800
0
votes
1
answer
127
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 ...
- 2,800
1
vote
1
answer
92
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 ...
- 1,270
0
votes
1
answer
74
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 "...
1
vote
1
answer
222
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 ...
- 3,576
1
vote
1
answer
53
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 ...
- 141
0
votes
1
answer
626
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 ...
- 171
0
votes
1
answer
83
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 ...
1
vote
1
answer
429
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, ...
- 59
0
votes
1
answer
275
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
1
answer
383
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 ...
- 193
0
votes
1
answer
12
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 ...
- 781
0
votes
2
answers
472
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 -...
- 33
0
votes
1
answer
136
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 ...
- 11
7
votes
1
answer
941
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 ...
- 4,900
0
votes
2
answers
85
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 ...
- 1,270
7
votes
2
answers
426
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 ...
- 193
1
vote
1
answer
247
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 ...
- 9,508
4
votes
1
answer
397
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 ...
- 1,193
0
votes
1
answer
467
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 ...
- 422
0
votes
2
answers
119
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. ...
- 326
0
votes
1
answer
290
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'...
- 163
2
votes
2
answers
463
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 ...
- 345
0
votes
1
answer
82
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 ...
- 411
1
vote
1
answer
322
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. ...
- 13
2
votes
2
answers
270
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 ...
- 2,137
-1
votes
1
answer
381
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
...
- 2,137
0
votes
1
answer
231
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
0
answers
206
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 ...
- 165
2
votes
1
answer
107
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
...
- 862
0
votes
0
answers
343
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 ...
- 196
1
vote
0
answers
2k
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(...
- 4,707
1
vote
2
answers
78
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....
- 635