Home » Microsoft » 70-762 » Which four Transact-SQL segments should you use to develop the solution?
DRAG DROP
You are tuning a database named MyDatabase.
You need to create an Extended Events session to capture execution plans for queries that run for at least 10 minutes. The following requirements must be met:
The target must write complete buffers to disk asynchronously.
The system must retain a maximum of 10 files
Each session must allocate no more than 10 megabytes (MB) of memory for event buffering.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation:
Step 1: CREATE EVENT SESSION SubOptimalExecPlans ON SERVER
Step 2: ADD EVENT sqlserver.query.query_pre_execution_showplan
Incorrect Answers:
Query_post_execution_showplan
Step 3: ADD TARGET package0.asynchronous_file_target .. max_rollower_files=10)
The target must write complete buffers to disk asynchronously.
The system must retain a maximum of 10 files
Step 4: WITH (MAX_MEMORY = 10 MB) ;
Each session must allocate no more than 10 megabytes (MB) of memory for event buffering.
Example:
CREATE EVENT SESSION test_session
ON SERVER
ADD EVENT sqlos.async_io_requested, ADD EVENT sqlserver.lock_acquired
ADD TARGET package0.etw_classic_sync_target
(SET default_etw_session_logfile_path = N’C:demotracessqletw.etl’ )
WITH (MAX_MEMORY=4MB, MAX_EVENT_SIZE=4MB);
References:
https://www.sqlservercentral.com/steps/stairway-to-sql-server-extended-events-level-4-extended-events-engine-essential-concepts
I would go for the post event because that allows you to make a better diagnosis. The remark about the performance overhead is true for both the pre and post event: this is mitigated by filtering on duration.
I agree. Query_pre_execution_showplan does not contain a duration field.
Correct answer is 2, 1, 3, 4.