August 12, 2019 at 3:46 am
The source code below is a part of source code to create an extended event in SQL Server 2008 R2,now there are 2 questions needing your help, thank you!
1. how to use SQL server script (or the other way) to know how many events in Extended event in SQL Server 2008 R2, like sqlserver.rpc_completed as below.
2. How to use SQL server script (or the other way) to know how many objects (like sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id ) can be monitored for each event in SQL server 2008 R2.
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlos.task_time,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([duration]>=(3000))),
August 12, 2019 at 10:19 am
one more question of Extended Event of SQL Server 2008, I used the following scripts to generate the captured files in SQL Server 2008, how can I read these files ? if I use "SELECT CAST(event_data AS XML) event_data, * FROM sys.fn_xe_file_target_read_file ('D:\log\filename.xet', NULL, NULL, NULL) " this script to read the generated log files, it doesn't work, how to encode the script of reading the generated log files? many many thanks!
---below is the scripts I used to monitor the activities, this scripts work fine.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='XWConcerns')
DROP EVENT SESSION XWConcerns ON SERVER
GO
CREATE EVENT SESSION [XWConcerns] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlos.task_time,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([duration]>=(3000))),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlos.task_time,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([duration]>=(300))) ,
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlos.task_time,sqlserver.database_id,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([duration]>=(300)))
ADD TARGET package0.asynchronous_file_target (SET filename=N'D:\log\',max_file_size=(20),max_rollover_files=(100))
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
August 12, 2019 at 11:23 am
I absolutely love Extended Events.
However, for SQL Server 2008R2, I don't recommend you use them. They were a 1.0 release then and the functionality just wasn't quite up to snuff. You'll be happier using trace events in 2008R2. Upgrade those servers and then you can move to all Extended Events for capturing information.
To answer your question how to read the files, yes, you can query them using the function. You can also use the Data Viewer in SQL Server Management Studio (it has to be the 2012 or greater version though, not 2008R2). That tool offers a bunch of ways to consume the data. Here's an example video.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2019 at 9:13 am
ok, thank you Grant Fritchey!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply