May 9, 2019 at 2:40 am
I'm a newibe of SQL server, I'd like to ask your advice on SQL Server default Trace and Extended Events. and below are my questions, thanks everyone ! thanks for your time!
1. About default trace
Normally the default trace of SQL Server is enabled, and I know there are five files to save the records of default trace, when the file reaches the data size limit, it will save the trace record in the next file, and when the fifth file data size reaches the limit, it wil overwrite the first file. here are my conerns of default trace.
a. Which event or operation will be recorded in default trace? such as create SQL server login user?Create certain database access user?insert statement?delete statement?update statement?drop object?alter table?alter view?alter procedure and so on, can they be recorded in default trace?
b. if some of the said operation can't be recorded in default trace, could we change the setting or script to record the operation we want to record ?
c. Does default trace record select statement or the statement to execute a stored procedure ? for example, If I want to monitor the duration time of runing some SQL statement or stored procedure.
d. when we use SQL profiler to trace the operation,there is a checkbox to let us choose if to show all columns, now I want to know if we can let default trace record the column we need?
e. I know there are 5 files to record to trace log, if the last file reaches the data size limit, then overwrite the first file, due to the need of performance monitoring, I want to the file can't be overwritten, if the fifth file is full, then the log will be recorded in the sixth file, when the sixth file is full then recorded in the seventh file..., how to do the setting and let trace log can't be overwritten ?
f. after finish the monitoring, I want to change back the trace log file setting to let the trace log file can be overwritten when it reach the data size limit, how to change it back?
2. Extended Events
a. there is Package0.event_file and Package0.ring_buffer under Extended events/Sessions/system_health to record system default extended event record since 2012, I want to know
how long the records can be kept(saved) or it can be always kept(saved) as the record of common table?
b. which table is used for saving the records of extended events?
c. I know user can create own events, after user creates own events, the record of extended event is also recorded in the same table or same file with Package0.event_file and Package0.ring_buffer ?
May 9, 2019 at 9:33 am
This was removed by the editor as SPAM
May 9, 2019 at 9:34 am
hi
Answers:
1.A
this query will show all events default trace captures
SELECT c.name [Category]
, e.name [Event]
,tc.name [Column]
,tc.type_name [DataType]
,tc.max_size [MaxSize]
FROM fn_trace_geteventinfo(1) evi
JOIN sys.trace_events e ON evi.eventid = e.trace_event_id
join sys.trace_categories c on e.category_id=c.category_id
join sys.trace_columns tc on (evi.columnid=tc.trace_column_id)
order by 1,2,3
1.B
1.B
I suspect it is possible to change the default trace using appropriate system procedures, but I wouldn't recommend it and leave it as it is.
1.C
It doesn't save such events. The initial idea of default trace is to be lightweight and do not add much overhead to the workload.
1.D
check BOL for sp_trace_setevent proc
1.E
it's not possible for the default trace
1.F
disable/enable default trace and it will be recreated by SQL server from the scratch
2.A,B,C
Ring_buffer resides in memory and has limited size.
Event_file resides on a disk and also has limited size.
Both targets are not shareable and write-accessible only by their sessions.
May 9, 2019 at 11:55 am
If you're just getting going, now is a great time to completely ignore trace and trace events and concentrate only on Extended Events. Instead of default trace, look to the system_health Extended Event session. It does pretty much all that the default trace does and a whole bunch more. For example, question 1.c, no, default trace doesn't capture queries and query metrics. You have to do that on your own. However, system_health will capture queries that have resource waits longer than 30 seconds. So, there's one reason to favor learning that over default trace. Want another? Default trace doesn't capture deadlock graphs but system_health does. This also means that on a server where you have not yet enabled trace flag 1204 or 1222 (necessary to get deadlock graph info into the error log), you're already capturing deadlocks. In fact, you probably don't need to use the trace flags any more.
2.a, you define where the files go for file storage and how long they are kept based on size and how many rollover files you want to define. As was already stated, ring_buffer goes to memory. It's usually considered a recommended practice (not necessarily a best practice) to avoid the ring_buffer in extended events. It uses a lot more resources.
2.c, you can query dynamic management views like sys.dm_xe_packages and sys.dm_xe_objects to see the definitions of Extended Events on a server. Or, use the Management Studio gui (Azure Data Studio can view them, but it's a little wonky still in this area).
"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
May 9, 2019 at 1:02 pm
I agree with Grant regarding ExtendedEvents , however while default trace is available and running, it still has lots of usefull information for a DBA. Imho, it's a valueable source of information, until you have your own set of EE sessions or special monitoring software.
May 9, 2019 at 10:07 pm
I've found the default trace useful, and have had clients who want to keep more than 5 files. I've built a simple Agent job that runs on a scheduled basis and checks if the trace has rolled over. If a rollover is detected then it copies the relevant closed files to another folder.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 11, 2019 at 4:24 am
Thanks everyone for your patient reply and help! but I still have below questions, sorry for bothering you again, thanks!
1. Grammar of creating Extended event below is caputured from Micorsoft Websit showing the grammar in creating extended event, but there is event_package_name.event_name in the add event statmente, I learned some examples of creating event, it seems that everyone use sqlserver as the name of event_package_name, is sqlserver regarded as the default name event_package_name by Micorsoft or can we use the other name of event_package_name ?
2. Code of Sample of Creating Extended event below,
a. when we add an event, such as sqlserver.rpc_completed, does it mean which event we want to monitor using the extended envent ?
b. sqlserver.database_name?sqlserver.nt_username and sqlserver.username...., does it mean which columns we'd like to monitor just like choosing which column to show when we use sql server profiler to trace ?
3. We can create an extended event to trace for a specified stored procedure ? if we can, can you give me an example? thanks very much!
4. How can we know which file(s) to record the log of the system extended event Package0.event_file( Extended events/Sessions/system_health/Package0.event_file ) ?
5. can we change the size limit of system extended event Package0.event_file to store more data ?
Grammar of creating Extended event:
CREATE EVENT SESSION event_session_name
ON SERVER
{
<event_definition> [ ,...n]
[ <event_target_definition> [ ,...n] ]
[ WITH ( <event_session_options> [ ,...n] ) ]
}
;
<event_definition>::=
{
ADD EVENT [event_module_guid].event_package_name.event_name
[ ( {
[ SET { event_customizable_attribute = <value> [ ,...n] } ]
[ ACTION ( { [event_module_guid].event_package_name.action_name [ ,...n] } ) ]
[ WHERE <predicate_expression> ]
} ) ]
}
Code of Sample of Creating Extended event:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SessionWaitStats')
DROP EVENT session SessionWaitStats ON SERVER;
GO
CREATE EVENT SESSION SessionWaitStats ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.username,
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and [duration]>=3000000
),
ADD EVENT sqlserver.sql_batch_completed
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.username,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and [duration]>=3000000
),
ADD EVENT sqlos.wait_info
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.sql_text,
sqlserver.username,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and opcode=1
and duration>1
and sql_text not like '%sp_MScdc_capture_job%'
--and username = ''
),
ADD EVENT sqlos.wait_info_external
(
ACTION
(
sqlos.task_time,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.username,
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.sql_text,
sqlserver.session_id,
sqlserver.transaction_id
) WHERE session_id>50
and opcode=1
and duration>1
and sql_text not like '%sp_MScdc_capture_job%'
--and username = ''
)
ADD TARGET package0.event_file
(
SET filename=N'F:\XEvent Files\CollectionSessionWaitStats',
max_file_size=(1024),
max_rollover_files=(10)
)
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
-- Enable/Disable event(START / STOP)
ALTER EVENT SESSION CollectionSessionWaitStats ON SERVER STATE=START
GO
May 11, 2019 at 1:56 pm
Here are a bunch of examples from my blog of using Extended Events to answer various questions. This should provide you with a bunch of what you're asking. I'll try to give you quick answers to your answers:
b. Those are actions. They're added functions. Be cautious using them because they contribute overhead. You don't need to ever add an action. As I said above, the event itself defines the data collected. You don't have to add more.
"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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply