Questions tagged [extended-events]
SQL Server Extended Events (Extended Events) is a general event-handling system for server systems.
1
vote
1answer
15 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
25 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
44 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
73 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 ...
8
votes
1answer
65 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 ...
2
votes
1answer
32 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
33 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
35 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
40 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
118 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 ...
1
vote
1answer
62 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
59 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
46 views
Testing Data Compression
Scenario: We have a 1 TB size table that gets queried a lot. I have suggested we use Data Compression to save on DB Space as well as help put more in memory. Because of the concern of CPU overhead, ...
-2
votes
1answer
72 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
90 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 ...
0
votes
0answers
33 views
Troubleshooting with extended events
In SQL Server Profiler, you can select all of the rows, and get the actual SQL that was executed and not just individual events.
Do you see the text in red on the bottom of the image I posted? That ...
1
vote
0answers
110 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
58 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
225 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 ...
5
votes
1answer
751 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 ...
2
votes
1answer
47 views
Explanation of Extended Events xml
I queried an Extended Events session and returned the XML. I am confused on what frame level 1, 2, 3 mean. As well as line, offsetStart and offsetEnd?
I am however able to convert the sql handle to ...
1
vote
1answer
271 views
Query plan do not get invalidated after update statistics
I'm running some tests to find out when query plans get invalidated after an update statistics. The machine I'm using for testing is a SQL Server Developer 2016 SP1 CU7.
I found an article on ...
1
vote
1answer
135 views
Determine which SQL drivers an application uses to connect to SQL server
I want to find out what SQL driver a third-party application (or any application for that matter) uses to connect to a SQL Server instance in production. It can be tough to get a fast answer from a ...
0
votes
0answers
452 views
Extended Event Reported Error Login Timeout
While poking around for a specific event I found myself discovering an error happening every second.
At the moment I cannot find any more information about the error as it only gets displayed in the ...
1
vote
1answer
45 views
Extended Events: Specify Session Data Storage left blank
What happens if the Data Storage is never configured?
2
votes
1answer
42 views
SQL 2008/R2 Extended Events - Row Count
I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.
In sql trace, this is possible. But I can'...
1
vote
1answer
233 views
Is there a service broker target for an extended event session
We use Service Broker heavily for asynchronous background processing.
Additionally we want to use it for extended event processing.
Is it possible to use Service Broker listener as target for an ...
1
vote
1answer
66 views
Extended Events replacement for Virtual file stats
Is there a way to monitor virtual filestats data with Extended Events, instead of doing (select * from sys.dm_io_virtual_file_stats) in SSMS?
1
vote
0answers
227 views
Track all changes to table with Extended Event Session
I am using the sql_statement_completed Event in SQL Server 2016 to track all SQL executed on a certain table. I have a Filter on sqlserver.sql_text like %tablename%.
This captures any SQL I execute ...
2
votes
0answers
251 views
StartTime and EndTime for the Attention Extended Event
The documentation says that the "Attention" extended event class has StartTime and EndTime as part of it.
But I can't seem to figure out how to get at it or record it...
Here is what I am using to ...
0
votes
1answer
152 views
When using XE Profiler in SSMS 17.3, the 'Database Name' column shows as either NULL or an empty string
I'm just starting to use SSMS 17.3 - and one of the main things I was looking forward to trialing was XE Profiler.
However, when you start a session (either Standard or TSQ) and add the Database Name ...
0
votes
1answer
110 views
Extended Events session file truncated on server restart
I have created an extended events session to capture login and logout information. The session is setup to store events to a file.
I understood that one of the advantages of using an event file (...
2
votes
2answers
355 views
Monitoring deadlocks in SQL Server 2014
We are experiencing deadlocks on SQL Server 2014 and I am trying to capture as much information about them as I can.
There is already Extended Events Session that captures xml_deadlock_report events ...
2
votes
1answer
121 views
Does Extended Event session without target affects server performance?
Extended event session can be created without targets. In such situation you can watch collected events in SQL Server Management Studio in the Live Data view. For such session a LiveStreamTarget is ...
1
vote
1answer
275 views
Extended Event doesn't capture every rpc_completed
I have a long running Extended Event session that captures rpc_completed events filtered for a few procs. It has found some that have run. But others it doesn't capture them when I know they have been ...
3
votes
1answer
63 views
High-resolution CPU usage information for batches and statements
I'm working on a tool that I use to evaluate the performance of SQL Server queries in a development environment. I use, and highly recommend, tools like SentryOne's Plan Explorer, but my tool reports ...
2
votes
1answer
112 views
dm_db_index_operational_stats showing more blocking than extended event report
I’ve implemented the XE to capture blocking on my instance (as per this great article https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comment-2470072. )
I did this because I ...
2
votes
0answers
49 views
SQL Server Extended Events Sessions on VIEWs
I'm trying to monitor when Views are being called in SQL Server.
Using Extended Events, I've created a Session and included event "sql_statement_completed".
Then I've added 1x filters on this:
...
2
votes
0answers
983 views
Way to import extended event data into table efficiently
create event session [my_xevent_session] on server
add
event sqlserver.rpc_completed(
set
collect_statement = 1
--collect_output_parameters = 1
action (
sqlserver....
2
votes
1answer
89 views
Extended event not staying on
I've created an extended event to debug a very rare issue in a web application. Very occasionally data from forms is not making it to a stored procedure, so I want to verify that when the SP is being ...
2
votes
1answer
385 views
Extended Events to find Blocking and Deadlocking
Came across another great article from Jeremiah Peschka called Finding Blocked Processes and Deadlocks using SQL Server Extended Events and set this up on my local MS SQL 2014 Developer edition and ...
13
votes
1answer
412 views
What is the maximum number of allowable bound actions for an extended event?
If you add "too many" actions to an event in an event session, you will receive this error:
Msg 25639, Level 16, State 23, Line 1 The event, "[event name]",
exceeds the number of allowable bound ...
3
votes
0answers
395 views
Extended Events - sqlserver.process_login_finish Not Firing?
We're trying to see whether a client is able to reach a database server, as it's receiving error:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
The error ...
13
votes
1answer
3k views
Extended event duration Millisecond or Microsecond?
There was a question in this forum about Extended Event module_end duration unit, which I answered. Details here.
Is it always in microseconds for all events?
0
votes
1answer
645 views
Filter extended event xml_deadlock_report by database
I have added an extended event log that logs blocked_process_report events and xml_deadlock_report events.
I can exclude certain databases from the blocked_process_report by adding WHERE ([...
5
votes
1answer
1k views
HOBT vs OBJECT in Extended Event lock_escalation resource_type field
I'm capturing the lock_escalation event on our production servers (running the Enterprise Edition of SQL Server 2014) and trying to make sense of the output, particularly the resource_type field. ...
2
votes
1answer
539 views
Extended event for lock count does not show locks [closed]
I recently came across the article Locking and Performance. It has following statements
If we manually monitor locking activity while this query executes, we see that without the page locking ...
-1
votes
1answer
64 views
How to get result from one table non-matching records in SQL Server [closed]
I have 2 columns in 1 table and both columns have almost the same record (old and new). I am looking for records in a new column which is record isn't in the old column.
Can anyone can help me with ...
3
votes
0answers
333 views
Extended Events not displaying today's deadlock information
I am using the following query to retrieve deadlock xml.
select create_time,XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select s.create_time,CAST(target_data as ...
4
votes
1answer
1k views
Extended Events filtering
I am trying to create a trace in Extended Events filtering with logins, in profiler we can filter with login names but I do not see that option in XE. How do I do that?
