Start up the auto_stats Extended Event, filter out some noise, and prepare yourself to monitor it.

CREATE EVENT SESSION [AutoStatsTest] ON SERVER 
ADD EVENT sqlserver.auto_stats(
    WHERE ([duration]>1 AND [object_id] > 0) ) 
ADD TARGET package0.ring_buffer

ALTER EVENT SESSION [AutoStatsTest] ON SERVER
STATE = START
GO

Find a temporal table. I had a copy of the StackOverflow2010 database to hand, so I just made Votes system versioned.

ALTER TABLE Votes ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE Votes
    SET (SYSTEM_VERSIONING = ON);
GO

With your temporal table, do something that will trigger an automatic update of stats. This worked for me

SELECT TOP (1000) [BountyAmount]
FROM [StackOverflow2010].[dbo].[Votes]
WHERE [PostId] 
			
			
			
				100

Given the duration (800 microseconds on my pathetic machine), I knew that this was nonsense. Upon consulting sys.dm_db_stats_properties, I found that the actual percentage was much less than 100.

/*
Save yourself the pain of writing
the stats query
and just use sp_BlitzIndex from GitHub
*/
EXEC sp_blitzindex @databasename = 'StackOverflow2010', @tablename = 'Votes'

In summary, **it appears that the auto_stats Extended Event reports an incorrect sample_percentage for temporal tables**. Is this a SQL Server bug or is it my misunderstanding? If it is my misunderstanding, then where can I read further? Individual Extended Events, as far as I know, are very lacking in documentation. I have only seen this happen with temporal tables. I have found it on both SQL Server 2022 on a real box (the table was multi-terabyte, so I screamed when I saw a 100% sample rate for the primary key's statistic) and on my local 2019 test box. Adding a columnstore index seems to help reproduce this, but I am not 100% sure.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColStore
on dbo.Votes
(
    Id,
    PostId
)

I am absolutely sure that there is a real bug here. However, my attempts to reproduce it have given inconsistent results. I suspect that you need a TB-scale table. **Final update:** I give up on explaining this one. The statistics for the table says that 90 times more rows than appeared in the actual execution plan (caught live as it was running in production, scanning the non-clustered columnstore index) were used to update the statistics. The number of rows in the actual execution plan is 10,000 times less than the table's row count and the auto_stats Extended Event says that 100% of the rows in the table were read. It is impossible for all of these to be true at the same time. I suspect that it might actually be the deleted bitmap that causes all of this.

How do you correlate SQL Database Audit log column "transaction_id" with a LSN either from the trans log or a CDC capture table? I am trying to do this because cdc only captures the data changes, while SQL Audit captures the who, when and what made the change. I am trying to correlate the two. thx ~j

according to this acticle: https://www.sqlshack.com/query-trace-column-values/ try to setup XE session which captures query_trace_column_values but it's does not work anymore on Microsoft SQL Server 2019 (RTM-CU27-GDR) (KB5040948) - 15.0.4382.1 (X64) Jul 1 2024 20:03:23 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) Anybody knows why it's happens? I am absolutely sure that earlier when I tested this event on the 2016 server it worked fine.

I want to create an Extended Events Session to track SQL Server Logins. The value for packet_size is 4096 for all of the SQL Server successful logins that the Login Extended Event has recorded. - Is a value of 4096 for packet_size a reliable method to identify a SQL Server Login Event? - Or is there a better/official method to only record SQL Server Authentication Logins I have tried using other filters such as username '' but that doesn't filter events with empty usernames. Thanks, Craig

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 overhead. My aim is to achieve a similar result as the 'rpc_completed' event of 'extended events', but the problem with using extended events is that their sessions log output into .xe* files and there is no option to choose a database table as far as I read from tutorials/docs. Can I (by any means), redirect extended events logs to a database table synchronously? I can sacrifice hardware resources for this, but can't pick methods like triggering interval-ed queries to read from *.xe files and insert them into a database table

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 of time. One option is to prevent errors from being logged twice if they happened on the same object in the same timestamp. I really don't think the second option is possible. Second option is to index the file in a way that it will be quick to filter errors by the error text and the object that caused them. So far I thought of creating a separate process that will run in the background and writes filtered values from the files to an indexed table, but its a bad solution. That process would need maintenance and could be expensive on IO resources. So far I didn't find a way to solve it on the extended event level. Does anyone have a better idea?

I'm going round in circles trying to work out how to collect the object_name in extended events, I can use it to filter like in the session definition below

CREATE EVENT SESSION [xetest] ON SERVER 
ADD EVENT sqlserver.module_start(
  ACTION(
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.database_name /*, sqlserver.object_name*/
  )
  WHERE (
    [package0].[divides_by_uint64]([sqlserver].[session_id], (10))
    AND object_name = N'Price_GetGenericOwner'
  )
)
ADD TARGET package0.ring_buffer(
  SET
    max_events_limit = (10000),
    max_memory = (4096)
)
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=OFF,
  STARTUP_STATE=OFF
)
GO

