MCQOPTIONS
Saved Bookmarks
MCQOPTIONS
This section includes 64 Mcqs, each offering curated multiple-choice questions to sharpen your SQL Server knowledge and support exam preparation. Choose a topic below to get started.
| 1. |
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 |
| Answer» B. N’%SQLDIR%\MSSQL\DATA\black.trc | |
| 2. |
Which of the following query returns the average latency for the most recent sessions? |
| A. | SELECT latency FROM sys.dm_cdc_log_audit_sessions WHERE session_id = 0 |
| B. | SELECT latency FROM sys.dm_cdc_log_scan_ WHERE session_id = 0 |
| C. | SELECT latency FROM sys.dm_cdc_audit_scan_sessions WHERE session_id = 0 |
| D. | SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 |
| Answer» E. | |
| 3. |
Which of the following event has event number 15 in the trace? |
| A. | DTCTransaction |
| B. | ErrorLog |
| C. | SQL:BatchStarting |
| D. | Lock:Cancel |
| Answer» D. Lock:Cancel | |
| 4. |
Which of the following query determine sessions that had an empty scan? |
| A. | SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0 |
| B. | SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count = 0 |
| C. | SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count < 0 |
| D. | SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count > 0 |
| Answer» B. SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count = 0 | |
| 5. |
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 |
| Answer» D. sp_stop_status | |
| 6. |
Which of the following table is used in the management data warehouse schema that is required for the Server Activity? |
| A. | snapshots.query_stat |
| B. | snapshots.os_latch_stats |
| C. | snapshots.active_sessions |
| D. | all of the mentioned |
| Answer» C. snapshots.active_sessions | |
| 7. |
_____________ obtains tracking information for all changes to a table that have occurred since a specific version. |
| A. | CHANGETABLE_CONTEXT |
| B. | CHANGETABLE |
| C. | CHANGE_TRACKING_IS_COLUMN_IN_MASK |
| D. | All of the mentioned |
| Answer» C. CHANGE_TRACKING_IS_COLUMN_IN_MASK | |
| 8. |
Which of the following script enable a Table for all and Net Changes Queries? |
| A. | \USE MyDB |
| B. | \USE MyDB |
| C. | \USE MyDB |
| D. | All of the mentioned |
| Answer» B. \USE MyDB | |
| 9. |
Which of the following pair of views has one to many relationship? |
| A. | sys.server_event_session_actions.event_session_id, sys.sys.server_event_sessions.event |
| B. | sys.server_event_session_actions, sys.sys.server_event_sessions.event_session_id |
| C. | sys.server_event_session_actions.event_session_id,sys.sys.server_event_sessions.event _session_id |
| D. | none of the mentioned |
| Answer» D. none of the mentioned | |
| 10. |
Which of the following mode allows for the collection and uploading of data to occur on demand? |
| A. | Non-cached mode |
| B. | Cached mode |
| C. | Mixed mode |
| D. | All of the mentioned |
| Answer» B. Cached mode | |
| 11. |
Which of the following is syntax for sp_trace_setevent? |
| A. | sp_trace_setevent [ @traceid = ] trace_id |
| B. | sp_trace_setevent [ @traceid = ] trace_id OUTPUT |
| C. | sp_trace_setevent [ @traceid = ] trace_id , |
| D. | None of the mentioned |
| Answer» B. sp_trace_setevent [ @traceid = ] trace_id OUTPUT | |
| 12. |
Which of the following wait type is for internal use only? |
| A. | ASYNC_NETWORK_IO |
| B. | AUDIT_LOGINCACHE_LOCK |
| C. | HADR_DBR_SUBSCRIBER_FILTER_LIST |
| D. | AUDIT_XE_SESSION_MGR |
| Answer» D. AUDIT_XE_SESSION_MGR | |
| 13. |
Which of the following is a change tracking function? |
| A. | WITH CHANGE_TRACK_CONTEXT |
| B. | CHANGE_TRACKING_MAX_VALID_VERSION() |
| C. | CHANGE_TRACKING_CURRENT_VERSION |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 14. |
Which of the following determines type or resource to which the lock applies? |
| A. | sp_lock |
| B. | sp_who |
| C. | sp_monitor |
| D. | sp_spaceused |
| Answer» B. sp_who | |
| 15. |
Which of the following disable database for Change Data Capture? |
| A. | EXEC sys.sp_cdc_ensable_db |
| B. | EXEC sys.sp_disable_db |
| C. | EXEC sys.sp_cdc_disable_db |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 16. |
The Activity Monitor in SQL Server Management Studio graphically displays information about ______________ |
| A. | User activity |
| B. | Blocked processes |
| C. | Locks |
| D. | All of the mentioned |
| Answer» E. | |
| 17. |
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 |
| Answer» E. | |
| 18. |
Which of the following is a valid syntax for WITH CHANGE_TRACKING_CONTEXT? |
| A. | WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) |
| B. | WITH CHANGE_TRACKI_CONTEXT (@sync_client_id_binary) |
| C. | WITH CHANGE_TRACKING_CON (@sync_client_id_binary) |
| D. | All of the mentioned |
| Answer» B. WITH CHANGE_TRACKI_CONTEXT (@sync_client_id_binary) | |
| 19. |
Which of the following script enable a table Without Using a Gating Role? |
| A. | USE MyDB |
| B. | USE MyDB |
| C. | USE MyDB |
| D. | None of the mentioned |
| Answer» B. USE MyDB | |
| 20. |
The cleanup job is initiated by running the parameterless stored procedure is ___________ |
| A. | sp_MScdc_cleanup_job |
| B. | sp_cdc_cleanup_change_job |
| C. | sp_cdc_cleanup_change_tables |
| D. | none of the mentioned |
| Answer» B. sp_cdc_cleanup_change_job | |
| 21. |
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 |
| Answer» D. All of the mentioned | |
| 22. |
Which of the following script will enable CDC on HumanResources.Shift table? |
| A. | USE AdventureWorks |
| B. | USE AdventureWorks |
| C. | USE AdventureWorks |
| D. | All of the mentioned |
| Answer» B. USE AdventureWorks | |
| 23. |
Which of the following is an extended event task? |
| A. | create an Extended Events session |
| B. | alter an Extended Events session |
| C. | find out what events are available in the registered packages |
| D. | all of the mentioned |
| Answer» E. | |
| 24. |
How many types of wait occur in SQL Server? |
| A. | 2 |
| B. | 3 |
| C. | 4 |
| D. | 5 |
| Answer» C. 4 | |
| 25. |
Which of the following database level event is raised whenever a REFERENCES permission is checked. |
| A. | EXECUTE |
| B. | REFERENCES |
| C. | UPDATE |
| D. | SELECT |
| Answer» C. UPDATE | |
| 26. |
Which of the following code snippet returns a version integer that is associated with the last committed transaction that was tracked by change tracking? |
| A. | SELECT @sync_new_received_anchor = change_tracking_cur_version() |
| B. | SELECT @sync_new_received_anchor = change_tracking_current_ver() |
| C. | SELECT @sync_new_received_anchor = change_tracking_current_version() |
| D. | SELECT @sync_new_received_anchor = change_track_current_version() |
| Answer» D. SELECT @sync_new_received_anchor = change_track_current_version() | |
| 27. |
Which of the following option can be used to launch Perfmon? |
| A. | Type “start perfmon” at the command prompt |
| B. | Go to Start | Programs | Administrative Tools | Performance Monitor |
| C. | Go to Start | Run | Perfmon |
| D. | All of the mentioned |
| Answer» E. | |
| 28. |
Which of the following is an event class? |
| A. | SQL:BatchCompleted |
| B. | Audit Password |
| C. | Lock:Blocked |
| D. | All of the mentioned |
| Answer» B. Audit Password | |
| 29. |
The contents of sys.dm_os_wait_stats can be reset by running which of the following command? |
| A. | DBCC PERF (‘sys.dm_os_wait_stats’, CLEAR); |
| B. | DBCC SQLP (‘sys.dm_os_wait_stats’, CLEAR); |
| C. | DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 30. |
Which of the following field in the following code is qualified name? |
| A. | updateusage |
| B. | @objname |
| C. | @updateusage |
| D. | All of the mentioned |
| Answer» C. @updateusage | |
| 31. |
Which of the following query returns the average throughput for the most recent sessions? |
| A. | SELECT command_count*duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 |
| B. | SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 |
| C. | SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sesss WHERE session_id = 0 |
| D. | None of the mentioned |
| Answer» C. SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sesss WHERE session_id = 0 | |
| 32. |
Which of the following is one of the most crucial performance counters for monitoring? |
| A. | Disk Write Bytes/sec |
| B. | Memory Queue Length |
| C. | Virtual Memory |
| D. | All of the mentioned |
| Answer» B. Memory Queue Length | |
| 33. |
Which of the following stored procedure reports snapshot information about current SQL Server user? |
| A. | sp_lock |
| B. | sp_who |
| C. | sp_monitor |
| D. | sp_user |
| Answer» C. sp_monitor | |
| 34. |
Which of the following scenario favours cached mode? |
| A. | Continuous collection of data |
| B. | Less frequent uploads |
| C. | Data collection and uploading of jobs on different schedules |
| D. | All of the mentioned |
| Answer» E. | |
| 35. |
If you do not want to use a gating role, explicitly set the @role_name parameter to ______________ |
| A. | 0 |
| B. | |
| C. | 1 |
| D. | None of the mentioned |
| Answer» C. 1 | |
| 36. |
Which of the following is the biggest disadvantage of change tracking? |
| A. | Schema changes are required in the server database |
| B. | Triggers are fired for each change that has been made to a row |
| C. | Logic for maintaining the correct row versions and deletions is easy |
| D. | All of the mentioned |
| Answer» C. Logic for maintaining the correct row versions and deletions is easy | |
| 37. |
__________ gives DBAs an option to shut down the server in case of an audit log failure. |
| A. | Shut down server on partial failure |
| B. | Shut down server on audit log failure |
| C. | Shut down server on complete failure |
| D. | None of the mentioned |
| Answer» C. Shut down server on complete failure | |
| 38. |
Which of the following wait occurs when a task is waiting for I/Os to finish? |
| A. | ASYNC_NETWORK_IO |
| B. | AUDIT_LOGINCACHE_LOCK |
| C. | AUDIT_ON_DEMAND_TARGET_LOCK |
| D. | AUDIT_XE_SESSION_MGR |
| Answer» B. AUDIT_LOGINCACHE_LOCK | |
| 39. |
_________ returns a row for each customizable column that was explicitly set on events and targets. |
| A. | sys.server_event_session_targets |
| B. | sys.server_event_session_fields |
| C. | Returns a row for each event in an event session |
| D. | All of the mentioned |
| Answer» C. Returns a row for each event in an event session | |
| 40. |
When does BROKER_TO_FLUSH wait type event occur? |
| A. | When the Service Broker queue task handler tries to shut down the task |
| B. | When the Service Broker transmitter is waiting for work |
| C. | When the Service Broker lazy flusher flushes the in-memory transmission objects to a work table |
| D. | All of the mentioned |
| Answer» D. All of the mentioned | |
| 41. |
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 |
| Answer» E. | |
| 42. |
Which of the following query selects incremental inserts from the Sales.Customer table at the server? |
| A. | IF @sync_initialized == 0 |
| B. | IF @sync_initialized = 0 |
| C. | IF @sync_initialized =0 |
| D. | None of the mentioned |
| Answer» D. None of the mentioned | |
| 43. |
Which of the following is a catalog view used for change tracking? |
| A. | sys.change_tracking_tables |
| B. | sys.change_tracking_views |
| C. | sys.change_tracking_columns |
| D. | none of the mentioned |
| Answer» B. sys.change_tracking_views | |
| 44. |
_____________ wait type applies to SQL Server 2014 only. |
| A. | LCK_M_U_ABORT_BLOCKERS |
| B. | HADR_WORK_QUEUE |
| C. | HADR_XRF_STACK_ACCESS |
| D. | HADR_WORK_POOL |
| Answer» B. HADR_WORK_QUEUE | |
| 45. |
Which of the DDL statement creates a session object that contains the metadata? |
| A. | CREATE EVENT SESSION |
| B. | CREATE SESSION |
| C. | CREATE EVENT SESSION METADATA |
| D. | None of the mentioned |
| Answer» B. CREATE SESSION | |
| 46. |
AUDIT_CHANGE_GROUP event is raised whenever any audit is _____________ |
| A. | Created |
| B. | Modified |
| C. | Delete |
| D. | All of the mentioned |
| Answer» E. | |
| 47. |
Which of the following requires VIEW SERVER STATE permission on the server? |
| A. | sys.dm_os_states |
| B. | sys.dm_os_wait_stats |
| C. | sys.dm_os_wait_states |
| D. | none of the mentioned |
| Answer» C. sys.dm_os_wait_states | |
| 48. |
Which of the code snippet collect the data every 5 minutes? |
| A. | DECLARE @schedule_uid uniqueidentifier; |
| B. | DECLARE @schedule_uid uniqueidentifier; |
| C. | DECLARE @schedule_uid uniqueidentifier; |
| D. | All of the mentioned |
| Answer» B. DECLARE @schedule_uid uniqueidentifier; | |
| 49. |
SQL Server Extended Events Packages consist of _____________ |
| A. | Events |
| B. | Predicates |
| C. | Types |
| D. | All of the mentioned |
| Answer» E. | |
| 50. |
Which of the following error is returned for a query for all changes when a parameter that is used to define the query interval is not valid? |
| A. | An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_partial_changes_ |
| B. | An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ |
| C. | An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ |
| D. | None of the mentioned |
| Answer» C. An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ | |