DRAG DROP
You have a database named MyDatabase. You must monitor all the execution plans in XML format by using
Microsoft SQL Trace. The trace must meet the following requirements:
– Capture execution plans only for queries that run the MyDatabase database.
– Filter out plans with event duration of less than or equal to 100 microseconds.
– Save trace results to a disk on the server.
You need to create the trace.
In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all
Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders
you select.
DECLARE @traceEventId int = 122;
DECLARE @traceColumnIdForTextData int = 1;
DECLARE @durationFilter bigint = 100
DECLARE @databaseID int;
SELECT @databaseId = DB_ID(‘MyDatabase’);
Select and Place:

Answer:

Explanation:
The following system stored procedures are used to define and manage traces:
* sp_trace_create is used to define a trace and specify an output file location as well asother options that I’ll
cover in the coming pages. This stored procedure returns a handle to the created trace, in the form of an
integer trace ID.
* sp_trace_setevent is used to add event/column combinations to traces based on the trace ID, as well as
toremove them, if necessary, from traces in which they have already been defined.
* sp_trace_setfilter is used to define event filters based on trace columns.
* sp_trace_setstatus is called to turn on a trace, to stop a trace, and to delete a trace definitiononce you’re done
with it. Traces can be started and stopped multiple times over their lifespan.
https://msdn.microsoft.com/en-us/library/cc293613.aspx