But when I try and collect the data as per this second example I get an error message, my intention is to collect all the SP names and analyse the data but this is ahrd if I can't collect it, can anybody shed some light on what I am doing wrong ?

CREATE EVENT SESSION [xetest] ON SERVER 
ADD EVENT sqlserver.module_start(
  ACTION(
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.database_name,
    object_name
  )
  WHERE (
    [package0].[divides_by_uint64]([sqlserver].[session_id], (10))
    AND object_name = N'Price_GetGenericOwner'
  )
)
ADD TARGET package0.ring_buffer(
  SET
    max_events_limit = (10000),
    max_memory = (4096)
)
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=OFF,
  STARTUP_STATE=OFF
)
GO

Msg 25623, Level 16, State 3, Line 14
The event action name, "object_name", is invalid, or the object could not be found

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 documentation from MS about the contents of the report & how to interpret each field. For example, I'd like to know: * What are clientoption1and clientoption2 * Why do I sometimes get an object_id that doesn't exist in my database (like this question ) * What does index_id indicate (probably answered at link above) * How to decode waitresource (here's a start ) * ... and the list goes on. I don't just want answers to these questions, I want something more complete. Is this an undocumented feature or am I just crap at finding it?

How can I determine the state of an Extended Events session (started or stopped) in Azure SQL Database? The DMV sys.dm_xe_database_sessions doesn't have this information, and none of the other sys.dm_xe_* DMVs seem to have an appropriate column. I want to do something like this to get a list of the sessions and the state of each for the current database:

SELECT s.[name], x.???
FROM sys.dm_xe_database_sessions s 
JOIN sys.dm_xe_??? x ON s.[name] = x.[name]

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 ADD EVENT sqlserver.login( ACTION(sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username) I am getting the following error. > The event name, "sqlserver.login", is invalid, or the object could not > be found. On Initial investigation I found that creating extended events using SSMS was introduced from 2012 and the only method to create EE is through SQL query. Is there a generic query that can help me capture this information in the 2008 server.

According to the documentation the returned column timestamp_utc should be of type *datetime2(7)* But when I query like this

SELECT 
    *
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE timestamp_utc > dateadd(hour, -1, GETUTCDATE())

It returns no rows. It returns the rows only when I add an explicit cast to *datetime2*

SELECT 
    *
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE cast(timestamp_utc as datetime2(7)) > dateadd(hour, -1, GETUTCDATE())

Which matches the last example in the documentation (even though no attention is drawn to it) Why is that?

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?

I created an Extended Events session - based on Standard SQL template. Just added Global filter on sqlserver.databasename for a particular DB. Started watching live data. Updated the session to edit it and added few more events. And clicked ok. The session was running during the edit happened. Now the Watch live data stopped adding rows. I did refresh the 'sessions' folder + the created session. But the Watch Live Data didn't resume. Closed and opened again by right clicking the session name and chose 'Watch Live Data'. Now it shows 'Retrieving event information from server' but never adds up any events. When checked the Target Data from eventfile. The last time it traced is before the edit happened. Though a day passed, the Target data never gets updated. Have to create new session. Does Extended Event session functions so? Or Am I wrong? Kindly help. Can't keep creating new session for every edit we need. Thank you! Adding screenshot from Standard XEvent Profiler which is too not working. Here is the script of the session - (Changed session name & DB name alone) CREATE EVENT SESSION [SessionName] ON SERVER ADD EVENT sqlserver.database_xml_deadlock_report( ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1) ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.lock_deadlock_chain(SET collect_database_name=(1),collect_resource_description=(1) ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)), ADD EVENT sqlserver.rpc_starting(SET collect_data_stream=(1) ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.sql_batch_completed( ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.sql_batch_starting( ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [sqlserver].[database_name]=N'DBName')), ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(1) ACTION(package0.event_sequence,package0.process_id,sqlos.cpu_id,SQLSatellite.AppName,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username) WHERE ([sqlserver].[database_name]=N'DBName')) ADD TARGET package0.event_file(SET filename=N'E:\SessionName.xel') WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON) GO Event global filter which I mentioned is as below - to apply in all events

It's notoriously impossible to decode [what the wait_resource pointer in the System Health Extended Event means](https://dba.stackexchange.com/questions/320594/identifying-wait-resource-for-wait-info-extended-events) . But if all of my long waits in that Extended Event for a certain time period are reporting the same pointer, then does that indicate anything at all? I'm experiencing high PAGELATCH_SH waits for this time period and expect that the reason why all of pointers are the same will be related to that. I've not included the XML here because I am absolutely certain that it won't help answer the question. I'm just trying to fathom if there is ever a case where I should pay attention to the wait_resource value in this Extended Event when it is obviously a pointer.

I am running SQL Server 2014 Developer Edition on my PC. I am trying to view the data in the system_health session. In SSMS, I have connected to the database, expanded the server / Management / Extended Events / Sessions. I see AlwaysON_health (stopped) and system_health (running). When I right-click on the system_health session, I get the following error: >The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage) > The Extended Events session named "system_health" could not be found. Make sure the session exists and is started. (Microsoft SQL Server, Error: 25728) I expand system_health and see the targets package0.event_file and package0.ring_buffer. If I right-click either target and choose "View Target Data", I get this error: >The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage) >Cannot view the function 'fn_MSXe_read_event_stream', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151) The function does exist. I can run it: select * from fn_MSXe_read_event_stream('system_health', 0); When I do, I get this error: Msg 25728, Level 16, State 10, Line 6 The Extended Events session named "system_health" could not be found. Make sure the session exists and is started. I know the system_health session is there. I see in in the list of sessions: select * from sys.dm_xe_sessions address name name ------------------ ------------- 0x00000001FF6510C1 system_health I have tried this with my own custom event sessions. I can't watch the live data on them, either. I can query the system_health ring buffer target data from sys.dm_xe_session_targets. Why can't I watch live data for any extended events session? (Note that there is a Microsoft feedback item for this issue.)

In the [docs](https://learn.microsoft.com/en-us/sql/relational-databases/reading-pages?view=sql-server-ver16) it says we can share index scans (Advanced Scanning). Now [here](https://www.mssqltips.com/sqlservertip/4472/sql-server-enterprise-advanced-scan-aka-merrygoround-scan/) they used wait stats and statistics and i did not find an extended events session that could directly identify it. The question is - how can i know that an advanced scan has been used? I know about ordered false property, but that just says that it did not use the order in the index key?

I have listed the event names present in my system_health extended event report. Based on these groupings, can I decide whether a group needs to be checked or not? Which ones are serious and must be checked, and which ones can be ignored? Specifically, I want to know how to determine if the following event groups can be ignored: scheduler_monitor_system_health_ring_buffer_recorded, sp_server_diagnostics_component_result, wait_info, error_reported, memory_broker_ring_buffer_recorded, and connectivity_ring_buffer_recorded.

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 be a valid Unix timestamp, even using microseconds. @@VERSION:

-none
Microsoft SQL Azure (RTM) - 12.0.2000.8 
	Jul  3 2019 10:02:53 
	Copyright (C) 2019 Microsoft Corporation

I have an extended event session set up to track Attention events, to monitor procedures that timed out. This is the create script: CREATE EVENT SESSION [ApplicationTimeout3] ON SERVER ADD EVENT sqlserver.attention( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username) WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))) ADD TARGET package0.event_file(SET filename=N'D:\ExtendedEvents\ApplicationTimeout3.xel',max_file_size=(100),max_rollover_files=(5)) 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=OFF,STARTUP_STATE=ON) GO The problem is that in the timeouts that I get, I do NOT get the sql_text. (Nor do I get tsql_frame or tsql_stack). It's not returned when I do the "Watch Live Data". I do SEE the timeouts, they occur at the same time as the ones I see when I query against sys.query_store_runtime_stats (with execution_type_desc = 'Aborted'). However, I get a limited set of fields back, and they're not the ones I need. Also incidentally, there are many MORE events logged via the Extended Event than what I see when I query sys.query_store_runtime_stats. I have no idea what they are. Any clues as to how I could actually get the sql that's being executed? I can get the stored procedure name by querying the query store tables. What I'd like to get is the parameters that the stored procedure is called with.

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 event captures the NT account in both fields. When I test connect with a SQL Server Authentication account, only the sqlserver.username field captures the account, I can understand why the NT field does not capture non-NT accounts. It looks like sqlserver.username captures everything, while sqlserver.nt_username is more selective. Other then what I have mentioned are there any differences between these two? Is there any case when sqlserver.nt_username might capture something NOT captured by sqlserver.username? In this case I am using the event sqlserver.sql_batch_starting but my question is for any event. With SQL versions 2012 to 2017. **Note:** Both of these fields are actions, so grabbing both increases overhead. While it is probably not significant overhead, I am trying to stay as lite as possible.