Questions tagged [extended-events]
SQL Server Extended Events (Extended Events) is a general event-handling system for server systems.
151
questions
1
vote
1answer
21 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. ...
1
vote
0answers
97 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
26 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
25 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
42 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
40 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
44 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 ...
0
votes
0answers
239 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
28 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
66 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
25 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
0answers
25 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
54 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
61 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
114 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
75 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
20 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
92 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 ...
1
vote
1answer
82 views
Can Extended Events 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?
thanks
0
votes
1answer
46 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
380 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 ...
3
votes
1answer
1k 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
245 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
46 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
28 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 ...
0
votes
1answer
90 views
Need Help in extended events SQL Server
I have created an extended event as below. I want to know how to query the historical data using T-SQL to see the issues at particular timestamps. The trace is running as of now
CREATE EVENT SESSION [...
10
votes
1answer
310 views
Incorrect process utilization from System health extended events?
I have been recently working on understanding the data or metrics being stored in system health extended events event file.
We are trying to implement the data collection of performance metrics using ...
0
votes
1answer
285 views
File growth and location change for system health extended events
I am continuing the questions over system health extended event from my previous question How are the system health extended events files rolling over?
I see system health file location by default ...
5
votes
1answer
465 views
How are the system health extended events files rolling over?
I see the default system health session events within SQL Server are 5 MB each with 4 files in total.
We are trying to fetch data from the system health in historical events but for some critical ...
4
votes
1answer
150 views
SQL Server 2016 still uses mixed_extent_allocation in TempDB
Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace.
I expected ...
-2
votes
1answer
154 views
Centralized Logs (error, deadlocks, slow queries) For SQL Servers
I want to establish a centralized log for many SQL Servers, similar to the products Graylog or elk stack.
What is the best and performant way to send these kind of logs to a log collector?
I ...
1
vote
1answer
31 views
Capture Extended Events Histogram only during time window
We have a problematic time window during night on the weekends. I want to set up multiple Extended Events sessions to capture some data and get insight.
Most of them will target a file, which I can ...
0
votes
2answers
2k views
How to view xEvent event_file data
I have a small xEvent that is sending the results as .xel to an event_file in G:\XEvents and I want to explore the data with SSMS. I have been looking for a solution and found several posts about ...
4
votes
0answers
66 views
Authoritative Extend Event object list?
I am looking for an authoritative list of objects for extended events, I want to know what version an object becomes available and some clues about what the value really represents. I have googled ...
3
votes
0answers
209 views
Difference between sqlserver.nt_username & sqlserver.username
I am just learning about SQL Extended Events.
I have a small event and for actions I have selected both sqlserver.nt_username & sqlserver.username
When I test connect with a NT account the ...
9
votes
1answer
148 views
What does it means when you have “sql_exit_invoked” in System Health?
I'm having problems with one of my SQL Server 2016 Standard servers. I have 8 production servers and this one is the only one who crashes randomly without having any trace in the log.
I have ...
asked Dec 13 '18 at 14:43
Danielle Paquette-Harvey
1,3221 gold badge11 silver badges21 bronze badges
2
votes
1answer
542 views
database_xml_deadlock_report or xml_deadlock_report
Is [database_xml_deadlock_report] the same as [xml_deadlock_report] just with extra fields?
I'm creating a basic deadlock and blocking capture and see both. Most examples online show [...
1
vote
1answer
50 views
Why does a query on a local instance with local database files cause NETWORK_IO waits?
I have followed this tutorial to get the wait stats of a particular query while it is 'in flight' using extended events. Whilst the tutorial refers to an index rebuild, I just did a select statement ...
1
vote
1answer
65 views
How to index of filter extended event files in Sql Server
I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
1
vote
1answer
272 views
how to add an extended events tracking session to SSAS?
I would prefer to have a script to achieve this, but as I am not so familiar with SSAS I am using the wizard to create an extended events session in SSAS.
The way I am doing it is right clicking on ...
2
votes
1answer
271 views
Extended Event Session Keeps Stopping
A few months ago I added an extended event to track blocked processes and deadlocks, with a file output to track occurrences.
Every now and again, I check the log file, but find that the extended ...
4
votes
1answer
336 views
SQL Query logging in extended events deadlock xml report is too short
How can I tell the sql server to log the whole query in the xml, and not truncate it?
example:
<inputbuf>
(@0 nvarchar(20),@1 nvarchar(10),@2 decimal(38,20),@3 int,@4 datetime,@5 nvarchar(...
5
votes
1answer
89 views
Precision for average_disk_seconds_per_read?
In SQL Server Extended Events, event = perfobject_logicaldisk, what is the actual unit of time for average_disk_seconds_per_read and average_disk_seconds_per_write?
All of the documentation I've ...
1
vote
0answers
93 views
Testing Data Compression
We have a 1TB table that gets queried a lot. I have suggested we use Data Compression to save on space as well as help put more in memory. Because of the concern of CPU overhead, I have been tasked ...
-2
votes
1answer
384 views
RID and Key lock on same object by a Select and Delete query
To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row?
Also, how did Delete query (SPID 121, on the right) is able to get X ...
1
vote
1answer
280 views
How do I access ETW Traces on SQL Server for linux?
I have SQL Server running in a docker image. I have an extended event session running on it. I can connect to it fine and view live data with SSMS. I can use T-SQL to view the histogram target. If ...
1
vote
1answer
396 views
SQL Server Trace Stopped Messages Displaying Random Login Name
When traces are started and stopped we get messages similar to the following in the SQL Server error log:
SQL Trace ID 2 was started by login "DOMAIN\loginA".
and
SQL Trace stopped. Trace ID = ...
1
vote
0answers
124 views
Cost / Capacity Planning: Quantify Data Ingress / Egress
To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information:
The list of clients that access the on-prem DB Server. I'd like to know: client ...
1
vote
1answer
856 views
How to interpret AlwaysOn event hadr_db_partner_set_sync_state
We have a program that listens to the SQL Server AlwaysOn_health event session
for state changes. When we simulate a secondary server going offline or online, we get the ...
7
votes
2answers
3k views
What is the right tool to process big .xel files (SQL Server Extended Events logs)?
My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.
Figured out the best way is via Extended Events ...
