SQL Server Questions and Answers – Profiling

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Profiling”.

1. Which of the following is the benefit of SQL Server Profiler?
a) Capturing the series of Transact-SQL statements that lead to a problem
b) Finding and diagnosing slow-running queries
c) Correlating performance counters to diagnose problems
d) All of the mentioned
View Answer

2. Point out the correct statement.
a) SQL Server Profiler also supports auditing the actions performed on instances of SQL Server
b) A profiler class is a type of event that can be traced
c) A profile category defines the way events are grouped within SQL Server Profiler
d) None of the mentioned
View Answer

3. Which of the following is an event class?
a) SQL:BatchCompleted
b) Audit Password
c) Lock:Blocked
d) All of the mentioned
View Answer

4. Which of the following stored procedure will stop the trace?
a) sp_trace_create.
b) sp_trace_setfilter
c) sp_trace_setstatus
d) sp_stop_status
View Answer

5. Point out the wrong statement.
a) If a filter is set, all events of the selected event classes are returned in the trace output
b) A trace captures data based on selected event classes, data columns, and filters. For example, you can create a trace to monitor exception errors
c) A data column is an attribute of event classes captured in the trace
d) All of the mentioned
View Answer

6. Which of the following is syntax for sp_trace_setevent?
a)

sp_trace_setevent [ @traceid = ] trace_id  
          , [ @eventid = ] event_id 
          , [ @columnid = ] column_id 
          , [ @ON = ] ON

b)

sp_trace_setevent  [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value  
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]

c)

sp_trace_setevent [ @traceid = ] trace_id ,
   [ @STATUS = ] STATUS

d) None of the mentioned
View Answer

7. Which of the following argument has option value 2 in sp_trace_create procedure?
a) TRACE_PRODUCE_BLACKBOX
b) SHUTDOWN_ON_ERROR
c) TRACE_FILE_ROLLOVER
d) All of the mentioned
View Answer

8. Which of the following events and their columns are contained in the trace?
a) Attention
b) Batch starting
c) Exception
d) All of the mentioned
View Answer

9. TRACE_PRODUCE_BLACKBOX file can be saved in the following location?
a) N’%SQLDIR%\MSSQL\DATA\blackbox.trc
b) N’%SQLDIR%\MSSQL\DATA\black.trc
c) N’%SQLDIR%\MSSQL\DATA\box.trc
d) None of the mentioned
View Answer

10. Which of the following event has event number 15 in the trace?
a) DTCTransaction
b) ErrorLog
c) SQL:BatchStarting
d) Lock:Cancel
View Answer

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]